Oracle de la collection dans la clause where

Je suis à l'aide de la collection dans un oracle bloc de code, car il n'y a pas de table variable(comme dans MS SQL Server). 

DECLARE
    TYPE I_NAME IS TABLE OF NVARCHAR2(512);     
    I_ITEMNAME      I_NAME := I_NAME(); 
BEGIN

Je suis à l'aide de "VRAC RECUEILLIR DANS I_ITEMNAME" pour combler la collection.

Je veux utiliser cette collection dans la clause where dans une requête SELECT, mais pas en mesure de trouver la méthode pour le faire. Actuellement je l'ai et de l'aide POUR la boucle et l'obtention de l'élément un par un.

Comment puis-je utiliser de la collection directement dans la clause where quelque chose comme

SÉLECTIONNEZ * À PARTIR DE TBL OÙ LE COL À I_ITEMNAME?

Merci,

InformationsquelleAutor par | 2013-04-24
  1. 12

    Vous ne pouvez pas utiliser déclarés localement collection dans une clause SQL:

    declare
    type i_name is table of nvarchar2(512);
    i_itemname i_name := i_name();
    c number;
begin
    select distinct owner bulk collect into i_itemname from all_objects;
    dbms_output.put_line(i_itemname.count);
    select count(*) into c
    from all_tables
    where owner in (select * from table(i_itemname));
    dbms_output.put_line(c);
end;
/

    where owner in (select * from table(i_itemname));
                                        *
ERROR at line 10:
ORA-06550: line 10, column 41:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 10, column 35:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored

    Mais vous pouvez si elle est déclarée dans le schéma de niveau, essentiellement afin de SQL connaît le type, pas seulement PL/SQL:

    create type i_name is table of nvarchar2(512);
/

Type created.

declare
    i_itemname i_name := i_name();      
    c number;
begin 
    select distinct owner bulk collect into i_itemname from all_objects;
    dbms_output.put_line(i_itemname.count);
    select count(*) into c from all_tables
    where owner in (select * from table(i_itemname));
    dbms_output.put_line(c);
end;
/

No errors.
18
128

PL/SQL procedure successfully completed.

    Vous pouvez aussi rejoindre le table construire plutôt que d'utiliser une sous-requête:

    ...
    select count(*) into c
    from table(i_itemname) t
    join all_tables at on at.owner = t.column_value;
...

    Je ne suis pas tout à fait clair ce que vous êtes dong si. (Si vous n'êtes pas à l'aide de la collection pour autre chose, vous feriez mieux de rejoindre les données brutes, mais je suppose que la collection est là pour une bonne raison).

    Comme @haki mentionné dans les commentaires, vous pouvez aussi le faire:

    ...
    select count(*) into c
    from all_tables
    where owner member of (i_itemname);
...

    ... tant que i_name et de la colonne que vous êtes en comparant avec sont du même type. Dans mon exemple, il trouve des lignes nulles parce que je suis en train de comparer nvarchar2 avec varchar2, mais serait de trouver un match si redéfini i_name comme varchar2(512). Dans votre cas, sans doute tab.col est nvarchar2 de toute façon.

    • +1 je pense que vous pouvez également l'utiliser comme ceci select count(*) from all_tables where owner member of (i_itemname).
    • sympa, je ne pense pas que j'ai jamais utilisé ça. Pour mon exemple, il ne trouve pas les matches parce que le type ne correspond pas - j'ai i_type comme nvarchar2 mais owner est varchar2, mais c'est chouette si i_type est redéfini de façon à correspondre. (J'ai ajouté qu'à ma réponse, mais si vous publiez votre propre réponse avec qui je vais l'enlever; ne cherche pas à prendre de crédit!)
    • c'est une parfaite explication , pour de meilleures performances - si votre taille de la collection va être énorme, il est préférable d'avoir le type indexées pour l'ex - create type i_name est le tableau de numéro index par PLS_INTEGER;
    InformationsquelleAutor Alex Poole