Fonction SOMMEPROD en PL/SQL

Version imprimable

Bonjour
je suis un debutant PL/sql.
j'essaie d'ecreire une fonction qui permet de faire le même calcul que la fonction SOMMEPROD de excel ,donc j'ai ecrit cette fonction:

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
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
 
create or replace
function Nombre_mois_cotise_entree(garantie in bp_garantie.bp_garantie_nom%type,
                                   typeresp in bp_garantie.bp_garantie_responsable_top%type,
                                   segment_nom in bp_segment.bp_segment_nom%type,
                                   generation in bp_generation.bp_generation_nom%type,
                                   annee in bp_data_commerciale.bp_data_com_annee%type)
 
return  number
as 
  entree number;
  mois number;
  i number;
  nombre_moy number;
  cursor c1 is
    select
      t1.bp_data_com_mens_in as entree,
      t1.bp_data_com_mens_mois as num_mois
    from
      bp_data_commerciale_mensuel t1
        inner join bp_garantie t2 on t2.bp_garantie_id = t1.bp_data_com_mens_gar_id
        inner join bp_generation t3 on t1.bp_data_com_generation_id=t3.bp_generation_id
        inner join bp_gamme t4 on t4.bp_gamme_id=t2.bp_garantie_gamme_id 
        inner join bp_segment t5 on t5.bp_segment_id=t4.bp_gamme_segment_id
      where 
        t2.bp_garantie_nom=garantie
      and
        t2.bp_garantie_responsable_top=typeresp
      and
        t5.bp_segment_nom=segment_nom
      and 
        t3.bp_generation_nom=generation
      and
        t1.bp_data_com_mens_annees=annee;
 
  cursor c2 is
    select
      sum(t1.bp_data_com_mens_in) as totale
    from
      bp_data_commerciale_mensuel t1
        inner join bp_garantie t2 on t2.bp_garantie_id = t1.bp_data_com_mens_gar_id
        inner join bp_generation t3 on t1.bp_data_com_generation_id=t3.bp_generation_id
        inner join bp_gamme t4 on t4.bp_gamme_id=t2.bp_garantie_gamme_id 
        inner join bp_segment t5 on t5.bp_segment_id=t4.bp_gamme_segment_id
    where 
      t2.bp_garantie_nom=garantie
    and
      t2.bp_garantie_responsable_top=typeresp
    and
      t5.bp_segment_nom=segment_nom
    and 
      t3.bp_generation_nom=generation
    and
      t1.bp_data_com_mens_annees=annee;
 
begin
       nombre_moy:=0;
       i:=0;
       for dcomrec in c1
        loop
          entree:=entree+dcomrec.entree;
          mois:=mois+dcomrec.num_mois;
          if dcomrec.num_mois=i+1  then
            nombre_moy:=nombre_moy + entree *(12-i);
          end if;
          i:=i+1;
        end loop;
      for totale_entree in c2
        loop
          nombre_moy:= nombre_moy / totale_entree.totale;
        end loop;
    return nombre_moy;
 
end;

A la compilation il n'y a pas d'erreur mais quand je l'appelle elle me retourne rien du tout ma question est :

est ce qu'il y a une fonction oracle qui fait la meme chose que SOMMEPROD
Sinon qu'elle peut m'aider à l'ecrire

Merci

Je ne sais pas ce que sommeprod sous excel fait (une histoire de produit de matrice), mais pour ton code :

Le curseur c2 ne ramène qu'une ligne, donc on peut le passer en select
Sauf erreur de ma part, le curseur c2 a les mêmes jointures que le curseur c1, on peut donc regrouper les 2 et inclure le sum dans le curseur c1, ça évite un select.
La variable mois est inutilisée, donc à supprimer du code.
L'affectation de nombre_moy se base sur le fait que num_mois du curseur soit égal à un incrément. Hors il n'y a pas de tri sur le curseur, donc ce n'est pas correct.
On va rajouter un order by dans le curseur, mais il se pourrait qu'un mois ne soit pas ramené par le select. Que faire dans ce cas ?
La variable entree n'est pas initialisée, donc vaut NULL, donc jamais incrémentée.

