[PL/SQL] Stockage d'un select

Version imprimable

Bonjour,

je cherche à faire une première procédure P1 qui fera la requete :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 
PROCEDURE P_GET_ETAB_STOCK_RESTE (PO_CUR OUT,
                                  PDI_JOURNEE_POSTALE IN DATE)
IS
 
BEGIN
 
	-- recuperation de la liste des etablissement
        SELECT DISTINCT(CTC.code_ctc ),
               CTC.libelle_ctc,
               CTC.id_ctc
        FROM CTC, STOCK_ETABLISSEMENT se, STOCK_MACRO_PT smp
        WHERE CTC.id_ctc = se.id_ctc
          AND (smp.stock != 0 OR smp.reste_en_coupure != 0)
          AND smp.id_stock_etablissemen = se.id_stock_etablissemen
          AND se.journee_postale = PDI_JOURNEE_POSTALE
          AND CTC.date_suppression IS NULL
          AND se.date_suppression IS NULL;
 
END P_GET_ETAB_STOCK_RESTE;

Et je cherche à stocker les éléments dans un objet. afin de récupérer ces éléments dans une deuxième procédure P2 et de faire un loop sur le retour de P1.

Est ce possible? J'avais pensé avec un cursor mais ca n'a pas l'air de foncttionner car mon select renvoit plusieurs champs...

Je ne comprends pas ton problème et je ne vois pas l'intérêt de passer par une procédure P1. Pour quoi ne pas faire directement un curseur dans ta procédure P2 ?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
procedure P2 is
cursor C_GET_ETAB_STOCK_RESTE (PDI_JOURNEE_POSTALE IN DATE) is
   SELECT DISTINCT(CTC.code_ctc ), 
               CTC.libelle_ctc, 
               CTC.id_ctc 
        FROM CTC, STOCK_ETABLISSEMENT se, STOCK_MACRO_PT smp 
        WHERE CTC.id_ctc = se.id_ctc 
          AND (smp.stock != 0 OR smp.reste_en_coupure != 0) 
          AND smp.id_stock_etablissemen = se.id_stock_etablissemen 
          AND se.journee_postale = PDI_JOURNEE_POSTALE 
          AND CTC.date_suppression IS NULL 
          AND se.date_suppression IS NULL; 
begin
  for v_ETAB_STOCK_RESTE in C_GET_ETAB_STOCK_RESTE (sysdate) loop
  ...
  end loop;
end;

05/12/2005, 12h08
lapartdombre

Pour des raisons d'optimisation et de non redondance du code. En fait cette procédure va être appellée par plusieurs autres procédures
05/12/2005, 12h17
BiM

Une table temporaire ?
05/12/2005, 12h30
lapartdombre

Il n'y a pas d'autres moyen qu'une table temporaire... Avec les cursors c pas possible???
05/12/2005, 12h53
plaineR

ou un package où tu déclares tous tes curseurs en entête
=> pas de redondance du code
=> ils peuvent être appelés de n'importe quelle procédure.

Sinon après tu as toujours les ref cursors.

En fait moi j'avais essayé ca :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
TYPE r_cursor IS REF CURSOR;
 
PROCEDURE P_GET_ETAB_STOCK_RESTE (PO_CUR OUT Pkg_Tb_Reste.r_cursor,
                                  PDI_JOURNEE_POSTALE IN DATE)
IS
	v_cur Pkg_Tb_Reste.r_cursor;
BEGIN
	OPEN v_cur FOR
	-- recuperation de la liste des etablissement
        SELECT DISTINCT(CTC.code_ctc ),
               CTC.libelle_ctc,
               CTC.id_ctc
        FROM CTC, STOCK_ETABLISSEMENT se, STOCK_MACRO_PT smp
        WHERE CTC.id_ctc = se.id_ctc
          AND (smp.stock != 0 OR smp.reste_en_coupure != 0)
          AND smp.id_stock_etablissemen = se.id_stock_etablissemen
          AND se.journee_postale = PDI_JOURNEE_POSTALE
          AND CTC.date_suppression IS NULL
          AND se.date_suppression IS NULL;
	PO_CUR := v_cur;
END P_GET_ETAB_STOCK_RESTE;

Que j'avais vu sur un exemple. Mais quelle est la différence entre un ref cursor et un cursor. Et comment je vais ensuite dans ma procédure 2 pour parcourir les éléments de mon curseur. Je n'y suis pas arrivé.

Voici un exemple d'utilisation des ref cursor :

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
create or replace package pack1 is
  type t_curs is ref cursor;
 
  procedure recherche_dispo (p_choix in varchar2, c_matable out t_curs);
  procedure affiche_dispo (p_choix in number);
 
end;
/
create or replace package body pack1 is
 
procedure recherche_dispo (p_choix in number,
                         c_matable out t_curs) is
begin
   if p_choix = 1 then
      open c_matable for select libelle1 from matable1; 
   elsif p_choix = 2 then
      open c_matable for select libelle2 from matable2; 
   end if;
end;
 
procedure affiche_dispo (p_choix in number) is
   c_curs t_curs;
   v_lib varchar2(100);
begin
   proc1(p_choix, c_curs);
   loop
      fetch c_curs into v_lib;
   exit when c_curs%notfound;
      dbms_output.put_line (v_lib);
   end loop;
   close c_curs;
end; 
 
end;
/

Les ref cursors sont également appelés curseurs variables en opposition aux curseurs statiques.

Dans ton cas, il me semble que déclarer en entête de package tous les curseurs dont tu as besoin serait plus simple et plus adapté que d'utiliser des ref cursors.

05/12/2005, 14h44
lapartdombre

Dans ton exemple ton select ne renvoit qu'un seul élément est ce que ca marche pareil pour plusieurs éléments
05/12/2005, 14h47
lapartdombre

Je ne peux pas les définir pour le package global, car en fonction de la procédure qu'il l'appelle, je passe des paramètre différents

Oui cela marche avec plusieurs élements aussi :
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 create or replace package pack1 is type t_curs is ref cursor; procedure recherche_dispo (p_choix in varchar2, c_matable out t_curs); procedure affiche_dispo (p_choix in number); end; / create or replace package body pack1 is procedure recherche_dispo (p_choix in number, c_matable out t_curs) is begin if p_choix = 1 then open c_matable for select code1, libelle1 from matable1; elsif p_choix = 2 then open c_matable for select code2, libelle2 from matable2; end if; end; procedure affiche_dispo (p_choix in number) is c_curs t_curs; v_lib varchar2(100); v_code varchar2(30); begin proc1(p_choix, c_curs); loop fetch c_curs into v_code, v_lib; exit when c_curs%notfound; dbms_output.put_line (v_code || ' - ' || v_lib); end loop; close c_curs; end; end; /
Citation:

Envoyé par lapartdombre

Je ne peux pas les définir pour le package global, car en fonction de la procédure qu'il l'appelle, je passe des paramètre différents

D'où la nécessité de bien spécifier son problème dès le départ pour ne pas avoir des propositions de solutions inadéquates. :wink:

[EDIT]
Qu'appelles-tu des paramètres différents ? Faisant référence à des colonnes différentes ? Construits-tu des ref cursor dynamiques ? Sinon je persiste (et je signe) je ne vois pas l'intérêt des ref cursor. Rien ne t'empêche de créer plusieurs curseurs sur une même tables ou de passer en paramètres d'un curseur des valeurs.
Les ref curseurs sont intéressants selon moi dans 2 cas principalement :
- curseur dynamiques
- curseurs renvoyant les mêmes colonnes en attaquant des tables différentes (cf. mon exemple)
[/EDIT]

05/12/2005, 14h55
lapartdombre

Oui désolé !!!

Mais on ne ferme jamais les curseur??? Ca ne pose pas de problèmes???
05/12/2005, 14h58
plaineR

Citation:

Envoyé par lapartdombre

Mais on ne ferme jamais les curseur??? Ca ne pose pas de problèmes???

:oops: C'est un oubli de ma part, c'est corrigé :wink:
05/12/2005, 15h09
lapartdombre

ET dans recherche_dispo, le curseur se ferme tout seul??? Ou pas besoin de le femer??

Non il ne faut pas le fermer, car c'est juste la procédure qui ouvre le bon curseur. C'est comme si tu faisais avec des curseurs statiques :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
procedure affiche_dispo (p_choix in number) is 
   curs1 cursor is select code1, libelle1 from matable1; 
   curs2 cursor is select code2, libelle2 from matable2; 
   v_lib varchar2(100); 
   v_code varchar2(30); 
 begin 
   if p_choix = 1 then
      open curs1;
   elsif p_choix = 1 then
      open curs2;
   end if;
   loop 
      fetch c_curs into v_code, v_lib; 
   exit when c_curs%notfound; 
      dbms_output.put_line (v_code || ' - ' || v_lib); 
   end loop; 
   close c_curs; 
end;