I am redirecting this file into mysql promt to add a stored procedure:
我正在将这个文件重定向到mysql promt,以添加一个存储过程:
DROP PROCEDURE IF EXISTS make_transaction;
DELIMITER //
CREATE PROCEDURE make_transaction(IN v_quote_id INT, IN v_your_id INT)
BEGIN
DECLARE v_is_seller BOOLEAN;
DECLARE v_option_type BOOLEAN;
DECLARE v_trader_id INT;
DECLARE v_premium DOUBLE(18, 4);
DECLARE v_offer_expires DATETIME;
DECLARE v_instrument_id INT;
DECLARE v_pretend_now DATETIME;
DECLARE v_buyer_id INT;
DECLARE v_seller_id INT;
DECLARE v_buyer_total_balance DOUBLE(18, 4);
SELECT
instrument_id,
trader_type,
option_type,
trader_id,
premium,
offer_expires
INTO
v_instrument_id,
v_is_seller,
v_option_type,
v_trader_id,
v_premium,
v_offer_expires
FROM
option_quotes
WHERE
quote_id = v_quote_id;
IF v_is_seller THEN
SET v_seller_id = v_trader_id;
SET v_buyer_id = v_your_id;
ELSE
SET v_buyer_id = v_trader_id;
SET v_seller_id = v_your_id;
END IF;
-- Last STOCK_TRADE time is assumed to be the current time
SELECT DATE_TIME
INTO v_pretend_now
FROM STOCK_TRADE
WHERE INSTRUMENT_ID=v_instrument_id
ORDER BY DATE_TIME DESC
LIMIT 1;
SELECT total_balance
INTO v_buyer_total_balance
FROM traders
WHERE trader_id=v_buyer_id;
IF offer_expires <= v_pretend_now THEN
SELECT 'That offer has expired';
ELSE IF v_buyer_total_balance < v_premium THEN
SELECT 'You do not have enough money to transact on this offering';
ELSE
INSERT INTO option_transactions
(
transaction_time,
quote_id,
buyer_id,
seller_id,
buyer_gain,
seller_gain
)
VALUES
(
v_pretend_now,
v_quote_id,
v_buyer_id,
v_seller_id,
NULL, -- line 85
NULL
);
END IF;
END //
DELIMITER ;
The error I am receiving trying to enter the stored procedure into db:
我试图将存储过程输入db的错误:
ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 85
第5行错误1064 (42000):SQL语法中有错误;检查与MySQL服务器版本相对应的手册,找到在第85行“附近”使用的正确语法
The option_transactions table looks like this:
option_transactions表如下所示:
+------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| quote_id | int(11) | NO | MUL | NULL | |
| buyer_id | int(11) | NO | MUL | NULL | |
| seller_id | int(11) | NO | MUL | NULL | |
| transaction_time | datetime | NO | | NULL | |
| buyer_gain | double(18,4) | YES | | NULL | |
| seller_gain | double(18,4) | YES | | NULL | |
+------------------+--------------+------+-----+---------+-------+
I am new at mySQL, but the insert into command near line 85 appears correct in syntax to me, I am not sure where the problem is. How may I fix this?
我是mySQL新手,但是在第85行附近的insert into命令在语法上是正确的,我不确定问题出在哪里。我该怎么解决这个问题呢?
(mysql server version 5.5.42)
(mysql服务器版本5.5.42)
1 个解决方案
#1
2
Line 85 is not in the middle of your INSERT statement, it's at the end of the CREATE PROCEDURE statement. If I paste your code into vim and :se nu
to show line numbers, and then I delete the initial lines before CREATE PROCEDURE, the last line is line 85:
第85行不在INSERT语句的中间,它位于CREATE PROCEDURE语句的末尾。如果我将你的代码粘贴到vim和:se nu来显示行号,然后在创建过程之前删除初始行,最后一行是第85行:
84 END IF;
85 END //
86
87 DELIMITER ;
So the line numbers in the error start counting from the first line of the statement, not the first line of the SQL script file.
因此,错误中的行号从语句的第一行开始计数,而不是SQL脚本文件的第一行。
Then I went looking and found an imbalanced blocks:
然后我去找,发现一个不平衡的方块:
IF offer_expires <= v_pretend_now THEN
ELSE IF v_buyer_total_balance < v_premium THEN
ELSE
END IF;
You used a nested IF/THEN/ELSE/END IF
inside an ELSE
. So you actually have two IF
statements, but only one END IF
.
您使用嵌套的IF/THEN/ELSE/END IF在ELSE中。实际上有两个IF语句,但只有一个IF。
To fix this, you have two options:
要解决这个问题,你有两个选择:
-
Make it one
IF
statement with anELSIF
:用ELSIF写一个IF语句:
IF offer_expires <= v_pretend_now THEN ELSEIF v_buyer_total_balance < v_premium THEN ELSE END IF;
See https://dev.mysql.com/doc/refman/5.7/en/if.html for docs on the syntax of
IF/THEN/ELSIF/ELSE/END IF
.关于IF/THEN/ELSIF/ELSE/END IF的语法,请参阅https://dev.mysql.com/doc/refman/5.7/en/if.html。
-
Finish both
IF
statements:完成这两个IF语句:
IF offer_expires <= v_pretend_now THEN ELSE IF v_buyer_total_balance < v_premium THEN ELSE END IF; END IF;
#1
2
Line 85 is not in the middle of your INSERT statement, it's at the end of the CREATE PROCEDURE statement. If I paste your code into vim and :se nu
to show line numbers, and then I delete the initial lines before CREATE PROCEDURE, the last line is line 85:
第85行不在INSERT语句的中间,它位于CREATE PROCEDURE语句的末尾。如果我将你的代码粘贴到vim和:se nu来显示行号,然后在创建过程之前删除初始行,最后一行是第85行:
84 END IF;
85 END //
86
87 DELIMITER ;
So the line numbers in the error start counting from the first line of the statement, not the first line of the SQL script file.
因此,错误中的行号从语句的第一行开始计数,而不是SQL脚本文件的第一行。
Then I went looking and found an imbalanced blocks:
然后我去找,发现一个不平衡的方块:
IF offer_expires <= v_pretend_now THEN
ELSE IF v_buyer_total_balance < v_premium THEN
ELSE
END IF;
You used a nested IF/THEN/ELSE/END IF
inside an ELSE
. So you actually have two IF
statements, but only one END IF
.
您使用嵌套的IF/THEN/ELSE/END IF在ELSE中。实际上有两个IF语句,但只有一个IF。
To fix this, you have two options:
要解决这个问题,你有两个选择:
-
Make it one
IF
statement with anELSIF
:用ELSIF写一个IF语句:
IF offer_expires <= v_pretend_now THEN ELSEIF v_buyer_total_balance < v_premium THEN ELSE END IF;
See https://dev.mysql.com/doc/refman/5.7/en/if.html for docs on the syntax of
IF/THEN/ELSIF/ELSE/END IF
.关于IF/THEN/ELSIF/ELSE/END IF的语法,请参阅https://dev.mysql.com/doc/refman/5.7/en/if.html。
-
Finish both
IF
statements:完成这两个IF语句:
IF offer_expires <= v_pretend_now THEN ELSE IF v_buyer_total_balance < v_premium THEN ELSE END IF; END IF;