MySQL存储过程Case语句语法错误

Question

我正在尝试创建一个存储过程，通过将订购数量和来自不同表(称为费率)的产品费率相乘并从预订表中获取数量来计算金额。计算后，我希望将其插入到另一个表中表“金额”。我总共有 5 个产品。例如，如果产品=“abc”，则为“abc”输入金额，并为其余产品的金额插入零。在这里，我使用 Case 语句，它会引发语法错误。请为我提供帮助。

DELIMITER $$
CREATE PROCEDURE `calculate_amount` (IN IN_book_id INT,IN IN_qty INT )
-- begin
BEGIN
-- declare
DECLARE prdct VARCHAR(10);
DECLARE cust_id INT(5);
-- select into
SELECT Product FROM Bookings WHERE Book_id=IN_book_id INTO prdct;
SELECT Cust_id FROM Bookings WHERE Book_id=IN_book_id INTO cust_id;
-- conditionals & action
CASE prdct
WHEN "20ltr"
THEN INSERT INTO Amount(Cust_id,Book_id,Can,300ml,500ml,1lit,2lit) VALUES(cust_id,IN_book_id,(SELECT Rate.Can*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0,0,0,0);

WHEN "300ml"
THEN INSERT INTO Amount(Cust_id,Book_id,Can,300ml,500ml,1lit,2lit) VALUES(cust_id,IN_book_id,0,(SELECT Rate.300ml*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0,0,0); 

WHEN "500ml"
THEN 
INSERT INTO Amount(Cust_id,Book_id,Can,300ml,500ml,1lit,2lit) VALUES(cust_id,IN_book_id,0,0,(SELECT Rate.500ml*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0,0); 

WHEN "1ltr"
THEN 
INSERT INTO Amount(Cust_id,Book_id,Can,300ml,500ml,1lit,2lit) VALUES(cust_id,IN_book_id,0,0,0,(SELECT Rate.1lit*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0); 

WHEN "2ltr"
THEN 
INSERT INTO Amount(Cust_id,Book_id,Can,300ml,500ml,1lit,2lit) VALUES(cust_id,IN_book_id,0,0,0,0,(SELECT Rate.2lit*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id)); 

ELSE 
    BEGIN
    END;
END CASE;
-- end
END;$$

DELIMITER ;

Answer 1

在SQL(一般情况下)中，CASE不用于程序条件流，它用于在变量赋值中返回奇异值。 MySQL 确实允许，但我建议避免使用它，因为其他数据库系统不支持它

典型场景:

--doing varied logic:
name = CASE 
    WHEN num = 1 THEN 'one' 
    WHEN othernum = 2 THEN 'two' 
END

--testing a single variable for being equal to values:
name = CASE num 
  WHEN 1 THEN 'one' 
  WHEN 2 THEN 'two' 
END

避免这种情况:

CASE 
  WHEN num = 1 THEN name = 'one' 
  WHEN num = 2 THEN name = 'two' 
END

如果您希望保持数据库技能的交叉移植性，那么本质上您应该在案例的“结果”中放入的唯一内容是一个值，而不是一个语句

IF ELSEIF ELSE 用于主要数据库中的条件程序流，它的 MySQL 文档在这里:

https://dev.mysql.com/doc/refman/8.0/en/if.html

另外，更喜欢使用 ' 表示字符串，而不是 "- 双引号是 MySQL 与标准的另一个偏差