使用PL/SQL游标计算学生加权平均分并更新ETUDIANT表RANGE列

1. 先计算加权平均分并生成符合要求的排名 #

先通过SQL关联三张表算出每个学生的加权平均分，再用RANK()窗口函数生成排名——这个函数会让相同分数的学生共享同一排名，且后续排名自动跳过重复名次（比如两个第1名之后，下一个直接是第3名）：

WITH student_avg AS (
    SELECT 
        n.id_etudiant,
        SUM(n.note * m.coefficient) / SUM(m.coefficient) AS weighted_avg
    FROM NOTES n
    JOIN MODULE m ON n.id_module = m.id_module
    GROUP BY n.id_etudiant
),
student_rank AS (
    SELECT 
        id_etudiant,
        weighted_avg,
        RANK() OVER (ORDER BY weighted_avg DESC) AS student_range
    FROM student_avg
)
SELECT * FROM student_rank;

如果需要连续排名（相同分数同排名，后续名次不跳过），把RANK()换成DENSE_RANK()即可。

2. 用PL/SQL游标实现更新 #

以下是用游标遍历计算结果、更新ETUDIANT表RANGE列的代码，注释已经标清关键步骤：

DECLARE
    -- 定义游标，包含学生ID和对应排名
    CURSOR c_student_rank IS
        WITH student_avg AS (
            SELECT 
                n.id_etudiant,
                SUM(n.note * m.coefficient) / SUM(m.coefficient) AS weighted_avg
            FROM NOTES n
            JOIN MODULE m ON n.id_module = m.id_module
            GROUP BY n.id_etudiant
        ),
        student_rank AS (
            SELECT 
                id_etudiant,
                RANK() OVER (ORDER BY weighted_avg DESC) AS student_range
            FROM student_avg
        )
        SELECT id_etudiant, student_range FROM student_rank;
        
    -- 声明与表字段匹配的变量，避免类型不兼容
    v_id_etudiant ETUDIANT.id_etudiant%TYPE;
    v_range ETUDIANT.RANGE%TYPE;
BEGIN
    -- 可选：清空原有排名，确保新数据准确
    UPDATE ETUDIANT SET RANGE = NULL;
    
    -- 打开游标并遍历
    OPEN c_student_rank;
    LOOP
        FETCH c_student_rank INTO v_id_etudiant, v_range;
        -- 遍历结束时退出循环
        EXIT WHEN c_student_rank%NOTFOUND;
        
        -- 更新当前学生的排名
        UPDATE ETUDIANT
        SET RANGE = v_range
        WHERE id_etudiant = v_id_etudiant;
    END LOOP;
    CLOSE c_student_rank;
    
    -- 提交更新
    COMMIT;
EXCEPTION
    -- 异常处理：更新失败时回滚并输出错误信息
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('更新失败：' || SQLERRM);
END;
/

3. 优化方案：批量更新（可选） #

如果学生数据量较大，游标逐行更新效率偏低，可以改用MERGE语句批量更新，不用写PL/SQL游标也能实现需求：

MERGE INTO ETUDIANT e
USING (
    WITH student_avg AS (
        SELECT 
            n.id_etudiant,
            SUM(n.note * m.coefficient) / SUM(m.coefficient) AS weighted_avg
        FROM NOTES n
        JOIN MODULE m ON n.id_module = m.id_module
        GROUP BY n.id_etudiant
    ),
    student_rank AS (
        SELECT 
            id_etudiant,
            RANK() OVER (ORDER BY weighted_avg DESC) AS student_range
        FROM student_avg
    )
    SELECT id_etudiant, student_range FROM student_rank
) sr
ON (e.id_etudiant = sr.id_etudiant)
WHEN MATCHED THEN
    UPDATE SET e.RANGE = sr.student_range;
COMMIT;

内容的提问来源于stack exchange，提问作者Seffih Oualid Redouan