MySQL Create Procedure syntax issue-CodePudding

I'm getting a syntax error around the INSERT INTO that I cannot seem to shake out what is the issue between what fellow developers I've asked in addition to the MySQL docs. Any assistance on what might be wrong here would be greatly appreciated.

CREATE PROCEDURE teacher_insert(
        IN p_id int,
        IN p_dept int,
        IN p_name varchar(50),
        IN p_phone varchar(50),
        IN p_mobile varchar(50)
        )
        BEGIN
            IF EXISTS(SELECT * FROM teacher WHERE id = p_id) THEN
            BEGIN
                UPDATE teacher
                SET dept = p_dept,
                    name = p_name,
                    phone = p_phone,
                    mobile = p_mobile
                WHERE id = p_id
            END
            ELSE
            BEGIN
                INSERT INTO teacher (id,dept,name,phone,mobile)
                VALUES(p_id,p_dept,p_name,p_phone,p_mobile)
            END
        END

CodePudding user response：

You need ; after each statement in a procedure.
You don't put SET before the list of columns in an INSERT.

But you're doing lots of extra work, because you can use a single INSERT with ON DUPLICATE KEY instead of checking for the key with IF (assuming the id column is a unique key).

CREATE PROCEDURE teacher_insert(
    IN p_id int,
    IN p_dept int,
    IN p_name varchar(50),
    IN p_phone varchar(50),
    IN p_mobile varchar(50)
    )
INSERT INTO teacher (id,dept,name,phone,mobile)
VALUES (p_id,p_dept,p_name,p_phone,p_mobile)
ON DUPLICATE KEY UPDATE 
    dept = VALUES(dept),
    name = VALUES(name),
    phone = VALUES(phone),
    mobile = VALUES(mobile)
;