如何优化Oracle PL/SQL存储过程以高效返回sys_refcursor

可以通过动态SQL+绑定变量的方式彻底消除重复代码，同时保证SQL的安全性和执行效率，具体实现如下：

优化后的代码实现 #

procedure query_my_table
(
    in_param_1        number, -- 请根据实际场景调整参数类型
    in_param_2        number,
    in_param_3        number,
    in_param_n        number,
    in_limit          number,
    in_offset         number,
    ref               out sys_refcursor
) is
    v_sql              varchar2(32767);
    v_bind_idx         pls_integer := 0;
begin
    -- 初始化固定的SELECT子句与FROM部分
    v_sql := 'SELECT json_object( ... cols ... format json) as json FROM my_table';

    -- 动态拼接WHERE子句
    v_bind_idx := v_bind_idx + 1;
    if in_param_n is null then
        v_sql := v_sql || ' WHERE col_1 = :' || v_bind_idx || ' AND col_2 = :' || (v_bind_idx+1) || ' AND col_3 = :' || (v_bind_idx+2);
        v_bind_idx := v_bind_idx + 2;
    else
        v_sql := v_sql || ' WHERE col_n = :' || v_bind_idx;
    end if;

    -- 动态拼接OFFSET/FETCH分页片段
    if nvl(in_offset, 0) > 0 then
        v_bind_idx := v_bind_idx + 1;
        v_sql := v_sql || ' OFFSET :' || v_bind_idx || ' ROWS';
        
        if nvl(in_limit, 0) > 0 then
            v_bind_idx := v_bind_idx + 1;
            v_sql := v_sql || ' FETCH NEXT :' || v_bind_idx || ' ROWS ONLY';
        end if;
    elsif nvl(in_limit, 0) > 0 then
        v_bind_idx := v_bind_idx + 1;
        v_sql := v_sql || ' FETCH NEXT :' || v_bind_idx || ' ROWS ONLY';
    end if;

    -- 根据参数分支传递绑定变量，打开游标
    if in_param_n is null then
        case
            when nvl(in_offset,0) >0 and nvl(in_limit,0)>0 then
                open ref for v_sql using in_param_1, in_param_2, in_param_3, in_offset, in_limit;
            when nvl(in_offset,0) >0 then
                open ref for v_sql using in_param_1, in_param_2, in_param_3, in_offset;
            when nvl(in_limit,0) >0 then
                open ref for v_sql using in_param_1, in_param_2, in_param_3, in_limit;
            else
                open ref for v_sql using in_param_1, in_param_2, in_param_3;
        end case;
    else
        case
            when nvl(in_offset,0) >0 and nvl(in_limit,0)>0 then
                open ref for v_sql using in_param_n, in_offset, in_limit;
            when nvl(in_offset,0) >0 then
                open ref for v_sql using in_param_n, in_offset;
            when nvl(in_limit,0) >0 then
                open ref for v_sql using in_param_n, in_limit;
            else
                open ref for v_sql using in_param_n;
        end case;
    end if;

exception
    when others then
        if ref%isopen then
            close ref;
        end if;
        -- 保留原异常处理逻辑
end query_my_table;

关键优化点说明 #

• 消除重复代码：固定的SELECT部分仅定义一次，仅动态拼接变化的WHERE与分页片段，彻底避免6个分支的冗余代码。
• 绑定变量防注入：所有输入参数通过绑定变量传递，既避免SQL注入风险，又能让Oracle复用执行计划，提升查询性能。
• 维护性提升：后续修改SELECT子句或条件逻辑时，仅需调整一处即可，无需在多个分支中重复修改。
• 场景全覆盖：通过参数判断逻辑，完整覆盖in_param_n空/非空、分页参数组合的所有场景。

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