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

Les meilleurs professeurs d'Initiation informatique disponibles
Laurent
4,5
4,5 (69 avis)
Laurent
60€
/h
Gift icon
1er cours offert !
Ilian
4,9
4,9 (21 avis)
Ilian
25€
/h
Gift icon
1er cours offert !
Houssem
4,9
4,9 (70 avis)
Houssem
60€
/h
Gift icon
1er cours offert !
Adrien
4,8
4,8 (56 avis)
Adrien
60€
/h
Gift icon
1er cours offert !
Sylvain
4,7
4,7 (26 avis)
Sylvain
20€
/h
Gift icon
1er cours offert !
Patrick
5
5 (33 avis)
Patrick
75€
/h
Gift icon
1er cours offert !
Thibault
5
5 (21 avis)
Thibault
50€
/h
Gift icon
1er cours offert !
Matthieu
4,9
4,9 (28 avis)
Matthieu
35€
/h
Gift icon
1er cours offert !
Laurent
4,5
4,5 (69 avis)
Laurent
60€
/h
Gift icon
1er cours offert !
Ilian
4,9
4,9 (21 avis)
Ilian
25€
/h
Gift icon
1er cours offert !
Houssem
4,9
4,9 (70 avis)
Houssem
60€
/h
Gift icon
1er cours offert !
Adrien
4,8
4,8 (56 avis)
Adrien
60€
/h
Gift icon
1er cours offert !
Sylvain
4,7
4,7 (26 avis)
Sylvain
20€
/h
Gift icon
1er cours offert !
Patrick
5
5 (33 avis)
Patrick
75€
/h
Gift icon
1er cours offert !
Thibault
5
5 (21 avis)
Thibault
50€
/h
Gift icon
1er cours offert !
Matthieu
4,9
4,9 (28 avis)
Matthieu
35€
/h
Gift icon
1er cours offert !
C'est parti

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
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)

            Bid : clé primaire

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

            Cid : clé primaire

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

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

            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
procédure stockée

            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
déclencheur

            create trigger <nom_trigger> on (insert | update
| delete ) for <nom_table>

            variables

            <nom_variable> : <type_variable>         // n fois

            begin

                        // liste d'instructions

            end

> Syntaxe du langage
de définition des procédures stockées

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

UPDATE         NEW.<attribut>            Nouvelle valeur (indiquée dans
l'instruction UPDATE)

DELETE          OLD.<attribut> Valeur de l'attribut avant la suppression du
n-uplet

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
: 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

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.

Vous avez aimé cet article ? Notez-le !

Aucune information ? Sérieusement ?Ok, nous tacherons de faire mieux pour le prochainLa moyenne, ouf ! Pas mieux ?Merci. Posez vos questions dans les commentaires.Un plaisir de vous aider ! :) 4,00 (3 note(s))
Loading...

Pierre

💾 Informaticien de Formation, je partage ici mes conseils pour vous aider à progresser en informatique et bureautique 💻