Discussion :

[moicwill]

Bonjour,

J'ai un petit pb.
J'aimerais savoir s'il est possible dans une fonction d'utiliser un pipelined et d'inclure le résultat d'une requete normale.

Je m'explik.
je veux une fonction qui me renvoit plusieurs lignes et colonnes en sortie.
mais ce que j'ai fait ne marche pas trop.

1- J'ai créé un type type1 correspond aux données d'une ligne.

2- J'ai créé un type type2 (table of type1)

3- J'ai créé une fonction:

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
create or replace function myfunction(n integer)
return type2 pipelined
is
 
a1 integer;
a2 varchar(20);
a3 integer;
 
myrow type1 := type1(null, null, null)
 
begin
select t1, t2, t3 
into a1, a2, a3
from matable
pipe row (myrow);
return;
 
end;
/

[pifor]

Une fonction PL/SQL peut retourner des lignes en mode pipeline, vous avez plusieurs exemples dans Oracle® Database PL/SQL User's Guide and Reference 10g Release 2 (10.2), chapitre 11 Tuning PL/SQL Applications for Performance dont celui-ci:

xample 11-23 Using a Pipelined Table Function For a Transformation

-- Define the ref cursor types and function
CREATE OR REPLACE PACKAGE refcur_pkg IS
TYPE refcur_t IS REF CURSOR RETURN employees%ROWTYPE;
TYPE outrec_typ IS RECORD (
var_num NUMBER(6),
var_char1 VARCHAR2(30),
var_char2 VARCHAR2(30));
TYPE outrecset IS TABLE OF outrec_typ;
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED;
END refcur_pkg;
/

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS
FUNCTION f_trans(p refcur_t)
RETURN outrecset PIPELINED IS
out_rec outrec_typ;
in_rec p%ROWTYPE;
BEGIN
LOOP
FETCH p INTO in_rec;
EXIT WHEN p%NOTFOUND;
-- first row
out_rec.var_num := in_rec.employee_id;
out_rec.var_char1 := in_rec.first_name;
out_rec.var_char2 := in_rec.last_name;
PIPE ROW(out_rec);
-- second row
out_rec.var_char1 := in_rec.email;
out_rec.var_char2 := in_rec.phone_number;
PIPE ROW(out_rec);
END LOOP;
CLOSE p;
RETURN;
END;
END refcur_pkg;
/
-- SELECT query using the f_transc table function
SELECT * FROM TABLE(
refcur_pkg.f_trans(CURSOR(SELECT * FROM employees WHERE department_id = 60)));


In the preceding query, the pipelined table function f_trans fetches rows from the CURSOR subquery SELECT * FROM employees ..., performs the transformation, and pipelines the results back to the user as a table. The function produces two output rows (collection elements) for each input row.

Note that when a CURSOR subquery is passed from SQL to a REF CURSOR function argument as in Example 11-23, the referenced cursor is already open when the function begins executing.

Dans votre exemple, il faudrait plutôt affecter a1, a2 et a3 à des champs d'une variable RECORD.

[pifor]

J'ai adapté votre exemple de la façon suivante.

1. Création de la spec. du package pour pouvoir créer des types:

Code :

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

1
2
3
4
5
6
7
 
CREATE OR REPLACE PACKAGE mypackage
IS
TYPE type2 IS TABLE OF matable%ROWTYPE; 
FUNCTION myfunction RETURN type2 PIPELINED;
END;
--

2. Création du corps du package:

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
 
CREATE OR REPLACE PACKAGE BODY mypackage IS
--
FUNCTION myfunction
RETURN type2 PIPELINED
IS
myrow matable%ROWTYPE;
BEGIN
FOR c IN (SELECT t1, t2, t3 FROM matable)
LOOP
myrow.t1 := c.t1;
myrow.t2 := c.t2;
myrow.t3 := c.t3;
PIPE ROW(myrow);
END LOOP;
RETURN;
END;
--
END;
/

Remarquez qu'il faut écrire une boucle avec un curseur et appeler PIPE ROW dans la boucle et que le RETURN est en dehors de la boucle et ne retourne rien !

Si j'ai les données suivantes dans matable:

Code :

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

1
2
3
4
5
6
7
8
 
 
SQL> select * from matable;
 
        T1 T2                           T3
---------- -------------------- ----------
         1 a                            10
         2 b                            20

Alors je peux appeler myfunction de la façon suivante et le résultat est:

Code :

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

1
2
3
4
5
6
7
 
SQL> select * from table(mypackage.myfunction);
 
        T1 T2                           T3
---------- -------------------- ----------
         1 a                            10
         2 b                            20

[moicwill]

Bonjour,

D'abord merci de vous attarder sur mon pb.

Apres avoir suivi vos recommandations, j'ai l'erreur suivante:
'ORA-06530 Reference to unintialized composite'
'ORA-06512 at' la ligne ou je commence a faire myrow.t1:= c.t1;

[pifor]

Merci de nous donner:
- la version de votre base de données Oracle
- le code complet que vous utilisez.

[moicwill]

autant pour moi,
ca marche parfaitement.

Encore une fois merci.

Maintenant que ca marche, essayons de comprendre.
Pourquoi l'utilisation des packages?

[pifor]

Parce qu'il faut créer des types et qu'il est plus propre d'utiliser des packages pour créer des types.