Discussion :

[mortimer.pw]

Bonjour,
Je souhaite créer un Package dans lequel une fonction renvoie un tableau. Le tableau serait alimenté par les valeurs retournées par un SELECT.

Voilà ce que j'ai écris :

CREATE TYPE LIG_ING AS OBJECT (JOUR DATE,PTG CHAR(5),LIGNE CHAR(3),LIB_LIGNE VARCHAR2(30),PLAN CHAR(3),LIB_PLAN VARCHAR2(30),FONCTION CHAR(3),LIB_FONCTION VARCHAR2(30),OPERATION CHAR(4),LIB_OPERATION VARCHAR2(30),O_F VARCHAR2(10),TEMPS NUMBER(7,4));
/

CREATE TYPE TAB_ING AS TABLE OF LIG_ING;
/


CREATE OR REPLACE PACKAGE Ing_pack AS
FUNCTION PtgSem(cIdPerso CHAR, cDebut CHAR, cFin CHAR) RETURN TAB_ING;
END Ing_pack;
/


CREATE OR REPLACE PACKAGE BODY Ing_pack AS
FUNCTION PtgSem(cIdPerso CHAR, cDebut CHAR, cFin CHAR) RETURN TAB_ING IS
CURSOR CPtg IS
SELECT P.jour,P.id_perso,P.id_ligne,A.lib_ligne,P.id_plan,A.lib_plan,P.id_fonction,A.lib_fonction,P.id_operation,A.lib_operation,P.ofs_cpt,P.temps
FROM Ptg_pers P, Vue_activite_geo A
WHERE (P.jour>=TO_DATE(cDebut,'YYYYMMDD') AND P.jour<=TO_DATE(cFin,'YYYYMMDD'))
AND P.id_perso=cIdPerso
AND P.id_ligne=A.id_ligne
AND P.id_plan=A.id_plan
AND P.id_fonction=A.id_fonction
AND P.id_operation=A.id_operation;
tTab TAB_ING;
BEGIN
?????
?????
END PtgSem;
END Ing_pack;

Comment faire pour mettre le contenu de mon curseur dans mon tableau ?

Merci de votre aide.

[marine.wante]

Bonjours mortimer.pw,
J'ai rencontré le même probleme mais je n'ai toujours pas de solution.
Aidez nous svp.

[mnitu]

Bonjour,
Je souhaite créer un Package dans lequel une fonction renvoie un tableau.
...

Pour faire quoi avec ?
La procédure qui renvoie un ref_cursor ne suffit pas ?

Code :

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

1
2
3
4
5
6
7
 
create or replace procedure p_refCursor (
p_refCur in out sys_refcursor )
Is
begin
  open p_refCur for select empno, ename, job from scott.emp;
end;

[mortimer.pw]

Bonjour et merci de ton aide,

Je suis apprenti dba et je dois fournir à un développeur un package avec différentes fonctions.

Une fonction qui prend en paramètre un tableau et va faire des INSERT pour chaque ligne du tableau.

Une fonction qui va faire un SELECT, mettre les lignes dans un tableau et le retourner.

Le but du tableau est de permettre au développeur d'exploiter facilement les données.

J'espère être clair et encore merci pour le soutien.

[mortimer.pw]

J'ai finalement fait 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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
CREATE OR REPLACE PACKAGE Ing_pack AS
        TYPE LIG_ING IS RECORD(JOUR PTG_PERS.JOUR%TYPE,PTG PTG_PERS.ID_PERSO%TYPE,ID_LIG PTG_PERS.ID_LIGNE%TYPE,LIB_LIG LIGNE.LIBELLE%TYPE,ID_PLAN PTG_PERS.ID_PLAN%TYPE,LIB_PLAN PLAN.LIBELLE%TYPE,ID_FONC PTG_PERS.ID_FONCTION%TYPE,LIB_FONC FONCTION.LIBELLE%TYPE,ID_OPE PTG_PERS.ID_OPERATION%TYPE,LIB_OPE OPERATION.LIBELLE%TYPE,O_F PTG_PERS.OFS_CPT%TYPE,TEMPS PTG_PERS.TEMPS%TYPE);
        TYPE TAB_ING IS TABLE OF LIG_ING;
	FUNCTION PtgSem(cIdPerso CHAR, cDebut CHAR, cFin CHAR) RETURN TAB_ING;
