Discussion :

[PoichOU]

Bonjour,


Admettons que j'ai la table suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F |
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  2  | NULL   | NULL  |
|  2 | Fruit   |   Orange | Clementine| 10  | NULL   | NULL  |
|  3 | Fruit   |   Orange | Sanguine  |  8  | NULL   | NULL  |
|  4 | Fruit   |   Pomme  | Granny    | 20  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Golden    |  8  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Rouge     | 12  | NULL   | NULL  |
--------------------------------------------------------------

Je voudrais remplir les colonnes P_ST_T, P_T_F et P_F où :
- P_ST_T est le pourcentage de quantité de sous_type par rapport à un type
- P_T_F est le pourcentage de quantité d'un type par rapport à une famille



exemple pour l'id 1 :
P_ST_T = Qté Mandarine / Qté Orange * 100
P_ST_T = 2 / 20 * 100
P_ST_T = 10

P_T_F = Qté Orange / Qté Fruit * 100
P_T_F = 20 / 60 * 100
P_T_F = 33,33


Il faut faire ce calcul pour chaque ligne et les mettre à jour.

C'est-à-dire obtenir le tableau suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F | 
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  2  |   10   | 33.33 | 
|  2 | Fruit   |   Orange | Clementine| 10  |   50   | 33.33 |
|  3 | Fruit   |   Orange | Sanguine  |  8  |   40   | 33.33 |
|  4 | Fruit   |   Pomme  | Granny    | 20  |   50   | 66.67 |
|  5 | Fruit   |   Pomme  | Golden    |  8  |   20   | 66.67 |
|  5 | Fruit   |   Pomme  | Rouge     | 12  |   30   | 66.67 |
--------------------------------------------------------------

Est-il possible de faire ça avec une procèdure stockée ?


Merci

PoichOU

[al1_24]

Il suffit de calculer les totaux dans des tables dérivées :

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
select    X.ID 
    ,    X.FAMILLE
    ,    X.TYPE
    ,    X.SOUS_TYPE
    ,    X.QTE
    ,    (ST.QTE / T.QTE)    as P_ST_T
    ,    (T.QTE / F.QTE)    as P_T_F 
from    MATABLE    as X
    inner join
        (    select    SOUS_TYPE
                ,    sum(QTE)    as QTE
            from    MATABLE
            group by    SOUS_TYPE
        )    as ST
        on    X.SOUS_TYPE = ST.SOUS_TYPE
    inner join
        (    select    TYPE
                ,    sum(QTE)    as QTE
            from    MATABLE
            group by    TYPE
        )    as T
        on    X.TYPE = F.TYPE
    inner join
        (    select    FAMILLE
                ,    sum(QTE)    as QTE
            from    MATABLE
            group by    FAMILLE
        )    as F
        on    X.FAMILLE = F.FAMILLE
;

On peut aussi le faire sans tables dérivées en utilisant les fonctions de regroupement analytiques...

[al1_24]

On peut aussi le faire sans tables dérivées en utilisant les fonctions de regroupement analytiques...

Comme ça :

Code :

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

1
2
3
4
5
6
7
8
select    ID 
    ,    FAMILLE
    ,    TYPE
    ,    SOUS_TYPE
    ,    QTE
    ,    (sum(QTE) over(partition by SOUS_TYPE) / sum(QTE) over(partition by TYPE))    as P_ST_T
    ,    (sum(QTE) over(partition by TYPE) / sum(QTE) over(partition by FAMILLE))    as P_T_F 
from    MATABLE

[PoichOU]

Ouahou !! j'suis bluffé par les soluces !


