Boucle d'un curseur paramétré

**mike devimo** · 12/11/2007, 12h43

Bonjour,

Situation:

Imaginez 3 tables reliées.
Une table "contrats" identifiée par cont_pk, relative aux contrats d'enseignes commerciales.

une table "contrats_ca" reprenant les chiffres d'affaires de ces contrats pour mois et année donnés.

Les contrats de ces enseignes commerciales sont renouvelables à tout instant.
D'un mois à l'autre, une enseigne peut changer de contrat, tout en sachant que dans la table "contrats_ca" les ca sont relatifs à un contrat et non une enseigne.

Une table "contrats_ca_liens2" : reliant les contrats d'une même enseigne afin de pouvoir retrouver le c.a. d'une enseigne pour un mois donné si celle-ci a plusieurs contrats dans une même année.

(Le but est de réaliser des statistiques sur les ca d'une période à une autre.)

Je cherche à réaliser une fonction qui me retourne le ca pour un contrat donné (mois et année en input).
Si le c.a. n'existe pas pour ce contrat, checker si ce ca existe pour un contrat lié au premier.

SQL :

Voici la fonction que j'utilise pour mon SELECT (voir plus loin)

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
CREATE OR REPLACE FUNCTION "DEV_GET_CA_TEST"  (
   lien        IN   number,
   mois        IN   contrats_ca.cont_ca_mois%TYPE,
   annee       IN   contrats_ca.cont_ca_annee%TYPE,
   contrat     IN   contrats_ca.cont_ca_cont_fk%TYPE
)
   RETURN number
IS
 
  ca_res        contrats_ca.cont_ca_tot%TYPE;
  i             BINARY_INTEGER:=1;
  nb            NUMBER;
  cont          NUMBER;
  TYPE tab  IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  cont_fk1      tab;
  ca            tab;
 
 
 CURSOR c_contrats_lies(lien NUMBER)
 IS 
 SELECT cont_fk
 FROM CONTRATS_CA_LIENS2 WHERE liens = lien;  
 
 
BEGIN
 
SELECT COUNT(cont_fk) INTO nb FROM CONTRATS_CA_LIENS2 WHERE liens = lien;
 
if nb = 0 then 
 
SELECT contrats_ca.cont_ca_tot into ca_res
FROM   contrats_ca 
WHERE 
    contrats_ca.cont_ca_cont_fk       = contrat 
AND contrats_ca.cont_ca_annee         = annee 
AND contrats_ca.cont_ca_mois          = mois;
 
else 
 
-- if nb <> 0 then 
 
OPEN c_contrats_lies(lien);
 
FOR i IN 1..nb LOOP
FETCH  c_contrats_lies INTO cont_fk1(i);
 
SELECT nvl(contrats_ca.cont_ca_tot,0) into ca(i)
FROM   contrats_ca
WHERE 
       contrats_ca.cont_ca_cont_fk = cont_fk1(i)
AND    contrats_ca.cont_ca_annee   = annee 
AND    contrats_ca.cont_ca_mois    = mois;
 
ca_res:=ca(i);
 
exit when ca_res <> 0;
 
END LOOP;
 
CLOSE c_contrats_lies;
 
end if ;
 
return ca_res;
 
EXCEPTION
WHEN OTHERS THEN
RETURN to_number(null);
 
 
END dev_get_ca_test;
/

SELECT (pour info)

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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
SELECT distinct
 
sites.cell_denomination                      as site,
ENSEIGNES.ens_libelle                        as enseigne,
SECTEURS_ACTIVITES.sect_act_libelle          as secteur,
CONTRATS.cont_pk                             as contrat,
 
NATURES_CONTRATS.nat_cont_libelle            as nat_cont,
round(contrats_cell_surf.sup_loc)            as sup_loc,
 
nvl(contrats_ca_liens2.liens,0)                                                    as lien,
pack_turnover_keys.dev_get_rem(3,2007,CONTRATS.cont_PK)                            as rem,
 
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007,CONTRATS.cont_pk))    as ca_1,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007,CONTRATS.cont_pk))    as ca_1b,
(NVL(Pack_Loyer.dev_get_ca(3-2,2007,CONTRATS.cont_pk),0))                         as ca_1c,
round(dev_get_ca_test(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007,CONTRATS.cont_pk)) as ca_1_test,
 
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007,CONTRATS.cont_pk)) as ca_2,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007,CONTRATS.cont_pk)) as ca_2b,
(NVL(Pack_Loyer.dev_get_ca(3-1,2007,CONTRATS.cont_pk),0))                      as ca_2c,
 
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007,CONTRATS.cont_pk))   as ca_3,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007,CONTRATS.cont_pk))   as ca_3b,
(NVL(Pack_Loyer.dev_get_ca(3,2007,CONTRATS.cont_pk),0))                        as ca_3c,
 
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007-1,CONTRATS.cont_pk)) as ca_11,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-2,2007-1,CONTRATS.cont_pk)) as ca_11b,
(NVL(Pack_Loyer.dev_get_ca(3-2,2007-1,CONTRATS.cont_pk),0))                      as ca_11c,
 
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007-1,CONTRATS.cont_pk)) as ca_22,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3-1,2007-1,CONTRATS.cont_pk)) as ca_22b,
(NVL(Pack_Loyer.dev_get_ca(3-1,2007-1,CONTRATS.cont_pk),0))                      as ca_22c,
 
