Oracle中如何获取CTE(公共表表达式)的列名？

1. 是否存在可直接返回CTE列名、满足示例用法的函数f？ #

Oracle没有提供能直接嵌入同一条SQL语句、传入cte_name.*就返回对应列名的内置函数。
你示例中期望的select f(a.*) from a写法无法直接实现：SQL引擎在解析语句时会把a.*直接展开为CTE对应的所有列名列表，自定义函数或内置函数都无法在取值阶段拿到a.*对应的原始元数据集合，函数接收到的只会是展开后的具体列值，而非列的定义信息。

2. CTE是否会被存储在特定位置？有没有等价于USER_TAB_COLUMNS的CTE列查询视图？ #

CTE是单条SQL执行范围内的临时逻辑结构，不会持久化存储在数据字典中，也不存在和USER_TAB_COLUMNS等价、可直接查询CTE列信息的系统视图，具体分两种场景：

• 未添加/*+ MATERIALIZE */提示的CTE：Oracle优化器会直接将CTE逻辑合并到主查询中执行，不会生成独立的存储对象，SQL执行完成后不会留下任何可追溯的存储记录。
• 添加了/*+ MATERIALIZE */提示的CTE：Oracle执行时会在临时表空间生成语句/会话级的临时段来存储CTE结果，但这个临时段是系统自动生成的内部对象，命名为随机格式（如SYS_TEMP_xxxxxx），仅在SQL执行过程中短暂存在，执行结束后会自动回收，且其定义不会写入常规的数据字典视图，无法通过类似查普通表的方式直接获取列信息。

3. 获取CTE列名的可行方案 #

如果需要拿到CTE的列名，可以通过DBMS_SQL包解析目标SQL的游标来实现，示例代码如下：

DECLARE
  v_cursor  INTEGER := DBMS_SQL.OPEN_CURSOR;
  v_col_cnt INTEGER;
  v_cols    DBMS_SQL.DESC_TAB;
BEGIN
  -- 传入包含目标CTE的完整SQL语句
  DBMS_SQL.PARSE(
    v_cursor,
    'with a as (select 1 a from dual) select a.* from a',
    DBMS_SQL.NATIVE
  );
  -- 解析游标拿到列元数据
  DBMS_SQL.DESCRIBE_COLUMNS(v_cursor, v_col_cnt, v_cols);
  -- 遍历输出列信息，和预期格式一致
  FOR i IN 1..v_col_cnt LOOP
    DBMS_OUTPUT.PUT_LINE('column'||i||'='||v_cols(i).col_name);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(v_cursor);
END;
/

执行上述代码即可输出你期望的结果：column1=a。

内容的提问来源于stack exchange，提问作者Pierre-olivier Gendraud