PL/SQL复合触发器数组未定义，求解决学生单日考试限制问题

核心错误分析 #

1. SQL无法访问PL/SQL数组：你在AFTER EACH ROW的SQL查询中直接引用PL/SQL关联数组t_student_exam_date，但SQL引擎无法识别PL/SQL的本地变量/类型，这是导致“数组未定义”的直接原因。
2. 逻辑缺陷：原代码仅统计原有数据的次数，但UPDATE操作会修改考试日期，需要同步调整旧日期和新日期的考试次数，否则会出现计数错误。同时全量加载表数据效率极低，尤其当表数据量大时。

修正后的复合触发器代码 #

CREATE OR REPLACE TRIGGER MARSEL.STUD_USPEV
FOR UPDATE OF DATA ON MARSEL.USPEV
COMPOUND TRIGGER
    -- 定义关联数组，键为学生ID+考试日期的组合，值为该组合的考试次数
    TYPE t_exam_count_type IS TABLE OF NUMBER
        INDEX BY VARCHAR2(100); -- 可根据student和data的实际长度调整键的长度
    v_exam_counts t_exam_count_type;

    -- BEFORE STATEMENT：预加载现有数据的(学生,日期)考试次数统计
    BEFORE STATEMENT IS
    BEGIN
        SELECT student || '|' || data, COUNT(*)
        BULK COLLECT INTO v_exam_counts.KEY, v_exam_counts.VALUE
        FROM MARSEL.USPEV
        GROUP BY student, data;
    END BEFORE STATEMENT;

    -- BEFORE EACH ROW：处理每行修改，调整计数并校验
    BEFORE EACH ROW IS
        v_old_key VARCHAR2(100);
        v_new_key VARCHAR2(100);
        v_new_count NUMBER;
    BEGIN
        -- 生成旧行和新行的唯一标识键
        v_old_key := :OLD.student || '|' || :OLD.data;
        v_new_key := :NEW.student || '|' || :NEW.data;

        -- 如果修改了考试日期，调整旧日期的计数
        IF v_old_key != v_new_key THEN
            -- 旧日期计数减1，减到0则删除该键
            IF v_exam_counts.EXISTS(v_old_key) THEN
                v_exam_counts(v_old_key) := v_exam_counts(v_old_key) - 1;
                IF v_exam_counts(v_old_key) = 0 THEN
                    v_exam_counts.DELETE(v_old_key);
                END IF;
            END IF;
        END IF;

        -- 计算新日期的当前总次数（原有计数+本次修改的1次）
        IF v_exam_counts.EXISTS(v_new_key) THEN
            v_new_count := v_exam_counts(v_new_key) + 1;
        ELSE
            v_new_count := 1;
        END IF;

        -- 校验次数是否超过2
        IF v_new_count > 2 THEN
            RAISE_APPLICATION_ERROR(-20000, '学生单日考试次数不能超过2次');
        END IF;

        -- 更新关联数组中的新日期计数
        v_exam_counts(v_new_key) := v_new_count;
    END BEFORE EACH ROW;
END STUD_USPEV;

代码说明 #

1. BEFORE STATEMENT阶段：通过GROUP BY统计现有数据中每个学生每天的考试次数，存储到关联数组中，避免全量加载行数据，提升效率。
2. BEFORE EACH ROW阶段：
   • 用“学生ID+分隔符+日期”生成唯一键，避免不同学生/日期组合冲突。
   • 如果修改了日期，先将旧日期的计数减1（因为该行不再属于旧日期）。
   • 计算新日期的总次数，若超过2则抛出错误。
   • 更新关联数组中的新日期计数，确保后续行修改时统计准确。
3. 避免变异表错误：全程通过预加载的统计数据校验，不直接查询正在修改的USPEV表，彻底解决mutating-table错误。

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