IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQL Oracle Discussion :

Boucle d'un curseur paramétré


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre averti
    Profil pro
    Inscrit en
    Décembre 2005
    Messages
    12
    Détails du profil
    Informations personnelles :
    Âge : 51
    Localisation : Belgique

    Informations forums :
    Inscription : Décembre 2005
    Messages : 12
    Par défaut Boucle d'un curseur paramétré
    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.

  2. #2
    McM
    McM est déconnecté
    Expert confirmé

    Homme Profil pro
    Développeur Oracle
    Inscrit en
    Juillet 2003
    Messages
    4 580
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Développeur Oracle

    Informations forums :
    Inscription : Juillet 2003
    Messages : 4 580
    Billets dans le blog
    4
    Par défaut
    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 ?

  3. #3
    Membre averti
    Profil pro
    Inscrit en
    Décembre 2005
    Messages
    12
    Détails du profil
    Informations personnelles :
    Âge : 51
    Localisation : Belgique

    Informations forums :
    Inscription : Décembre 2005
    Messages : 12
    Par défaut
    Un tout grand merci Mcm !

    cela fonctionne parfaitement bien.

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

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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. SQLDynamique vs Curseur paramétré
    Par jlinho2 dans le forum SQL
    Réponses: 16
    Dernier message: 03/04/2008, 14h53
  2. [PL/SQL] boucle dans un curseur
    Par arnauann dans le forum SQL
    Réponses: 4
    Dernier message: 31/08/2007, 14h35
  3. Curseurs paramétrés en T-SQL ??
    Par evans dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 06/04/2006, 15h35
  4. déclaration de curseur paramétré
    Par new_wave dans le forum Oracle
    Réponses: 3
    Dernier message: 21/11/2005, 14h53
  5. Curseurs paramétrables
    Par KNITTEL dans le forum MS SQL Server
    Réponses: 5
    Dernier message: 08/06/2005, 14h52

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo