Remerciez-le!

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

1/19 Université M’Hamed Bougara Boumerdès, Faculté des Sciences, Département In

1/19 Université M’Hamed Bougara Boumerdès, Faculté des Sciences, Département Informatique Cours (Master 1) : Bases de Données Avancées, (Responsable : A. AIT BOUZIAD) SQL PROCEDURAL Dans les différentes parties de ce chapitre, on utilisera la base de données suivante : 2/19 Partie 1 : Bases du langage de programmation 1. Introduction - La norme SQL n’intègre pas Les structures de contrôle habituelles d’un langage (IF, WHILE…) - Elles apparaissent dans une sous-partie optionnelle de la norme (ISO/IEC 9075-5:1996. Flow-control statements). - Le langage procédural de MySQL est une extension de SQL, permettant de faire cohabiter les structures de contrôle avec des instructions SQL. 2. Généralités 2.1. Structure d’un bloc d’instruction - Pour exécuter un bloc il faut l’inclure dans une procédure cataloguée. 2.2. Portée des objets - Un objet (variable, curseur ou exception) déclaré dans un bloc est accessible dans les sous-blocs. - Un objet déclaré dans un sous-bloc n’est pas visible du bloc supérieur. 2.3. Casse et lisibilité - Mysql procédural est non « case sensitive ». numBrevet et NumBREVET désignent le même identificateur - Lisibilité du code : 2.4. Identificateurs - Permet de nommer un objet utilisé dans un bloc - Commence par une lettre (ou un chiffre) - N’est pas limité en nombre de caractères Exemple : 2.5. Commentaires - Monolignes : commençant avec le symbole « -- » et finissant à la fin de la ligne - multilignes, commençant par « /* » et finissant par « */ » 3/19 3. Variables - Les variables qui sont déclarées (et éventuellement initialisées) par la directive DECLARE - Deux types de variables sont disponibles sous MySQL : o scalaires : recevant une seule valeur d’un type SQL (ex : colonne d’une table) o externes : définies dans la session 3.1. Variables scalaires - Syntaxe : où DEFAULT permet d’initialiser la (ou les) variable(s). Le tableau suivant décrit quelques exemples : 3.2. Affectations Il existe plusieurs possibilités pour affecter une valeur à une variable : - l’affectation comme on la connaît dans les langages de programmation : SET variable := expression - la directive DEFAULT (voir plus haut) - la directive INTO d’une requête (SELECT… INTO variable FROM…) 3.3. Restrictions 3.4. Résolution de noms - Dans des instructions SQL, le nom de la variable est prioritairement interprété au détriment de la colonne de la table (de même nom) Exemple : Ici , l’instruction DELETE supprime tous les pilotes de la table (et non pas seulement le pilote de nom 'Bougara'), car MySQL considère les deux identificateurs comme étant la même variable DECLARE nom VARCHAR(16) DEFAULT 'Bougara'; DELETE FROM Pilote WHERE nom = nom ; - Solutions: o nommer toutes les variables différemment des colonnes (utiliser un préfixe, par exemple). o utiliser une étiquette de bloc 4/19 Exemple : 3.5. Opérateurs - Les opérateurs SQL étudiés au chapitre ‘Rappel SQL’ (logiques, arithmétiques, de concaténation…) sont disponibles au sein d’un sous-programme. - Les règles de priorité sont les mêmes que dans le cas de SQL. - L’opérateur IS NULL permet de tester une formule avec la valeur NULL. Toute expression arithmétique contenant une valeur nulle est évaluée à NULL. Exemple : 3.6. Variables de session - Il est possible de passer en paramètres d’entrée d’un bloc des variables externes. Ces variables sont dites de session (user variables). - Elles n’existent que durant la session. - On déclare ces variables en ligne de commande à l’aide du symbole « @ ». Exemple : 3.7. Conventions recommandées - Intérêt : lisibles et maintenables 5/19 3.8. Exemple de Bloc - Restriction Mysql : pas possible d’exécuter des blocs anonymes (sous-programme sans nom et qui n’est pas stocké dans la base) - Un bloc doit être inclus dans une procédure cataloguée a appellée dans l’interface de commande Exemple : extraction de nombre d’heures de vol du pilote de nom 'Placide Fresnais'. La redéfinition du délimiteur à « $ » permet d'utiliser, dans le bloc, le symbole « ; » pour terminer chaque instruction. 3.9. Structures de contrôle 3.9.1. Structures conditionnelles Deux structures pour programmer des actions conditionnées : la structure IF et la structure CASE 3.9.1.1. Trois formes de IF Exemple : Pas de structure FOR pour l’instant. 6/19 3.9.1.2. Structure CASE Deux façons d’utiliser le CASE : Exemple : de cas où le CASE est plus adapté que le IF 3.9.2. Structures de contrôle répétitives Mysql intègre les structures de contrôles WHILE, REPEAT et LOOP mais pas encore le FOR. 3.9.2.1. Structure WHILE Syntaxe : Exemple 1 : calcule la somme des 100 premiers entiers. Exemple 2 : recherche le premier numéro 4 dans une chaîne de caractères 7/19 3.9.2.2. Structure « REPEAT .. UNTIL » Syntaxe : Exemples : Reprenons les 2 exemples du WHILE 3.9.2.3. Structure boucle sans fin « LOOP » Syntaxe : Exemple : Reprenons l’exemple qui calcul la somme des 100 premiers entiers en utilisant deux boucles sans fin. La directive ITERATE force à reprendre l’exécution au début de la boucle. 8/19 Notes :  LEAVE peut être aussi utilisé pour sortir d’un bloc (s’il est étiqueté).  LEAVE et ITERATE peuvent aussi être employés au sein de structures REPEAT ou WHILE.  LOOP devient sans fin si vous n’utilisez pas l’instruction LEAVE qui passe en séquence du END LOOP. 3.10. Interactions avec la base On décrit ici les mécanismes que MySQL offre pour interfacer un sous-programme avec une base de données. 3.10.1. Extraire des données Syntaxe : Remarque : la directive INTO permet de charger des variables à partir de valeurs de colonnes. Exemple 1 : extraction de la colonne « compa » pour le pilote de code 'PL-2' dans différents contextes : Note : Pour traiter des requêtes renvoyant plusieurs enregistrements, il faudra utiliser des curseurs (étudiés plus loin). Exemple 2 (utilisation de fonction monoligne): Chargement de la variable avec le nom du pilote de code 'PL-1' en majuscules. Exemple 3 (utilisation de fonction multiligne) : Affectation à la variable le maximum du nombre d’heures de vol, tous pilotes confondus. 9/19 3.10.2. Manipuler des données Les instructions de manipulation, par un sous-programme de données sont les mêmes que celles utilisées par SQL, à savoir INSERT, UPDATE et DELETE. 3.10.2.1. Insertions Exemple : Note : Dans le cas d’une erreur, une exception qui précise la nature du problème est levée et peut être interceptée par la directive HANDLER (voir plus loin). Si une telle directive n’existe pas dans le bloc qui contient l’instruction INSERT, la première exception fera s’interrompre le programme. 3.10.2.2. Modifications Syntaxe : Exemple : modification de différents enregistrements 10/19 3.10.2.3. Suppressions Syntaxe : Exemple : Suppression de différents enregistrements 3.11. Transactions Définition : Une transaction est un bloc d’instructions LMD faisant passer la base de données d’un état cohérent à un autre état cohérent. Toutes les instructions de la transaction doivent s’exécuter entièrement ou pas du tout. Si une erreur survient au cours d’une transaction, toutes les instructions déjà exécutées sont annulées. Exemple : transfert d’un compte 1(Codevi) vers un compte 2(CompteCourant). Propriétés : (ACID) Une transaction assure : - l’Atomicité des instructions qui sont considérées comme une seule opération (principe du tout ou rien) - la Cohérence : passage d’un état cohérent de la base à un autre état cohérent - l’Isolation des transactions entre elles - la Durabilité des opérations : les mises à jour perdurent même si une panne se produit après la transaction 3.11.1. Début et fin d’une transaction Deux instructions permettent de marquer le début d’une transaction : 1. START TRANSACTION ou 2. BEGIN Note : 1. Entre BEGIN et END d’un programme MySQL, il est possible d’écrire plusieurs transactions. 2. Le fait de commencer une transaction termine implicitement celle qui précédait. Une transaction se termine : a) explicitement par les instructions : 1. SQL COMMIT ou 2. ROLLBACK 11/19 b) implicitement : 1. à la première commande SQL du LDD rencontrée (CREATE, ALTER, DROP…) 2. à la fin normale d’une session utilisateur avec déconnexion 3. à la fin anormale d’une session utilisateur (sans déconnexion). 3.11.2. Mode de validation  Deux modes de fonctionnement sont possibles : - celui par défaut (autocommit) qui valide systématiquement toutes les instructions reçues par la base. Dans ce mode, il est impossible de revenir en arrière afin d’annuler une instruction. - Le mode inverse (autocommit off) qui se déclare à l’aide de l’instruction suivante Syntaxe :  Le tableau suivant précise la validité de la transaction en fonction des événements possibles : 3.11.3. Exemple de transaction Etant donnée la procédure suivante : Etape 1 : Exécution du bloc dans l’interface, Etape 2 : Déconnection soit en cassant la fenêtre (icône en haut à droite), soit proprement avec exit. Etape 3 : - On se reconnecte, - Résultat : l’enregistrement n’est pas présent dans la table ‘TableaVous’. Même quand la fin du programme est normale, la transaction n’est pas validée (car il manque COMMIT). Etape 4 : - Relance du bloc en ajoutant l’instruction COMMIT après l’insertion. - Résultat : l’enregistrement est présent dans la table, même après une déconnexion douce ou dure. 3.11.4. Contrôle uploads/Ingenierie_Lourd/ 01-sql-procedural.pdf