Oracle SQL 如何通过自定义函数计算平均成绩并插入目标表

前置说明 #

Oracle中自定义函数默认无法直接执行INSERT/UPDATE/DELETE等DML操作，要实现该需求需要为函数声明自治事务，让函数内部的事务和调用方事务独立运行。

步骤1：创建主键生成序列 #

AVGGRADE表的ID字段需要唯一主键值，我们通过序列自动生成：

CREATE SEQUENCE SEQ_AVGGRADE_ID 
START WITH 1 
INCREMENT BY 1 
NOCACHE 
NOCYCLE;

步骤2：创建自定义函数 #

全量覆盖版本（每次清空旧数据后重新插入） #

CREATE OR REPLACE FUNCTION INSERT_STUDENT_AVG_GRADE
RETURN NUMBER -- 返回成功插入的记录条数
IS
    PRAGMA AUTONOMOUS_TRANSACTION; -- 声明自治事务，支持DML操作
    V_INSERT_CNT NUMBER;
BEGIN
    -- 清空历史平均成绩，不需要可删除该行
    DELETE FROM AVGGRADE;

    -- 统计平均成绩并插入目标表
    INSERT INTO AVGGRADE(ID, STUDENTID, AVG)
    SELECT
        SEQ_AVGGRADE_ID.NEXTVAL,
        STUDENTID,
        ROUND(AVG(GRADE), 0) -- 匹配AVG字段的整数类型要求
    FROM GRADE
    GROUP BY STUDENTID;

    V_INSERT_CNT := SQL%ROWCOUNT;
    COMMIT; -- 自治事务需要独立提交
    RETURN V_INSERT_CNT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK; -- 出错回滚
        RAISE; -- 抛出异常便于定位问题
END;
/

增量更新版本（避免重复插入，已存在的学生更新成绩） #

如果不需要清空旧数据，仅需要更新已有学生的平均成绩、插入新增学生的成绩，把上面函数中的DELETE+INSERT逻辑替换为以下MERGE逻辑即可：

MERGE INTO AVGGRADE T
USING (
    SELECT STUDENTID, ROUND(AVG(GRADE),0) AVG_GRADE 
    FROM GRADE 
    GROUP BY STUDENTID
) S
ON (T.STUDENTID = S.STUDENTID)
WHEN MATCHED THEN UPDATE SET T.AVG = S.AVG_GRADE
WHEN NOT MATCHED THEN INSERT (ID, STUDENTID, AVG) 
VALUES (SEQ_AVGGRADE_ID.NEXTVAL, S.STUDENTID, S.AVG_GRADE);

步骤3：调用函数 #

PL/SQL块调用 #

SET SERVEROUTPUT ON;
DECLARE
    V_CNT NUMBER;
BEGIN
    V_CNT := INSERT_STUDENT_AVG_GRADE();
    DBMS_OUTPUT.PUT_LINE('成功处理'||V_CNT||'条学生平均成绩记录');
END;
/

直接查询调用 #

SELECT INSERT_STUDENT_AVG_GRADE() AS 处理记录数 FROM DUAL;

补充提示 #

如果没有强制要求用函数实现，Oracle更推荐使用存储过程完成DML类操作，逻辑和上述函数完全一致，仅需要把返回值修改为存储过程的OUT参数即可。

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