Oracle存储过程中动态列多关联查询的EXECUTE IMMEDIATE使用问题

你现有代码无法实现需求的核心原因有两点：

1. 你定义的qry_stmt_result_type是单列字符串关联数组，只能存储单字段查询结果，无法承载多列的行结构数据
2. 因为查询列、关联表都是动态的，列数据类型未知，无法预先定义固定结构的行类型，不能直接用EXECUTE IMMEDIATE + BULK COLLECT的硬编码写法，需要使用Oracle原生的DBMS_SQL包处理动态结果集。

正确实现代码 #

CREATE OR REPLACE PROCEDURE RULE_TEST AS 
  qry_stmt CLOB;
  -- DBMS_SQL 核心变量
  v_cursor_id INTEGER;
  v_col_cnt INTEGER;
  v_col_desc DBMS_SQL.DESC_TAB;
  v_fetch_status INTEGER;
  -- 单行数据关联数组：key为列名，value为列值（统一转字符串适配未知类型）
  TYPE row_map_type IS TABLE OF VARCHAR2(32767) INDEX BY VARCHAR2(100);
  -- 结果集关联数组：key为行号，value为单行数据
  TYPE result_set_type IS TABLE OF row_map_type INDEX BY BINARY_INTEGER;
  v_result_set result_set_type;
  v_current_row row_map_type;
  v_col_value VARCHAR2(32767);
BEGIN
  -- 你的动态SQL，已修正原SQL遗漏关联表名的语法错误
  qry_stmt:='SELECT col_1, col_2, col_3, col_N FROM table_x 
            JOIN table_y on table_x.col_1=table_y.col_5
            JOIN table_z on table_y.col_2=table_z.col_3
            WHERE table_z.col_2 = 1';

  -- 1. 打开动态游标
  v_cursor_id := DBMS_SQL.OPEN_CURSOR;
  -- 2. 解析动态SQL
  DBMS_SQL.PARSE(v_cursor_id, qry_stmt, DBMS_SQL.NATIVE);
  -- 3. 执行SQL
  v_fetch_status := DBMS_SQL.EXECUTE(v_cursor_id);
  -- 4. 获取查询列的元数据（列名、类型、长度等）
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor_id, v_col_cnt, v_col_desc);
  
  -- 5. 为所有列定义接收变量，统一按字符串接收适配未知类型
  FOR i IN 1..v_col_cnt LOOP
    DBMS_SQL.DEFINE_COLUMN(v_cursor_id, i, v_col_value, 32767);
  END LOOP;

  -- 6. 逐行读取结果存入关联数组
  LOOP
    v_fetch_status := DBMS_SQL.FETCH_ROWS(v_cursor_id);
    EXIT WHEN v_fetch_status = 0;
    
    v_current_row.DELETE;
    -- 逐列读取存入当前行关联数组
    FOR i IN 1..v_col_cnt LOOP
      DBMS_SQL.COLUMN_VALUE(v_cursor_id, i, v_col_value);
      -- Oracle默认列名大写，直接用列名作为key方便后续按列名取值
      v_current_row(UPPER(v_col_desc(i).col_name)) := v_col_value;
    END LOOP;
    v_result_set(v_result_set.COUNT + 1) := v_current_row;
  END LOOP;

  -- 7. 关闭游标
  DBMS_SQL.CLOSE_CURSOR(v_cursor_id);

  -- 8. 循环遍历结果集，支持按列名直接取值
  FOR i IN v_result_set.FIRST..v_result_set.LAST LOOP
    DBMS_OUTPUT.put_line('===== 第'||i||'行数据 =====');
    -- 遍历输出当前行所有列
    FOR j IN v_result_set(i).FIRST..v_result_set(i).LAST LOOP
      DBMS_OUTPUT.put_line(v_result_set(i).KEY(j) || '：' || v_result_set(i)(v_result_set(i).KEY(j)));
    END LOOP;
    -- 单独取指定列的值，直接用列名作为key即可
    DBMS_OUTPUT.put_line('col_1值：' || v_result_set(i)('COL_1'));
  END LOOP;        

EXCEPTION
  WHEN OTHERS THEN
    -- 异常处理避免游标泄漏
    IF DBMS_SQL.IS_OPEN(v_cursor_id) THEN
      DBMS_SQL.CLOSE_CURSOR(v_cursor_id);
    END IF;
    RAISE;
END RULE_TEST;
/

注意事项 #

• 如果需要保留原始数据类型（比如DATE、NUMBER不转字符串），可以通过v_col_desc(i).col_type判断列类型，分别声明对应类型的变量接收值即可
• Oracle默认将未加双引号的列名转为大写存储，所以取值时key需要用大写，比如v_result_set(i)('COL_1')，如果SQL中用双引号指定了小写列名则对应使用小写key
• 如果结果集体量较大，可以调整为批量fetch模式提升性能，不用逐行读取
• 原SQL的JOIN语法遗漏了关联表名，代码中已经做了修正

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