Oracle PL/SQL嵌套OPEN FOR语句实现问题求助

问题概述 #

现有遗留系统正从内联SQL迁移至存储过程，以规避SQL注入、实现会话授权和统一错误处理。当前实现的exec_sql_text存储过程可接收动态SQL文本和JSON参数数组，针对SELECT/UPDATE/INSERT/DELETE分别使用OPEN FOR或EXECUTE IMMEDIATE执行，但存在两个核心问题：

• 参数数量受限，仅支持最多3个参数，无法覆盖100+参数的场景；
• 尝试嵌套OPEN FOR实现动态游标打开失败，期望实现类似EXECUTE IMMEDIATE的嵌套调用能力。

核心解决方案 #

1. 用DBMS_SQL解决100+参数绑定限制 #

Oracle原生OPEN FOR USING最多支持255个参数，但手动编写大量占位符不现实。DBMS_SQL包支持动态解析SQL并绑定任意数量的参数，完美适配100+参数场景。

重构后的exec_sql_text存储过程

CREATE OR REPLACE PROCEDURE exec_sql_text(
    p_sql_text IN VARCHAR2,
    p_params IN JSON,
    p_out_cursor OUT SYS_REFCURSOR
) AS
    l_cursor_id INTEGER;
    l_bind_count INTEGER;
    l_param_value VARCHAR2(4000);
    l_result INTEGER;
    l_json_array JSON_ARRAY_T;
BEGIN
    l_cursor_id := DBMS_SQL.OPEN_CURSOR;
    BEGIN
        -- 解析动态SQL
        DBMS_SQL.PARSE(l_cursor_id, p_sql_text, DBMS_SQL.NATIVE);
        
        -- 解析JSON参数数组
        l_json_array := JSON_ARRAY_T(p_params);
        l_bind_count := l_json_array.GET_SIZE;
        
        -- 批量绑定参数（此处示例为VARCHAR2，可扩展至NUMBER/DATE等类型）
        FOR i IN 1..l_bind_count LOOP
            l_param_value := l_json_array.GET_STRING(i-1); -- JSON数组索引从0开始
            DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':' || i, l_param_value);
        END LOOP;
        
        -- 区分处理查询与DML语句
        IF UPPER(SUBSTR(TRIM(p_sql_text), 1, 6)) = 'SELECT' THEN
            -- 将DBMS_SQL游标转换为SYS_REFCURSOR输出
            p_out_cursor := DBMS_SQL.TO_REFCURSOR(l_cursor_id);
        ELSE
            -- 执行DML并关闭游标
            l_result := DBMS_SQL.EXECUTE(l_cursor_id);
            DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
        END IF;
    EXCEPTION
        WHEN OTHERS THEN
            IF DBMS_SQL.IS_OPEN(l_cursor_id) THEN
                DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
            END IF;
            RAISE;
    END;
END;
/

2. 实现嵌套OPEN FOR调用的正确方式 #

原生OPEN FOR无法直接执行OPEN :1 FOR :2这类动态PL/SQL语句，需通过DBMS_SQL执行动态PL/SQL块，或先打开内部游标再在外部逻辑中复用。

方式1：先打开内部游标，再构造外部动态SQL

DECLARE
    l_inner_sql VARCHAR2(1024) := 'SELECT * FROM test_table WHERE id = :1';
    l_outer_sql VARCHAR2(1024) := 'SELECT t.name, t.create_time FROM (SELECT * FROM test_table) t WHERE t.id = :1';
    l_params JSON := JSON_ARRAY('1');
    l_inner_cursor SYS_REFCURSOR;
    l_outer_cursor SYS_REFCURSOR;
BEGIN
    -- 打开内部游标
    exec_sql_text(l_inner_sql, l_params, l_inner_cursor);
    -- 打开嵌套逻辑的外部游标
    exec_sql_text(l_outer_sql, l_params, l_outer_cursor);
    
    -- 返回结果集
    DBMS_SQL.RETURN_RESULT(l_inner_cursor);
    DBMS_SQL.RETURN_RESULT(l_outer_cursor);
END;
/

方式2：用DBMS_SQL动态执行打开游标的PL/SQL块

DECLARE
    l_inner_sql VARCHAR2(1024) := 'SELECT * FROM test_table';
    l_cursor_id INTEGER;
    l_ref_cursor SYS_REFCURSOR;
BEGIN
    l_cursor_id := DBMS_SQL.OPEN_CURSOR;
    -- 动态执行打开游标的PL/SQL语句
    DBMS_SQL.PARSE(l_cursor_id, 'BEGIN OPEN :1 FOR ' || l_inner_sql || '; END;', DBMS_SQL.NATIVE);
    DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':1', l_ref_cursor);
    DBMS_SQL.EXECUTE(l_cursor_id);
    DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
    
    -- 返回结果
    DBMS_SQL.RETURN_RESULT(l_ref_cursor);
END;
/

原嵌套代码失败原因 #

原测试代码中open cur for outer_text using cur2, inner_text;的错误在于：OPEN FOR是PL/SQL语句，不能作为动态SQL的字符串内容直接执行，必须通过DBMS_SQL解析执行完整的PL/SQL块，或直接构造目标SQL后打开游标。

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