Oracle存储过程报错PLS-00103：遇到符号THEN的语法问题

问题描述 #

编写的存储过程执行时触发如下错误：

Error(6375,30): PLS-00103: Encountered the symbol "THEN" when expecting one of the following: ( - + case mod new not null

存储过程代码如下：

PROCEDURE INS_UPD_WF_BY_CMM_FTTX
(
  PJOB_PROGRESS_ID IN TBL_FIBER_INV_JOB_PROGRESS.JOB_PROGRESS_ID%TYPE,  
  PHOTO_ACCEPTENCE_DATE IN NVARCHAR2,
  PLIT_ACCEPTENCE_DATE IN NVARCHAR2,
  PAPPROVED_BY IN TBL_FIBER_INV_JOB_PROGRESS.APPROVED_BY%TYPE,  
  PAPPROV_REJECT_REMARK IN TBL_FIBER_INV_JOB_PROGRESS.APPROV_REJECT_REMARK%TYPE,
  PMODIFIED_BY IN TBL_FIBER_INV_JOB_PROGRESS.MODIFIED_BY%TYPE,
  PUMS_GROUP_ASS_BY_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_ID%TYPE,
  PUMS_GROUP_ASS_BY_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_BY_NAME%TYPE,
  PUMS_GROUP_ASS_TO_ID IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_ID%TYPE,
  PUMS_GROUP_ASS_TO_NAME IN TBL_FIBER_INV_JOB_PROGRESS.UMS_GROUP_ASS_TO_NAME%TYPE,
  PSTATUS_ID IN TBL_FIBER_INV_JOB_PROGRESS.STATUS_ID%TYPE,
  PHOTO_ACTUAL_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACTUAL_LENGTH%TYPE,
  PLIT_ACTUAL_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.LIT_ACTUAL_LENGTH%TYPE,
  POUTMSG OUT NVARCHAR2
)
AS
VCNT NUMBER:=0;
VCMMROUTE NUMBER:=0;
VCMMLIT NUMBER:=0;
VLENGTH_CHNG_CNT NUMBER:=0;
VJOB_ID NUMBER;
VSPAN_TYPE NVARCHAR2(20):='';
VSPAN_ID NVARCHAR2(50):='';
VLINK_ID NVARCHAR2(50):='';
VMAINTZONECODE VARCHAR2(50):='';
VNELEG NUMBER;
VNELEG1 NUMBER;                               
VNELEG2 NUMBER;                               
JOB_NE_LENGTH NUMBER;
BEGIN
        SELECT COUNT(*) INTO VLENGTH_CHNG_CNT
          FROM TBL_FIBER_INV_JOB_PROGRESS
          WHERE JOB_PROGRESS_ID = PJOB_PROGRESS_ID
          AND HOTO_ACTUAL_LENGTH = PHOTO_ACTUAL_LENGTH
          AND LIT_ACTUAL_LENGTH = PLIT_ACTUAL_LENGTH;
        SELECT DISTINCT JOB_ID INTO VJOB_ID
          FROM TBL_FIBER_INV_JOB_PROGRESS
          WHERE JOB_PROGRESS_ID = PJOB_PROGRESS_ID;
          
BEGIN
        IF PSTATUS_ID = 4 OR THEN
        
BEGIN
        BEGIN
              UPDATE TBL_FIBER_INV_JOB_PROGRESS
                SET HOTO_ACCEPTENCE_DATE = TO_DATE(PHOTO_ACCEPTENCE_DATE,'DD/MM/YYYY'),
                    LIT_ACCEPTENCE_DATE = TO_DATE(PLIT_ACCEPTENCE_DATE,'DD/MM/YYYY'),
                    APPROVED_BY = PAPPROVED_BY,
                    APPROVED_DATE = CASE WHEN PAPPROVED_BY IS NULL THEN NULL ELSE SYSDATE END,
                    REJECTED_BY = NULL,
                    REJECTED_DATE = NULL,
                    APPROV_REJECT_REMARK = PAPPROV_REJECT_REMARK,
                    MODIFIED_BY = PMODIFIED_BY,
                    MODIFIED_DATE = SYSDATE,
                    UMS_GROUP_ASS_BY_ID = PUMS_GROUP_ASS_BY_ID,
                    UMS_GROUP_ASS_BY_NAME = PUMS_GROUP_ASS_BY_NAME,
                    UMS_GROUP_ASS_TO_ID = NULL,
                    UMS_GROUP_ASS_TO_NAME = NULL,
                    UMS_GROUP_ASS_TO_DATE = NULL,
                    JOB_PROGRESS_FLAG = 1,
                    STATUS_ID = PSTATUS_ID,
                    HOTO_ACTUAL_LENGTH = PHOTO_ACTUAL_LENGTH,
                    LIT_ACTUAL_LENGTH = PLIT_ACTUAL_LENGTH,
                    HOTO_OFFER_LEN_UPD_BY = PUMS_GROUP_ASS_BY_NAME
                WHERE JOB_PROGRESS_ID   = PJOB_PROGRESS_ID;               
              UPDATE TBL_FIBER_INV_JOBS SET JOB_FLAG = 1 WHERE JOB_ID = VJOB_ID;     
              
            POUTMSG :='SUCCESS|HOTO process completed and Request closed';
        END;
        ELSE
BEGIN
                UPDATE TBL_FIBER_INV_JOB_PROGRESS
                SET HOTO_ACCEPTENCE_DATE = TO_DATE(PHOTO_ACCEPTENCE_DATE,'DD/MM/YYYY'),
                    LIT_ACCEPTENCE_DATE = TO_DATE(PLIT_ACCEPTENCE_DATE,'DD/MM/YYYY'),
                    APPROVED_BY = PAPPROVED_BY,
                    APPROVED_DATE = CASE WHEN PAPPROVED_BY IS NULL THEN NULL ELSE SYSDATE END,
                    REJECTED_BY = NULL,
                    REJECTED_DATE = NULL,
                    APPROV_REJECT_REMARK = PAPPROV_REJECT_REMARK,
                    MODIFIED_BY = PMODIFIED_BY,
                    MODIFIED_DATE = SYSDATE,
                    UMS_GROUP_ASS_BY_ID = PUMS_GROUP_ASS_BY_ID,
                    UMS_GROUP_ASS_BY_NAME = PUMS_GROUP_ASS_BY_NAME,
                    UMS_GROUP_ASS_TO_ID = PUMS_GROUP_ASS_TO_ID,
                    UMS_GROUP_ASS_TO_NAME = PUMS_GROUP_ASS_TO_NAME,
                    UMS_GROUP_ASS_TO_DATE = SYSDATE,
                    JOB_PROGRESS_FLAG = 1,
                    STATUS_ID = PSTATUS_ID,
                    HOTO_ACTUAL_LENGTH = PHOTO_ACTUAL_LENGTH,
                    LIT_ACTUAL_LENGTH = PLIT_ACTUAL_LENGTH,
                    HOTO_OFFER_LEN_UPD_BY = PUMS_GROUP_ASS_BY_NAME
                WHERE JOB_PROGRESS_ID = PJOB_PROGRESS_ID;
SELECT JB.SPAN_ID,JB.LINK_ID,JB.MAINTENANCEZONECODE,JB.MAINT_ZONE_NE_SPAN_LENGTH,JB.SPAN_TYPE INTO VSPAN_ID,VLINK_ID,VMAINTZONECODE,JOB_NE_LENGTH,VSPAN_TYPE
          FROM TBL_FIBER_INV_JOBS JB WHERE JB.JOB_ID= VJOB_ID;
          
        IF 1 = 1 THEN
        BEGIN
             UPDATE TBL_FIBER_INV_JOBS SET MAINT_ZONE_NE_SPAN_LENGTH = VNELEG WHERE JOB_ID = VJOB_ID;    
             UPDATE TBL_FIBER_INV_JOB_PROGRESS SET NE_SPAN_LENGTH = VNELEG WHERE JOB_ID = VJOB_ID;
        END;
         
       POUTMSG :='SUCCESS|HOTO process partially completed';     
        END IF;                
END;
 
  
            
    EXCEPTION  
    WHEN OTHERS THEN
      ERROR_CODE := SQLCODE;
      ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 200);
      ROLLBACK;
      POUTMSG :='ERROR|Error occurred on record updation';
      PKG_FIBER_HOTO_COMP_TEST.INS_ERRORLOG(PMODIFIED_BY,'','DB : INS_UPD_WF_BY_CMM_FTTX',ERROR_CODE||' : '||ERROR_MESSAGE);
      
