oracle - Oracle 将结果集放入 FORALL 中的变量中
问题描述
我有以下 plsql 块
declare
TYPE t_mds_ids IS TABLE OF mds.id%TYPE;
l_mds_ids t_mds_ids;
l_mds_parents t_mds_parents;
begin
SELECT id BULK COLLECT INTO l_mds_ids FROM mds;
FORALL indx IN l_mds_ids.FIRST .. l_mds_ids.LAST
select l_mds_ids(indx), ch.id_employee_parent
into l_mds_parents
FROM hierarchy_all ch
CONNECT BY ch.id_employee = prior ch.id_employee_parent
START WITH ch.id_employee = l_mds_ids(indx);
EXECUTE IMMEDIATE 'truncate table mds_hierarchy_all';
insert into mds_hierarchy_all
select * from l_mds_parents;
end;
t_mds_parents 声明为
create or replace type r_mds_parents as object (
id_mds number(5,0),
id_employee number(5,0)
);
/
create or replace type t_mds_parents as table of r_mds_parents;
/
我得到一个异常 ORA-00947:没有足够的值
我真的需要在 FORALL 循环的每次迭代中将多行的结果集放入 TABLE TYPE 的变量中。我不能在 l_mds_parents 中使用 BULK COLLECT,因为它在 FORALL 内部受到限制。是否只有使用临时表而不是表变量的解决方案?
解决方案
我不认为你可以用forall
. 您可以使用嵌套循环:
declare
TYPE t_mds_ids IS TABLE OF mds.id%TYPE;
l_mds_ids t_mds_ids;
l_mds_parents t_mds_parents;
begin
SELECT id BULK COLLECT INTO l_mds_ids FROM mds;
l_mds_parents := NEW t_mds_parents();
FOR indx IN l_mds_ids.FIRST .. l_mds_ids.LAST LOOP
FOR rec IN (
select l_mds_ids(indx) as id_employee, ch.id_employee_parent
FROM hierarchy_all ch
CONNECT BY ch.id_employee = prior ch.id_employee_parent
START WITH ch.id_employee = l_mds_ids(indx)
) LOOP
l_mds_parents.extend();
l_mds_parents(l_mds_parents.COUNT)
:= NEW r_mds_parents (rec.id_employee, rec.id_employee_parent);
END LOOP;
END LOOP;
EXECUTE IMMEDIATE 'truncate table mds_hierarchy_all';
insert into mds_hierarchy_all
select * from table(l_mds_parents);
end;
/
但是你根本不需要使用 PL/SQL;使用单个分层查询,或者在这里可能更简单,递归子查询分解:
insert into mds_hierarchy_all /* (id_mds, id_employee) -- better to list columns */
with rcte (id_mds, id_employee) as (
select m.id, ha.id_employee_parent
from mds m
join hierarchy_all ha on ha.id_employee = m.id
union all
select r.id_mds, ha.id_employee_parent
from rcte r
join hierarchy_all ha on ha.id_employee = r.id_employee
)
select * from rcte;
db<> 摆弄一些虚构的数据。
推荐阅读
- python - My list prints out the Objects instead of the values
- python-3.x - Reshaping 1-D Data to 2-D Data for Matplotlib Pcolormesh
- php - PHP parse array to get index wise result
- python - Scrapy - multiple spiders - processing data from one spider while other are still running
- apache-spark - PySpark 中的 ModuleNotFoundError 在 serializers.py 中引起
- python - bad key error occurred when tried to fetch data from api endpoint in python?
- apache-flink - Supported data types in Flink SQL
- apache-kafka - Kafka Schema Registry JSON Schema Restproxy 问题
- python - Python模块(pyenv)安装不起作用
- flutter - 如何在颤振中使用电报 api 创建消息模块