round(dev_get_ca24(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007-1,CONTRATS.cont_pk)) as ca_33,
round(dev_get_ca25(NVL(CONTRATS_CA_LIENS2.liens,0),3,2007-1,CONTRATS.cont_pk)) as ca_33b,
(NVL(Pack_Loyer.dev_get_ca(3,2007-1,CONTRATS.cont_pk),0))                      as ca_33c,
 
nvl(pack_turnover_trim.dev_get_ca_Q(nvl(contrats_ca_liens2.liens,0),2007,3,CONTRATS.cont_PK),0)     as Q,
nvl(pack_turnover_trim.dev_get_ca_Q(nvl(contrats_ca_liens2.liens,0),2007-1,3,CONTRATS.cont_PK),0)   as Qprev,
nvl((PACK_PROPRIO.dev_get_cumul121(nvl(contrats_ca_liens2.liens,0),3,2007,CONTRATS.cont_PK)),0)     AS cumul12,
nvl((PACK_PROPRIO.dev_get_cumul121(nvl(contrats_ca_liens2.liens,0),3,2007-1,CONTRATS.cont_PK)),0)   AS cumul12prev
 
 
FROM
 
CONTRATS,
contrats_ca_liens2,
ENSEIGNES,
SECTEURS_ACTIVITES,
contrats_cell_surf,
CONTRATS_TIERS,
NATURES_CONTRATS,
sites
 
WHERE
    (contrats_ca_liens2.cont_fk(+)     = contrats.cont_pk)
and (ENSEIGNES.ens_pk(+)               = CONTRATS.cont_ens_fk)
AND (CONTRATS.cont_pk                  = CONTRATS_TIERS.cont_trs_cont_fk)
AND (NATURES_CONTRATS.nat_cont_pk      = CONTRATS.cont_nat_cont_fk)
AND (CONTRATS_TIERS.cont_trs_typ_trs   = '1')
AND (CONTRATS.cont_pk                  = contrats_cell_surf.cont_pk)
AND (SECTEURS_ACTIVITES.sect_act_pk(+) = CONTRATS.cont_sect_act_fk)
AND (cont_site_fk                      = 2754)
 
and (sites.cell_pk                     = contrats.cont_site_fk)
--AND (cont_nat_cont_fk                  IN (11, 12))                                                                                                                      -- emph, com
 
--and CONTRATS.cont_avenant_prisedeffet  = (select max(CONTRATS.cont_avenant_prisedeffet) from contrats contrats1 where contrats1.cont_ens_fk = contrats.cont_ens_fk)
 
