oracle – 在’IN’子句中使用绑定变量

我想查询一个数字列表到一个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> 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.

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!