在存储过程中调试mySQL语法错误

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

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 an ELSIF:

用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

84     END IF;
85 END //
86
87 DELIMITER ;