如何移除记录类型嵌套表中的重复值？

错误原因说明 #

你遇到的PLS-00306错误，是因为MULTISET UNION DISTINCT仅支持基于SQL对象类型的集合，而你定义的id_rec_type是PL/SQL本地记录类型，不属于SQL层面可识别的对象类型，因此无法使用该操作符。

以下是几种可行的去重方案：

方案1：从查询源头直接去重（推荐，效率最高） #

既然数据来自数据库表，直接在查询阶段用DISTINCT或GROUP BY去重，避免后续PL/SQL层面的额外处理：

FOR rec IN (SELECT DISTINCT id, value
            FROM   table
            WHERE  active = 'Y' )
LOOP
    loc_id_records.EXTEND;
    loc_id_records(loc_id_records.LAST).id := rec.id;
    loc_id_records(loc_id_records.LAST).value := rec.value;
END LOOP;

或者用GROUP BY实现相同效果：

FOR rec IN (SELECT id, value
            FROM   table
            WHERE  active = 'Y'
            GROUP BY id, value)
LOOP
    -- 填充逻辑同上
END LOOP;

方案2：将PL/SQL记录改为SQL对象类型（支持MULTISET操作） #

如果必须在集合层面去重，可以把PL/SQL记录替换为SQL对象类型，这样就能正常使用MULTISET UNION DISTINCT：

步骤1：创建SQL对象类型 #

可以在包外全局定义，或者在包规范中声明（SQL类型为全局可见）：

CREATE OR REPLACE TYPE id_obj_type AS OBJECT (
    id     NUMBER,
    value  NUMBER
);
/

CREATE OR REPLACE TYPE id_obj_table AS TABLE OF id_obj_type;
/

步骤2：修改存储过程代码 #

DECLARE
    loc_id_records        id_obj_table := id_obj_table();
    p_id_records          id_obj_table := id_obj_table();
BEGIN
    FOR rec IN (SELECT id, value
                FROM   table
                WHERE  active = 'Y' )
    LOOP
        loc_id_records.EXTEND;
        loc_id_records(loc_id_records.LAST) := id_obj_type(rec.id, rec.value);
    END LOOP;

    -- 执行去重操作
    p_id_records := id_obj_table() MULTISET UNION DISTINCT loc_id_records;
END;

方案3：手动遍历嵌套表去重（保留原PL/SQL记录类型） #

如果不想修改原有类型定义，可以手动遍历集合，用新集合存储不重复元素：

DECLARE
    loc_id_records        id_record := id_record();
    p_id_records          id_record := id_record();
    v_exists BOOLEAN;
BEGIN
    -- 填充原始数据
    FOR rec IN (SELECT id, value
                FROM   table
                WHERE  active = 'Y' )
    LOOP
        loc_id_records.EXTEND;
        loc_id_records(loc_id_records.LAST).id := rec.id;
        loc_id_records(loc_id_records.LAST).value := rec.value;
    END LOOP;

    -- 手动去重逻辑
    FOR i IN loc_id_records.FIRST .. loc_id_records.LAST LOOP
        v_exists := FALSE;
        -- 检查目标集合是否已有相同记录
        FOR j IN p_id_records.FIRST .. p_id_records.LAST LOOP
            IF p_id_records(j).id = loc_id_records(i).id 
               AND p_id_records(j).value = loc_id_records(i).value THEN
                v_exists := TRUE;
                EXIT;
            END IF;
        END LOOP;
        -- 无重复则添加到目标集合
        IF NOT v_exists THEN
            p_id_records.EXTEND;
            p_id_records(p_id_records.LAST) := loc_id_records(i);
        END IF;
    END LOOP;
END;

注意：该方法适合小数据集，大数据量下双重循环效率较低。

方案4：用TABLE函数结合SQL去重（Oracle 12c+） #

通过管道函数将PL/SQL集合转为SQL可查询的表，再用DISTINCT去重：

步骤1：在包内定义管道函数 #

PACKAGE your_package IS
    TYPE id_rec_type IS RECORD (
        id     NUMBER,
        value  NUMBER
    );
    TYPE id_record IS TABLE OF id_rec_type;

    FUNCTION get_distinct_records(p_records id_record) RETURN id_record PIPELINED;
END your_package;
/

PACKAGE BODY your_package IS
    FUNCTION get_distinct_records(p_records id_record) RETURN id_record PIPELINED IS
        TYPE temp_rec IS RECORD (id NUMBER, value NUMBER);
        TYPE temp_table IS TABLE OF temp_rec;
        v_temp temp_table := temp_table();
    BEGIN
        -- 转换为临时集合
        FOR i IN p_records.FIRST .. p_records.LAST LOOP
            v_temp.EXTEND;
            v_temp(v_temp.LAST).id := p_records(i).id;
            v_temp(v_temp.LAST).value := p_records(i).value;
        END LOOP;

        -- SQL层面去重后返回
        FOR rec IN (SELECT DISTINCT id, value FROM TABLE(v_temp)) LOOP
            PIPE ROW(id_rec_type(rec.id, rec.value));
        END LOOP;
        RETURN;
    END get_distinct_records;
END your_package;
/

步骤2：在存储过程中调用 #

DECLARE
    loc_id_records        id_record := id_record();
    p_id_records          id_record := id_record();
BEGIN
    -- 填充原始数据
    FOR rec IN (SELECT id, value
                FROM   table
                WHERE  active = 'Y' )
    LOOP
        loc_id_records.EXTEND;
        loc_id_records(loc_id_records.LAST).id := rec.id;
        loc_id_records(loc_id_records.LAST).value := rec.value;
    END LOOP;

    -- 调用函数去重
    SELECT * BULK COLLECT INTO p_id_records 
    FROM TABLE(your_package.get_distinct_records(loc_id_records));
END;

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