我想查询一个数字列表到一个plsql变量,并在另一个SQL查询的in子句中使用它.我在下面创建了一个我想做的测试用例.
我做谷歌的解决方案,我认为它必须可能以某种方式,但我只是不让它运行.请帮我解决一下编译解决方案.
CREATE OR REPLACE PROCEDURE PROCEDURE1 as type t_id is table of number; v_ids t_id; v_user_ids number; BEGIN -- fill variable v_id with id's,user_id is of type number select user_id bulk collect into v_ids from user_users; -- then at a later stage ... issue a query using v_id in the in clause select user_id into v_user_ids from user_users -- this line does not compile ( local collection type not allowed in sql statements) where user_id in ( v_ids ); END PROCEDURE1;
解决方法
使用sql类型：
sql> create type t_id is table of number; 2 / Type created. sql> CREATE OR REPLACE PROCEDURE PROCEDURE1 2 as 3 v_ids t_id; 4 v_user_ids number; 5 BEGIN 6 7 -- fill variable v_id with id's,user_id is of type number 8 select user_id 9 bulk collect into v_ids 10 from user_users 11 where user_id between 100 and 120; 12 13 select user_id into v_user_ids 14 from user_users 15 where user_id in (select /*+ cardinality(t,10) */ t.column_value from table(v_ids) t) 16 and rownum = 1; 17 18 dbms_output.put_line(v_user_ids); 19 20 END PROCEDURE1; 21 / Procedure created. sql> exec procedure1 100
其中基数(t,10)应该是对数组中有多少元素的合理猜测.
注意：
像你一样使用无限制的批量收集：
8 select user_id 9 bulk collect into v_ids 10 from user_users;
如果你的数组最终会有数千或更多的行,那么你通常不会很好,因为你对内存施加了太大的压力并最终导致代码崩溃.你可以更好地使用显式游标open x for ..和一个带有limit子句的循环中的批量提取,即fetch x bulk collect到v_ids limit 100并分批处理100-1000.
