基于BATCH_ID参数的PL/SQL存储过程：校验EMP_TBL与X_LOOKUP_TBL并更新错误信息

源表信息 #

员工表 EMP_TBL 结构及初始数据如下：

Batch_ID    Emp_ID  Account Src_Owner      Flag  Err_Reason
1           E1      SAL     FUSION           Y      VALID
1           E2      EXP     FUSION           Y      VALID
1           E5      SUS     ORCL             Y      VALID
2           E6      SUS     BBL              Y      VALID
2           E3      SAL     BBL              Y      VALID
2           E8      EXP     FUSION           Y      VALID
2           E4      SAL     EBS              Y      VALID
2           E11     SAL     EBS              Y      VALID 
1           E98     EXP     VBL              Y      VALID
1           E12     SUS     ORCL             Y      VALID
2           E43     DDD     VBL              Y      VALID

默认值：FLAG = 'Y'，Err_Reason = 'VALID'

参考Lookup表信息 #

校验用Lookup表 X_LOOKUP_TBL 结构及数据如下：

Prkey   Account Src_Owner
1       SAL     FUSION
2       EXP     EBS
3       null    ORCL

校验与更新规则 #

将EMP_TBL中指定批次的Account和Src_Owner字段与X_LOOKUP_TBL中的有效值比对，执行以下更新逻辑：

• 若Account不在X_LOOKUP_TBL的有效值范围内（Lookup表中Account为null时，仅匹配Src_Owner，不校验Account），则标记InvalidAccount
• 若Src_Owner不在X_LOOKUP_TBL的有效值范围内，则标记Invalid Owner
• 不匹配时更新FLAG = 'N'，Err_Reason按实际不匹配项拼接（多个原因用, 分隔）
• 匹配时保持FLAG = 'Y'，Err_Reason = 'VALID'

预期更新结果 #

执行校验后，EMP_TBL应更新为如下状态：

Batch_ID    Emp_ID  Account Src_Owner   Flag    Err_Reason
1           E1      SAL     FUSION      Y       VALID
1           E2      EXP     FUSION      Y       VALID
1           E5      SUS     ORCL        N       InvalidAccount
2           E6      SUS     BBL         N       InvalidAccount, Invalid Owner
2           E3      SAL     BBL         N       Invalid Owner
2           E8      EXP     FUSION      Y       VALID
2           E4      SAL     EBS         Y       VALID
2           E11     SAL     EBS         Y       VALID
1           E98     EXP     VBL         N       Invalid Owner
1           E12     SUS     ORCL        N       InvalidAccount
2           E43     DDD     VBL         N       InvalidAccount, Invalid Owner

存储过程要求 #

开发PL/SQL存储过程，需满足：

• 接收BATCH_ID作为输入参数
• 仅对该批次的数据进行校验与更新
• 支持后续在循环中按需调用

实现代码 #

CREATE OR REPLACE PROCEDURE VALIDATE_EMPLOYEE_BATCH(
    P_BATCH_ID IN EMP_TBL.BATCH_ID%TYPE
) AS
BEGIN
    UPDATE EMP_TBL e
    SET 
        FLAG = CASE WHEN EXISTS (
            SELECT 1 
            FROM X_LOOKUP_TBL l
            WHERE (l.Account IS NULL OR e.Account = l.Account)
              AND e.Src_Owner = l.Src_Owner
        ) THEN 'Y' ELSE 'N' END,
        Err_Reason = CASE 
            WHEN EXISTS (
                SELECT 1 
                FROM X_LOOKUP_TBL l
                WHERE (l.Account IS NULL OR e.Account = l.Account)
                  AND e.Src_Owner = l.Src_Owner
            ) THEN 'VALID'
            ELSE 
                RTRIM(
                    CASE WHEN NOT EXISTS (
                        SELECT 1 
                        FROM X_LOOKUP_TBL l
                        WHERE (l.Account = e.Account OR l.Account IS NULL)
                          AND e.Src_Owner = l.Src_Owner
                    ) THEN 'InvalidAccount, ' ELSE '' END ||
                    CASE WHEN NOT EXISTS (
                        SELECT 1 
                        FROM X_LOOKUP_TBL l
                        WHERE e.Src_Owner = l.Src_Owner
                    ) THEN 'Invalid Owner' ELSE '' END,
                    ', '
                )
        END
    WHERE e.Batch_ID = P_BATCH_ID;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END VALIDATE_EMPLOYEE_BATCH;
/

代码说明 #

• 核心逻辑通过子查询判断当前记录的Account和Src_Owner是否匹配Lookup表中的有效组合
• 使用CASE语句分别处理FLAG和Err_Reason的更新
• 用RTRIM去除拼接后可能多余的末尾逗号和空格
• 包含异常处理，确保更新失败时回滚事务

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