如果存储过程中存在其他问题。我尝试运行它，但每次"then"和"end"都出错

Question

我想我的else if不起作用

`elseif (oprtr = 1 and datetype = 0)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM CREATED) = EXTRACT(YEAR FROM created)
                                                                                AND EXTRACT(MONTH FROM CREATED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM CREATED) = EXTRACT(DAY FROM created);
elseif (oprtr = 2 and datetype = 0)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED > created;
elseif (oprtr = 0 and datetype = 1)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED < created;
elseif (oprtr = 1 and datetype = 1)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM LASTMODIFIED) = EXTRACT(YEAR FROM created)
                                                                                AND EXTRACT(MONTH FROM LASTMODIFIED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM LASTMODIFIED) = EXTRACT(DAY FROM created);
elseif (oprtr = 2 and datetype = 1)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED > created;
end if;

"

Answer 1

将ELSIF拼错为ELSEIF。

根据兴趣，这里可能是使用CASE的好地方:

case datetype
  when 0 then
    case oprtr
      when 0 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED < created;
      when 1 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM CREATED) = EXTRACT(YEAR FROM created)
                                                                                AND EXTRACT(MONTH FROM CREATED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM CREATED) = EXTRACT(DAY FROM created);
      when 2 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED > created;
      end case;
  when 1 then
    case oprtr
      when 0 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED < created;
      when 1 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM LASTMODIFIED) = EXTRACT(YEAR FROM created)
                                                                                AND EXTRACT(MONTH FROM LASTMODIFIED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM LASTMODIFIED) = EXTRACT(DAY FROM created);
      when 2 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED > created;
    end case;
end case;

但是，如果可能遇到datetype和oprtr的其他值，则需要将else null;添加到每个case。

Answer 2

尝试ELSIF代替ELSEIF