Exercice Langage SQL : Procédures Stockées (Cas Compte Bancaire)

Rédigé par GC Team, Publié le 13 Décembre 2011, Mise à jour le Mardi, 30 Novembre 1999 00:00
Participez au vote ☆☆☆☆☆★★★★★

EXERCICE " COMTES BANCAIRES " : Procédures stockées 

Contexte de travail :

Vous participez à la réalisation d'un logiciel permettant de gérer les comptes bancaires d'un particulier.

Le fonctionnement prévu est le suivant :

Dans un premier temps, l'utilisateur définit les différents comptes à gérer en déclarant pour chacun le numéro de compte, le nom de la banque, ...

L'utilisateur saisit ensuite chacune des opérations réalisées sur l'un de ces comptes. Une opération correspond à un débit (paiement par chèque de la facture de téléphone, retrait d'espèces à l'aide d'une carte bancaire, ...) ou à un crédit (dépôt à la banque de chèques reçus par exemple).

Chaque fin de mois, l'utilisateur reçoit un "relevé de compte" (un par compte bancaire). Il effectue alors le rapprochement entre les opérations qu'il a saisies et celles constatées par la banque :

  • Pointage des opérations saisies apparaissant sur le relevé. Ce pointage consiste à mémoriser le fait qu'une opération a été constatée par la banque. L'opération est dite "pointée" et le numéro du relevé est mémorisé.
  • Ajout des opérations non saisies apparaissant sur le relevé (réception d'un virement correspondant au salaire mensuel par exemple). Ces opérations sont automatiquement pointées puisqu'elles ont été constatées par la banque.

Solde des comptes

Les opérations permettent de calculer le solde d'un compte, différence entre la somme des

opérations de crédit et la somme des opérations de débit.

En fait, chaque compte possède deux soldes :

- Le solde réel qui prend en considération toutes les opérations saisies par l'utilisateur, qu'elles aient été constatées ou non par la banque.

- Le solde en banque qui ne prend en considération que les opérations constatées par la banque, c'est-à-dire ayant fait l'objet d'un "pointage" à partir d'un relevé.

L'application est développée autour d'un SGBDR. Au niveau physique, on trouve notamment deux tables :

- COMPTE, table mémorisant les informations concernant les comptes courants.

- OPERATION, table mémorisant chaque opération concernant un compte courant.

Le calcul des soldes des comptes conduit à additionner un grand nombre d'opérations, et ce depuis la mise en service de l'application. Pour éviter ces calculs, il a été décidé de mémoriser les soldes réel et "en banque" dans la table COMPTE.

Dès lors se pose le problème de la cohérence entre les opérations et le solde des comptes. La solution adoptée est le recours à un ensemble de procédures stockées et de déclencheurs (triggers) destinés à maintenir à jour les soldes dans la table COMPTE.

Schéma de la base de données

BANQUE (Bid, Bnom)

COMPTE (Cid, Cnumero, Clibelle, CsoldeReel, CsoldeBanque, Cbanque)

Cbanque : clé étrangère en référence à Bid de BANQUE

OPERATION (Oid, Odate, Omontant, Osens, Oreleve, Ocompte)

Oreleve : clé étrangère en référence à Rid de RELEVE

Ocompte : clé étrangère en référence à Cid de COMPTE

RELEVE(Rid, Rdate, Rcompte)

Rcompte : clé étrangère en référence à Cid de COMPTE

Remarques :

- CsoldeReel et CsoldeBanque représentent respectivement le "solde réel" et le "solde en banque" du compte.

- Osens est un caractère mémorisant la nature de l'opération ('d' pour débit, opération diminuant le solde du compte ; 'c' pour crédit, opération augmentant le solde du compte).

- Oreleve contient le numéro du relevé (Rid) ayant permis de pointer l'opération. Cet attribut contient la valeur NULL si l'opération n'a pas encore été pointée.

Travail à Réaliser

  • Rédigez l'ensemble des procédures stockées et des déclencheurs nécessaires à la gestion des informations calculées CsoldeReel et CsoldeBanque
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

CREATE PROCEDURE MAJSOLDEBANQUE
PARAMETERS
@idCompte : entier
@sens : caractère
@montant : réel
begin
 IF (sens='c') then
UPDATE compte SET CsoldeBanque=CsoldeBanque+@montant
WHERE Cid=@idCompte
                else
                               UPDATE compte SET CsoldeBanque=CsoldeBanque-@montant
                               WHERE Cid=@idCompte
                endif
end
CREATE PROCEDURE MAJSOLDEREEL
PARAMETERS
@idCompte entier
@sens : caractère
@montant : réel
begin
                IF (@sens='c') then
                               UPDATE compte SET CsoldeReel=CsoldeReel+@montant WHERE Cid=@idCompte
                else
                               UPDATE compte SET CsoldeReel=CsoldeReel-@montant WHERE Cid=@idCompte
                endif
end
CREATE TRIGGER trigInsOperation ON INSERT FOR OPERATION                      
VARIABLES
@sens : caractère
begin
                majSoldeReel(new.Ocompte, new.Osens, new.Omontant)
                IF (new.Oreleve IS NOT NULL) then
                               majSoldeBanque(new.Ocompte, new.Osens, new.Omontant)
                endif
end
CREATE TRIGGER trigUpdateOperation ON UPDATE FOR OPERATION                      
VARIABLES
@sens : caractère
begin
                // Annulation du montant originel de l"'"opération en affectant
                // ce même montant en sens inverse              
                IF (old.Osens='c') then @sens='d' else @sens='c' endif
                majSoldeReel(old.Ocompte, @sens, old.Omontant)
                IF (old.Oreleve IS NOT NULL) then
                               majSoldeBanque(old.Ocompte, @sens, old.Omontant)
                endif
                // MAJ du compte par le nouveau montant
majSoldeReel(new.Ocompte, new.Osens, new.Omontant)
                IF (new.Oreleve IS NOT NULL) then
                               majSoldeBanque(new.Ocompte, new.Osens, new.Omontant)
                endif
end
CREATE TRIGGER trigDelOperation ON DELETE FOR OPERATION                      
VARIABLES
@sens : caractère
begin
 
IF (old.Osens='c') then @sens='d' else @sens='c' endif
                majSoldeReel(old.Ocompte, @sens, old.Omontant)
                IF (old.Oreleve IS NOT NULL) then
                               majSoldeBanque(old.Ocompte, @sens, old.Omontant)
                endif
end