如何获取Oracle内联视图列及数据类型并动态转换为嵌套表对象？

1. 定义参数对象与嵌套表类型 #

先创建自定义的参数对象和对应的嵌套表类型，后续新增参数无需修改类型结构：

-- 单个参数的对象类型
CREATE OR REPLACE TYPE param_obj AS OBJECT (
  param_key VARCHAR2(100),
  param_value VARCHAR2(2000)
);
/

-- 存储多参数的嵌套表类型
CREATE OR REPLACE TYPE param_tab AS TABLE OF param_obj;
/

2. 编写动态提取参数的函数 #

利用DBMS_SQL解析内联视图的元数据（列名+对应值），自动生成键值对存入嵌套表，绕过user_tab_columns无法读取内联视图元数据的限制：

CREATE OR REPLACE FUNCTION get_params(p_sql IN VARCHAR2) RETURN param_tab IS
  v_param_tab param_tab := param_tab();
  v_cursor INTEGER;
  v_col_count INTEGER;
  v_desc DBMS_SQL.DESC_TAB;
  v_value VARCHAR2(2000);
BEGIN
  v_cursor := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cursor, p_sql, DBMS_SQL.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_count, v_desc);

  -- 遍历所有列，提取列名与对应值
  FOR i IN 1..v_col_count LOOP
    DBMS_SQL.DEFINE_COLUMN(v_cursor, i, v_value, 2000);
    IF DBMS_SQL.EXECUTE_AND_FETCH(v_cursor) > 0 THEN
      DBMS_SQL.COLUMN_VALUE(v_cursor, i, v_value);
      v_param_tab.EXTEND;
      v_param_tab(v_param_tab.LAST) := param_obj(v_desc(i).col_name, v_value);
    END IF;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(v_cursor);
  RETURN v_param_tab;
END;
/

3. 替换原内联视图链的调用方式 #

原内联视图写法示例：

WITH params AS (
  SELECT '2024-01-01' AS start_date, '2024-06-30' AS end_date, 'SALES' AS report_type FROM DUAL
)
SELECT * FROM your_report_sql CROSS JOIN params;

现在改用函数调用，直接传入内联视图的SQL字符串即可自动生成参数嵌套表，新增参数时只需修改传入的SQL，无需调整函数或Unpivot逻辑：

DECLARE
  v_params param_tab;
BEGIN
  -- 传入原内联视图SQL，获取参数集合
  v_params := get_params('SELECT ''2024-01-01'' AS start_date, ''2024-06-30'' AS end_date, ''SALES'' AS report_type FROM DUAL');
  
  -- 报表SQL可通过TABLE()函数展开参数使用
  FOR rec IN (SELECT * FROM TABLE(v_params)) LOOP
    DBMS_OUTPUT.PUT_LINE(rec.param_key || ': ' || rec.param_value);
  END LOOP;
END;
/

4. 性能优化提示 #

如果担心动态SQL的硬解析问题，可以将常用的参数SQL封装成存储过程，或者在函数中加入绑定变量逻辑，减少重复解析开销。

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