La comparaison de la liste de valeurs par rapport à la table

J'ai essayé de trouver une solution à ce problème pour un certain temps, mais sans succès, de sorte que toute aide serait grandement appréciée. Liste d'Id doit être comparée à une table et de trouver les enregistrements qui existent (et l'un de leurs valeurs) et qui sont inexistants.
Il y a une liste d'Identifiants, au format texte:

100,
200,
300

une table DB:

ID(PK)   value01 value02 value03 .....
--------------------------------------
100       Ann
102       Bob
300       John
304       Marry
400       Jane

et de sortie, j'ai besoin de:

100 Ann
200 missing or empty or whatever indication
300 John

Solution la plus évidente consiste à créer une table et de le rejoindre, mais j'ai uniquement un accès en lecture (DB est fermé le fournisseur du produit, je suis juste un utilisateur). L'écriture d'une fonction PL/SQL semble aussi compliqué parce que le tableau a 200+ colonnes et 100k+ enregistrements et j'ai pas eu de chance avec la création d'un tableau dynamique d'enregistrements. Aussi, la liste des Identifiants à être vérifié contient des centaines d'Identifiants et j'ai besoin de le faire régulièrement de sorte que toute solution où chaque ID doit être changé en séparer ligne de code n'est pas très utile.
Une base de données Oracle 10g.

InformationsquelleAutor ffflyer | 2013-02-14
  1. 1

    il y a beaucoup construit en public les types de collection. vous pouvez tirer parti de l'un d'eux comme ceci:

    with ids as (select /*+ cardinality(a, 1) */ column_value id
               from table(UTL_NLA_ARRAY_INT(100, 200, 300)) a
            )
select ids.id, case when m.id is null then '**NO MATCH**' else m.value end value
  from ids
         left outer join my_table m
                     on m.id = ids.id;

    pour voir une liste de types publics sur votre base de données, exécutez :

    select owner, type_name, coll_type, elem_type_name, upper_bound, precision, scale from all_coll_types
 where elem_type_name in ('FLOAT', 'INTEGER', 'NUMBER', 'DOUBLE PRECISION')

    l'indice

    /*+ cardinality(a, 1) */

    sert juste à dire oracle combien d'éléments sont dans notre tableau (si non spécifié, la valeur par défaut est une hypothèse de 8k éléments). mise à raisonnablement exactes.

    • C'est exactement ce que je cherchais, merci.
    InformationsquelleAutor DazzaL
  2. 0

    Vous pouvez transformer une variable dans une requête à l'aide de CONNECT BY (testé sur 11g, doit travailler sur+ 10g):

    SQL> WITH DATA AS (SELECT '100,200,300' txt FROM dual)
  2  SELECT regexp_substr(txt, '[^,]+', 1, LEVEL) item FROM DATA
  3  CONNECT BY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) + 1;

ITEM
--------------------------------------------
100
200
300

    Vous pouvez ensuite rejoindre ce résultat à la table comme s'il s'agissait d'un affichage standard:

    SQL> WITH DATA AS (SELECT '100,200,300' txt FROM dual)
  2  SELECT v.id, dbt.value01
  3    FROM dbt
  4    RIGHT JOIN
  5      (SELECT to_number(regexp_substr(txt, '[^,]+', 1, LEVEL)) ID
  6         FROM DATA
  7       CONNECT BY LEVEL <= length(txt) - length(REPLACE(txt, ',', '')) + 1) v
  8       ON dbt.id = v.id;

        ID VALUE01
---------- ----------
       100 Ann
       300 John
       200
    InformationsquelleAutor Vincent Malgrat
  3. 0

    Une façon de lutter contre cela est de créer dynamiquement une expression de table commune qui peut être inclus dans la requête. La finale synatx vous seriez viser pour est:

    with list_of_values as (
  select 100 val from dual union all
  select 200 val from dual union all
  select 300 val from dual union all
  ...)
select
  lov.val,
  ...
from
  list_of_values lov left outer join 
  other_data     t   on (lov.val = t.val)

    Ce n'est pas très élégant, en particulier pour les grands ensembles de valeurs, mais la compatibilité avec une base de données sur laquelle vous pourriez avoir quelques privilèges est très bonne.

    InformationsquelleAutor David Aldridge