Oracle PL/SQL如何将多行多列数值型数据存入数值集合？

错误原因说明 #

你之前的写法存在两个核心问题：

1. 类型不匹配：emp_ids(emp_id1, emp_id2, emp_id3, emp_id4)是每行生成一个集合对象，但bulk collect into l_emp_ids需要每行返回单个NUMBER类型值来填充集合元素，导致类型冲突。
2. 返回行数过多：去掉bulk collect后，查询返回4行（每行对应一个集合），但into l_emp_ids只能接收单个集合实例，触发行数量不匹配错误。

方法1：用UNPIVOT实现（推荐，纯SQL/PLSQL通用） #

这是最简便的方案，通过UNPIVOT将多列拆为单行数据，直接生成符合要求的集合，或直接关联员工表：

直接关联员工表（无需提前生成集合） #

select e.*
from employees e
join (
    select emp_id
    from matrix_table
    unpivot (
        emp_id for emp_col in (emp_id1, emp_id2, emp_id3, emp_id4)
    )
) m on e.emp_id = m.emp_id;

生成emp_ids集合后再关联 #

declare
    l_emp_ids emp_ids;
begin
    -- 用UNPIVOT拆分行，再批量收集到集合
    select emp_id
    bulk collect into l_emp_ids
    from matrix_table
    unpivot (
        emp_id for emp_col in (emp_id1, emp_id2, emp_id3, emp_id4)
    );

    -- 后续用集合关联员工表
    for emp_rec in (
        select e.*
        from employees e
        join table(l_emp_ids) v on e.emp_id = v.column_value
    ) loop
        -- 自定义业务逻辑
        dbms_output.put_line('员工ID：' || emp_rec.emp_id);
    end loop;
end;
/

方法2：PLSQL循环扩展集合（适合动态列场景） #

如果列数不固定或无法使用UNPIVOT，可以通过循环遍历每行，逐个添加员工ID到集合：

declare
    l_emp_ids emp_ids := emp_ids();
    type matrix_row is record(
        emp_id1 number, emp_id2 number, emp_id3 number, emp_id4 number
    );
    l_row matrix_row;
    cursor c_matrix is select emp_id1, emp_id2, emp_id3, emp_id4 from matrix_table;
begin
    open c_matrix;
    loop
        fetch c_matrix into l_row;
        exit when c_matrix%notfound;

        -- 扩展集合并添加当前行的4个员工ID
        l_emp_ids.extend(4);
        l_emp_ids(l_emp_ids.count - 3) := l_row.emp_id1;
        l_emp_ids(l_emp_ids.count - 2) := l_row.emp_id2;
        l_emp_ids(l_emp_ids.count - 1) := l_row.emp_id3;
        l_emp_ids(l_emp_ids.count) := l_row.emp_id4;
    end loop;
    close c_matrix;

    -- 关联员工表使用
    select e.* bulk collect into ...
    from employees e
    join table(l_emp_ids) v on e.emp_id = v.column_value;
end;
/

方法3：用COLLECT函数直接生成集合 #

利用Oracle的collect聚合函数，直接将拆分行的员工ID聚合为集合：

declare
    l_emp_ids emp_ids;
begin
    select cast(collect(emp_id) as emp_ids)
    into l_emp_ids
    from (
        select emp_id
        from matrix_table
        unpivot (
            emp_id for emp_col in (emp_id1, emp_id2, emp_id3, emp_id4)
        )
    );

    -- 后续关联逻辑
end;
/

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