Oracle存储过程执行报错PLS-00103：IF符号不符合语法要求

错误信息 #

Error(5668,11): PLS-00103: Encountered the symbol "IF" when expecting one of the following: ;

错误触发位置标注在代码中的ELSIF // here I am getting error行。

问题分析 #

1. ELSIF结构错误：原代码中END IF;后直接使用ELSIF，既缺少条件判断表达式，又破坏了IF-ELSIF-ELSE的语法结构，PL/SQL要求ELSIF必须紧跟IF分支且带条件。
2. 多余BEGIN/END嵌套：多处不必要的BEGIN块导致代码结构混乱，编译器无法识别分支边界。
3. UPDATE语法错误：SET子句末尾多了一个右括号，触发语法中断。
4. 未声明变量：EXCEPTION块中使用的ERROR_CODE和ERROR_MESSAGE未在AS段声明。
5. 查询冗余：已在开头查询过PCNT_JOBID，无需在ELSIF分支重复执行相同查询；更新分支未获取现有JOB_ID，导致UPDATE无目标。

修复后的完整代码 #

PROCEDURE INS_WORKFLOW_FIP_FTTX
(
  PFSA_ID IN TBL_FIBER_INV_JOBS.FSA_ID%TYPE,   
  PUG_LENGTH IN TBL_FIBER_INV_JOBS.FSA_UG%TYPE,
  PAR_LENGTH IN TBL_FIBER_INV_JOBS.FSA_AERIAL%TYPE,
  PCREATED_BY IN TBL_FIBER_INV_JOBS.CREATED_BY%TYPE, 
  PMAINTENANCEZONECODE IN TBL_FIBER_INV_JOBS.MAINTENANCEZONECODE%TYPE, 
  PMAINTENANCEZONENAME IN TBL_FIBER_INV_JOBS.MAINTENANCEZONENAME%TYPE, 
  PNE_LENGTH IN TBL_FIBER_INV_JOBS.MAINT_ZONE_NE_SPAN_LENGTH%TYPE,
  PSTATUS_ID IN TBL_FIBER_INV_JOB_PROGRESS.STATUS_ID%TYPE,
  PSPAN_TYPE IN TBL_FIBER_INV_JOBS.SPAN_TYPE%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,
  PHOTO_OFFERED_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_OFFERED_LENGTH%TYPE,  
  PHOTO_ACCEPTANCE_DATE IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACCEPTENCE_DATE%TYPE,
  PSPVENDORXML IN XMLTYPE,
  POUTMSG OUT NVARCHAR2
)   

