PL/SQL存储过程与触发器开发任务求解

Hey there! Let's tackle each of your PL/SQL tasks one by one, with clear logic and code examples tailored to your schema.

1. 存储过程：删除讲座及关联研讨班 #

思路 #

要执行删除，必须确保两个前提：

• 该讲座没有学生注册（检查participate_student_lecture表）
• 该讲座关联的所有研讨班都没有学生注册（通过seminar关联participate_student_seminar表检查）
  如果任一条件不满足，抛出自定义错误；若条件符合，先删除关联的研讨班（因为seminar依赖lecture的外键），再删除讲座本身。

代码实现 #

CREATE OR REPLACE PROCEDURE delete_lecture(p_id_l IN lecture.id_l%TYPE)
IS
    v_student_count NUMBER;
    v_seminar_student_count NUMBER;
BEGIN
    -- 检查讲座是否有学生注册
    SELECT COUNT(*) INTO v_student_count
    FROM participate_student_lecture
    WHERE id_l = p_id_l;

    -- 检查关联研讨班是否有学生注册
    SELECT COUNT(*) INTO v_seminar_student_count
    FROM participate_student_seminar ps
    JOIN seminar s ON ps.id_seminar = s.id_seminar
    WHERE s.id_l = p_id_l;

    IF v_student_count > 0 OR v_seminar_student_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20001, '无法删除讲座：该讲座或其关联研讨班存在已注册的学生');
    END IF;

    -- 先删除关联的研讨班
    DELETE FROM seminar WHERE id_l = p_id_l;
    -- 再删除讲座
    DELETE FROM lecture WHERE id_l = p_id_l;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('讲座及关联研讨班已成功删除');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20002, '指定的讲座不存在');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20003, '删除操作失败：' || SQLERRM);
END;
/

2. 存储过程：删除研讨班 #

思路 #

核心检查点是该研讨班是否有学生注册（查询participate_student_seminar表）。如果有注册记录，抛出错误；否则直接删除研讨班。

代码实现 #

CREATE OR REPLACE PROCEDURE delete_seminar(p_id_seminar IN seminar.id_seminar%TYPE)
IS
    v_student_count NUMBER;
BEGIN
    -- 检查研讨班是否有学生注册
    SELECT COUNT(*) INTO v_student_count
    FROM participate_student_seminar
    WHERE id_seminar = p_id_seminar;

    IF v_student_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20004, '无法删除研讨班：该研讨班存在已注册的学生');
    END IF;

    DELETE FROM seminar WHERE id_seminar = p_id_seminar;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('研讨班已成功删除');
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20005, '指定的研讨班不存在');
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20006, '删除操作失败：' || SQLERRM);
END;
/

3. 触发器：注册研讨班时自动关联讲座 #

思路 #

当学生注册研讨班（插入participate_student_seminar）时，先从seminar表获取该研讨班对应的讲座ID，再检查学生是否已注册该讲座。如果未注册，自动插入participate_student_lecture记录。

代码实现 #

CREATE OR REPLACE TRIGGER trg_auto_register_lecture
AFTER INSERT ON participate_student_seminar
FOR EACH ROW
DECLARE
    v_id_l lecture.id_l%TYPE;
    v_exists NUMBER;
BEGIN
    -- 获取研讨班对应的讲座ID
    SELECT id_l INTO v_id_l
    FROM seminar
    WHERE id_seminar = :NEW.id_seminar;

    -- 检查学生是否已注册该讲座
    SELECT COUNT(*) INTO v_exists
    FROM participate_student_lecture
    WHERE id_stud = :NEW.id_stud AND id_l = v_id_l;

    IF v_exists = 0 THEN
        INSERT INTO participate_student_lecture(id_stud, id_l)
        VALUES (:NEW.id_stud, v_id_l);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE_APPLICATION_ERROR(-20007, '关联的研讨班不存在，无法自动注册讲座');
END;
/

4. 触发器：均衡分配学生至讲座的研讨班 #

思路 #

当学生注册讲座（插入participate_student_lecture）时，若该讲座有1-3个研讨班，按交替方式分配：

1. 获取该讲座下的所有研讨班并按ID排序（保证分配顺序固定）
2. 统计该讲座下已分配到研讨班的学生总数
3. 通过取模运算确定当前学生应分配的研讨班（比如第1个学生到研讨班1，第2到2，第3到3，第4回到1，以此类推）

代码实现 #

CREATE OR REPLACE TRIGGER trg_balance_seminar_assignment
AFTER INSERT ON participate_student_lecture
FOR EACH ROW
DECLARE
    TYPE seminar_list IS TABLE OF seminar.id_seminar%TYPE;
    v_seminars seminar_list;
    v_total_seminars NUMBER;
    v_assigned_count NUMBER;
    v_target_seminar seminar.id_seminar%TYPE;
BEGIN
    -- 获取当前讲座下的所有研讨班，按id排序
    SELECT id_seminar BULK COLLECT INTO v_seminars
    FROM seminar
    WHERE id_l = :NEW.id_l
    ORDER BY id_seminar;

    v_total_seminars := v_seminars.COUNT;

    -- 只处理有1-3个研讨班的情况
    IF v_total_seminars BETWEEN 1 AND 3 THEN
        -- 统计该讲座下已分配到研讨班的学生总数
        SELECT COUNT(*) INTO v_assigned_count
        FROM participate_student_seminar ps
        JOIN seminar s ON ps.id_seminar = s.id_seminar
        WHERE s.id_l = :NEW.id_l;

        -- 计算要分配的研讨班索引（从0开始，取模后加1）
        v_target_seminar := v_seminars((v_assigned_count MOD v_total_seminars) + 1);

        -- 将学生分配到目标研讨班
        INSERT INTO participate_student_seminar(id_stud, id_seminar)
        VALUES (:NEW.id_stud, v_target_seminar);
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- 如果讲座没有研讨班，不做处理
        NULL;
END;
/

补充说明 #

如果需要更稳定的分配顺序（比如学生取消注册后不打乱原有分配逻辑），可以创建一个单独的表来跟踪每个讲座的下一个分配研讨班索引，或者为每个讲座创建专属序列。上述示例基于已分配学生总数取模，适合学生不会取消注册的场景。

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