如何在PL/SQL的函数或存储过程中返回强类型CURSOR

原代码存在的问题 #

• 返回类型错误：函数声明返回EMPLOYEES表类型，但实际需返回自定义的强类型游标cur_empData
• 类型与变量混淆：cur_empData是游标类型而非变量，需先声明该类型的变量才能使用
• 强类型游标不支持动态SQL：强类型游标要求编译时确定返回结构，不能用OPEN ... FOR 字符串这类动态SQL写法，必须改用静态SQL
• 条件逻辑缺陷：p_startId > 0 OR p_endId > 0会导致单参数为0时生成无效范围（如BETWEEN 0 AND 100），应改为AND判断确保范围有效

修正后的强类型游标函数 #

FUNCTION getEmpCursorStrong (
    p_startId NUMBER, 
    p_endId NUMBER
)
RETURN cur_empData  -- 返回自定义的强类型游标
IS
    -- 定义强类型游标类型，建议放在包规范中方便外部调用
    TYPE cur_empData IS REF CURSOR RETURN EMPLOYEES%ROWTYPE;
    l_cur cur_empData;  -- 声明该类型的游标变量
BEGIN
    -- 用静态SQL满足强类型游标编译时校验要求
    IF p_startId > 0 AND p_endId > 0 THEN
        OPEN l_cur FOR
            SELECT * 
            FROM EMPLOYEES
            WHERE employee_id BETWEEN p_startId AND p_endId;
    ELSE
        OPEN l_cur FOR
            SELECT * 
            FROM EMPLOYEES;
    END IF;

    RETURN l_cur;
END getEmpCursorStrong;

修正后的调用存储过程 #

需将游标变量声明为强类型cur_empData，而非弱类型SYS_REFCURSOR：

PROCEDURE printEmpData (
    p_startId NUMBER DEFAULT 0, 
    p_endId NUMBER DEFAULT 0
)
AS
    -- 声明强类型游标变量
    cur_empData cur_empData;
    rec_empData EMPLOYEES%ROWTYPE;
BEGIN
    dbms_output.put_line('WEAK CURSOR OUTPUT:');
    cur_empData := getEmpCursorWeak(p_startId, p_endId);        

    LOOP
        FETCH cur_empData INTO rec_empData;            
        EXIT WHEN cur_empData%NOTFOUND;
        dbms_output.put_line( ' with id: ' || rec_empData.employee_id || ' - ' || rec_empData.LAST_NAME);
    END LOOP;

    CLOSE cur_empData;

    dbms_output.put_line('##########################');
    dbms_output.put_line('STRONG CURSOR OUTPUT HERE:');
    
    -- 调用强类型游标函数并输出结果
    cur_empData := getEmpCursorStrong(p_startId, p_endId);
    LOOP
        FETCH cur_empData INTO rec_empData;            
        EXIT WHEN cur_empData%NOTFOUND;
        dbms_output.put_line( ' with id: ' || rec_empData.employee_id || ' - ' || rec_empData.LAST_NAME);
    END LOOP;
    CLOSE cur_empData;

END printEmpData;

补充说明 #

若必须使用动态SQL，只能采用弱类型的SYS_REFCURSOR。强类型游标要求返回结构在编译阶段确定，无法适配运行时动态生成的SQL语句。

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