Discussion :

[Tarmineldur]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[Tarmineldur]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[Tarmineldur]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[Zyniel]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;