SHARE
TWEET

Untitled

a guest Nov 19th, 2019 103 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP PROCEDURE IF EXISTS exercice;
  2. DELIMITER |
  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|
  77. DELIMITER ;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top