oracle PL/SQL 如何选择成表类型的局部变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5611377/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL how do I select into table type local variable
提问by 0xDEAD BEEF
PROCEDURE "ARCHIVE_CASE_LIST"
(
a_case_id_list IN INLISTNUMBERS
)
IS
l_customers INLISTNUMBERS;
INLISTNUMBERS is Oracle table of numbers;
INLISTNUMBERS 是 Oracle 数字表；
How do I prepare pre-calculated list of customers and store them in l_customers, so that I don't need to use that long select statement in other update/select statements?
如何准备预先计算的客户列表并将它们存储在 l_customers 中，以便我不需要在其他更新/选择语句中使用那个长的 select 语句？
insert into table(l_customers) <-- fail
select distinct case1.customer_id into l_customers from case case1
where case1.case_id in (select column_value from table(a_case_id_list)) and
not exists (select 0 from case case2 where case2.customer_id = case1.customer_id and
case2.lifecycle_code not in (code_id('LIFECYCLE','A'), code_id('LIFECYCLE','D')));
update customer set customer.lifecycle_code = code_id('LIFECYCLE','A')
where customer.customer_id in (select column_value from table(l_customers));
open l_persons for
select person_id from person where person.customer_id in
(select column_value from table(l_customers));
回答by Codo
Use a SELECTstatement with BULK COLLECT:
使用带有BULK COLLECT的SELECT语句：
select distinct case1.customer_id bulk collect into l_customers
from case case1
where case1.case_id in (select column_value from table(a_case_id_list)) and
not exists (select 0 from case case2 where case2.customer_id = case1.customer_id and
case2.lifecycle_code not in (code_id('LIFECYCLE','A'), code_id('LIFECYCLE','D')));
