mysql - 一个返回true/false的sql函数，存在一些数据计算问题

Question

我需要在 sql 中创建一个函数，如果 "totalOrderSum" 返回 0 或 1低于 1000或更高。输入的参数是 @artNr , @orderNr和@amount 。问题是我必须找到 @artNr来自另一张表的价格并获取该价格 * @amount之后我必须得到总数 TotalAmount从另一张表中查看是否 (TotalAmount + (@price * @amount) < 1000) .

我下面有一些 sql 代码，我想你知道我想用这些代码做什么。
非常感谢。

CREATE FUNCTION Lab2_spAddOrderLine(@artNr INT,@orderNr INT,@amount) RETURNS BIT
WITH SCHEMABINDING
AS BEGIN
    DECLERE @result BIT, @price INT,@totalPrice
    IF(
         SELECT Price 
         FROM Lab2_Article 
         WHERE ArtNr=@artNr AS @price
         AND WHERE (
                    SELECT TotalAmount 
                    FROM Lab2_CostomOrder 
                    WHERE OrderNr = @orderNr AS @totalPrice 
                    AND WHERE ((@totalPrice +(@price * @amount)) < 1000
                   )
      )
         SET @result = 1
     ELSE
         SET @result = 0
     RETURN @result
 END

Answer 1

DELIMITER //
CREATE PROCEDURE lab2_addOrderLineProcedure
(
   IN articleNr INT,
   IN orderNr INT,
   IN amount INT,
   OUT result BIT
)
BEGIN
   DECLARE price INT;
   DECLARE totalPrice INT;

   SET price = (SELECT Price FROM Lab2_Article WHERE ArtNr = articleNr);
   SET totalPrice = ((SELECT TotalPrice FROM Lab2_CostomOrder WHERE orderNr = orderNr) + (price * amount));

   IF totalPrice < 1000 THEN
      INSERT INTO Lab2_OrderLine VALUES (articleNr, orderNr, amount);
      SET result = 1;
   ELSE
      SET result = 0;
   END IF;
END //
DELIMITER ;