mysql - 存储过程在mysql phpmyadmin＃1064中

我试图在phpmyadmin中的Mysql中编写存储过程，但遇到错误：

DELIMITER //
DROP PROCEDURE IF EXISTS USP_SwitchTable //
CREATE PROCEDURE USP_SwitchTable(IN idTable1 INT, IN idTable2 INT)
AS BEGIN
    DECLARE idFirstBill INT;
    DECLARE idSecondBill INT;
    SELECT idFirstBill = id FROM bill WHERE id_table = idTable1 AND status = 0;
    SELECT idSecondBill = id FROM bill WHERE id_table = idTable2 AND status = 0;

    IF (idFirstBill IS NULL)
    BEGIN
    INSERT bill (id_table, bill_maker, status) VALLUES (idTable1, 1, 0);
    SELECT idFirstBill = MAX(id) FROM bill WHERE id_table = idTable1 AND status = 0;
    END


    IF (idSecondBill  IS NULL)
    BEGIN
    INSERT bill (id_table, bill_maker, status) VALLUES (idTable2, 1, 0);
    SELECT idSecondBill = MAX(id) FROM bill WHERE id_table = idTable2 AND status = 0;
    END

    SELECT id INTO IDBillInfoTable FROM bill_info WHERE id_bill = idSecondBill;

    UPDATE bill_info SET id_bill = idSecondBill WHERE id_bill = idFirstBill;

    UPDATE bill_info SET id_bill = idFirstBill WHERE id IN (SELECT * FROM IDBillInfoTable);

    DROP TABLE IDBillInfoTable;

END//
DELIMITER ;

这就是它的意思：


  错误＃1064-您的SQL语法有错误；检查与您的MariaDB服务器版本相对应的手册以获取正确的语法，以在“ BEGIN INSERT帐单（id_table，bill_maker，状态）VALLUES（idTable1、1、0）附近使用”；在第9行


我该怎么办才能解决问题？我对此有点陌生，谢谢大家

最佳答案

MySQL IF子句使用THEN END IF代替BEGIN和END。

您可能还想对IDBillInfoTable使用临时表，以便它与并发使用。

IF (idSecondBill  IS NULL) THEN
  INSERT INTO bill (id_table, bill_maker, status)
  VALUES (idTable2, 1, 0);

  SELECT MAX(id) into idSecondBill
  FROM bill
  WHERE id_table = idTable2 AND status = 0;
END IF;

和临时表创建：

CREATE TEMPORARY TABLE IDBillInfoTable AS
SELECT id
FROM bill_info
WHERE id_bill = idSecondBill;

并删除临时表

DROP TEMPORARY TABLE IDBillInfoTable;

关于mysql - 存储过程在mysql phpmyadmin＃1064中，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58631231/