Voila un code un peu corrigé, mais il faut vérifier les différents cas possibles sur les mois qui peuvent manquer, et sur la valeur 0 de tot
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 37 38 39 40 41 42 43 44 FUNCTION Nombre_mois_cotise_entree(garantie IN bp_garantie.bp_garantie_nom%TYPE, typeresp IN bp_garantie.bp_garantie_responsable_top%TYPE, segment_nom IN bp_segment.bp_segment_nom%TYPE, generation IN bp_generation.bp_generation_nom%TYPE, annee IN bp_data_commerciale.bp_data_com_annee%TYPE) RETURN number AS entree number := 0; i number := 0; nombre_moy number := 0; tot NUMBER := 0; CURSOR c1 IS SELECT t1.bp_data_com_mens_mois AS num_mois, t1.bp_data_com_mens_in AS entree, SUM(t1.bp_data_com_mens_in) AS totale FROM bp_data_commerciale_mensuel t1 INNER JOIN bp_garantie t2 ON t2.bp_garantie_id = t1.bp_data_com_mens_gar_id INNER JOIN bp_generation t3 ON t1.bp_data_com_generation_id=t3.bp_generation_id INNER JOIN bp_gamme t4 ON t4.bp_gamme_id=t2.bp_garantie_gamme_id INNER JOIN bp_segment t5 ON t5.bp_segment_id=t4.bp_gamme_segment_id WHERE t2.bp_garantie_nom=garantie AND t2.bp_garantie_responsable_top=typeresp AND t5.bp_segment_nom=segment_nom AND t3.bp_generation_nom=generation AND t1.bp_data_com_mens_annees=annee ORDER BY t1.bp_data_com_mens_mois; BEGIN FOR dcomrec IN c1 LOOP entree := entree + NVL(dcomrec.entree,0); tot := tot + NVL(dcomrec.totale,0); IF dcomrec.num_mois = i + 1 THEN nombre_moy := nombre_moy + entree *(12-i); END IF; i:=i+1; END LOOP; IF tot <> 0 THEN nombre_moy:= nombre_moy / tot; END IF; RETURN nombre_moy; END;

Après réflexion, Je pense que le test

Code:

IF dcomrec.num_mois = i + 1

ne sert à rien.

Vérifie si ce fonctionnement est correct :

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
FUNCTION Nombre_mois_cotise_entree(garantie IN bp_garantie.bp_garantie_nom%TYPE,
                                   typeresp IN bp_garantie.bp_garantie_responsable_top%TYPE,
                                   segment_nom IN bp_segment.bp_segment_nom%TYPE,
                                   generation IN bp_generation.bp_generation_nom%TYPE,
                                   annee IN bp_data_commerciale.bp_data_com_annee%TYPE)
 
RETURN  number
AS 
  entree number := 0;
  nombre_moy number := 0;
  tot NUMBER := 0;
BEGIN
  FOR dcomrec IN (SELECT t1.bp_data_com_mens_mois AS num_mois, t1.bp_data_com_mens_in AS entree, SUM(t1.bp_data_com_mens_in) AS totale
                FROM bp_data_commerciale_mensuel t1
                    INNER JOIN bp_garantie t2 ON t2.bp_garantie_id = t1.bp_data_com_mens_gar_id
                    INNER JOIN bp_generation t3 ON t1.bp_data_com_generation_id=t3.bp_generation_id
                    INNER JOIN bp_gamme t4 ON t4.bp_gamme_id=t2.bp_garantie_gamme_id 
                    INNER JOIN bp_segment t5 ON t5.bp_segment_id=t4.bp_gamme_segment_id
                  WHERE t2.bp_garantie_nom=garantie
                  AND t2.bp_garantie_responsable_top=typeresp
                  AND t5.bp_segment_nom=segment_nom
                  AND t3.bp_generation_nom=generation
                  AND t1.bp_data_com_mens_annees=annee
                  ORDER BY t1.bp_data_com_mens_mois)
  LOOP
    entree := entree + NVL(dcomrec.entree,0);
    tot := tot + NVL(dcomrec.totale,0);
   	nombre_moy := nombre_moy + entree *(12-dcomrec.num_mois+1);
  END LOOP;
 
  IF tot <> 0 
  THEN RETURN nombre_moy / tot; 
  ELSE RETURN NULL;
  END IF;  
 
