问题描述
我正在尝试创建一个存储过程，该过程通过将订购数量&来自不同表的产品费率，称为费率"并从预订表"中获取数量.计算后，我希望将其插入到另一个金额"表中.我总共有5个产品.例如，如果product ='abc'，则将'abc'的金额放入，并为其余产品的金额插入零.在这里，我正在使用一个Case语句，该语句引发语法错误.请给我您的帮助.
I'm trying to create a stored procedure that calculates the amount by multiplying the ordered quantity & the rate of the product from different tables called Rate and getting Quantity from the Bookings Table.After calculating I want it to be inserted into another Table 'Amount'. I am having totally 5 products. If for example product = 'abc', then put amount for 'abc' and insert zero for the rest of the products' amount. Herein, I am using a Case Statement which throws syntax error. Kindly please offer me your help.
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 ;
推荐答案
在SQL中(通常)，CASE不用于程序条件流，它用于返回变量赋值中的奇异值. MySQL确实允许使用它，但我建议避免使用它，因为其他数据库系统不支持它
In SQL (in general), CASE is not used for program conditional flow, it is used to return singular values in variable assignment. MySQL does allow it but I recommend to avoid using it as other database systems don't support it
典型场景:
--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 you're after keeping your database skills cross portable, essentially the only thing you should put inside the "result" of a case is a value, not a statement
IF ELSEIF ELSE用于主要数据库中的条件程序流，有关它的MySQL文档在这里:
IF ELSEIF ELSE is used for conditional program flow in major databases, the MySQL docs for it are here:
https://dev.mysql.com/doc/refman/8.0/zh-CN/if.html
另外，对于字符串，更喜欢使用'而不是'-双引号是MySQL与标准的另一种偏离
As another aside, prefer use of ' for strings, not " - double quotes are another MySQL deviation from standard
这篇关于MySQL存储过程案例语句语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！