Remerciez-le!

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

SQL : proc´ edures stock´ ees et triggers Achref El Mouelhi Docteur de l’univer

SQL : proc´ edures stock´ ees et triggers Achref El Mouelhi Docteur de l’universit´ e d’Aix-Marseille Chercheur en programmation par contrainte (IA) Ing´ enieur en g´ enie logiciel elmouelhi.achref@gmail.com 06 Octobre 2017, H & H: Research and Training 1 / 20 Plan 1 Proc´ edures stock´ ees 2 D´ eclencheurs 06 Octobre 2017, H & H: Research and Training 2 / 20 Proc´ edures stock´ ees SQL Proc´ edures stock´ ees (stored procedures) Disponible depuis la version 5 de MySQL Ensemble d’instructions SQL portant un nom, qu’on peut l’utiliser pour l’appeler : call nomProcedure() Facilitant certains traitement sur une ou plusieurs tables (possibilit´ e d’effectuer de tests, boucles...) Minimisant le traﬁc entre le client et le serveur de donn´ ees 06 Octobre 2017, H & H: Research and Training 3 / 20 Proc´ edures stock´ ees SQL Pour cr´ eer une proc´ edure stock´ ee CREATE PROCEDURE nomProcedure (les param` etres) BEGIN -- traitements = les instructions SQL END 06 Octobre 2017, H & H: Research and Training 4 / 20 Proc´ edures stock´ ees SQL Pour cr´ eer une proc´ edure stock´ ee CREATE PROCEDURE nomProcedure (les param` etres) BEGIN -- traitements = les instructions SQL END Remarques Chaque instruction SQL d’une proc´ edure doit se terminer par ; (d´ elimiteur) Chaque requˆ ete SQL doit aussi se terminer par ; Il faut changer le d´ elimiteur avant le d´ ebut de la proc´ edure et le remettre ` a la ﬁn 06 Octobre 2017, H & H: Research and Training 4 / 20 Proc´ edures stock´ ees SQL Pour cr´ eer une proc´ edure stock´ ee DELIMITER | CREATE PROCEDURE nomProcedure (les param` etres) BEGIN -- traitements = les instructions SQL END | DELIMITER ; 06 Octobre 2017, H & H: Research and Training 5 / 20 Proc´ edures stock´ ees SQL Exemple Cr´ eons une proc´ edure stock´ ee qui augmente la salaire de la personne ayant le salaire minimum dans la table personne Le montant ` a ajouter au salaire est pass´ e en param` etre 06 Octobre 2017, H & H: Research and Training 6 / 20 Proc´ edures stock´ ees SQL Exemple Cr´ eons une proc´ edure stock´ ee qui augmente la salaire de la personne ayant le salaire minimum dans la table personne Le montant ` a ajouter au salaire est pass´ e en param` etre D´ eclaration de la proc´ edure DELIMITER | CREATE PROCEDURE augmenterSalaireMin(somme int) BEGIN DECLARE id int; SELECT num INTO id FROM personne WHERE salaire = (SELECT MIN(salaire) FROM personne); UPDATE personne SET salaire = salaire + somme WHERE num = id; END | DELIMITER ; 06 Octobre 2017, H & H: Research and Training 6 / 20 Proc´ edures stock´ ees SQL Explication DECLARE permet de d´ eclarer une variable et DEFAULT de l’initialiser INTO permet d’indiquer le nom de la variable dans laquelle on va placer le contenu du SELECT 06 Octobre 2017, H & H: Research and Training 7 / 20 Proc´ edures stock´ ees SQL Explication DECLARE permet de d´ eclarer une variable et DEFAULT de l’initialiser INTO permet d’indiquer le nom de la variable dans laquelle on va placer le contenu du SELECT Remarque SELECT INTO permet de s´ electionner seulement une seule ligne. Une erreur sera g´ en´ er´ ee si la requˆ ete s´ electionne plusieurs lignes. En cas de doute, pensez ` a ajouter LIMIT 1. 06 Octobre 2017, H & H: Research and Training 7 / 20 Proc´ edures stock´ ees SQL Appel de la proc´ edure (ex´ ecution) CALL augmenterSalaireMin(50); 06 Octobre 2017, H & H: Research and Training 8 / 20 Proc´ edures stock´ ees SQL Appel de la proc´ edure (ex´ ecution) CALL augmenterSalaireMin(50); Pour consulter le code de la proc´ edure augmenterSalaireMin SHOW CREATE PROCEDURE augmenterSalaireMin; 06 Octobre 2017, H & H: Research and Training 8 / 20 Proc´ edures stock´ ees SQL Appel de la proc´ edure (ex´ ecution) CALL augmenterSalaireMin(50); Pour consulter le code de la proc´ edure augmenterSalaireMin SHOW CREATE PROCEDURE augmenterSalaireMin; Pour supprimer la proc´ edure augmenterSalaireMin DROP PROCEDURE IF EXISTS augmenterSalaireMin; 06 Octobre 2017, H & H: Research and Training 8 / 20 Proc´ edures stock´ ees SQL Appel de la proc´ edure (ex´ ecution) CALL augmenterSalaireMin(50); Pour consulter le code de la proc´ edure augmenterSalaireMin SHOW CREATE PROCEDURE augmenterSalaireMin; Pour supprimer la proc´ edure augmenterSalaireMin DROP PROCEDURE IF EXISTS augmenterSalaireMin; Remarque On ne peut modiﬁer une proc´ edure avec MySQL. Il faut donc supprimer puis recr´ eer. 06 Octobre 2017, H & H: Research and Training 8 / 20 Proc´ edures stock´ ees SQL Il est possible d’utiliser une structure de contrˆ ole de type if ... then ... else DELIMITER | CREATE PROCEDURE augmenterSalaireMin(somme int) BEGIN DECLARE id INT; DECLARE smic INT; DECLARE min INT; SET smic = 1200; SELECT MIN(salaire) INTO min FROM personne; SELECT num INTO id FROM personne WHERE salaire = min LIMIT 1; IF min > smic THEN UPDATE personne SET salaire = salaire + somme WHERE num = id; ELSE UPDATE personne SET salaire = smic + somme WHERE num = id; END IF; END | DELIMITER ; 06 Octobre 2017, H & H: Research and Training 9 / 20 Proc´ edures stock´ ees SQL Il est possible d’utiliser une structure de contrˆ ole de type if ... then ... else DELIMITER | CREATE PROCEDURE augmenterSalaireMin(somme int) BEGIN DECLARE id INT; DECLARE smic INT; DECLARE min INT; SET smic = 1200; SELECT MIN(salaire) INTO min FROM personne; SELECT num INTO id FROM personne WHERE salaire = min LIMIT 1; IF min > smic THEN UPDATE personne SET salaire = salaire + somme WHERE num = id; ELSE UPDATE personne SET salaire = smic + somme WHERE num = id; END IF; END | DELIMITER ; Il existe aussi ELSEIF pour enchaˆ ıner les tests. 06 Octobre 2017, H & H: Research and Training 9 / 20 Proc´ edures stock´ ees SQL Autre structure de contrˆ ole : CASE ... WHEN ... THEN ... ELSE CASE nomVariable WHEN value1 THEN traitement1; WHEN value2 THEN traitement2; ... ELSE autreTraitement; END CASE; 06 Octobre 2017, H & H: Research and Training 10 / 20 Proc´ edures stock´ ees SQL Autre structure de contrˆ ole : CASE ... WHEN ... THEN ... ELSE CASE nomVariable WHEN value1 THEN traitement1; WHEN value2 THEN traitement2; ... ELSE autreTraitement; END CASE; Exercice En utilisant CASE ... WHEN ... THEN ... ELSE, ´ ecrire une proc´ edure stock´ ee qui permet d’augmenter le salaire de la personne qui habite ` a Marseille et qui a un v´ ehicule de 200 euros si son salaire est ´ egal au SMIC, 100 euros sinon. 06 Octobre 2017, H & H: Research and Training 10 / 20 Proc´ edures stock´ ees SQL La boucle : WHILE ... DO WHILE condition(s) DO -- traitements END WHILE; 06 Octobre 2017, H & H: Research and Training 11 / 20 Proc´ edures stock´ ees SQL La boucle : WHILE ... DO WHILE condition(s) DO -- traitements END WHILE; Exercice En utilisant WHILE ... DO, ´ ecrire une proc´ edure stock´ ee qui prend deux param` etres : n et somme permet d’ajouter n fois somme au salaire de la personne qui habite ` a Marseille et qui a un v´ ehicule 06 Octobre 2017, H & H: Research and Training 11 / 20 Proc´ edures stock´ ees SQL La boucle : repeat ... until REPEAT -- traitements UNTIL condition(s) END REPEAT; 06 Octobre 2017, H & H: Research and Training 12 / 20 Proc´ edures stock´ ees SQL La boucle : repeat ... until REPEAT -- traitements UNTIL condition(s) END REPEAT; Exercice Refaire l’exercice pr´ ec´ edent avec repeat ... until 06 Octobre 2017, H & H: Research and Training 12 / 20 Proc´ edures stock´ ees SQL On peut aussi d´ eﬁnir des libell´ es et utiliser ITERATE ... LEAVE label_loop: boucle -- peut ˆ etre WHILE, REPEAT ou autre -- traitements IF conditions THEN LEAVE label_loop; END IF; IF autres_conditions THEN ITERATE label_loop; END IF; END LOOP; 06 Octobre 2017, H & H: Research and Training 13 / 20 Proc´ edures stock´ ees SQL On peut aussi d´ eﬁnir des libell´ es et utiliser ITERATE ... LEAVE label_loop: boucle -- peut ˆ etre WHILE, REPEAT ou autre -- traitements IF conditions THEN LEAVE label_loop; END IF; IF autres_conditions THEN ITERATE label_loop; END IF; END LOOP; Explication ITERATE permet de relancer une it´ eration en ignorant le reste du code (de la boucle) LEAVE permet de quitter la boucle en ignorant le reste du code (de la boucle) 06 Octobre 2017, H & H: Research and Training 13 / 20 Proc´ edures stock´ ees SQL La boucle LOOP ... LEAVE label_loop: LOOP -- traitements IF condition THEN LEAVE label_loop; END IF; END LOOP; 06 Octobre 2017, H & H: Research and Training 14 / 20 D´ eclencheurs SQL D´ eclencheurs (triggers) Un ensemble d’instructions SQL attach´ e ` a une table Ex´ ecut´ e avant ou uploads/Voyage/ sql-procedures-stockees-et-triggers.pdf