使用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.