如何让返回SYS_REFCURSOR的函数无数据时返回NULL？解决无效游标错误

以下是返回SYS_REFCURSOR的包体代码，包规范同样返回SYS_REFCURSOR，调用时出现无效游标错误，需求是实现函数在无数据时返回NULL。

原代码：

FUNCTION SUMMARY
    (i_name VARCHAR2, i_id VARCHAR2, i_label VARCHAR2)
RETURN SYS_REFCURSOR
IS 
    rc_result           SYS_REFCURSOR;
    v_sql               CLOB;
    Server      VARCHAR2(100) := '@AI';
    v_r_count      NUMBER;
    v_sp_count       VARCHAR2(200);
    V VARCHAR2(10);
BEGIN
    EXECUTE IMMEDIATE 'select count(*) from run'||Server||' where id='''||i_id||''' and label='''||i_label||''' and re is not null' INTO v_r_count;
    EXECUTE IMMEDIATE 'select table_name from all_tables where table_name like ''%se%'' and owner ='''||i_name||'''' INTO v_sp_count;
    
    IF v_r_count > 0 THEN 
    BEGIN
        v_sql := 'select RE from run'||v_Server||' where id='''||i_id||''' and label='''||i_label||'''';      
    END;   
    ELSIF v_sp_count IS NOT NULL THEN
    BEGIN
        v_sql := 'SELECT
                        process,
                        desc,
                        p_desc,
                        date
                    FROM
                        '||i_name||'.se
                    WHERE
                            errors = 1
                        AND lower(p_desc) LIKE lower(''%summary%'')';
    END;
    ELSE v_sql := NULL;
    END IF;
    
    OPEN result FOR v_sql;    
    RETURN result;
    
    EXCEPTION 
        WHEN no_data_found THEN
             RETURN NULL;
        WHEN OTHERS THEN
            dbms_output.put_line(SQLERRM);
END;

问题分析 #

1. 游标变量名不匹配：声明的游标变量是rc_result，但打开游标时使用了未定义的result，直接触发无效游标错误。
2. NULL SQL处理缺失：当进入ELSE分支时v_sql被赋值为NULL，此时执行OPEN ... FOR NULL会抛出无效游标异常。
3. 变量名拼写错误：声明的服务器变量是Server，但拼接SQL时误用了v_Server，会触发标识符未定义错误。
4. SQL注入风险：直接拼接用户输入参数到SQL语句中，存在安全隐患。
5. 异常处理不完整：OTHERS分支仅输出错误但未返回值，会导致函数执行失败；第二个EXECUTE IMMEDIATE无数据时会直接触发全局NO_DATA_FOUND异常，不符合业务逻辑。

修正后的代码 #

FUNCTION SUMMARY
    (i_name VARCHAR2, i_id VARCHAR2, i_label VARCHAR2)
RETURN SYS_REFCURSOR
IS 
    rc_result           SYS_REFCURSOR;
    v_sql               CLOB;
    v_Server            VARCHAR2(100) := '@AI';
    v_r_count           NUMBER;
    v_sp_count          VARCHAR2(200);
BEGIN
    -- 使用绑定变量避免SQL注入，修正表名拼接逻辑
    EXECUTE IMMEDIATE 'select count(*) from run'||v_Server||' where id = :1 and label = :2 and re is not null' 
        INTO v_r_count
        USING i_id, i_label;
        
    -- 局部捕获无数据异常，避免触发全局分支
    BEGIN
        EXECUTE IMMEDIATE 'select table_name from all_tables where table_name like ''%se%'' and owner = :1' 
            INTO v_sp_count
            USING i_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            v_sp_count := NULL;
    END;
    
    IF v_r_count > 0 THEN 
        v_sql := 'select RE from run'||v_Server||' where id = :1 and label = :2';      
    ELSIF v_sp_count IS NOT NULL THEN
        v_sql := 'SELECT
                        process,
                        desc,
                        p_desc,
                        date
                    FROM
                        '||i_name||'.se
                    WHERE
                            errors = 1
                        AND lower(p_desc) LIKE lower(''%summary%'')';
    ELSE 
        -- 无数据场景直接返回NULL，跳过游标打开操作
        RETURN NULL;
    END IF;
    
    -- 根据SQL类型选择是否使用绑定变量打开游标
    IF v_r_count > 0 THEN
        OPEN rc_result FOR v_sql USING i_id, i_label;
    ELSE
        OPEN rc_result FOR v_sql;
    END IF;
    
    RETURN rc_result;
    
EXCEPTION 
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
        RETURN NULL; -- 异常场景统一返回NULL，确保函数总有返回值
END;

修改说明 #

• 修正游标变量名，确保声明与使用的变量一致。
• 统一变量命名，解决标识符未定义错误。
• 无数据场景直接返回NULL，避免打开NULL游标引发的异常。
• 动态SQL改用绑定变量，消除SQL注入风险。
• 为单条查询添加局部异常捕获，符合业务逻辑判断流程。
• 完善全局异常处理，确保异常场景下函数能返回有效值。

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