Oracle单参数返回表函数通过UNION ALL返回多列值的最优实现

核心优化逻辑：原写法三次查询同一张表同一入参过滤的行，存在重复IO浪费，以下方案均在完全对齐原逻辑输出结果的前提下，减少扫表次数。 #

方案1：单次扫表行转列（性能最优，推荐） #

仅对目标行做一次查询，通过行转列直接输出三个字段值，无冗余扫描。

前置准备（仅需执行一次） #

创建SQL层可用的嵌套表类型，用于函数返回：

CREATE OR REPLACE TYPE num_list_t IS TABLE OF NUMBER;
/

函数实现（兼容Oracle 9i及以上所有版本） #

CREATE OR REPLACE FUNCTION get_vals (p_function_parameter IN NUMBER)
RETURN num_list_t PIPELINED
IS
BEGIN
  FOR rec IN (
    -- 单次扫描目标数据，通过常量行关联实现行转列
    SELECT 
      CASE lv.lv
        WHEN 1 THEN t.column_1
        WHEN 2 THEN t.column_2
        WHEN 3 THEN t.column_3
      END AS val
    FROM my_table t
    CROSS JOIN (
      SELECT 1 lv FROM DUAL 
      UNION ALL SELECT 2 FROM DUAL 
      UNION ALL SELECT 3 FROM DUAL
    ) lv
    WHERE t.id = p_function_parameter
    -- 如需固定输出顺序为1、2、3，可开启下一行
    -- ORDER BY lv.lv
  )
  LOOP
    PIPE ROW (rec.val);
  END LOOP;
  RETURN;
END;
/

若使用Oracle 11g及以上版本，行转列部分可替换为内置UNPIVOT语法，代码更简洁：

SELECT val
FROM my_table t
UNPIVOT (val FOR col IN (column_1, column_2, column_3))
WHERE t.id = p_function_parameter

调用方式 #

SELECT * FROM TABLE(get_vals(你的入参值));

方案2：CTE预查询（兼容原逻辑结构，便于扩展） #

如果需要保留原有UNION ALL的代码结构（后续可能新增列、调整列取值逻辑），可先通过CTE取出一次目标行数据，后续UNION直接基于内存结果计算，避免三次扫表。

CREATE OR REPLACE FUNCTION get_vals (p_function_parameter IN NUMBER)
RETURN num_list_t PIPELINED
IS
BEGIN
  FOR rec IN (
    WITH base AS (
      -- 仅一次过滤查询目标行
      SELECT column_1, column_2, column_3 
      FROM my_table 
      WHERE id = p_function_parameter
    )
    SELECT column_1 AS VAL FROM base
    UNION ALL
    SELECT column_2 AS VAL FROM base
    UNION ALL
    SELECT column_3 AS VAL FROM base
  )
  LOOP
    PIPE ROW (rec.VAL);
  END LOOP;
  RETURN;
END;
/

注意事项 #

• 若my_table的id非唯一，上述两种方案的输出结果和原三次UNION ALL写法完全一致，会输出所有匹配行的三个字段值。
• 函数入参的类型需要和my_table.id的实际字段类型保持一致。
• Oracle不保证查询输出的默认顺序，如需固定VAL的输出顺序为1、2、3，请在查询最后添加对应排序字段的ORDER BY语句。
• 若使用Oracle 12c及以上版本，无需预先创建存储函数，可直接在查询中用WITH FUNCTION语法临时定义逻辑，减少数据库对象管理成本，示例：

  WITH FUNCTION get_vals(p_id IN NUMBER) RETURN num_list_t IS
  BEGIN
    FOR rec IN (
      SELECT column_1 AS val FROM my_table WHERE id = p_id
      UNION ALL
      SELECT column_2 AS val FROM my_table WHERE id = p_id
      UNION ALL
      SELECT column_3 AS val FROM my_table WHERE id = p_id
    ) LOOP
      PIPE ROW(rec.val);
    END LOOP;
    RETURN;
  END;
  SELECT * FROM TABLE(get_vals(123));
  

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