使用游标FOR循环实现合同到期员工通知功能报错求助

原代码的核心问题 #

• 仅处理最后一条符合条件的数据：C1游标循环结束后，V_EMP只保留最后一个员工的ID，没有遍历处理所有合同到期1-3个月的员工；同理C2循环后V_USER只存最后一个用户ID，完全不符合“所有符合条件员工”的需求。
• 员工与经理未关联：需求是给符合条件员工的经理发通知，但原代码没有从员工表关联获取经理的USER_ID，NL_NOTIF_EMP_HEADER的查询和员工信息完全脱节，逻辑不成立。
• SELECT INTO存在异常风险：当NL_NOTIF_EMP_HEADER中对应P_NOTIF_ID有多条记录时，SELECT ... INTO会触发TOO_MANY_ROWS异常，没有任何处理。
• 通知逻辑未绑定到每个员工：原代码只在找到员工和用户后发送一次通知，没有针对每个符合条件的员工及其经理执行通知流程。

修正后的代码 #

CREATE OR REPLACE FUNCTION NOTIFICATION_SENDING(P_NOTIF_ID NUMBER) RETURN NUMBER
IS
    V_EMAIL    VARCHAR2(1);
    V_SMS      VARCHAR2(1);
    V_ESS      VARCHAR2(1);
    V_MOBILE   VARCHAR2(1);
    -- 游标获取所有合同到期1-3个月的员工及其经理ID（假设员工表有MANAGER_USER_ID字段，需按实际表结构调整）
    CURSOR C_ELIGIBLE_EMPLOYEES IS
        SELECT 
            hec.EMP_ID,
            hec.MANAGER_USER_ID
        FROM HR_EMP_CONTRACT_HEADER hec
        WHERE MONTHS_BETWEEN(hec.EDATE, SYSDATE) BETWEEN 1 AND 3;
BEGIN
    -- 遍历每一个符合条件的员工
    FOR emp_rec IN C_ELIGIBLE_EMPLOYEES LOOP
        -- 查询该员工经理的通知配置
        BEGIN
            SELECT EMAIL, SMS, ESS, MOBILE
            INTO V_EMAIL, V_SMS, V_ESS, V_MOBILE
            FROM NL_NOTIF_EMP_HEADER
            WHERE NOTIF_ID = P_NOTIF_ID
              AND USER_ID = emp_rec.MANAGER_USER_ID;

            -- 根据通知开关调用对应发送函数
            IF V_EMAIL = 'Y' THEN
                NOTIF_EMAIL_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
            END IF;
            IF V_SMS = 'Y' THEN
                NOTIF_SMS_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
            END IF;
            IF V_ESS = 'Y' THEN
                NOTIF_ESS_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
            END IF;
            IF V_MOBILE = 'Y' THEN
                NOTIF_MOBILE_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
            END IF;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- 若该经理无通知配置，直接跳过（可按需添加日志记录）
                NULL;
            WHEN TOO_MANY_ROWS THEN
                -- 若该经理有多条配置，取第一条执行（可按需调整逻辑）
                SELECT EMAIL, SMS, ESS, MOBILE
                INTO V_EMAIL, V_SMS, V_ESS, V_MOBILE
                FROM NL_NOTIF_EMP_HEADER
                WHERE NOTIF_ID = P_NOTIF_ID
                  AND USER_ID = emp_rec.MANAGER_USER_ID
                FETCH FIRST 1 ROW ONLY;
                -- 重新执行通知发送
                IF V_EMAIL = 'Y' THEN
                    NOTIF_EMAIL_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
                END IF;
                IF V_SMS = 'Y' THEN
                    NOTIF_SMS_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
                END IF;
                IF V_ESS = 'Y' THEN
                    NOTIF_ESS_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
                END IF;
                IF V_MOBILE = 'Y' THEN
                    NOTIF_MOBILE_SENDING(P_NOTIF_ID, emp_rec.MANAGER_USER_ID);
                END IF;
        END;
    END LOOP;
    RETURN 0; -- 返回成功标识，可根据业务需求调整返回值
END NOTIFICATION_SENDING;
/

修正说明 #

1. 游标C_ELIGIBLE_EMPLOYEES直接关联员工合同和经理信息，确保遍历所有符合条件的员工
2. 针对每个员工的经理单独查询通知配置，添加异常处理避免无数据或多数据的报错
3. 每个符合条件的员工对应执行一次通知发送逻辑，完全匹配需求
4. 增强了代码健壮性，处理了常见的查询异常场景

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