j'ai même trouvé une p'tite erreur dans la première soluce (preuve que je l'ai comprise)

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
SELECT    X.ID 
    ,    X.FAMILLE
    ,    X.TYPE
    ,    X.SOUS_TYPE
    ,    X.QTE
    ,    (ST.QTE / T.QTE)    AS P_ST_T
    ,    (T.QTE / F.QTE)    AS P_T_F 
FROM    MATABLE    AS X
    INNER JOIN
        (    SELECT    SOUS_TYPE
                ,    sum(QTE)    AS QTE
            FROM    MATABLE
            GROUP BY    SOUS_TYPE
        )    AS ST
        ON    X.SOUS_TYPE = ST.SOUS_TYPE
    INNER JOIN
        (    SELECT    TYPE
                ,    sum(QTE)    AS QTE
            FROM    MATABLE
            GROUP BY    TYPE
        )    AS T
        ON    X.TYPE = T.TYPE
    INNER JOIN
        (    SELECT    FAMILLE
                ,    sum(QTE)    AS QTE
            FROM    MATABLE
            GROUP BY    FAMILLE
        )    AS F
        ON    X.FAMILLE = F.FAMILLE
;

merci al1_24 pour tes réponses aussi bonnes et rapide

Il me reste plus qu'à trouver comment faire ça dans une procèdure stockée...

[PoichOU]

Quelqu'un connait un bon tuto qui explique comment créer une procèdure stockée ?

Je voudrais mettre à jour chaque ligne de la table.

Pour info, j'utilise une base de données Oracle.

Voici le sript de création de la table :

Code :

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

1
2
3
4
5
6
7
8
9
10
create table TUTOS_PROC_STOCKEE  (
	ID 		INTEGER 	not null,
	FAMILLE		VARCHAR2(20)	not null,
	TYPE		VARCHAR2(20)	not null,
	SOUS_TYPE	VARCHAR2(20)	not null,
	QUANTITE	NUMBER		not null,
	P_ST_T 		NUMBER,
	P_T_F 		NUMBER,
    primary key (ID)
);

Voici le sript d'initialisation des données :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (1, 'Fruit', 'Orange', 'Mandarine', 2, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (2, 'Fruit', 'Orange', 'Clementine', 10, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (3, 'Fruit', 'Orange', 'Sanguine', 8, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (4, 'Fruit', 'Pomme', 'Granny', 20, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (5, 'Fruit', 'Pomme', 'Golden', 8, NULL, NULL);
insert into TUTOS_PROC_STOCKEE (ID,FAMILLE, TYPE, SOUS_TYPE, QUANTITE, P_ST_T, P_T_F) 
values (6, 'Fruit', 'Pomme', 'Rouge', 12, NULL, NULL);

Merci de m'aider

PoichOU

[al1_24]

Pourquoi vouloir le faire avec une procédure stockée alors qu'une vue est largement suffisante ?

[PoichOU]

Y a 2 raisons à ça : la première c'est que les pourcentages doivent être stockées dans la base et la seconde est que je voudrais essayé de le faire avec une procèdure sotckée. C'est un exemple très simplifié qui je l'espère va me permettre de faire ma première procèdure stockée.


J'ai réussi à faire une procédure qui met à jour la valeur de P_ST_T d'après un id et la nouvelle valeur du P_ST_T.

Code :

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

1
2
3
4
5
6
7
8
CREATE OR REPLACE PROCEDURE update_tutos_proc_stockee
(
	PN$ID IN TUTOS_PROC_STOCKEE.ID%Type,
	PN$pstt IN TUTOS_PROC_STOCKEE.P_ST_T%Type
) IS
  BEGIN
  UPDATE TUTOS_PROC_STOCKEE SET P_ST_T=PN$pstt WHERE ID=PN$ID;
  END;

Ce que je voudrais faire maintenant, c'est appeler cette procèdure pour chaque réponse de la vue.

Est-ce possible ?

[Vikisme]

Un trigger c'est pas mieux pour faire ça ?

Sinon pour ton problème... bah un curseur ça sert à ça nan ? C'est ce que tu cherches ?

[PoichOU]

Un trigger me permettrait d'executer un code à chaque insertion c'est ça ?
pourquoi pas, j'essayerai une fois que j'aurai réussi à le faire via une procèdure stockée.

Vikisme j'ai essayé d'utiliser un cursor mais sans succès, peut être que tu peux m'aider ?

Voilà la procèdure stockée que j'ai fait :

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
CREATE OR REPLACE PROCEDURE update_pourcent
(
    CHOIX_FAMILLE IN TUTOS_PROC_STOCKEE.FAMILLE%Type
)
IS
    DECLARE
    CURSOR vue IS 
        SELECT    ID 
                  ,(sum(QUANTITE) over(partition BY SOUS_TYPE) / sum(QUANTITE) over(partition BY TYPE))    AS P_ST_T
                  ,    (sum(QUANTITE) over(partition BY TYPE) / sum(QUANTITE) over(partition BY FAMILLE))    AS P_T_F 
        FROM    TUTOS_PROC_STOCKEE;
        WHERE   FAMILLE=CHOIX_FAMILLE;
 
        tutos_proc TUTOS_PROC_STOCKEE%ROWTYPE;
 
        BEGIN
                OPEN vue;
                LOOP
                        FETCH vue INTO tutos_proc;
                        UPDATE TUTOS_PROC_STOCKEE
                        SET P_ST_T=tutos_proc.P_ST_T WHERE ID=tutos_proc.ID;
                        EXIT WHEN vue%NOTFOUND;
                END LOOP;
        END;

ensuite je l'appel avec :

Code :

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

CALL update_pourcent('Fruit');

Mais j'obtient le message d'erreur suivant :

Code :

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

[CALL - 0 row(s), 0.031 secs]  [Error Code: 6575, SQL State: 65000]  ORA-06575: Fonction UPDATE_POURCENT est dans un état non valide

je débute complètement avec les procèdures stockées, pouvez vous m'aider ?

Merci

PoichOU

[SQLpro]

par principe (regles de non redondance) on ne stocke jamais les données calculées. Votre exemple n'est donc franchement pas bon.

Une requête en effet suffit à permettre de voir les infos.

A +

[PoichOU]

Je suis d'accord que l'exemple est certainement mal choisi, mais mon but ici est de comprendre comment on peut résoudre ce type de problèmatique avec des procèdures stockées.

[PoichOU]

Bonjour,

je resors mon p'tit topic car j'y un problème.

Sur les valeurs suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F |
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  2  | NULL   | NULL  |
|  2 | Fruit   |   Orange | Clementine| 10  | NULL   | NULL  |
|  3 | Fruit   |   Orange | Sanguine  |  8  | NULL   | NULL  |
|  4 | Fruit   |   Pomme  | Granny    | 20  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Golden    |  8  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Rouge     | 12  | NULL   | NULL  |
--------------------------------------------------------------

J'obtiens grâce à la requête d'al1_24 le tableau suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F | 
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  2  |   10   | 33.33 | 
|  2 | Fruit   |   Orange | Clementine| 10  |   50   | 33.33 |
|  3 | Fruit   |   Orange | Sanguine  |  8  |   40   | 33.33 |
|  4 | Fruit   |   Pomme  | Granny    | 20  |   50   | 66.67 |
|  5 | Fruit   |   Pomme  | Golden    |  8  |   20   | 66.67 |
|  5 | Fruit   |   Pomme  | Rouge     | 12  |   30   | 66.67 |
--------------------------------------------------------------

la requête d'al1_24 :

Code :

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

1
2
3
4
5
6
7
8
SELECT    ID 
    ,    FAMILLE
    ,    TYPE
    ,    SOUS_TYPE
    ,    QTE
    ,    (sum(QTE) over(partition BY SOUS_TYPE) / sum(QTE) over(partition BY TYPE))    AS P_ST_T
    ,    (sum(QTE) over(partition BY TYPE) / sum(QTE) over(partition BY FAMILLE))    AS P_T_F 
FROM    MATABLE

Mais si j'ai le tableau suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F |
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  0  | NULL   | NULL  |
|  2 | Fruit   |   Orange | Clementine|  0  | NULL   | NULL  |
|  3 | Fruit   |   Orange | Sanguine  |  0  | NULL   | NULL  |
|  4 | Fruit   |   Pomme  | Granny    | 20  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Golden    |  8  | NULL   | NULL  |
|  5 | Fruit   |   Pomme  | Rouge     | 12  | NULL   | NULL  |
--------------------------------------------------------------

Je tombe sur une erreur

Code :

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

ORA-01476: le diviseur est égal à zéro

car sum(QTE) over(partition BY TYPE) renvoi 0

Comment puis-je faire pour obtenir le resultat suivant :

Code :

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

1
2
3
4
5
6
7
8
9
10
--------------------------------------------------------------
| id | Famille |   Type   | Sous_type | Qte | P_ST_T | P_T_F | 
--------------------------------------------------------------
|  1 | Fruit   |   Orange | Mandarine |  0  |    0   |   0   | 
|  2 | Fruit   |   Orange | Clementine|  0  |    0   |   0   |
|  3 | Fruit   |   Orange | Sanguine  |  0  |    0   |   0   |
|  4 | Fruit   |   Pomme  | Granny    | 20  |   50   | 100   |
|  5 | Fruit   |   Pomme  | Golden    |  8  |   20   | 100   |
|  5 | Fruit   |   Pomme  | Rouge     | 12  |   30   | 100   |
--------------------------------------------------------------

quelqu'un peut saurait me dire comment je peux faire ?

[al1_24]

quelqu'un peut saurait me dire comment je peux faire ?

Tester le diviseur avant de faire la division...

[PoichOU]

oui c'est ce que je voudrais faire, mais je ne connais pas la syntaxe.

J'ai testé ça :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT    ID 
    ,    FAMILLE
    ,    TYPE
    ,    SOUS_TYPE
    ,    QTE
    ,    (sum(QTE) over(partition BY SOUS_TYPE) / sum(QTE) over(partition BY TYPE))    AS P_ST_T
    ,    (sum(QTE) over(partition BY TYPE) / sum(QTE) over(partition BY FAMILLE))    AS P_T_F 
FROM    MATABLE
WHERE sum(QTE) over(partition BY TYPE) <>0

Y a t il un moyen de mettre une clause IF ?


PoichOU

[al1_24]

Y a t il un moyen de mettre une clause IF ?

Pas de IF, mais CASE

[PoichOU]

merkiiiiiiiiiiiiiii