能否创建返回嵌套记录类型表的流水线表函数？

你遇到的核心问题是：Oracle的SQL层不支持将包含嵌套记录类型的集合作为流水线函数的返回类型——PL/SQL内部可以定义这类嵌套记录和表类型，但流水线函数需要返回SQL可见的类型，而嵌套记录类型仅在PL/SQL中可见，无法被SQL识别，因此创建包时会报错。

替代方案：使用SQL对象类型替代PL/SQL记录类型 #

SQL对象类型是数据库级别的可见类型，支持嵌套定义，对应的集合类型可以作为流水线函数的返回值，完美解决重复定义结构的问题。

步骤1：定义嵌套的SQL对象类型 #

CREATE OR REPLACE TYPE ra1_obj AS OBJECT (
    one INTEGER,
    two INTEGER
);
/

CREATE OR REPLACE TYPE ra2_obj AS OBJECT (
    r1 ra1_obj,
    three INTEGER,
    four INTEGER  -- 修正原代码拼写错误：fore → four
);
/

CREATE OR REPLACE TYPE ta1_obj AS TABLE OF ra1_obj;
/

CREATE OR REPLACE TYPE ta2_obj AS TABLE OF ra2_obj;
/

步骤2：创建包含流水线函数的包 #

CREATE OR REPLACE PACKAGE pa AS
    FUNCTION pa1 RETURN ta1_obj PIPELINED;
    FUNCTION pa2 RETURN ta2_obj PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY pa AS
    FUNCTION pa1 RETURN ta1_obj PIPELINED IS
    BEGIN
        -- 示例逻辑：生成ra1_obj数据
        PIPE ROW(ra1_obj(1, 2));
        PIPE ROW(ra1_obj(3, 4));
        RETURN;
    END pa1;

    FUNCTION pa2 RETURN ta2_obj PIPELINED IS
        v_ra1 ra1_obj;
    BEGIN
        -- 复用pa1的结果作为嵌套对象
        FOR rec IN (SELECT * FROM TABLE(pa1)) LOOP
            v_ra1 := ra1_obj(rec.one, rec.two);
            PIPE ROW(ra2_obj(v_ra1, 5, 6));
            PIPE ROW(ra2_obj(v_ra1, 7, 8));
        END LOOP;
        RETURN;
    END pa2;
END;
/

验证调用 #

在SQL中可以直接查询流水线函数的结果，包括嵌套对象的字段：

-- 查询pa1的结果
SELECT t.one, t.two FROM TABLE(pa.pa1) t;

-- 查询pa2的嵌套结果
SELECT t.r1.one, t.r1.two, t.three, t.four FROM TABLE(pa.pa2) t;

适配你的分步逻辑需求 #

针对你需要拆分WITH子句为带参数的分步函数的场景，用SQL对象类型可以轻松实现逻辑复用，无需重复定义结构：

示例：分步函数的实现 #

-- 定义第一步的对象类型和表类型
CREATE OR REPLACE TYPE t_step1 AS OBJECT (
    id INTEGER,
    val VARCHAR2(100)
);
/
CREATE OR REPLACE TYPE t_step1_tab AS TABLE OF t_step1;
/

-- 定义第二步的对象类型（嵌套第一步的对象）
CREATE OR REPLACE TYPE t_step2 AS OBJECT (
    step1_data t_step1,
    other_f1 NUMBER,
    other_f2 DATE
);
/
CREATE OR REPLACE TYPE t_step2_tab AS TABLE OF t_step2;
/

-- 实现分步函数
CREATE OR REPLACE PACKAGE step_pkg AS
    FUNCTION f_step1(p_arg INTEGER) RETURN t_step1_tab PIPELINED;
    FUNCTION f_step2(p_arg INTEGER) RETURN t_step2_tab PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY step_pkg AS
    FUNCTION f_step1(p_arg INTEGER) RETURN t_step1_tab PIPELINED IS
    BEGIN
        -- 模拟第一步的业务逻辑，使用参数p_arg过滤
        FOR rec IN (
            SELECT id, val FROM some_table WHERE filter_col = p_arg
        ) LOOP
            PIPE ROW(t_step1(rec.id, rec.val));
        END LOOP;
        RETURN;
    END f_step1;

    FUNCTION f_step2(p_arg INTEGER) RETURN t_step2_tab PIPELINED IS
    BEGIN
        -- 复用f_step1的结果，关联其他表
        FOR rec IN (
            SELECT 
                t_step1(s1.id, s1.val) AS step1_data,
                o.f1, o.f2
            FROM TABLE(f_step1(p_arg)) s1
            JOIN other_table o ON s1.id = o.ref_id
        ) LOOP
            PIPE ROW(t_step2(rec.step1_data, rec.f1, rec.f2));
        END LOOP;
        RETURN;
    END f_step2;
END;
/

最终查询调用 #

你可以直接在SQL中访问嵌套对象的字段，配合你的skip函数使用：

SELECT 
    skip(t.step1_data.id, t.step1_data.val) AS step1_cols,
    skip(t.other_f1, t.other_f2) AS step2_cols
FROM TABLE(step_pkg.f_step2(123)) t;

为什么不推荐重复定义记录结构？ #

重复定义会导致维护成本飙升——一旦底层结构需要修改，所有重复的定义都要同步更新，极易出现不一致的问题。使用SQL对象类型可以做到一次定义，多处复用，完全符合你的需求。

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