mysql - 存储过程中出现意外的逗号

Question

我是存储过程中的新手，我创建了存储，但最后它说未定义逗号，如果条件我在代码中做错了什么，我的代码中有任何错误，或者请显示这里是我的代码

seeimage

DELIMITER //
CREATE PROCEDURE passport_information(IN passengerid VARCHAR(255),IN firstname VARCHAR(255),IN middlename VARCHAR(255),IN lastname VARCHAR(255),IN country VARCHAR(255),
    IN passportno VARCHAR(255),IN expirydate VARCHAR(255),IN createddate VARCHAR(255),IN createdby VARCHAR(255),IN modifiedby VARCHAR(255),IN operationtype VARCHAR(255))
BEGIN
set operationtype =  operationtype;

 IF(passengerid IS NULL) THEN
        SET passengerid := '';
    END IF;

 IF(firstname IS NULL) THEN
        SET firstname := '';
    END IF;
     IF(middlename IS NULL) THEN
        SET middlename := '';
    END IF;
     IF(lastname IS NULL) THEN
        SET lastname := '';
    END IF;
     IF(country IS NULL) THEN
        SET country := '';
    END IF;
     IF(passportno IS NULL) THEN
        SET passportno := '';
    END IF;
     IF(expirydate IS NULL) THEN
        SET expirydate := STR_TO_DATE('1-01-2012', '%d-%m-%Y');
    END IF;
     IF(createddate IS NULL) THEN
        SET createddate := CURDATE();
    END IF;
         IF(createdby IS NULL) THEN
        SET createdby := CURDATE();
    END IF;
     IF(modifiedby IS NULL) THEN
        SET modifiedby := '';
    END IF;

case 
when operationtype='selectall' then (select * from passport_information);
when operationtype='retrievewithid' then (select * from passport_information where PassengerID=passengerid);
when operationtype='delete' then (delete from passport_information where PassengerID=passengerid);
when operationtype='insert' then (INSERT passport_information 
    (PassengerID,Firstname,Middlename,Lastname,Country,Passportno,Expirydate,Createddate,Createdby,Modifiedby)
values (passengerid,firstname,middlename,lastname,country,passportno,expirydate,createddate,createdby,modifiedby));
when operationtype='update' then (UPDATE   passport_information SET PassengerID=passengerid,Firstname=firstname,
    Middlename=middlename,Lastname=lastname,Country=country,Passportno=passportno,Expirydate=expirydate,Createddate=createddate,Createdby=createdby,Modifiedby=modifiedby where PassengerID=passengerid);
end case;
END//
DELIMITER;

Answer 1

这里有很多错误，您似乎尚未决定是使用 case 还是 if..elseif，分隔符语句在语句和分隔符之间需要一个空格(请参阅 end//和分隔符；)。我的偏好 - 尽管可选，但在插入中包含 into 语句。试试这个而不是你的案例陈述

if       operationtype='selectall' then 
            select * from passport_information;
elseif operationtype='retrievewithid' then 
            select * from passport_information where PassengerID=passengerid;
elseif operationtype='delete' then 
            delete from passport_information where PassengerID=passengerid;
elseif operationtype='insert' then 
            INSERT  passport_information 
            (PassengerID,Firstname,Middlename,Lastname,Country,Passportno,Expirydate,Createddate,Createdby,Modifiedby)
            values (passengerid,firstname,middlename,lastname,country,passportno,expirydate,createddate,createdby,modifiedby);
elseif operationtype='update' then 
            UPDATE   passport_information SET PassengerID=passengerid,Firstname=firstname,
            Middlename=middlename,Lastname=lastname,Country=country,Passportno=passportno,Expirydate=expirydate,Createddate=createddate,Createdby=createdby,Modifiedby=modifiedby where PassengerID=passengerid;
end if;
END //

DELIMITER ;