mysql - MySQL 存储过程案例语句语法错误 - 续 2
问题描述
以下代码中有一个语法错误。mysql的响应是
错误 1064:您的 SQL 语法有错误;检查与您的 MySQL 服务器版本相对应的手册,以获取在 ''300ml','500ml','1lit','2lit') 附近使用的正确语法 VALUES(m_cust_id,IN_book_id,(SELECT Rate.Can*Book' at line 24条SQL语句
以下是程序。
CREATE PROCEDURE `calculate_amount` (in IN_book_id INT, in IN_qty INT )
BEGIN
-- declare
DECLARE m_prdct VARCHAR(10);
DECLARE m_cust_id INT(5);
-- select into
SELECT
Product
FROM
Bookings
WHERE
Book_id = IN_book_id INTO m_prdct;
SELECT
Cust_id
FROM
Bookings
WHERE
Book_id = IN_book_id INTO m_cust_id;
-- conditionals & action
IF (m_prdct = '20ltr') THEN
INSERT INTO Amount (Cust_id,Book_id,Can,'300ml','500ml','1lit','2lit') VALUES(m_cust_id,IN_book_id,(SELECT Rate.Can*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0,0,0,0);
ELSEIF (m_prdct = '300ml') THEN
INSERT INTO Amount(Cust_id,Book_id,Can,'300ml','500ml','1lit','2lit') VALUES(m_cust_id,IN_book_id,0,(SELECT Rate.300ml*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0,0,0);
ELSEIF (m_prdct = '500ml') THEN
INSERT INTO Amount(Cust_id,Book_id,Can,'300ml','500ml','1lit','2lit') VALUES(m_cust_id,IN_book_id,0,0,(SELECT Rate.500ml*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0,0);
ELSEIF (m_prdct = '1ltr') THEN
INSERT INTO Amount(Cust_id,Book_id,Can,'300ml','500ml','1lit','2lit') VALUES(m_cust_id,IN_book_id,0,0,0,(SELECT Rate.1lit*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id),0);
ELSE
INSERT INTO Amount(Cust_id,Book_id,Can,'300ml','500ml','1lit','2lit') VALUES(m_cust_id,IN_book_id,0,0,0,0,(SELECT Rate.2lit*Bookings.Qty FROM Rate,Bookings WHERE Bookings.Book_id=IN_book_id));
-- end
END IF;
END
解决方案
推荐阅读
- java - 如何获取模型中所有被覆盖的操作/属性?
- firebase - 如何在firestore中访问请求用户的数据
- java - Java:错误:不支持发布版本 13
- php - 将变量(id)从一个函数传递到另一个函数
- ionic-framework - 如何修复 http.post() 请求在 android oreo 版本上不起作用
- python - 如何修复“InvalidArgumentError”:占位符问题
- php - 此路由不支持 GET 方法。支持的方法:POST
- arrays - K 最近的元素
- python - 从另一个未排序的 numpy 数组中的数据中查找未排序的 numpy 数组中值的索引位置
- c# - ProgressBar OutOfRange.Exception