Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

2 Cours + Exercices sur Transact-SQL Transact – SQL Procedures Curseurs Déclenc

2 Cours + Exercices sur Transact-SQL Transact – SQL Procedures Curseurs Déclencheurs(Triggers) Table des matières I- NOTIONS EN TRANSACT SQL ................................................................................................... 3 I.1 La declaration d’une variable ..................................................................... 3 I.2 l’affectation d’une valeur à une variable ........................................................................... 3 I.3 affichage d’informations .................................................................................................. 4 I.4 les structures alternatives ............................................................................. 4 I.5 l’instruction case ............................................................................................................. 5 I.6 Exercices ................................................................................................. 5 I.7 les structures répétitives .............................................................................. 7 I.8 Le test de modification d’une colonne .............................................................................. 7 I.9 le branchement ......................................................................................... 8 I.10 la transaction .......................................................................................... 8 I.11 l’affichage des messages d’erreurs ............................................................................... 9 II- les curseurs ............................................................................................................................. 10 II.1 définition ...............................................................................................10 II.2 exercices ...............................................................................................12 III. La procédure stockée ............................................................................................................ 13 III.1 Création d'une procédure stockée ..................................................................13 III.2 Cryptage d’une procédure stockée .............................................................................. 18 III.3 suppression d’une procédure stockée ......................................................................... 19 III.4 modification d’une procédure stockée ......................................................................... 19 IV les fonctions ............................................................................................................................ 19 V- les déclencheurs (Triggers) ................................................................................................... 20 V.1 définition ..............................................................................................20 V.2 Types de Triggers .....................................................................................21 V.3 Fonctionnement des tables inserted et deleted ...................................................21 V.4 Fonctionnement des Triggers INSTEAD OF et AFTER.............................................................. 22 V.5 Création d’un déclencheur (Trigger) ............................................................................. 22 V.6 Exercices ...............................................................................................23 V.7 suppression d’un déclencheur(Trigger) ....................................................................... 25 V.8 modification d’un déclencheur(Trigger) ....................................................................... 25 VI les séries d’exercices ..................................................................................................................... 26 VI.1 Exercices sur les procédures ........................................................................26 Exercices sur les déclencheurs(Triggers) 31 VI.2 3 Cours + Exercices sur Transact-SQL Pour les exemples de ce cours, la base de données SQL Server GestionCom qui sera utilisée est comme suivante : I- NOTIONS EN TRANSACT SQL I.1 La déclaration d’une variable Syntaxe : Declare @Nom_Variable Type_Donnée Exemples :  Declare @a int  Declare @b varchar(10) Remarque : Par convention les noms des variables doivent toujours être précédés du symbole @ I.2 l’affectation d’une valeur à une variable Syntaxe : Select @Nom_Variable = valeur Select @Nom_Variable = (Select ...from...Where) ou Set @Nom_Variable =valeur Set @Nom_Variable = (Select ...from...Where) Exemples :  Set @a=1 -- Affecte la valeur 1 à la variable @a  Set @a=(Select count(NumArt) from Article) -- Affecte le nombre d'articles enregistrés dans la table article à la variable @a  Set @b='commande' -- Affecte la valeur 'commande' à la variable @b 4 Cours + Exercices sur Transact-SQL I.3 affichage d’informations Syntaxe : Print Elément_A_Afficher Exemples : Soient @a et @b des variables de type Chaîne de caractères, @c et @d des variables de type entier  Print 'Bonjour' -- Affiche le texte Bonjour  Print @a -- Affiche la valeur de @a  Print @c -- Affiche la valeur de @c  Print @c + @d -- Affiche la somme des variables @c et @d  Print convert(varchar, @c) + @b -- Affiche la valeur de @c concaténé avec la valeur de @b mais puisque @c est de type numérique et qu'on ne peut jamais concaténer une valeur numérique avec une valeur chaîne de caractères, il faut passer par une fonction de conversion dont la syntaxe est la suivante : Convert (Type de conversion, Valeur à convertir) I.4les structures alternatives If...Else : Syntaxe : If Condition Begin Instructions End Else Begin Instructions End Remarques :  Si une instruction Select apparaît dans la condition, il faut la mettre entre parenthèses  Si dans la clause If ou Else il existe une seule instruction, on peut omettre le Begin et le End Exemple : On souhaite vérifier si le stock de l'article portant le numéro 10 a atteint son seuil minimum. Si c'est le cas afficher le message 'Rupture de stock' : Declare @QS Declare @SM int Select @QS = (Select QteEnStock from article Where NumArt =10) Select @SM = (Select SeuilMinimum from article Where NumArt =10) If @QS<=@SM Print 'Rupture de stock' Else Print 'Stock disponible' 5 Cours + Exercices sur Transact-SQL I.5 l’instruction case Case : Permet d'affecter, selon une condition, une valeur à un champ dans une requête Select Syntaxe : Case When Condition1 Then Résultat 1 When Condition2 Then Résultat 2 ... Else Résultat N End Exemple : Afficher la liste des articles (Numéro, Désignation et prix) avec en plus une colonne Observation qui affiche 'Non Disponible' si la quantité en stock est égale à 0, 'Disponible' si la quantité en stock est supérieure au stock Minimum et 'à Commander' sinon. I.6Exercices : 1.. Ecrire un programme qui calcule le montant de la commande numéro 10 et affiche un message 'Commande Normale' ou 'Commande Spéciale' selon que le montant est inférieur ou supérieur à 100000 DH 2.. Ecrire un programme qui supprime l'article numéro 8 de la commande numéro 5 et met à jour le stock. Si après la suppression de cet article, la commande numéro 5 n'a plus d'articles associés, la supprimer. 3.. Ecrire un programme qui affiche la liste des commandes et indique pour chaque commande dans une colonne Type s'il s'agit d'une commande normale (montant <=100000 DH) ou d'une commande spéciale (montant > 100000 DH) 4.. A supposer que toutes les commandes ont des montants différents, écrire un programme qui stocke dans une nouvelle table temporaire les 5 meilleures commandes (ayant le montant le plus élevé) classées par montant décroissant (la table à créer aura la structure suivante : NumCom, DatCom, MontantCom) 5.. Ecrire un programme qui :  Recherche le numéro de commande le plus élevé dans la table commande et l'incrémente de 1  Enregistre une commande avec ce numéro  Pour chaque article dont la quantité en stock est inférieure ou égale au seuil minimum enregistre une ligne de commande avec le numéro calculé et une quantité commandée égale au triple du seuil minimum Select NumArt, DesArt, PUArt, 'Observation' = Case When QteEnStock=0 then 'Non Disponible' When QteEnStock>SeuilMinimum then 'Disponible' Else 'à Commander' End From Article 6 Cours + Exercices sur Transact-SQL Solution : 1. 2. 3. 4. Create Table T1 (NumCom int, DatCom DateTime, MontantCom decimal) Insert into T1 Select Top 5 C.NumCom, DatCom, Sum(PUArt*QteCommandee) as Mt From Commande C, Article A, LigneCommande LC Where C.NumCom=LC.NumCom and LC.NumArt=A.NumArt Group by C.NumCom, DatCom Order by Mt Desc Select C.NumCom, DatCom, Sum(PUArt*QteCommandee), 'Type'= Case When Sum(PUArt*QteCommandee) <=10000 then 'Commande Normale' Else 'Commande Spéciale' End From Commande C, Article A, LigneCommande LC Where C.NumCom=LC.NumCom and LC.NumArt=A.NumArt Group by C.NumCom, DatCom Declare @Qte decimal Set @Qte=(select QteCommandee from LigneCommande where NumCom=5 and NumArt=8) Delete from LigneCommande where NumCom=5 and NumArt=8 Update article set QteEnStock=QteEnStock+@Qte where NumArt=8 if not exists (select numcom from LigneCommande where NumCom=5) Delete from commande where NumCom=5 Declare @Montant decimal Set @Montant=(Select Sum(PUArt*QteCommandee) from Commande C, Article A, LigneCommande LC where C.NumCom=LC.NumCom and LC.NumArt=A.NumArt and C.NumCom=10) If @Montant is null Begin Print 'Cette Commande n''existe pas ou elle n''a pas d''ingrédients' Return End if @Montant <=10000 Print 'Commande Normale' Else Print 'Commande Spéciale' 7 Cours + Exercices sur Transact-SQL If update (numCom) Print 'Numéro de commande modifié' 5. I.7 les structures répétitives Syntaxe : While Condition Begin instructions End Remarques :  Le mot clé Break est utilisé dans une boucle While pour forcer l'arrêt de la boucle  Le mot clé Continue est utilisé dans une boucle While pour annuler l'itération en cours et passer aux itérations suivantes (renvoyer le programme à la ligne du while) Exemple : Tant que la moyenne des prix des articles n'a pas encore atteint 20 DH et le prix le plus élevé pour un article n'a pas encore atteint 30 DH, augmenter les prix de 10% et afficher après chaque modification effectuée la liste des articles. Une fois toutes les modifications effectuées, afficher la moyenne des prix et le prix le plus élevé : I.8 Le test de modification d’une colonne L'instruction If Update renvoie une valeur true ou false pour déterminer si une colonne spécifique d'une table a été modifié par une instruction insert ou update (cette instruction est utilisée spécialement dans les déclencheurs et ne s'applique pas à une instruction Delete). Syntaxe : If Update (Nom_Colonne) Begin … End Exemple : While ((Select avg(puart) from article)<20) and (select max(puart) from article) <30) Begin Update article Set puart=puart+(puart*10)/100 Select * from article End Select avg(puart) as moyenne , max(puart) as [Prix élevé] from article if exists(select NumArt from article where QteEnStock<=SeuilMinimum) Begin Declare @a int set @a=(select max(NumCom) from commande) + 1 insert into commande values(@a, getdate()) insert into lignecommande Select @a, NumArt, SeuilMinimum * 3 From article Where QteEnStock <=SeuilMinimum End 8 Cours + Exercices sur Transact-SQL I.9le branchement L'instruction Goto renvoie l'exécution du programme vers un point spécifique repéré par une étiquette Syntaxe : Goto Etiquette Remarque : Pour créer une étiquette, il suffit d'indiquer son nom suivi de deux points (:) Exemple : L'exemple précédent peut être écrit ainsi en utilisant l'instruction goto : I.10 la transaction Une transaction permet d'exécuter un groupe d'instructions. Si pour une raison ou une autre l'une de ces instructions n'a pas pu être exécutée, tous le groupe d'instructions est annulé (le tout ou rien) :  Pour démarrer une transaction on utilise l'instruction Begin Tran  Pour valider la transaction et rendre les traitements qui lui sont associés effectifs, on utilise l'instruction Commit Tran  Pour interrompre une transaction en cours qui n'a pas encore été uploads/Industriel/ t-sql.pdf