mysql - 我正在尝试在 mysql phpmyadmin 中创建一个存储过程

Question

我正在尝试获取一个字段，如果它不存在，我想将它插入到表中，但出现错误。对于我尝试的第一个查询

CREATE PROCEDURE reg_val( IN pid VARCHAR(30), IN pyd VARCHAR(50), IN dat VARCHAR(20), OUT value VARCHAR(50)  )
    BEGIN TRANSACTION
    DECLARE @id AS VARCHAR(50)
    SELECT @id = paymentid FROM table WHERE phoneid=pid
    IF @id IS NULL
    BEGIN
       INSERT INTO user_info (phoneid, paymentid, created_at) VALUES (pid, pyd, dat) 
       SELECT @id = SCOPE_IDENTITY()
    END
    SELECT @id
COMMIT TRANSACTION

我收到这个错误

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 'DECLARE @id AS VARCHAR(50) SELECT @id = paymentid FROM table WHERE phoneid=' at line 3

所以我无法解决这个问题，我尝试了这个新查询

CREATE PROCEDURE reg_val( IN pid VARCHAR(30), IN pyd VARCHAR(50), IN dat VARCHAR(20), OUT value VARCHAR(50)  )
    BEGIN
        IF EXISTS (SELECT phoneid FROM user_info WHERE phoneide=pid) THEN
           SELECT paymentid FROM user_info WHERE phoneid=pid
        ELSE
           INSERT INTO user_info (phoneid, paymentid, created_at) VALUES (pid, pyd, dat) 
           SELECT paymentid FROM user_info WHERE phoneid=SCOPE_IDENTITY()
        END IF

但是我也遇到了这个错误

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 'ELSE THEN INSERT INTO user_info (phoneid, paymentid, created_at) VAL' at line 5

也许我对形成复杂查询的理解不正确，但我们将不胜感激。

Answer 1

请如下更改存储过程(检查注释中的更改):

DELIMITER $$   // change delimiter
CREATE PROCEDURE reg_val( IN pid VARCHAR(30), IN pyd VARCHAR(50), IN dat VARCHAR(20), OUT VALUE VARCHAR(50)  )
    BEGIN
        IF EXISTS (SELECT phoneid FROM user_info WHERE phoneide=pid) THEN
           SELECT paymentid FROM user_info WHERE phoneid=pid; // use semicolon
        ELSE 
           INSERT INTO user_info (phoneid, paymentid, created_at) VALUES (pid, pyd, dat);// use semicolon
           SELECT paymentid FROM user_info WHERE phoneid=SCOPE_IDENTITY();// use semicolon
        END IF;// use semicolon
        END;// use end and semicolon