Utilisation de la variable bind dans la clause 'IN'.

Question

Je veux interroger une liste de chiffres dans une variable plsql et l'utiliser dans une clause in dans une autre requête sql. J'ai créé un testcase ci-dessous de ce que je veux faire.

J'ai cherché la solution sur Google et je pense que cela doit être possible d'une manière ou d'une autre, mais je n'arrive pas à la faire fonctionner. Veuillez m'aider à trouver une solution de compilation.

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;

Answer 1

En utilisant un type 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

où cardinality(t, 10) devrait être une estimation raisonnable du nombre d'éléments de votre tableau.

note : si vous utilisez un collecteur de masse non borné comme vous l'avez fait :

  8    select user_id
  9    bulk collect into v_ids
 10    from user_users;

n'est généralement pas une bonne idée si votre tableau peut contenir plusieurs milliers de lignes ou plus, car vous exercez une pression trop forte sur la mémoire et finirez par faire planter le code. Il est préférable d'utiliser un curseur explicite. open x for .. et une recherche en masse dans une boucle avec la clause de limite c'est-à-dire fetch x bulk collect into v_ids limit 100 et les traiter par lots de 100 à 1000.