创建Oracle存储过程时触发PLS-00103错误：END符号异常

错误信息 #

Error(6384,1): PLS-00103: Encountered the symbol "END"

错误原因分析 #

1. IF语句结构不完整：第一个IF VCOUNTERCHILD = 0仅编写了THEN BEGIN...END;，未添加对应的END IF;，导致PL/SQL解析器无法匹配语句块结构，触发符号错误。
2. 变量/参数缺失：代码中用到的VCOUNTERCHILD、PHOTO_OFFERED_DATE、PCREATED_BY等变量要么未在AS段声明，要么未作为存储过程输入参数定义，会引发后续编译错误。
3. IN参数赋值违规：RETURNING JOB_PROGRESS_ID INTO PJOB_PROGRESS_ID;中PJOB_PROGRESS_ID是IN类型参数，无法被赋值，必须改用局部变量接收返回值。
4. 日期比较逻辑不严谨：使用to_char转换日期进行比较易引发格式匹配问题，且性能低于直接用TRUNC处理。

修正后的完整代码 #

PROCEDURE INS_WORKFLOW_FTTX_PARTIAL
(
  PJOB_PROGRESS_ID IN TBL_FIBER_INV_JOB_PROGRESS.JOB_PROGRESS_ID%TYPE,
  PSTATUS_ID IN TBL_FIBER_INV_JOB_PROGRESS.STATUS_ID%TYPE,
  PFSA_UG_ACTUAL IN TBL_FIBER_INV_JOB_PROGRESS.FSA_UG_ACTUAL%TYPE,
  PFSA_AR_ACTUAL IN TBL_FIBER_INV_JOB_PROGRESS.FSA_AERIAL_ACTUAL%TYPE,
  PMDU_ACTUAL_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.FSA_MDU_ACTUAL%TYPE,
  PAPPROVED_BY IN TBL_FIBER_INV_JOB_PROGRESS.APPROVED_BY%TYPE,
  PREJECTED_BY IN TBL_FIBER_INV_JOB_PROGRESS.REJECTED_BY%TYPE,
  PAPPROV_REJECT_REMARK IN TBL_FIBER_INV_JOB_PROGRESS.APPROV_REJECT_REMARK%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_ACCEPTANCE_DATE IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_ACCEPTENCE_DATE%TYPE,
  PHOTO_OFFERED_DATE IN VARCHAR2,
  PHOTO_OFFERED_LENGTH IN TBL_FIBER_INV_JOB_PROGRESS.HOTO_OFFERED_LENGTH%TYPE,
  PNELENGTH IN TBL_FIBER_INV_JOB_PROGRESS.NE_SPAN_LENGTH%TYPE,
  PCREATED_BY IN TBL_FIBER_INV_JOB_PROGRESS.CREATED_BY%TYPE,
  SPLKID IN TBL_FIBER_INV_MSTDATA.SPAN_LINK_ID%TYPE,
  PMAINTENANCEZONECODE IN TBL_FIBER_INV_MSTDATA.MAINTENANCE_ZONE_CODE%TYPE,
  PJOB_PROGRESS_ID_OLD IN TBL_FIBER_INV_JOB_PROGRESS.JOB_PROGRESS_ID%TYPE,
  PSPVENDORXML IN XMLTYPE,
  POUTMSG OUT NVARCHAR2
)

AS
VCNTSPVENCNT NUMBER :=0;
VJOB_ID NUMBER := 0;
VCOUNTERCHILD NUMBER := 0;
VNEW_JOB_PROGRESS_ID TBL_FIBER_INV_JOB_PROGRESS.JOB_PROGRESS_ID%TYPE;

BEGIN

SELECT JOB_ID INTO VJOB_ID FROM TBL_FIBER_INV_JOB_PROGRESS WHERE JOB_PROGRESS_ID = PJOB_PROGRESS_ID;

SELECT COUNT(JOB_PROGRESS_ID) 
INTO VCOUNTERCHILD FROM TBL_FIBER_INV_JOB_PROGRESS c 
INNER JOIN TBL_FIBER_INV_JOBS p 
ON c.job_id = p.job_id 
AND c.job_id = VJOB_ID 
AND TRUNC(p.CREATED_DATE) = TRUNC(SYSDATE + 1);

IF VCOUNTERCHILD = 0 THEN
    INSERT INTO TBL_FIBER_INV_JOB_PROGRESS
    (
        JOB_ID,
        STATUS_ID,
        HOTO_OFFERED_DATE,
        APPROV_REJECT_REMARK,
        HOTO_OFFERED_LENGTH,
        NE_SPAN_LENGTH,
        CREATED_BY,
        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,
        MODIFIED_BY,
        MODIFIED_DATE,
        APPROVED_BY,
        APPROVED_DATE,
        FSA_UG_ACTUAL,
        FSA_AERIAL_ACTUAL,
        FSA_MDU_ACTUAL
    )
    VALUES
    (
        VJOB_ID,
        PSTATUS_ID,
        TO_DATE(PHOTO_OFFERED_DATE,'DD/MM/YYYY'),
        PAPPROV_REJECT_REMARK,
        PHOTO_OFFERED_LENGTH,                
        PNELENGTH,
        PCREATED_BY,
        PUMS_GROUP_ASS_BY_ID,
        PUMS_GROUP_ASS_BY_NAME,
        PUMS_GROUP_ASS_TO_ID,
        PUMS_GROUP_ASS_TO_NAME,
        SYSDATE,
        PAPPROVED_BY,
        SYSDATE,
        PCREATED_BY,
        SYSDATE,
        PFSA_UG_ACTUAL,
        PFSA_AR_ACTUAL,
        PMDU_ACTUAL_LENGTH
    )RETURNING JOB_PROGRESS_ID INTO VNEW_JOB_PROGRESS_ID;
    
    IF VNEW_JOB_PROGRESS_ID > 0 THEN
        POUTMSG := 'SUCCESS|'||VNEW_JOB_PROGRESS_ID;
        UPDATE TBL_FIBER_INV_MSTDATA 
        SET REQUEST_ID = VJOB_ID 
        WHERE SPAN_LINK_ID = SPLKID 
        AND MAINTENANCE_ZONE_CODE = PMAINTENANCEZONECODE;  
        COMMIT;
    END IF;
ELSE
    POUTMSG := 'WARNING|Record not created - Already a child record for job id '||PJOB_PROGRESS_ID_OLD||' ';
END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        POUTMSG := 'ERROR|Job progress ID '||PJOB_PROGRESS_ID||' not found';
        ROLLBACK;
    WHEN OTHERS THEN
        POUTMSG := 'ERROR|'||SQLERRM;
        ROLLBACK;

END INS_WORKFLOW_FTTX_PARTIAL;  

关键修正点 #

• 补全IF语句的END IF;，删除多余嵌套的BEGIN...END;块
• 声明缺失的局部变量，补充未定义的输入参数
• 将RETURNING赋值目标改为局部变量，避免修改IN参数
• 用TRUNC替换to_char处理日期比较，提升准确性与性能
• 添加异常处理块，捕获并处理查询无数据、通用异常，确保事务安全
• 修正参数名不一致问题：将INSERT中的PFSA_MDU_ACTUAL改为对应输入参数PMDU_ACTUAL_LENGTH

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