Oracle PL/SQL自定义包函数调用无返回结果原因排查

算力阿峰

2026-9-28

问题原因分析

SQL调用函数的事务操作限制
Oracle规定在SELECT查询中调用的函数，不允许执行COMMIT、ROLLBACK这类事务控制语句，也不允许执行会修改数据的DML操作（默认规则）。你的count_positive函数调用了内部存储过程number_requests，而该存储过程中包含COMMIT语句以及INSERT/UPDATE操作，直接在SELECT中调用会触发ORA-14551、ORA-14552错误，导致函数执行中断无返回结果。
变量未声明问题
代码中使用的subj、count_student两个变量，既没有在包体的全局声明区域定义，也没有在函数/存储过程内部声明，运行时会触发标识符未定义的报错，导致函数无法正常执行。
参数类型不匹配导致查询无结果
函数入参sub定义为NVARCHAR2类型，调用时传入的字符串常量默认是VARCHAR2类型，若数据库字符集和国家字符集存在差异，隐式转换后会导致S.subj_name = sub条件匹配不到数据。加上查询语句中添加了GROUP BY S.subj_name，当无匹配数据时，COUNT(*)查询不会返回0，而是返回空结果集，触发NO_DATA_FOUND异常，函数未捕获该异常就会直接中断，无返回值。而你直接执行独立查询时，字符串常量和字段类型匹配，所以可以正常返回结果。

修复方案

处理事务限制问题：要么将number_requests存储过程声明为自治事务，添加自治事务编译指令PRAGMA AUTONOMOUS_TRANSACTION;，自治事务的提交不会影响外层主事务，满足SQL调用的要求；要么将COMMIT操作移到函数调用的上层业务逻辑中，不要在函数内部执行事务控制。

PROCEDURE number_requests AS
PRAGMA AUTONOMOUS_TRANSACTION; -- 新增自治事务声明
BEGIN
    INSERT INTO package_table (subject,counts,callCount)
            VALUES (subj,count_student,1);
    exception
        when dup_val_on_index then
            update package_table 
            set    callCount = callCount + 1,
                 counts = count_student
            where  subject = subj;
COMMIT;
END number_requests;

在包体开头添加全局变量声明：

CREATE OR REPLACE PACKAGE BODY test_action AS
    -- 新增全局变量声明
    subj NVARCHAR2(200);
    count_student INTEGER;
    
    -- 原有函数和存储过程代码

修正参数类型匹配问题：调用函数时显式将入参转换为NVARCHAR2类型，或者将函数入参类型修改为和S.subj_name字段一致的类型：

SELECT test_action.count_positive(TO_NCHAR('ИНФОРМАТИКА')) FROM DUAL;

给函数添加异常捕获逻辑，避免无匹配数据时执行中断：

FUNCTION count_positive (sub NVARCHAR2)
    RETURN INTEGER
AS
BEGIN 
    count_student := 0;
    subj := sub;

    SELECT COUNT(*) 
        INTO count_student 
        FROM D8_EXAMS E JOIN D8_SUBJECT S ON E.subj_id = S.subj_id
    WHERE E.mark > 3 
        AND S.subj_name = sub
    GROUP BY S.subj_name;
    number_requests();          
    return count_student;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        count_student := 0;
        number_requests();
        RETURN 0;
    WHEN OTHERS THEN
        RETURN -1;
END count_positive;

内容的提问来源于stack exchange，提问作者Миша Попов