Chapitres
- 01. Delete from client where nomcli = 'Dupont'
- 02. Update clients set precli = 'zoe' where nomcli = 'Durand'
- 03. EXERCICE " COMTES BANCAIRES " : Procédures stockées
- 04. Contexte de travail
- 05. Travail à Réaliser
- 06. Correction
- 07. Question 1 :
- 08. Correction exercice " comptes bancaires : procédures stockées "Question 2 :
- 09. Question 3 :
Modèle relationnel :
- Clients (nucli,nomcli, precli, adcli, cpcli, vilcli, telcli)
- Categ (codcat, libcat)
- Articles (refart,desart,phart, codcat#)
- Stock (refart, coddépot, qtesto, stomini, stomaxi)
- Commandes (nucmd,datecmd, txremise, nucli#, etatcmd)
Contraintes : pour nucmd, les n° doivent être affectés dans l'ordre de création, etatcmd ne peut prendre que les valeurs : EC(encours), LP(livrée partiellement),
LI(livrée) ;SO(soldée)
Ligcmd (nucmd, nulig,
refart#, qtecmd)
Histofac ( nufac,
datefac, nucmd#, totht, étatfac)
Contraintes :étatfac prend les valeurs NR(non réglée) ; RP 'réglée
partiellement), RT (réglée totalement)
Principe des mises à jour :
Les mises à jour sont enregistrées en mémoires dans des
tables logiques : inserted, deleted ou updated selon l'instruction sql
utilisée INSERT, DELETE ou UPDATE.
Insert : les nouvelles lignes ajoutées le sont dans la table d'origine et dans la table inserted de la table origine.
Insert into client (nucli, nomcli,precli) values (4, 'Martin','Marc')
Table clients origine
nucli | Nomcli | precli |
1 | Albert | charles |
2 | Dupont | leon |
3 | Durand | Jean |
4 | Martin | Marc |
Table inserted :
nucli | Nomcli | precli |
4 | Martin | Marc |
Delete : suppression des lignes dans la table origine et ajout des lignes supprimées dans la table deleted
Delete from client where nomcli = 'Dupont'
Table clients origine
nucli | Nomcli | precli |
1 | Albert | charles |
3 | Durand | Jean |
4 | Martin | Marc |
Table Deleted
nucli | Nomcli | precli |
2 | Dupont | leon |
Update : les lignes d'origine (sans modif) sont dans la table
Deleted, les lignes modifiées sont dans Inserted et dans la table d'origine.
Update clients set precli = 'zoe' where nomcli = 'Durand'
Table clients origine
nucli | Nomcli | precli |
1 | Albert | charles |
3 | Durand | Zoe |
4 | Martin | Marc |
Table Deleted
nucli | Nomcli | precli |
3 | Durand | Jean |
Table Inserted :
nucli | Nomcli | precli |
3 | Durand | Zoe |
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 - 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 - 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. Schéma de la base de BANQUE (Bid, Bnom) Bid : clé primaire COMPTE (Cid, Cnumero, Cid : clé primaire Cbanque : clé étrangère en référence à Bid de BANQUE OPERATION (Oid, Odate, Oid : clé primaire 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) Rid : clé primaire 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. Extrait de la documentation du SGBD utilisé > Création d'une create procedure <nom_procédure> parameters <nom_paramètre> : <type_paramètre> // n fois variables <nom_variable> : <type_variable> // n fois begin // liste d'instructions end > Création d'un create trigger <nom_trigger> on (insert | update variables <nom_variable> : <type_variable> // n fois begin // liste d'instructions end > Syntaxe du langage Le langage permet l'utilisation de tous les types de données courants pour la déclaration des paramètres et des variables. Les instructions "classiques" sont présentes : affectation, conditionnelle, boucle, appel de procédure, ... > Intégration de SQL Le langage de définition des procédures stockées supporte l'utilisation de requêtes SQL selon la norme d'intégration de SQL dans un langage hôte (variables hôtes notamment). > Accès au n-uplet concerné par un trigger Lors de l'écriture d'un trigger, il est possible d'accéder aux valeurs des attributs du n-uplet concerné par l'opération. Le tableau ci-dessous résume les possibilités. Opération Syntaxe Signification INSERT NEW.<attribut> Valeur de l'attribut indiquée dans l'instruction INSERT UPDATE OLD.<attribut> Valeur de l'attribut avant l'instruction UPDATE NEW.<attribut> Nouvelle valeur (indiquée dans DELETE OLD.<attribut> Valeur de l'attribut avant la suppression du |
Travail à Réaliser
1. Présentez le schéma entité-association correspondant au schéma de la base de données.2. Rédigez l'ensemble des procédures stockées et des déclencheurs nécessaires à la gestion des informations calculées CsoldeReel et CsoldeBanque.3. Discutez de l'intérêt de cette solution. |
NB : Old est ce que l'on
appelle Deleted en SQLSERVER, New correspond à Inserted
Correction
Question 1 :
Remarque : la présence des propriétés calculées CsoldeReel et CsoldeBanque sur le schéma est discutable, mais elles permettent une meilleure appréhension des réalités manipulées, pour le développeur comme pour l'utilisateur. |
Correction exercice " comptes bancaires : procédures stockées "Question 2 :
CREATE PROCEDURE MAJSOLDEBANQUE PARAMETERS @idCompte @sens @montant : réel begin if (sens='c') then update compte set where else update compte set where endif end CREATE PROCEDURE MAJSOLDEREEL PARAMETERS @idCompte @sens @montant : réel begin if (@sens='c') then update compte set else update compte set 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 begin // Annulation du montant originel de l'opération en // ce même montant en sens inverse if (old.Osens='c') then @sens='d' else @sens='c' 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, 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 majSoldeReel(old.Ocompte, @sens, old.Omontant) if (old.Oreleve is not null) then majSoldeBanque(old.Ocompte, endif end |
Question 3 :
Le fait de maintenir ces informations calculées permettra d'obtenir de meilleurs temps de réponse lors de la consultation des comptes. La centralisation du calcul au niveau du SGBD permet d'éviter de nombreuses erreurs de codage dans les différents écrans ou états utilisant ces informations. De la même manière, la maintenabilité de l'application se trouve améliorée. Dans le cas d'une utilisation de la base de données dans une architecture client/serveur (ce qui est assez improbable dans le contexte décrit), la solution des procédures stockées permet également de minimiser le trafic sur le réseau et d'optimiser les temps d'exécution. |
Si vous désirez une aide personnalisée, contactez dès maintenant l’un de nos professeurs !
l’honneur de vous présenter ma demande
je suis ingénieur en programmation,
et je sollicite d’aide concernant la création d’une application de soit vb.net ou cshap connecter a sql server capable de faire sortir la liste de compte et le journal enfin le grand livre
Bonjour ! Superbe projet que celui de créer une application ! Vous pourrez y arriver avec bien plus de facilités grâce à nos professeurs, partout en France : https://www.superprof.fr/cours/developpement-web/france/
N’hésitez pas à les contacter, ils/elles se feront une joie de vous aider dans ce projet !
Bonne journée !
Bonjour Ir en programmation mobile suis entrain de travail sur une application de microcredit, je voulais juste vous demande comment on peut calculer le taux d’interet avec un trigger un fois le clien prend un pret de deux mois avec taux d’interet de 7.155 pas annee