END;

13/08/2012, 13h04
hazem.abd

Merci McM sauf que j'aurai toujours besoins du curseur C2 pour faire la somme car il m'affiche l'erreur suivante :

Citation:

ORA-00937: la fonction de groupe ne porte pas sur un groupe simple
00937. 00000 - "not a single-group group function"
*Cause:

http://www.bernardcordier.com/Images...sommeprod1.gif

La fonction SOMMEPROD permet d'effectuer horizontalement le produit de deux valeurs situées dans des colonnes différentes et d'en additionner les résultats.
Par exemple, pour le mois 1, le chiffre d'affaires total des deux restaurants se calcule de la façon suivante : (850x3)+(700x2)+(880x3)+((780x2) = 8 150.

j'ai fais les modifs suivante et ça marche :

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
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
 
function Nombre_mois_entree(garantie in bp_garantie.bp_garantie_nom%type,
                                   typeresp in bp_garantie.bp_garantie_responsable_top%type,
                                   segment_nom in bp_segment.bp_segment_nom%type,
                                   generation in bp_generation.bp_generation_nom%type,
                                   annee in bp_data_commerciale.bp_data_com_annee%type)
 
return  number
as 
  entree number;
  mois number;
  i number;
  nombre_moy number;
 
  cursor c1 is
    select
      t1.bp_data_com_mens_in as entre,
      t1.bp_data_com_mens_mois as num_mois
 
    from
      bp_data_commerciale_mensuel t1
        inner join bp_garantie t2 on t2.bp_garantie_id = t1.bp_data_com_mens_gar_id
        inner join bp_generation t3 on t1.bp_data_com_generation_id=t3.bp_generation_id
        inner join bp_gamme t4 on t4.bp_gamme_id=t2.bp_garantie_gamme_id 
        inner join bp_segment t5 on t5.bp_segment_id=t4.bp_gamme_segment_id
      where 
        t2.bp_garantie_nom=garantie
      and
        t2.bp_garantie_responsable_top=typeresp
      and
        t5.bp_segment_nom=segment_nom
      and 
        t3.bp_generation_nom=generation
      and
        t1.bp_data_com_mens_annees=annee;
  cursor c2 is
    select
      sum(t1.bp_data_com_mens_in) as totale
    from
      bp_data_commerciale_mensuel t1
        inner join bp_garantie t2 on t2.bp_garantie_id = t1.bp_data_com_mens_gar_id
        inner join bp_generation t3 on t1.bp_data_com_generation_id=t3.bp_generation_id
        inner join bp_gamme t4 on t4.bp_gamme_id=t2.bp_garantie_gamme_id 
        inner join bp_segment t5 on t5.bp_segment_id=t4.bp_gamme_segment_id
    where 
      t2.bp_garantie_nom=garantie
    and
      t2.bp_garantie_responsable_top=typeresp
    and
      t5.bp_segment_nom=segment_nom
    and 
      t3.bp_generation_nom=generation
    and
      t1.bp_data_com_mens_annees=annee;
 
 
begin
      nombre_moy:=0;
      entree:=0;
      mois:=0;
      i:=0;
       for dcomrec in c1
        loop
          entree:=entree+dcomrec.entre;
          mois:= mois + dcomrec.num_mois;
          if(mois = i+1) then
            nombre_moy := nombre_moy + (entree * (12-i));
          end if;
          i:=i+1;
          entree:=0;
          mois:=0;
        end loop;
       for drec in c2
       loop
        nombre_moy:=nombre_moy /drec.totale;
       end loop;
    return nombre_moy;
 
end;

s'il y a une autre méthode de faire je suis preneur merci

13/08/2012, 13h29
skuatamad

Une simple somme annalytique devrait faire l'affaire non ?

Code:

sum(qte_vendue*prix_vente) over(partition by annee, mois) as SOMMEPROD_par_mois