Université Sultan Moulay Slimane A.
U
Faculté des Sciences et Techniques 2020/2021
Béni Mellal LST INFO
Département Informatique
TP 2 --- Transact-SQL
Procédures stockées, fonctions et triggers
Exercice1 : écrire des procédures stockées qui :
affiche la liste des produits et leur libelle
calcule le nombre de produits par commande
affiche la liste des commandes effectuées entre deux dates
retourne le nombre de commandes
retourne le nombre de produits d’une commande dont le numéro est
passé en entrée
Exercice 2 : soit le modèle relationnel suivant :
Mouvement (num_mvt, type, qte, cod_prod_fini#)
Produitfini (cod_prod_fini, nom, qtestock)
Composition (cod_prod_fini, cod_prod_brut, qte_util)
Fournisseur (num_fr, nom_fr, adr_fr, nb_prod_fournis)
Produitbrut (cod_prod_brut, nom, prix, num_fr#)
créer les tables mouvement, produitfini et fournisseur
Créer les procédures stockées qui :
i. Crée les tables produitbrut et composition ;
ii. Affiche le nombre de produits bruts par produit fini ;
iii. Retourne le prix d’achat le plus elevé ;
iv. Affiche pour chaque produit fini :
La quantité en stock pour ce produit
La liste des mouvements concernant ce produit
La quantité totale en sortie et la quantité totale en entrée
La différence qui sera comparée à la quantité en stock. Si elle
correspond, afficher ‘stock ok’ sinon ‘problème de stock’
Exercice 3:
Toujours avec le même exemple de DB_LIGNE_COMMANDE, créez les fonctions
suivantes :
a. créez une fonction qui retourne le nombre des produits existants
b. créez une fonction qui retourne le nombre total des commandes
c. créez une fonction qui retourne le nombre de produits commandés par
une commande donnée
d. recréez cette fonction en retournant une table
e. créez une fonction qui retourne le prix total d’une commande donnée
f. afficher le résultat sous la forme suivante : ‘le prix total de la
commande : est : ’
g. recréez cette fonction sous la forme d’une table
h. quelle est la commande du prix élevé ?
i. créez une fonction qui affiche l’état d’une commande selon le nombre
de produits qu’elle contient : si ce nombre est inférieur à une valeur x
‘petite’
si ce nombre est entre les valeurs données x et y
‘moyenne’
autrement ‘grande’
j. retourner une table à l’aide de cette fonction (numéro de la commande
et son état)
k. Créez une fonction qui effectue le même traitement que (f) en
n’affichant que les commandes ayant un nombre supérieur à un seuil
minimal qui est supposé <<< x
Exercice4 : Sur la base de données Ligne de commande que nous avons déjà vu,
essayer d’écrire les triggers qui :
a. Interdit la modification des commandes
b. Empêche la suppression des commandes qui ont des articles associés
c. Après une suppression d’une ligne de commande, remet le stock à jour
et vérifie s’il s’agit de la dernière ligne pour cette commande ; si oui la
commande sera supprimée
d. Vérifie les quantités disponibles après l’ajout d’une ligne de commande
et met le stock à jour
e. Vérifie les quantités disponibles après la modification d’une ligne de
commande et met le stock à jour
f. Ajoute un produit tout en vérifiant son existence ; s’il existe ; il doit
afficher un message d’erreur ; sinon il l’insère dans la table ;
Au lieu d’effectuer une suppression d’un produit, liste tous produits existants avec
leurs quantités en stock.