AS
PJOB_PROGRESS_ID NUMBER:=0;
PJOB_ID NUMBER :=0;
PCNT_JOBID NUMBER := -1;
ERROR_CODE NUMBER; -- 新增变量声明
ERROR_MESSAGE VARCHAR2(200); -- 新增变量声明
BEGIN   
    -- 仅执行一次计数查询
    SELECT COUNT(JOB_ID) INTO PCNT_JOBID 
    FROM TBL_FIBER_INV_JOBS
    WHERE FSA_ID = PFSA_ID
      AND MAINTENANCEZONECODE = PMAINTENANCEZONECODE;           
    
    IF PCNT_JOBID = 0 THEN
        -- 插入新记录分支
        INSERT INTO TBL_FIBER_INV_JOBS
          (
            FSA_ID,
            FSA_UG,
            FSA_AERIAL,
            CREATED_BY,
            MAINTENANCEZONECODE,
            MAINTENANCEZONENAME,
            SPAN_TYPE,
            MAINT_ZONE_NE_SPAN_LENGTH
          )
          VALUES
          (
            PFSA_ID,
            PUG_LENGTH,
            PAR_LENGTH,
            PCREATED_BY,
            PMAINTENANCEZONECODE,
            PMAINTENANCEZONENAME,
            PSPAN_TYPE,
            PNE_LENGTH
          ) RETURNING JOB_ID INTO PJOB_ID;
          
        IF PJOB_ID > 0 THEN
            INSERT INTO TBL_FIBER_INV_JOB_PROGRESS
              (
                JOB_ID,
                FSA_UG,
                FSA_AERIAL,
                CREATED_BY,
                CREATED_DATE,
                STATUS_ID,
                UMS_GROUP_ASS_BY_ID,
                UMS_GROUP_ASS_BY_NAME,
                UMS_GROUP_ASS_TO_ID,
                UMS_GROUP_ASS_TO_NAME,
                UMS_GROUP_ASS_TO_DATE,
                HOTO_OFFERED_LENGTH,
                HOTO_ACCEPTENCE_DATE,
                NE_SPAN_LENGTH,
                MODIFIED_BY,
                MODIFIED_DATE
              )
              VALUES
              (
                PJOB_ID,
                PUG_LENGTH,
                PAR_LENGTH,
                PCREATED_BY,
                SYSDATE,
                PSTATUS_ID,
                PUMS_GROUP_ASS_BY_ID,
                PUMS_GROUP_ASS_BY_NAME,
                PUMS_GROUP_ASS_TO_ID,
                PUMS_GROUP_ASS_TO_NAME,
                SYSDATE,
                PHOTO_OFFERED_LENGTH,
                PHOTO_ACCEPTANCE_DATE,
                PNE_LENGTH,
                PCREATED_BY,
                SYSDATE
              ) RETURNING JOB_PROGRESS_ID INTO PJOB_PROGRESS_ID;           
            
            -- 清理并插入供应商信息
            DELETE FROM TBL_FIBER_INV_VENDORINFO 
            WHERE JOB_ID = PJOB_ID;    
    
            FOR SPVENDORINFO IN
            (
                SELECT ASPVENDORDETAILS.EXTRACT('ROW/VendorID/text()').GETSTRINGVAL() AS ASP_VENDOR_ID, 
                       ASPVENDORDETAILS.EXTRACT('ROW/VendorName/text()').GETSTRINGVAL() AS ASP_VENDOR_NAME, 
                      ASPVENDORDETAILS.EXTRACT('ROW/VendorCode/text()').GETSTRINGVAL() AS ASP_VENDOR_CODE,
                      ASPVENDORDETAILS.EXTRACT('ROW/FromDate/text()').GETSTRINGVAL() AS ASP_VENDOR_START_DATE,
                      ASPVENDORDETAILS.EXTRACT('ROW/ToDate/text()').GETSTRINGVAL() AS ASP_VENDOR_END_DATE
                FROM TABLE(XMLSEQUENCE(PSPVENDORXML.EXTRACT('SPVENDORDETAILS/ROW'))) ASPVENDORDETAILS
            )
            LOOP
                INSERT INTO TBL_FIBER_INV_VENDORINFO
                      (
                        SP_VENDOR_CODE,
                        SP_VENDOR_START_DATE,
                        SP_VENDOR_END_DATE,
                        JOB_ID
                      )
                      VALUES
                      (
                        SPVENDORINFO.ASP_VENDOR_CODE,
                        TO_DATE(SPVENDORINFO.ASP_VENDOR_START_DATE,'DD/MM/YYYY'),
                         TO_DATE(SPVENDORINFO.ASP_VENDOR_END_DATE,'DD/MM/YYYY'),
                        PJOB_ID                
                      );
            END LOOP;               
            POUTMSG :='SUCCESS|Record inserted successfully';
            COMMIT;
        END IF;       
    ELSIF PCNT_JOBID > 0 THEN
        -- 更新现有记录分支：先获取已存在的JOB_ID
        SELECT JOB_ID INTO PJOB_ID
        FROM TBL_FIBER_INV_JOBS
        WHERE FSA_ID = PFSA_ID
          AND MAINTENANCEZONECODE = PMAINTENANCEZONECODE;
          
        UPDATE TBL_FIBER_INV_JOB_PROGRESS
        SET
            FSA_UG = PUG_LENGTH,
            FSA_AERIAL = PAR_LENGTH,
            CREATED_BY = PCREATED_BY,
            CREATED_DATE = SYSDATE,
            STATUS_ID = PSTATUS_ID,
            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,
            HOTO_OFFERED_LENGTH = PHOTO_OFFERED_LENGTH,
            HOTO_ACCEPTENCE_DATE = PHOTO_ACCEPTANCE_DATE,
            NE_SPAN_LENGTH = PNE_LENGTH,
            MODIFIED_BY = PCREATED_BY,
            MODIFIED_DATE = SYSDATE
        WHERE JOB_ID = PJOB_ID -- 新增WHERE条件，避免全表更新
        RETURNING JOB_PROGRESS_ID INTO PJOB_PROGRESS_ID;
        
        -- 清理并插入供应商信息
        DELETE FROM TBL_FIBER_INV_VENDORINFO 
        WHERE JOB_ID = PJOB_ID;    
    
        FOR SPVENDORINFO IN
        (
            SELECT ASPVENDORDETAILS.EXTRACT('ROW/VendorID/text()').GETSTRINGVAL() AS ASP_VENDOR_ID, 
                   ASPVENDORDETAILS.EXTRACT('ROW/VendorName/text()').GETSTRINGVAL() AS ASP_VENDOR_NAME, 
                  ASPVENDORDETAILS.EXTRACT('ROW/VendorCode/text()').GETSTRINGVAL() AS ASP_VENDOR_CODE,
                  ASPVENDORDETAILS.EXTRACT('ROW/FromDate/text()').GETSTRINGVAL() AS ASP_VENDOR_START_DATE,
                  ASPVENDORDETAILS.EXTRACT('ROW/ToDate/text()').GETSTRINGVAL() AS ASP_VENDOR_END_DATE
            FROM TABLE(XMLSEQUENCE(PSPVENDORXML.EXTRACT('SPVENDORDETAILS/ROW'))) ASPVENDORDETAILS
        )
        LOOP
            INSERT INTO TBL_FIBER_INV_VENDORINFO
                  (
                    SP_VENDOR_CODE,
                    SP_VENDOR_START_DATE,
                    SP_VENDOR_END_DATE,
                    JOB_ID
                  )
                  VALUES
                  (
                    SPVENDORINFO.ASP_VENDOR_CODE,
                    TO_DATE(SPVENDORINFO.ASP_VENDOR_START_DATE,'DD/MM/YYYY'),
                     TO_DATE(SPVENDORINFO.ASP_VENDOR_END_DATE,'DD/MM/YYYY'),
                    PJOB_ID                
                  );
        END LOOP;               
        POUTMSG :='SUCCESS|Record updated successfully';
        COMMIT;
    ELSE
        -- 兜底分支（理论上不会触发）
        POUTMSG := 'EXISTS|Record already exists';
    END IF;
           
EXCEPTION  
    WHEN OTHERS THEN
        ERROR_CODE := SQLCODE;
        ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 200);
        
        ROLLBACK;
        
        POUTMSG := 'ERROR|Error occurred on record creation';
        
        PKG_FIBER_HOTO_COMP_NEW.INS_ERRORLOG(PCREATED_BY, PFSA_ID, 'DB : INS_WORKFLOW_FIP_FTTX',ERROR_CODE||' : '||ERROR_MESSAGE);
END INS_WORKFLOW_FIP_FTTX;

关键修复点 #

• 修正IF-ELSIF-ELSE结构：给ELSIF添加条件PCNT_JOBID > 0，匹配正确的END IF闭合逻辑。
• 移除多余BEGIN/END嵌套：简化代码结构，避免分支边界混淆。
• 修复UPDATE语法：删除SET子句末尾的多余右括号，新增WHERE条件避免全表更新。
• 补充变量声明：在AS段声明EXCEPTION块用到的ERROR_CODE和ERROR_MESSAGE。
• 优化查询逻辑：仅执行一次COUNT查询，更新分支新增JOB_ID查询以定位目标记录。
• 删除无效代码：移除开头注释掉的冗余代码。

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