Retourner un tableau

Version imprimable

Bonjour a tous,

Je souhaite utiliser deux vues dans une fonction pl/sql, est ce seulement possible ?

Sinon, une solution de contournement serait de juste faire ces deux requetes et de stocker leur resultat dans des tables qui seraient des variables declarees au debut de la fonction. Comment declare t'on une variable de type table ? (d'ou les points d'interogations dans le code ci dessous)
Ma table finale possede normalement une dizaine de colonne, i faut que je declare chacun de leur type ?

Pour l'instant, ca donne ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 CREATE OR REPLACE FUNCTION GETDAY_TR (myDate DATE) RETURN number IS myNewInt number; myOldInt number; oldVue ??? ; newVue ??? ; resultTable ??? ; BEGIN select case when 'MONDAY' = SUBSTR(to_char(myDate,'DAY'),0,6) then 3 else 1 end as myDay into myNewInt from dual; select case when 'MONDAY' = SUBSTR(to_char(myDate,'DAY'),0,6) then 4 when 'TUESDAY' = SUBSTR(to_char(myDate,'DAY'),0,7) then 4 else 2 end as myDay into myOldInt from dual; Select ...,...,...,...,...,...,...,... into oldVue From ..... Where .......... ; Select ...,...,...,...,...,...,...,...,...,..., into newVue From ..... Where ..........; Select ....,...,...,...,...,...,...,...,...,...,...,...,...,...,...,...,...,...,...,...,...,..., into resultVue From newVue a LEFT JOIN oldVue c ON (a.Description=c.Description_old); return(resultVue); END;

Bon, j'ai regle la question des vues en utilisant des sous requetes. Mon probleme devient donc le suivant : Comment retourner une "table" avec plusieurs colones ?

Pensez vous aue auelaue chose comme ca puisse fonctionner ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE OR REPLACE TYPE t_mytype AS OBJECT(column1 Number, column2 Number); END;
 
CREATE OR REPLACE TYPE t_mytype_tab AS TABLE OF t_mytype;
 
CREATE OR REPLACE FUNCTION GETDAY_TR (myDate DATE) 
RETURN t_mytype_tab IS
return_table t_mytype_tab ;
i INTEGER; --counter
BEGIN
--initialize our return_table object and counter
return_table := t_mytype_tab();
i:=0;
--fill our return object, there are many ways in which to do so, a possible one is with a for loop with cursors
FOR cur IN ([Select Nb_Of_A, Nb_Of_B from maTable]) LOOP
--increment counter
 
i:=i+1;
--add row to return object
return_table.extend(); 
--fill this new row with one row from the QUERY results
--NOTE: since we declared our type t_mytype as a row with 2 columns, you MUST fill it with exactly 2 values, otherwise you may get the ORA-00947 error
return_table(i):= t_mytype(cur.query_column1, cur.query_column2);
END LOOP;
RETURN return_table;
END myfunction;

En fait mon probleme est seulement de reussir a remplir mon tableau... Apparemment, on peut faire ca avec un curseur ou via un select into mais je ne vois pas comment :?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
--  CREATE OR REPLACE TYPE aTable AS OBJECT(col1 char(2), col2 char(3));
 
      -- CREATE OR REPLACE TYPE aTableCol AS TABLE OF aTable;
 
 
    CREATE OR REPLACE FUNCTION myTableFunction (myDate DATE) 
        RETURN aTableCol IS
        return_table aTableCol;
        i INTEGER; --counter
 
        BEGIN
        --initialize our return_table object and counter
        return_table := aTableCol();
        i:=0;
        --fill our return object, there are many ways in which to do so, a possible one is with a for loop with cursors
        FOR cur IN (Select Nb_Of_Calcul, Nb_Of_Error from St_Run_Application) LOOP
 
        --increment counter
        i:=i+1;
        --add row to return object
        return_table.extend(); 
        --fill this new row with one row from the QUERY results
        --NOTE: since we declared our type aTableCol as a row with 2 columns, you MUST fill it with exactly 2 values, otherwise you may get the ORA-00947 error
        return_table(i):= aTable(cur.query_col1, cur.query_col2);
        END LOOP;
        RETURN return_table;
    END  myTableFunction;

Une possible implémentation...

Bonjour,

Petite question déjà sur votre problème. J'ai parcouru vos messages, je peux comprendre un éventuel besoin de retourner les données sous formes de table.

Afin de mieux déjà cerner votre soucis technique, pourriez-vous essayer de contextualiser votre problématique:

De quel type de données partez-vous ?
Pourquoi tenir à retourner une table ?
Pourquoi parler de l'utilisation de deux vues ?
Comment accéder à vos données
Quelle est la version de votre DB ?

Mes questions ont seulement pour but de permettre aux personnes lisant votre fil de mieux comprendre les tenants et aboutissants et ainsi de se sentir plus à l'aise dans votre contexte et de proposer des réponses adéquates :]

Une possible solution cependant, en ne faisant que répondre à votre problématique. Ce code est à tester, et si possible à mettre en concurrence ou concordance avec d'autres avis.

NOTE: Testé en 10gR2 uniquement.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 -- Types Oracles ~ au problème CREATE OR REPLACE TYPE t_mytype AS OBJECT(column1 Number, column2 Number); CREATE OR REPLACE TYPE t_mytype_tab AS TABLE OF t_mytype; -- Création de la fonction avec retour d'un tableau de Types Oracle CREATE OR REPLACE FUNCTION getdata_tr(i_nbcnt NUMBER) RETURN t_mytype_tab IS l_return_tab t_mytype_tab; BEGIN -- Initialisation pour éviter ORA-06531 en cas d'erreur dans le SQL lors du parcours -- du tableau de données ultérieurement. Il sera initialisé mais vide. -- Suffisant dans le cas présent l_return_tab := t_mytype_tab(); -- Utilisation d'une instruction de récupération en masse BULK des données -- Dans le cas de structures Oracle DB et non PL/SQL, il est nécessaire de -- retourner pour chaque tupple de données un object du Type pour le mettre -- dans le tableau SELECT t_mytype(level, level) BULK COLLECT INTO l_return_tab FROM dual d CONNECT BY level <= i_nbcnt; -- Retour des données ... RETURN l_return_tab; END getdata_tr; / -- Exemple d'appel DECLARE -- Déclaration de la variable du tableau pour stockage tab_mytype t_mytype_tab; BEGIN -- Récupération des données via appel tab_mytype := getdata_tr(10); -- Exploitation des données. -- A noter qu'ici on sait que les données dans le tableau sont non éparpillées (NOT SPARSE) FOR i IN tab_mytype.FIRST .. tab_mytype.LAST LOOP dbms_output.put_line ('tab_mytype('||i||') => ' || tab_mytype(i).column1 || ',' || tab_mytype(i).column2); END LOOP; END; / DROP FUNCTION getdata_tr; DROP TYPE t_mytype_tab; DROP TYPE t_mytype;