时间戳

首页 > 解决方案 > 如何编译包含 if & elsif 语句的程序？

sql - 如何编译包含 if & elsif 语句的程序？

问题描述

再会，

我正在尝试创建以下过程，该过程将根据工作类别更新表（annouce_form），另外我想知道是否可以在一个过程中包含多个更新命令：

    CREATE OR REPLACE PROCEDURE UPDATE_ANNOUCMENT_FORM (   
    p_annoucment_id IN annouce_form.ANNOUCMENT_ID%TYPE,  
    p_current_doc   IN annouce_form.CURRENT_DOC%TYPE,
    p_current_other IN annouce_form.current_other%TYPE,
    p_job_category  IN REGISTRATION_FORM.JOB_CATEGORY%TYPE
    )  
    IS  
    BEGIN  

 --first update
   IF p_job_category = 'Medical' THEN
   UPDATE annouce_form
   SET CURRENT_DOC = p_current_doc + 1 
   WHERE ANNOUCMENT_ID = p_annoucment_id;
   ELSIF p_job_category != 'Medical' THEN 
   SET CURRENT_OTHER = p_current_other + 1
   WHERE ANNOUCMENT_ID = p_annoucment_id;
   end if; 

 --second update
   UPDATE annouce_form
   SET CURRENT_OTHER = p_current_other + 1
   WHERE ANNOUCMENT_ID = p_annoucment_id;

   COMMIT;
   END;

在 SQL 开发人员工具中编译后收到以下错误：

    Procedure UPDATE_ANNOUCMENT_FORM compiled

    LINE/COL  ERROR
    --------- -------------------------------------------------------------
    19/1      PL/SQL: SQL Statement ignored
    19/5      PL/SQL: ORA-00922: missing or invalid option
    20/1      PLS-00103: Encountered the symbol "WHERE" when expecting one of the following:     
    ( begin case declare else elsif end exit for goto if loop mod    null pragma raise return 
    select update while with    <an identifier> <a double-quoted delimited-identifier>    <a 
    bind variable> << continue close current delete fetch lock    insert open rollback 
    savepoint set sql execute commit forall    merge pipe purge 
    Errors: check compiler log

标签： sqloracleplsql

解决方案

好吧，这是错误的：

--first update
IF p_job_category = 'Medical' THEN
   UPDATE annouce_form
   SET CURRENT_DOC = p_current_doc + 1 ;
ELSIF p_job_category != 'Medical' THEN 
   SET CURRENT_OTHER = p_current_other + 1;       --> this is 
   WHERE ANNOUCMENT_ID = p_annoucment_id;         --> wrong
end if;

我想应该是

IF p_job_category = 'Medical'
THEN
  UPDATE annouce_form
     SET CURRENT_DOC = p_current_doc + 1;
ELSIF p_job_category != 'Medical'
THEN
  UPDATE annouce_form                               --> probably like this
     SET CURRENT_OTHER = p_current_other + 1
   WHERE ANNOUCMENT_ID = p_annoucment_id;
END IF;

但你应该知道得更好。

推荐阅读