mysql - IF Else 在存储过程中不起作用

Question

CREATE PROCEDURE p1 
(
    IN name_val VARCHAR(255),
    OUT iJobID  INT
)


BEGIN

    IF NOT EXISTS (SELECT id FROM test WHERE id='11')

        BEGIN

            INSERT INTO test(name) VALUES(name_val);
            SET iJobID :=  LAST_INSERT_ID();
        END

    ELSE
        BEGIN

             UPDATE test SET name=name_val WHERE id = 11; 
        END 

    INSERT INTO vasu2(vname) VALUES(name_val);
    SET @ivD :=  LAST_INSERT_ID();

    INSERT INTO vasu(id, id2) VALUES(iJobID, @ivD); 
END;

Answer 1

您正在使用的 IF 语法很可能用于 T-SQL。此外，您需要更改 delimiter。

DELIMITER $$
CREATE PROCEDURE p1 
(
    IN name_val VARCHAR(255),
    OUT iJobID  INT
)
BEGIN

    SET @recCount := (SELECT COUNT(*) FROM test WHERE id = 11);
    IF @recCount > 0 THEN
        INSERT INTO test(name) VALUES(name_val);
        SET iJobID :=  LAST_INSERT_ID();
    ELSE
        UPDATE test SET name=name_val WHERE id = 11; 
    END IF;

    INSERT INTO vasu2(vname) VALUES(name_val);
    SET @ivD :=  LAST_INSERT_ID();

    INSERT INTO vasu(id, id2) VALUES(iJobID, @ivD); 
END $$
DELIMITER ;

• Using Stored Procedures