oracle – PL / SQL如何选择进入表类型的局部变量
发布时间:2021-05-17 21:31:56 所属栏目:站长百科 来源:网络整理
导读:PROCEDURE "ARCHIVE_CASE_LIST"( a_case_id_list IN INLISTNUMBERS)IS l_customers INLISTNUMBERS; INLISTNUMBERS是Oracle数字表; 如何准备预先计算的客户列表并将它们存储在l_customers中,这样我就不需要在其他更新/选择语句中使用那个长选择语句? insert
PROCEDURE "ARCHIVE_CASE_LIST" ( a_case_id_list IN INLISTNUMBERS ) IS l_customers INLISTNUMBERS; INLISTNUMBERS是Oracle数字表; 如何准备预先计算的客户列表并将它们存储在l_customers中,这样我就不需要在其他更新/选择语句中使用那个长选择语句? 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));使用带有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','D'))); (编辑:ASP站长) 【免责声明】本站内容转载自互联网,其相关言论仅代表作者个人观点绝非权威,不代表本站立场。如您发现内容存在版权问题,请提交相关链接至邮箱:bqsm@foxmail.com,我们将及时予以处理。 |
相关内容
未处理完善
-
无相关信息
最新更新