END Ing_pack;
/
 
CREATE OR REPLACE PACKAGE BODY Ing_pack AS
	FUNCTION PtgSem(cIdPerso CHAR, cDebut CHAR, cFin CHAR) RETURN TAB_ING IS
                tTabIng TAB_ING;
                CURSOR CPtg IS
                        SELECT P.jour,P.id_perso,P.id_ligne,A.lib_ligne,P.id_plan,A.lib_plan,P.id_fonction,A.lib_fonction,P.id_operation,A.lib_operation,P.ofs_cpt,P.temps
                        FROM Ptg_pers P, Vue_activite_geo A
                        WHERE (P.jour>=TO_DATE(cDebut,'YYYYMMDD') AND P.jour<=TO_DATE(cFin,'YYYYMMDD'))
                        AND P.id_perso=cIdPerso
                        AND P.id_ligne=A.id_ligne
                        AND P.id_plan=A.id_plan
                        AND P.id_fonction=A.id_fonction
                        AND P.id_operation=A.id_operation
			AND P.id_type_trt_ptg='NV';
        BEGIN
                OPEN CPtg;
		IF CPtg%NOTFOUND THEN
			tTabIng(1).JOUR:='';
		ELSE
	                FETCH CPtg BULK COLLECT INTO tTabIng;
		END IF;
                CLOSE CPtg;
                RETURN (tTabIng);
 
	END PtgSem;
END Ing_pack;
/
EXIT;
 
 
SET SERVEROUTPUT ON
DECLARE
	tTabIng ING_PACK.TAB_ING;
BEGIN
	tTabIng:=Ing_pack.PtgSem('00219','20080602','20080607');
	IF tTabIng.COUNT=0 THEN
		dbms_output.put_line('Tableau vide');
	ELSE
	FOR i IN tTabIng.FIRST..tTabIng.LAST LOOP
		dbms_output.put_line(tTabIng(i).JOUR||' '||tTabIng(i).PTG||' '||tTabIng(i).ID_LIG||' '||tTabIng(i).LIB_LIG||' '||tTabIng(i).ID_PLAN||' '||tTabIng(i).LIB_PLAN||' '||tTabIng(i).ID_FONC||' '||tTabIng(i).LIB_FONC||' '||tTabIng(i).ID_OPE||' '||tTabIng(i).LIB_OPE||' '||tTabIng(i).O_F||' '||tTabIng(i).TEMPS);
	END LOOP;
	END IF;
END;
/
EXIT;

[mnitu]

Bref, parce que tu y tiens, voilà un exemple:

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
 
create or replace type dept_scalar_type AS object (
DEPTNO  NUMBER(2),
DNAME   VARCHAR2(14),
LOC     VARCHAR2(13)
)
/
create or replace type dept_table_type AS TABLE of dept_scalar_type
/
CREATE OR REPLACE FUNCTION dept_ptab
RETURN dept_table_type
IS
  l_data dept_table_type := dept_table_type();
Begin
  SELECT dept_scalar_type(d.deptno, d.dname, d.loc) 
    Bulk collect Into l_data
    FROM scott.dept d; 
  --  
  return l_data;
End;
/

Sauf usage assez particulier je n’utiliserais pas cette solution.

[mortimer.pw]

Pourquoi ne pas utiliser cette solution ?
Quelle solution utiliser ?

[mnitu]

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
 
SQL> set serveroutput on
SQL> variable x refcursor
SQL> r
  1  declare
  2    l_empno emp.empno%Type;
  3    l_ename emp.ename%Type;
  4    l_job   emp.job%Type;
  5  begin
  6     p_refcursor(:x);
  7     loop
  8       fetch :x Into l_empno, l_ename, l_job;
  9       exit when :x%NOTFOUND;
 10       dbms_output.put_line(l_empno||' '||l_ename);
 11     end loop;
 12* end;
 
7369 SMITH
7499 ALLEN
...

C'est un exemple similaire avec celui que tu a posté pour tester ta fonction mais il utilise un refcursor à la place d'un tableau.
La solution qui remonte en mémoire tout le contenu d'une table peut coûter cher.