RqLAUxmE

1DROP PROCEDURE IF EXISTS exercice;
2DELIMITER |
3    CREATE PROCEDURE exercice ()
4    BEGIN
5
6    DECLARE T_noCli,T_tempnoCli   char(5);
7    DECLARE T_nom     varchar(25);
8    DECLARE T_rue     varchar(25);
9    DECLARE T_ville   varchar(25);
10    DECLARE T_tel  char(10);
11
12    DECLARE T_noCmpt      char(7);
13    DECLARE T_dateCreate  date;
14    DECLARE T_typeCmpt    varchar(15);
15    DECLARE T_soldeOuverture decimal(15,2);
16    DECLARE T_solde       decimal(15,2);
17    DECLARE T_noSucc      char(3);
18
19    DECLARE T_noTrx     int;
20    DECLARE T_dateTrx   date;
21    DECLARE T_montant   decimal(15,2);
22    DECLARE T_descTrx	  varchar(25);
23    DECLARE T_nature		char(2);
24
25    DECLARE fin BOOLEAN DEFAULT FALSE;
26    DECLARE curseur CURSOR FOR
27        select noCli,noCmpt,nom,rue,ville,tel,soldeOuverture,solde,descTrx,montant,nature
28        from client natural join aacces natural join compte natural join trx
29        ORDER BY noCli;
30
31    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;
32    CREATE TABLE LSTCOM( CH1 CHAR(30),CH2 CHAR(35),CH3 CHAR(60),CH4 CHAR(30),CH5 CHAR(2);
33    OPEN curseur;
34    FETCH curseur INTO  T_noCli,T_noCmpt,T_nom,T_rue,T_ville,T_tel,T_soldeOuverture,T_solde,T_descTrx,T_montant,T_nature;
35    set T_tempnoCli = T_noCli;
36    INSERT INTO LSTCOM(CH1,CH2,CH3,CH4,CH5) VALUES (T_noCli,T_nom,T_tel,'','');
37
38    while NOT fin DO
39    IF(T_tempnoCli = T_noCli)
40        THEN 
41            INSERT INTO LSTCOM VALUES ('',T_noCmpt,T_soldeOuverture,T_solde,'');
42            INSERT INTO LSTCOM VALUES ('',T_dateTrx,T_descTrx,'montant CR','montant DB');
43
44            If(T_nature = 'CR')
45            THEN
46                INSERT INTO LSTCOM VALUES ('','','',T_montant,'');
47            end IF;
48
49            If(T_nature = 'DB')
50            THEN
51                INSERT INTO LSTCOM VALUES ('','','','',T_montant);
52            end IF;
53        ELSE 
54            BEGIN
55                INSERT INTO LSTCOM VALUES ('','','','','');
56                INSERT INTO LSTCOM(CH1,CH2,CH3,CH4,CH5) VALUES (T_noCli,T_nom,T_tel,'','');
57            END;
58        
59    END IF;
60    FETCH curseur INTO  T_noCli,T_noCmpt,T_nom,T_rue,T_ville,T_tel,T_soldeOuverture,T_solde,T_descTrx,T_montant,T_nature;
61    END WHILE;
62
63            CLOSE curseur;
64        SELECT * FROM LSTCOM;
65        DROP TABLE LSTCOM;
66
67
68
69
70
71
72
73
74   
75
76    END|
77DELIMITER ;