AND (ENSEIGNES.ens_libelle IS NOT NULL)
 
AND TO_DATE(TO_CHAR(CONTRATS.cont_date_resiliation,'DD/MM/YYYY'),'DD/mm/yyyy') > TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007-1)),'DD/mm/yyyy')
 
AND TO_DATE(TO_CHAR(CONTRATS.cont_avenant_prisedeffet,'DD/MM/YYYY'),'DD/mm/yyyy') < TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007)),'DD/mm/yyyy')
 
and cont_avenant = F_GetContAvenant(cont_initial,cont_instance,TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007)),'DD-MM-YYYY'))
 
 
--and (to_char(CONTRATS.cont_avenant_prisedeffet,'dd-mm-yyyy') = Pack_Loyer.dev_get_last_contrat(enseignes.ens_libelle, sites.cell_pk, NATURES_CONTRATS.nat_cont_pk, 15, 3, 2007))
 
--AND (
--TO_DATE(CONCAT((CONCAT(TO_CHAR(15)||'/',TO_CHAR(3)||'/')),TO_CHAR(2007)),'DD/mm/yyyy')
--BETWEEN 
--TO_DATE(TO_CHAR(CONTRATS.cont_avenant_prisedeffet,'DD/MM/YYYY'),'DD/mm/yyyy') AND TO_DATE(TO_CHAR(CONTRATS.cont_date_resiliation,'DD/MM/YYYY'),'DD/mm/yyyy')
--)
 
--and contrats.cont_actif = '1'
 
order by enseigne;

PROBLEME

Lorsque le contrat n'est "lié" à aucun autre (nb=0 dans la fonction) j'obtiens un CA , lorsqu'il est lié je n'obtiens rien (NULL). Pq ?

Que dois -je changer dans ma syntaxe liée au curseur pour que celà fonctionne. merci bcp pour votre aide.

**McM** · 12/11/2007, 13h52

Voici la fonction simplifiée

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
52
53
54
CREATE OR REPLACE FUNCTION DEV_GET_CA_TEST  (
   lien        IN   NUMBER,
   mois        IN   contrats_ca.cont_ca_mois%TYPE,
   annee       IN   contrats_ca.cont_ca_annee%TYPE,
   contrat     IN   contrats_ca.cont_ca_cont_fk%TYPE
)
   RETURN NUMBER
IS
 
  ca_res        contrats_ca.cont_ca_tot%TYPE;
  nb            NUMBER;
 
BEGIN
 
	SELECT COUNT(cont_fk) 
	INTO nb 
	FROM CONTRATS_CA_LIENS2
	WHERE liens = lien;
 
	IF nb = 0 
	THEN 
 
		SELECT cont_ca_tot 
		INTO ca_res
		FROM CONTRATS_CA 
		WHERE cont_ca_cont_fk     = contrat 
		AND cont_ca_annee         = annee 
		AND cont_ca_mois          = mois;
 
	ELSE 
 
		BEGIN	
			SELECT c.cont_ca_tot 
			INTO ca_res
			FROM  CONTRATS_CA_LIENS2 l, CONTRATS_CA c
			WHERE l.liens = lien
			AND   c.cont_ca_cont_fk = l.cont_fk
			AND   c.cont_ca_annee   = annee 
			AND   c.cont_ca_mois    = mois
			AND   c.cont_ca_tot <> 0
			AND   ROWNUM = 1;
		WHEN OTHERS THEN
			RETURN TO_NUMBER(NULL);
		END;
 
	END IF ;
 
	RETURN ca_res;
 
EXCEPTION
WHEN OTHERS THEN
RETURN TO_NUMBER(NULL);
 
END dev_get_ca_test;

Sinon, t'as vu que ton SELECT utilise dev_get_ca et pas dev_get_ca_test ?

**mike devimo** · 12/11/2007, 15h52

Un tout grand merci Mcm !

cela fonctionne parfaitement bien.

---------------------------------

ps: une bière à bxl qd tu veux !

Boucle d'un curseur paramétré

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager