Comment utiliser un Oracle Tableau Associatif dans une requête SQL

ODP.Net expose la possibilité de passer des Tableaux Associatifs comme params dans une procédure stockée Oracle à partir de C#. Son une belle fonctionnalité, sauf si vous essayez d'utiliser les données contenues dans ce tableau associatif dans une requête sql.

La raison pour cela est qu'il nécessite un changement de contexte - SQL SQL types et un tableau associatif passé en PL/SQL comme ceci est en réalité défini comme un PL/SQL de type. Je pense que les types définis dans un PL/SQL package/procédure/fonction PL/SQL types pendant qu'un type créé à l'extérieur de ces objets est un type SQL (si vous pouvez fournir plus de précisions sur ce, merci de le faire mais ce n'est pas le but de cette question).

Donc, la question est de savoir quelles sont les méthodes que vous pouvez utiliser pour convertir le code PL/SQL tableau associatif param quelque chose qui a à l'intérieur de la procédure peut être utilisée dans une instruction sql comme ceci:

OPEN refCursor FOR
SELECT T.*
FROM   SOME_TABLE T,
       ( SELECT COLUMN_VALUE V
         FROM   TABLE( associativeArray )
       ) T2
WHERE  T.NAME = T2.V;

Pour les besoins de cet exemple, le "associativeArray" est un simple tableau de varchar2(200) indexées par PLS_INTEGER. En C#, la associativeArry param est rempli avec un string[].

N'hésitez pas à discuter d'autres façons de faire cela en plus de l'aide d'un tableau associatif mais savez à l'avance de ces solutions ne seront pas acceptées. Pourtant, je suis intéressé à voir d'autres options.

Voir ici pour un problème similaire + solution similaire: stackoverflow.com/questions/1625649/...

OriginalL'auteur ScottCher | 2009-11-11

11

Je voudrais créer un type de base de données comme ceci:
```
create type v2t as table of varchar2(30);
/
```
Puis dans la procédure:
```
FOR i IN 1..associativeArray.COUNT LOOP
    databaseArray.extend(1);
    databaseArray(i) := associativeArray(i);
END LOOP;

OPEN refCursor FOR
SELECT T.*
FROM   SOME_TABLE T,
       ( SELECT COLUMN_VALUE V
         FROM   TABLE( databaseArray )
       ) T2
WHERE  T.NAME = T2.V;
```
(où databaseArray est déclarée de type v2t.)

Yep, ça marche.
Son incroyable de voir comment de nombreux exemples, il y a de l'aide de tableaux Associatifs dans les boucles pour exécuter des inserts et que telle, mais pas que discuter ou donner des exemples pour éluder le changement de contexte en question. J'espère que cela sera utile à d'autres.
Ce n'est pas la création d'un tableau associatif - c'est la création d'une collection. À noter également: asscoiativeArray.FIRST peut pas être 1 et associativeArray.COUNT et associativeArray.LAST peut ne pas être égal (surtout si le tableau associatif est rare).
Je n'ai pas dit que c'était "comment créer un tableau associatif", j'ai d'expliquer comment je pourrais résoudre le problème de l'utilisation de données dans SQL qui est actuellement dans un tableau associatif - en le déplaçant à une collection. J'ai repris le tableau associatif n'est pas clairsemé, c'est vrai - si non, alors un code différent serait nécessaire clairement!
Si le tableau associatif est un non fragmentées 1-tableau indexé, alors c'est presque toujours un indicateur que vous n'avez pas besoin d'un tableau associatif dans la première place et qu'il serait mieux d'avoir commencé avec une collection (dans ce cas, cette question devient académique).

OriginalL'auteur Tony Andrews

Vous ne pouvez pas utiliser des tableaux associatifs dans le SQL champ d'application - ils ne sont utilisables que dans le PL/SQL champ d'application.

Une méthode consiste à cartographier le tableau associatif à une collection (qui peut être utilisé dans le générateur de champ si le type de collection a été défini dans le code SQL de la portée et de ne pas le PL/SQL champ d'application).

SQL:

CREATE TYPE VARCHAR2_200_Array_Type AS TABLE OF VARCHAR2(200);
/

PL/SQL

DECLARE
  TYPE associativeArrayType IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
  i                PLS_INTEGER;
  associativeArray associativeArrayType;
  array            VARCHAR2_200_Array_Type;
  cur              SYS_REFCURSOR;
BEGIN
  -- Sample data in the (sparse) associative array
  associativeArray(-2) := 'Test 1';
  associativeArray(0)  := 'Test 2';
  associativeArray(7)  := 'Test 3';

  -- Initialise the collection
  array := VARCHAR2_200_Array_Type();

  -- Loop through the associative array
  i := associativeArray.FIRST;
  WHILE i IS NOT NULL LOOP
    array.EXTEND(1);
    array(array.COUNT) := associativeArray(i);
    i := associativeArray.NEXT(i);
  END LOOP;

  -- Use the collection in a query
  OPEN cur FOR
    SELECT *
    FROM   your_table
    WHERE  your_column MEMBER OF array;
END;
/

OriginalL'auteur MT0

Vous devez vous connecter pour publier un commentaire.