Oracle多态表函数传入两个TABLE参数报PLS-00766的解决方法

问题描述 #

参考多态表函数官方说明，我在开发名为skip_col_not_in_model的多态表函数，目标是移除目标表中与参考模型表不匹配的列，仅保留两表的同名列。编写的PL/SQL包代码如下：

CREATE OR REPLACE PACKAGE pkg_skip_col
AS
    FUNCTION skip_col (tab TABLE, col COLUMNS)
        RETURN TABLE
        PIPELINED ROW POLYMORPHIC USING pkg_skip_col;

    FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T, col DBMS_TF.COLUMNS_T)
        RETURN DBMS_TF.DESCRIBE_T;

    FUNCTION skip_col_not_in_model (tab TABLE, tabk_model TABLE)
        RETURN TABLE
        PIPELINED ROW POLYMORPHIC USING pkg_skip_col;

    FUNCTION describe (tab         IN OUT DBMS_TF.TABLE_T,
                       tab_model   IN     DBMS_TF.TABLE_T)
        RETURN DBMS_TF.DESCRIBE_T;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_skip_col
AS

    FUNCTION describe (tab         IN OUT DBMS_TF.TABLE_T,
                       tab_model   IN     DBMS_TF.TABLE_T)
        RETURN DBMS_TF.DESCRIBE_T
    AS
    BEGIN
        FOR i IN 1 .. tab.column.COUNT ()
        LOOP
            FOR j IN 1 .. tab_model.column.COUNT ()
            LOOP
                tab.column (i).PASS_THROUGH :=
                    tab.column (i).DESCRIPTION.NAME = tab_model.column (i).DESCRIPTION.NAME ;
                EXIT WHEN tab.column (i).PASS_THROUGH;
            END LOOP;
        END LOOP;

        RETURN NULL;
    END;

    FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T, col DBMS_TF.COLUMNS_T)
        RETURN DBMS_TF.DESCRIBE_T
    AS
    BEGIN
        FOR i IN 1 .. tab.column.COUNT ()
        LOOP
            FOR j IN 1 .. col.COUNT ()
            LOOP
                tab.column (i).PASS_THROUGH :=
                    tab.column (i).DESCRIPTION.NAME != col (j);
                EXIT WHEN NOT tab.column (i).PASS_THROUGH;
            END LOOP;
        END LOOP;

        RETURN NULL;
    END;

END;
/

当前包体可以正常编译，但包规范编译失败，抛出如下错误：

[Error] Compilation (24: 59): PLS-00766: more than one parameter of TABLE type is not allowed

目前单表搭配COLUMNS参数的多态函数可以正常运行，参考用例如下：

WITH a (a1,a2) AS (SELECT 1,2 FROM dual) SELECT * FROM 
pkg_skip_col.skip_col(a,COLUMNS(a1))

返回结果：a1:1，符合预期

我期望实现的效果是传入两个表，自动筛选保留两表的同名列，参考用例如下：

WITH a (a1,a2) AS (SELECT 1,2 FROM dual),
       b (a1,a2,a3) AS (SELECT 1,2,3 FROM dual)
SELECT * FROM  pkg_skip_col.skip_col_not_in_model(a,b)

期望返回结果仅包含a1、a2列，对应值为a1:1, a2: 2

问题排查 #

经确认，报错根源是Oracle多态表函数不支持传入两个TABLE类型参数。我曾考虑将模型表以字符串形式传入，通过查询all_tab_columns视图获取列信息实现逻辑，但该方案代码可读性差，且无法支持公用表表达式（CTE）场景。

可行解决方案 #

Oracle 19c及以上版本的多态表函数确实仅允许一个TABLE类型入参，要实现双表同名列筛选且支持CTE场景，可以通过语义分析获取模型表列元数据+COLUMNS参数隐式传递的方式实现，不需要传表名字符串：

• 调整函数入参：仅保留第一个TABLE类型作为待处理目标表，第二个模型表的列通过COLUMNS算子传入，COLUMNS支持传入任意表的所有列，且能在describe阶段拿到列名，完全不依赖数据字典。
• 修正原有代码的逻辑bug：原代码中遍历模型表列时错误使用了tab_model.column(i)，应该用tab_model.column(j)，否则列序号匹配错误。

修正后的可运行代码如下：

CREATE OR REPLACE PACKAGE pkg_skip_col
AS
    -- 原有单表跳过指定列的函数保留
    FUNCTION skip_col (tab TABLE, col COLUMNS)
        RETURN TABLE
        PIPELINED ROW POLYMORPHIC USING pkg_skip_col;

    FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T, col DBMS_TF.COLUMNS_T)
        RETURN DBMS_TF.DESCRIBE_T;

    -- 调整入参：模型表列通过COLUMNS传入，不使用第二个TABLE参数
    FUNCTION skip_col_not_in_model (tab TABLE, model_cols COLUMNS)
        RETURN TABLE
        PIPELINED ROW POLYMORPHIC USING pkg_skip_col;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_skip_col
AS
    FUNCTION describe (tab IN OUT DBMS_TF.TABLE_T, col DBMS_TF.COLUMNS_T)
        RETURN DBMS_TF.DESCRIBE_T
    AS
        v_pass BOOLEAN;
    BEGIN
        FOR i IN 1 .. tab.column.COUNT LOOP
            v_pass := FALSE;
            -- 遍历传入的列，匹配到同名列则保留
            FOR j IN 1 .. col.COUNT LOOP
                IF tab.column(i).DESCRIPTION.NAME = col(j) THEN
                    v_pass := TRUE;
                    EXIT;
                END IF;
            END LOOP;
            tab.column(i).PASS_THROUGH := v_pass;
        END LOOP;
        RETURN NULL;
    END;
END;
/

调用方式如下，完全支持CTE场景，不需要传表名：

WITH a (a1,a2) AS (SELECT 1,2 FROM dual),
     b (a1,a2,a3) AS (SELECT 1,2,3 FROM dual)
SELECT * FROM pkg_skip_col.skip_col_not_in_model(a, COLUMNS(b.*))

执行后会自动保留a表和b表的同名列a1、a2，返回结果符合预期。COLUMNS(b.*)的写法足够直观，且完全规避了多TABLE参数的限制，同时支持CTE、临时表、子查询等所有场景，不依赖数据字典查询。

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