END INS_UPD_WF_BY_CMM_FTTX;

错误原因及修正 #

1. 触发PLS-00103的直接原因 #

代码中IF PSTATUS_ID = 4 OR THEN这一行存在语法错误：OR运算符后缺少具体的判断条件，PL/SQL解析器无法识别不完整的逻辑表达式，因此在遇到THEN时抛出报错。

• 修正方式：
  • 如果是需要判断多个状态值，补充完整条件，例如IF PSTATUS_ID = 4 OR PSTATUS_ID = 5 THEN；
  • 如果是误写了OR，直接删除该关键字，改为IF PSTATUS_ID = 4 THEN。

2. 其他潜在语法/逻辑问题 #

• 未声明变量：异常处理块中使用的ERROR_CODE和ERROR_MESSAGE未在存储过程的变量声明段定义，编译时会额外报错。需在AS部分添加：

  ERROR_CODE NUMBER;
  ERROR_MESSAGE VARCHAR2(200);
  

• 冗余嵌套块：代码存在多层不必要的BEGIN...END嵌套，可简化结构以提升可读性。
• 未初始化变量：VNELEG变量在更新语句中被使用，但从未赋值，执行时会导致对应字段被更新为NULL，需补充该变量的赋值逻辑。
• 字段拼写疑问：参数PHOTO_ACTUAL_LENGTH对应表字段为HOTO_ACTUAL_LENGTH，需确认是否为拼写错误，避免业务逻辑偏差。
• SELECT INTO异常风险：SELECT DISTINCT JOB_ID INTO VJOB_ID和SELECT JB.SPAN_ID... INTO VSPAN_ID...如果返回0行或多行，会抛出NO_DATA_FOUND或TOO_MANY_ROWS异常，建议添加针对性异常处理或确保查询逻辑唯一返回一行。

内容的提问来源于stack exchange，提问作者Nadeem