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

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