Programmer avec MySQL - eyrolles.com€¦© Groupe Eyrolles, 2006, 2011, ISBN : 978-2-212-12869-7 © Éditions Eyrolles 223 Chapitre 6 ... Partie II Programmation procédurale 224

��

��

e éditione édition22

SQL - Transactions - PHP - Java - Optimisations

Programmer avec

MySQL

© Groupe Eyrolles, 2006, 2011, ISBN : 978-2-212-12869-7

© Éditions Eyrolles 223

Chapitre 6

Bases du langage de programmation

Ce chapitre décrit les caractéristiques générales du langage proc édural de programmation deMySQL :● structure d’un programme ;● déclaration et affectation de variables ;● structures de contrôle (si, tant que, répéter, pour) ;● mécanismes d’interaction avec la base ;● programmation de transactions.

Généralités

Les structures de contrôle habituelles d’un langage (IF, WHILE…) ne font pas partie intégrantede la norme SQL. Elles apparaissent dans une sous-partie optionnelle de la norme(ISO/IEC 9075-5:1996. Flow-control statements). MySQL les prend en compte.Le langage procédural de MySQL est une extension de SQL, car il permet de faire cohabiterles habituelles structures de contr ôle ( si, pour et tant que pour les plus connues) avec desinstructions SQL (principalement SELECT, INSERT, UPDATE et DELETE).

Environnement client-serveurDans un environnement client-serveur, chaque instruction SQL donne lieu à l ’envoi d ’unmessage du client vers le serveur suivi de la réponse du serveur vers le client. Il est préférablede travailler avec un sous-programme (qui sera stock é, en fait, c ôté serveur) plut ôt qu’avecune suite d’instructions SQL susceptibles d’encombrer le trafic réseau. En effet, un bloc donnelieu à un seul échange sur le r éseau entre le client et le serveur. Les r ésultats intermédiairessont traités côté serveur et seul le résultat final est retourné au client.

4055_ Page 223 Mercredi, 22. décembre 2010 6:43 18

Partie II Programmation procédurale

224 © Éditions Eyrolles

AvantagesLes principaux avantages d ’utiliser des sous-programmes (proc édures ou fonctions catalo-guées qui sont stockées côté serveur) sont :● La modularité : un sous-programme peut être composé d’autres blocs d ’instructions. Un

sous-programme peut aussi être réutilisable, car il peut être appelé par un autre.● La portabilité : un sous-programme est indépendant du système d’exploitation qui héberge

le serveur MySQL. En changeant de système, les applicatifs n’ont pas à être modifiés.● L’intégration avec les données des tables : on retrouvera avec ce langage proc édural tous

les types de donn ées et d ’instructions disponibles sous MySQL, des m écanismes pourparcourir des r ésultats de requ êtes (curseurs), pour traiter des erreurs ( handlers) et pourprogrammer des transactions (COMMIT, ROLLBACK, SAVEPOINT).

● La sécurité, car les sous-programmes s’exécutent dans un environnement a priori sécurisé(SGBD) où il est plus facile de garder la ma îtrise sur les ordres SQL exécutés et donc surles agissements des utilisateurs.

Structure d’un blocUn bloc d’instructions est composé de :● BEGIN (section obligatoire) contient le code incluant ou non des directives SQL se termi-

nant par le symbole « ; » ;● DECLARE (directive optionnelle) déclare une variable, un curseur, une exception, etc. ;● END ferme le bloc. Un bloc peut être imbriqu é dans un autre bloc. Pour tester un bloc, nous verrons dans lasection « Tests des exemples », qu’il faut l’inclure dans une procédure cataloguée. MySQL neprend pas encore en charge les procédures anonymes (sans nom).

Figure 6-1 Trafic sur le réseau d’instructions SQL

CALL Bloc

SGBDExécution globale

SELECT …;

UPDATE ….;

INSERT INTO …;….

SGBDExécution requête par requête

Client

Bloc

Suited’instructions Serveur

BEGINSELECT …;

UPDATE ….;

INSERT INTO…;….END;



chapitre n° 6 Bases du langage de programmation

Portée des objetsLa portée d’un objet (variable, curseur ou exception) est la zone du programme qui peut yaccéder. Un objet déclaré dans un bloc est accessible dans les sous-blocs. En revanche, unobjet déclaré dans un sous-bloc n’est pas visible du bloc supérieur (principe des accolades deslangages C et Java).

Casse et lisibilitéComme SQL, les sous-programmes sont capables d ’interpréter les caract ères alphanuméri-ques du jeu de caractères sélectionné. Aucun objet manipulé par programme n’est sensible à lacasse (not case sensitive). Ainsi numeroBrevet et NumeroBREVET désignent le même identi-ficateur (tout est traduit en minuscules au niveau du dictionnaire des donn ées). Les r èglesd’écriture classiques concernant l ’indentation et les espaces entre variables, mots-cl és etinstructions doivent être respectées dans un souci de lisibilité.

Figure 6-2 Structure d’un bloc d’instructions MySQL

BEGIN[DECLARE déclaration]…-- code…

…END;

BEGIN[DECLARE déclaration ]...-- code…END;

BEGIN[DECLARE déclaration ]...-- code…END;

Figure 6-3 Visibilité des objets

BEGINDECLARE v_brevet CHAR(6);…-- v_brevet accessible

…

END;

BEGINDECLARE v_nom VARCHAR (20);-- v_brevet et v_nom accessibles…END;

v_nom inaccessible

Tableau 6-1 Lisibilité du code

Peu lisible C’est mieux

IF x>y THEN SET max:=x;ELSE SET max:=y;END IF; IF x>y THEN SET max:=x;ELSE SET max:=y;END IF;




IdentificateursAvant de parler des diff érents types de variables MySQL, d écrivons comment il est possiblede nommer les objets des sous-programmes. Un identificateur commence par une lettre (ou unchiffre). Un identificateur n’est pas limité en nombre de caractères. Les autres signes pourtantconnus du langage sont interdits, comme le montre le tableau suivant :

CommentairesMySQL prend en charge deux types de commentaires : monolignes, commençant au symbole« -- » et finissant à la fin de la ligne ; et multilignes, commençant par « /* » et finissant par« */ ». Le tableau suivant décrit quelques exemples :

Variables

Un sous-programme est capable de manipuler des variables qui sont d éclarées (et éventuelle-ment initialis ées) par la directive DECLARE. Ces variables permettent de transmettre desvaleurs à des sous-programmes via des param ètres, ou d ’afficher des états de sortie sousl’interface. Deux types de variables sont disponibles sous MySQL :● scalaires : recevant une seule valeur d’un type SQL (ex : colonne d’une table) ;● externes : définies dans la session et qui peuvent servir de paramètres d’entrée ou de sortie.

Tableau 6-2 Identificateurs

Autorisés Interdits

t2code_brevet2nombresMysql_t

moi&toi (symbole « & »)debit-credit (symbole « - »)on/off (symbole « / »)code brevet (symbole espace)

Tableau 6-3 Commentaires

Sur une ligne Sur plusieurs lignes

SELECT nbHVol INTO v_nbHVol FROM Pilote WHERE nom = 'Gratien Viel';SET v_bonus := v_nbHVol*0.15;

SELECT salaire INTO v_salaire FROM Pilote

WHERE nom = 'Thierry Albaric';

SET v_bonus := v_salaire*0.15;

-- Lecture de la table Pilote

–- Extraction heures de vol

-- Calcul

/* Lecture de la table Pilote */

/* Extraction du salairepour calculer le bonus */

/*Calcul*/




Variables scalairesLa déclaration d’une variable scalaire est de la forme suivante :

DECLARE nomVariable1[,nomVariable2...] typeMSQL [DEFAULT expression];

● DEFAULT permet d ’initialiser la (ou les) variable(s) – pas forc ément à l’aide d’une cons-tante. Le tableau suivant décrit quelques exemples :

AffectationsIl 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 ). Vous pouvez aussi utiliser le symbole « = », mais il est plus prudentde le réserver à la programmation de conditions ;

● la directive DEFAULT ;● la directive INTO d’une requête (SELECT … INTO variable FROM …).

Restrictions

Le type tableau ( array) n’est pas encore pr ésent dans le langage de MySQL. Cela peut êtrepénalisant quand on d ésire travailler en interne avec des r ésultats d ’extractions de taillemoyenne.

Il est impossible d’utiliser un identificateur dans une expression, s’il n’est pas déclaré au préa-lable. Ici, la déclaration de la variable v_maxi est incorrecte :

DECLARE v_maxi INT DEFAULT 2 * v_mini;

DECLARE v_mini INT DEFAULT 15;

Tableau 6-4 Déclarations

Déclarations Commentaires

DECLARE v_dateNaissance DATE; Déclare la variable sans l’initialiser. Équivalent à SET v_dateNaissance := NULL;

DECLARE v_capacite SMALLINT(4) DEFAULT 999; Initialise la variable à 999.

DECLARE v_trouve BOOLEAN DEFAULT TRUE; Initialise la variable à vrai (1).

DECLARE v_Dans2jours DATE DEFAULT ADDDATE(SYSDATE(),2);

Initialise la variable à dans 2 jours.

:=

5.1 et 5.5




Comme la plupart des langages récents, les déclarations multiples sont permises. Celle qui suitest juste :

DECLARE INT;

Résolution de nomsLors des conflits potentiels de noms (variables ou colonnes) dans des instructions SQL (prin-cipalement INSERT, UPDATE, DELETE et SELECT), le nom de la variable est prioritairementinterprété au détriment de la colonne de la table (de même nom).Dans l’exemple suivant, l’instruction DELETE supprime tous les pilotes de la table (et non passeulement le pilote de nom 'Placide Fresnais'), car MySQL consid ère les deux identificateurscomme étant la même variable, et non pas comme colonne de la table et variable.

DECLARE nom VARCHAR(16) DEFAULT 'Placide Fresnais';DELETE FROM Pilote WHERE ;

Pour se pr émunir de tels effets de bord, une seule solution existe : elle consiste à nommertoutes les variables diff éremment des colonnes (en utilisant un pr éfixe, par exemple). Uneautre solution serait d’utiliser une étiquette de bloc (block label) pour lever d’éventuellesambiguïtés. Bien qu’il soit possible d’employer des étiquettes de blocs (aussi disponibles pourles structures de contrôle), on ne peut pas encore préfixer des variables pour en distinguer unede même nom entre différents blocs.

OpérateursLes opérateurs SQL étudiés au chapitre 4 (logiques, arithmétiques, de concaténation…) sontdisponibles au sein d ’un sous-programme. Les r ègles de priorité sont les m êmes que dans lecas de SQL.

L’opérateur IS NULL permet de tester une formule avec la valeur NULL. Toute expressionarithmétique contenant une valeur nulle est évaluée à NULL.

Le tableau suivant illustre quelques utilisations possibles d’opérateurs logiques :

i, j, k

nom = nom

Tableau 6-5 Éviter les ambiguïtés

Préfixer les variables Étiquette de bloc (préfixe pas opérationnel)

DECLARE v_nom VARCHAR(16) DEFAULT 'Placide Fresnais';…DELETE FROM Pilote WHERE ;--ouDELETE FROM Pilote WHERE ;

DECLARE nom VARCHAR(16) DEFAULT 'Placide Fresnais'; DELETE FROM Pilote WHERE principal.nom = nom;

;

nom = v_nom

v_nom = nom

principal: BEGIN

END principal




Variables de sessionIl est possible de passer en paramètres d’entrée d’un bloc des variables externes. Ces variablessont dites de session (user variables). Elles n’existent que durant la session. On déclare cesvariables en ligne de commande à l’aide du symbole « @ ».

SET [, @var2 = expression2] ...

Le tableau suivant illustre un exemple de deux variables de session : on extrait le nom et lenombre d ’heures de vol d ’un pilote (table d écrite au d ébut du chapitre 4) augmenté d ’unnombre en paramètre. Son numéro de brevet et la durée du vol sont lus au clavier. Ces varia-bles de session ne sont bien sûr pas à déclarer dans le bloc

Tableau 6-6 Utilisation d’opérateurs

Code MySQL Commentaires

DECLARE v_compteur INT(3) DEFAULT 0;DECLARE v_boolean BOOLEAN;DECLARE v_nombre INT(3);

Trois déclarations dont une avec initialisation.

SET v_compteur := v_compteur+1; Incrémentation de v_compteur (opérateur +)

SET v_boolean := (v_compteur=v_nombre); v_boolean reçoit NULL, car la condition est fausse.

SET v_boolean := (v_nombre IS NULL); v_boolean reçoit TRUE (1 en fait), car la condi-tion est vraie.

@var1 = expression1

Tableau 6-7 Variables de session

Code MySQL Résultat

delimiter $

…BEGIN DECLARE v_nom CHAR(16); DECLARE v_nbHVol DECIMAL(7,2); SELECT nom,nbHVol INTO v_nom, v_nbHVol FROM Pilote WHERE brevet = ; SET v_nbHVol := v_nbHVol + ; SELECT v_nom, v_nbHVol;END;$

mysql> CALL sp1()$

+------------------+----------+| v_nom | v_nbHVol |+------------------+----------+| Placide Fresnais | 2465.00 |+------------------+----------+

Web

SET @vs_num = 'PL-4'$SET @vs_hvol = 15$

@vs_num@vs_hvol




Conventions recommandéesAdoptez les conventions d ’écriture suivantes pour que vos programmes MySQL soient plusfacilement lisibles et maintenables :

Test des exemples

Parce qu’il n’est pas encore possible d ’exécuter des blocs anonymes (sous-programme sansnom et qui n ’est pas stocké dans la base), vous devez les inclure dans une proc édure catalo-guée que vous appellerez dans l’interface de commande.L’exemple suivant extrait le nombre d ’heures de vol du pilote de nom 'Placide Fresnais'.Pensez à redéfinir le d élimiteur à « $ » (par exemple) pour pouvoir utiliser, dans le bloc, lesymbole « ; » pour terminer chaque instruction.

Le résultat dans l’interface de commande est le suivant. Allez-y tester vos exemples, mainte-nant.

Tableau 6-8 Conventions

Objet Convention Exemple

Variable v_nomVariable v_compteur

Constante c_nomConstante c_pi

Variable de session (globale) vs_nomVariable vs_brevet

Tableau 6-9 Tester un exemple de bloc

Préfixer les variables Commentaire

delimiter $SET @vs_nom = 'Placide Fresnais'$

Déclaration du délimiteur et d’une variable de session.

DROP PROCEDURE sp1$ Suppression de la procédure.

CREATE PROCEDURE sp1() Création de la procédure.

BEGIN DECLARE v_nbHVol DECIMAL(7,2); SELECT nbHVol INTO v_nbHVol FROM Pilote WHERE nom = @vs_nom; SELECT v_nbHVol;END;$

Bloc d’instructions.

Trace du résultat.Fin du bloc

CALL sp1()$ Appel de la procédure.

Web




Structures de contrôle

En tant que langage procédural, MySQL offre la possibilité de programmer :● les structures conditionnelles si et cas (IF… et CASE) ;● des structures répétitives tant que, répéter et boucle sans fin (WHILE, REPEAT et LOOP).

Pas de structure FOR pour l’instant. Deux directives supplémentaires qui sont toutefois à utiliseravec modération : LEAVE qui sort d’une boucle (ou d’un bloc étiqueté) et ITERATE qui force leprogramme à refaire un tour de boucle depuis le début.

Structures conditionnellesMySQL propose deux structures pour programmer des actions conditionnées : la structure IFet la structure CASE.

Trois formes de IFSuivant les tests à programmer, on peut distinguer trois formes de structure IF : IF-THEN (si-alors), IF-THEN-ELSE (avec le sinon à programmer), et IF-THEN-ELSEIF (imbrications deconditions).Le tableau suivant donne l ’écriture des diff érentes structures conditionnelles IF. Notez« END IF » en fin de structure, et non pas « ENDIF ». L’exemple affiche un message diff é-rent selon la nature du numéro de téléphone contenu dans la variable v_telephone.

Figure 6-4 Exécution d’un bloc




Conditions booléennesLes tableaux suivants précisent le résultat d’opérateurs logiques qui mettent en jeu des varia-bles booléennes pouvant prendre trois valeurs ( TRUE, FALSE, NULL). Bien s ûr, en l ’absenced’un vrai type booléen, MySQL représente TRUE avec 1, et FALSE avec 0. Il est à noter que lanégation de NULL (NOT NULL) renvoie une valeur nulle.

Structure CASEComme l’instruction IF, la structure CASE permet d’exécuter une séquence d’instructions enfonction de différentes conditions. La structure CASE est utile lorsqu’il faut évaluer une mêmeexpression et proposer plusieurs traitements pour diverses conditions.

Tableau 6-10 Structures IF

IF-THEN IF-THEN-ELSE IF-THEN-ELSEIF

IF condition THEN instructions;END IF;

IF condition THEN instructions;ELSE instructions;END IF;

IF condition1 THEN instructions;ELSEIF condition2 THEN instructions2; ELSE instructions3;END IF;

BEGIN DECLARE v_telephone CHAR(14) DEFAULT '06-76-85-14-89'; SELECT "C'est un portable"; ELSE SELECT "C'est un fixe..."; END IF;END;

IF SUBSTR(v_telephone,1,2)='06' THEN

Tableau 6-11 Opérateur AND

AND TRUE FALSE NULL

TRUE TRUE FALSE NULL

FALSE FALSE FALSE FALSE

NULL NULL FALSE NULL

Tableau 6-12 Opérateur OR

OR TRUE FALSE NULL

TRUE TRUE TRUE TRUE

FALSE TRUE FALSE NULL

NULL TRUE NULL NULL




Selon la nature de l’expression et des conditions, une des deux écritures suivantes peut êtreutilisée :

Le tableau suivant nous livre l ’écriture avec IF d’une programmation qu’il est plus rationneld’effectuer avec une structure CASE (de type searched) :

Structures répétitivesÉtudions à présent les trois structures répétitives tant que, répéter et boucle sans fin.

Structure tant queLa structure tant que se programme à l’aide de la syntaxe suivante. Avant chaque it ération (etnotamment avant la première), la condition est évaluée. Si elle est vraie, la s équence d’instruc-tions est ex écutée, puis la condition est r éévaluée pour un éventuel nouveau passage dans la

Tableau 6-13 Structures CASE

CASE searched CASE

CASE variable WHEN expr1 THEN instructions1; WHEN expr2 THEN instructions2; … WHEN exprN THEN instructionsN; [ELSE instructionsN+1;]END CASE;

CASE WHEN condition1 THEN instructions1; WHEN condition2 THEN instructions2; … WHEN conditionN THEN instructionsN; [ELSE instructionsN+1;]END CASE;

Tableau 6-14 Différentes programmations

IF CASE

BEGINDECLARE v_mention CHAR(2);DECLARE v_note DECIMAL(4,2) DEFAULT 9.8; ...

IF v_note >= 16 THEN SET v_mention := 'TB'; ELSEIF v_note >= 14 THEN SET v_mention := 'B'; ELSEIF v_note >= 12 THEN SET v_mention := 'AB'; ELSEIF v_note >= 10 THEN SET v_mention := 'P'; ELSE ;END IF;...

CASE WHEN v_note >= 16 THEN SET v_mention := 'TB'; WHEN v_note >= 14 THEN SET v_mention := 'B'; WHEN v_note >= 12 THEN SET v_mention := 'AB'; WHEN v_note >= 10 THEN SET v_mention := 'P'; ELSE SET v_mention := 'R';END CASE;...

Web

SET v_mention := 'R'




boucle. Ce processus continue jusqu’à ce que la condition soit fausse pour passer en s équenceaprès le END WHILE. Quand la condition n’est jamais fausse, on dit que le programme boucle…

[etiquette:] WHILE condition DO

instructions;

END WHILE [etiquette];

Le tableau suivant décrit la programmation de deux tant que. Le premier calcule la somme des100 premiers entiers. Le second recherche le premier numéro 4 dans une chaîne de caractères.

Cette structure est la plus puissante, car elle permet de programmer aussi un répéter, uneboucle sans fin et m ême un pour (qui n ’est pas encore op érationnel). Elle doit être utiliséequand il est nécessaire de tester une condition avant d’exécuter les instructions contenues dansla boucle.

Structure répéterLa structure répéter se programme à l’aide de la syntaxe REPEAT… UNTIL.

Enfin un répéter qui se programme comme il faut (à savoir « répéter… jusqu’à condition »). Leslangages C et Java nous avaient déformé cette traduction par do {…} while(condition)qui n écessite d ’écrire l ’inverse de la condition du jusqu’à de l ’algorithmique. Ouf, MySQL(comme Oracle) a bien programmé la structure répéter en traduisant ce fameux jusqu’à par ladirective until, et non plus par ce fâcheux while.

Tableau 6-15 Structures tant que

Condition simple Condition composée

DECLARE v_somme INT DEFAULT 0;DECLARE v_entier SMALLINT DEFAULT 1;

SET v_somme := v_somme+v_entier; SET v_entier := v_entier+1;

; SELECT v_somme;

+---------+| v_somme |+---------+| 5050 |+---------+

DECLARE v_telephone CHAR(14) DEFAULT '06-76-85-14-89';DECLARE v_trouve BOOLEAN DEFAULT FALSE;DECLARE v_indice SMALLINT DEFAULT 1;

IF SUBSTR(v_telephone,v_indice,1) = '4' THEN SET v_trouve := TRUE; ELSE SET v_indice := v_indice + 1; END IF;

; IF v_trouve THEN SELECT CONCAT('Trouvé 4 à l''indice :',v_indice);END IF;

Trouvé 4 à l'indice : 11

Web

WHILE (v_entier <= 100) DO

END WHILEWHILE (v_indice <= 14 AND NOT v_trouve) DO

END WHILE




[etiquette:] REPEAT

instructions;

UNTIL condition END REPEAT [etiquette];

La particularité de cette structure est que la première itération est effectuée quelles que soientles conditions initiales. La condition n’est évaluée qu’en fin de boucle.● Si la condition est fausse, la s équence d’instructions est de nouveau ex écutée. Ce processus

continue jusqu’à ce que la condition soit vraie pour passer en séquence après le END REPEAT.● Quand la condition n’est jamais vraie, on dit aussi que le programme boucle…Le tableau suivant d écrit la programmation de la somme des 100 pr emiers entiers et de larecherche du premier numéro 4 dans une chaîne de caractères, à l’aide de la structure répéter.Les variables sont les mêmes qu’au tableau précédent.

Cette structure doit être utilisée quand il n ’est pas n écessaire de tester la condition avec lesdonnées initiales, avant d’exécuter les instructions contenues dans la boucle.

Structure boucle sans finLa syntaxe générale de cette structure est programmée par la directive LOOP. Elle devient sansfin si vous n’utilisez pas l’instruction LEAVE qui passe en séquence du END LOOP.

[etiquette:] LOOP

instructions;

END LOOP [etiquette];

Le tableau suivant donne l’écriture du calcul de la somme des 100 premiers entiers en utilisantdeux boucles sans fin (qui se terminent toutefois, car tout a une fin , mais celles-l à je lesprogramme avec LEAVE). J ’en profite pour pr ésenter ITERATE qui force à reprendrel’exécution au début de la boucle.

Tableau 6-16 Structures répéter

Condition simple Condition composée

REPEATSET v_somme := v_somme + v_entier;SET v_entier := v_entier + 1;

UNTIL END REPEAT;

REPEAT IF SUBSTR(v_telephone,v_indice,1) = '4' THEN SET v_trouve := TRUE; ELSE SET v_indice := v_indice + 1; END IF;UNTIL END REPEAT;IF v_trouve THEN SELECT CONCAT('Trouvé 4 à l''indice : ',v_indice);END IF;

Web

v_entier > 100

(v_indice > 14 OR v_trouve)




Il est à noter que LEAVE peut être aussi utilisé pour sortir d’un bloc (s’il est étiqueté). LEAVE etITERATE peuvent aussi être employés au sein de structures REPEAT ou WHILE.

Redirection (GOTO)Célèbre pour faire tendre un programme vers une configuration plut ôt de feu d’artifice que decours d’eau tranquille, l’instruction GOTO est bien connue, mais souvent mal utilis ée. Elle peutêtre pratique dans certains cas, pour sortir d ’une boucle ou d ’un bloc. Il n ’est pas souhaitableque vous utilisiez GOTO, à moins que vous écriviez vos algorithmes avec des organigrammes.

Dans le livre blanc MySQL 5.0 Stored Procedures New Features Series – Part 1 (disponible àl’adresse suivante : http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html),Peter Gulutzan, architecte de MySQL, évoquait les concepts de « GOTO etiquette; » et« LABEL etiquette; ». Ces derniers, qui ont vu le jour au d ébut de la p ériode bêtaversion 5.0, n’ont pas été implémentés en version de production.

Structure pourRenommée pour les parcours de vecteurs, tableaux et matrices en tout genre, la structure pourse caractérise par la connaissance a priori du nombre d’itérations que le programmeur souhaitefaire effectuer à son algorithme. La syntaxe g énérale de cette structure est programm ée danstous les langages par l’instruction for.

La structure de contr ôle FOR n’est pas encore impl émentée par MySQL. Vous devrez la pro-grammer par un répéter (REPEAT…), un tant que (WHILE…) ou encore par une boucle sans fin(LOOP…). Pour chacune de ces alternatives, vous devrez d éfinir un indice qui évoluera d’unevaleur initiale à une valeur finale par une incrémentation en fin de boucle.

Interactions avec la base

Cette section d écrit les m écanismes que MySQL offre pour interfacer un sous-programmeavec une base de données.

Tableau 6-17 Structures de boucles sans fin

Avec LEAVE Avec ITERATE

SET v_somme := v_somme + v_entier; SET v_entier := v_entier + 1; IF v_entier > 100 THEN ; END IF;

;

SET v_somme := v_somme + v_entier; SET v_entier := v_entier + 1; IF v_entier <= 100 THEN ; END IF; ;

;

Webboucle1: LOOP

LEAVE boucle1

END LOOP boucle1

boucle1: LOOP

ITERATE boucle1

LEAVE boucle1END LOOP boucle1

5.1 et 5.5

5.1 et 5.5




Extraire des donnéesLa principale instruction capable d ’extraire des donn ées contenues dans des tables estSELECT. Étudiée au chapitre 4 dans un contexte SQL, la particularit é de cette instruction auniveau d’un sous-programme est la directive INTO qui permet de charger des variables à partirde valeurs de colonnes, comme le montre la syntaxe suivante :

SELECT col1 [,col2 ...]INTO variable1 [,variable2 ...] FROM nomTable ...;

Cette instruction peut aussi être utilisée à l’extérieur d’un bloc pour charger une variable desession, par exemple.

Veillez à ne récupérer qu’un seul enregistrement à l’aide du WHERE de la requ ête. C’est logi-que, puisque vous désirez ne charger qu’une valeur par variable.

• Si vous en e xtrayez plusieurs, vous verrez l ’erreur : « ERROR 1172 (42000): Resultconsisted of more than one row ».

• Si vous n’en extrayez aucun (no data found), aucune erreur n’est soulevée et la variable estinchangée (elle reste initialisée à la valeur présente avant la requête).

Colonnes simplesLe tableau suivant d écrit l’extraction de la colonne compa pour le pilote de code 'PL-2' dansdifférents contextes :

Tableau 6-18 Extraction de données

Code MYSQL Commentaires

BEGIN DECLARE v_comp VARCHAR(15); SELECT FROM Pilote WHERE brevet='PL-2'; ...END;

Chargement d ’une variable locale à un bloc.Nécessité d ’appeler par la suite cette proc é-dure (CALL).

SET @vs_compa=''$SELECT compa FROM Pilote WHERE brevet='PL-2'$...

Chargement d ’une variable de session horsd’un sous-programme.

SET @vs_compa=''$CREATE PROCEDURE sp1() BEGIN SELECT FROM Pilote WHERE brevet='PL-2'; ...END;

Chargement d’une variable de session dans unsous-programme.

compa INTO v_comp

INTO @vs_compa

compa INTO @vs_compa




Pour traiter des requ êtes renvoyant plusieurs enregistrements, il faudra utiliser des curseurs(étudiés au chapitre suivant).

Fonctions SQLIl est naturel que les fonctions SQL (mono et multilignes) étudiées au chapitre 4 soientégalement disponibles dans un sous-programme, à condition de les utiliser au sein d ’uneinstruction SELECT. Deux exemples sont d écrits dans le tableau suivant : le premier char-gera la variable avec le nom du pilote de code 'PL-1' en majuscules (table d écrite au débutdu chapitre 4) ; le second affectera à la variable le maximum du nombre d ’heures de vol,tous pilotes confondus.

Manipuler des donnéesLes principales instructions disponibles pour manipuler, par un sous-programme, leséléments d ’une base de donn ées sont les m êmes que celles propos ées par SQL, à savoirINSERT, UPDATE et DELETE. Pour libérer les verrous au niveau d’un enregistrement (et destables), il faudra ajouter les instructions COMMIT ou ROLLBACK (aspects étudiés en fin dechapitre).

InsertionsLe tableau suivant d écrit l’insertion de différents enregistrements sous plusieurs écritures (ilest aussi possible d’utiliser des variables de session) :Comme sous SQL, il faut respecter les noms, types et domaines de valeurs des colonnes. Demême, les contraintes de vérification (CHECK qui n’est pas encore opérationel et NOT NULL) etd’intégrité (PRIMARY KEY et FOREIGN KEY) doivent être valides.Dans le cas inverse, une exception qui pr écise la nature du probl ème est lev ée et peut êtreinterceptée par la directive HANDLER (voir chapitre suivant). Si une telle directive n’existe pas

Tableau 6-19 Utilisation de fonctions

Monoligne Multiligne

BEGIN DECLARE v_nomEnMAJUSCULES CHAR(20); SELECT FROM Pilote WHERE brevet = 'PL-1';SELECT v_nomEnMAJUSCULES;END;

BEGIN DECLARE v_plusGrandHVol DECIMAL(7,2); SELECT FROM Pilote;SELECT v_plusGrandHVol ;END;

+-------------------+| v_nomEnMAJUSCULES |+-------------------+| GRATIEN VIEL |+-------------------+

+-----------------+| v_plusGrandHVol |+-----------------+| 2450.00 |+-----------------+

Web

UPPER(nom)INTO v_nomEnMAJUSCULES

MAX(nbHVol) INTO v_plusGrandHVol




dans le bloc qui contient l ’instruction INSERT, la premi ère exception fera s ’interrompre leprogramme.

ModificationsConcernant la mise à jour de colonnes par UPDATE, la clause SET peut être ambiguë dans lesens où l’identificateur à gauche de l’opérateur d’affectation est toujours une colonne de basede données, alors que celui à droite de l ’opérateur peut correspondre à une colonne ou à unevariable.

UPDATE nomTable SET col1 = { variable1 | expression1 | autrecol | (requête) } [,col2 = ...] [WHERE ... ];

Si aucun enregistrement n’est modifié, aucune erreur ne se produit et aucune exception n ’estlevée.

Alors que les aff ectations dans le code MYSQL ( SET …) peuvent s ’écrire par les symboles« := » ou « = », les comparaisons ou affectations SQL nécessitent le symbole « = ».

Le tableau suivant d écrit la modification de diff érents enregistrements (il est aussi possibled’employer des variables de session).

Tableau 6-20 Insertion d’enregistrements


BEGIN DECLARE v_brevet VARCHAR(6) DEFAULT 'PL-7'; DECLARE v_nom VARCHAR(6); DECLARE v_HVol DECIMAL(7,2) DEFAULT 0; DECLARE v_comp VARCHAR(6);

Déclaration des variables locales aubloc.

INSERT INTO Pilote VALUES ;

Insertion d’un enregistrement en rensei-gnant les colonnes par des constantes.

SET v_nom := 'Fabrice Peyrard'; SET v_comp := 'SING'; INSERT INTO Pilote VALUES ;END;

Insertion d’un enregistrement en rensei-gnant les colonnes par des variableslocales.

Web

('PL-6', 'Jules Ente', 3000, 'AF')

(v_brevet,v_nom,v_HVol,v_comp)




SuppressionsLa suppression par DELETE peut être ambiguë (même raison que pour l ’instruction UPDATE)au niveau de la clause WHERE.

DELETE FROM nomTable [WHERE col1 = { variable1 | expression1 | autrecol | (requête) } [,col2 = ...] ];

Si aucun enregistrement n’est modifié, aucune erreur ne se produit et aucune exception n ’estlevée.

Le tableau suivant d écrit la suppression de diff érents enregistrements (il est aussi possibled’utiliser des variables de session).

Tableau 6-21 Modifications d’enregistrements


BEGIN DECLARE v_dureeVol DECIMAL(3,1) DEFAULT 4.8;

Déclaration.

UPDATE Pilote SET WHERE brevet= 'PL-6';

Modification d’un enregistrement de la table Pilote en utilisant une variable.

UPDATE Pilote SET WHERE compa = 'AF';END;

Modification de plusieurs enregistrements de la table Pilote en utilisant une cons-tante.

Web

nbHVol= nbHVol + v_dureeVol

nbHVol= nbHVol + 10

Tableau 6-22 Suppression d’enregistrements


BEGIN DECLARE v_hVolMini DECIMAL(7,2) DEFAULT 1000.00;

DELETE FROM Pilote WHERE nbHVol < ;

DELETE FROM Pilote WHERE ; Supprime les enregistrements dela table Pilote dont le nombred’heures de vol est inf érieur à1 000.Supprime un pilote.

DELETE FROM Pilote WHERE ;END;

Ne supprime aucun pilote.

Web

v_hVolMini

brevet = 'PL-3'

brevet = NULL




Transactions

Une transaction est un bloc d ’instructions LMD faisant passer la base de donn ées d’un étatcohérent à un autre état cohérent. Si un problème logiciel ou matériel survient au cours d’unetransaction, aucune des instructions contenues dans la transaction n ’est effectuée, quel quesoit l’endroit de la transaction où est intervenue l’erreur.On peut supposer que la majorité des transactions sous MySQL sont programmées dans lelangage du serveur. Les langages plus évolués permettent aussi de développer des transactionsà travers des API (par exemple la m éthode commit est comprise dans le paquetagejava.sql).L’exemple typique d ’une transaction est celui du transfert d ’un compte épargne vers uncompte courant. Imaginez qu’après une panne votre compte épargne a été débité de la sommede 500 €, sans que votre compte courant soit cr édité du même montant ! Vous ne seriez pastrès content des services de votre banque (à moins que l’erreur ne soit intervenue dans l’autresens). Le m écanisme transactionnel emp êche un tel sc énario en invalidant toutes les op éra-tions faites depuis le d ébut de la transaction, si une panne survient au cours de cette m êmetransaction.

CaractéristiquesUne 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 (lecture consistante, mécanisme décrit plus loin) ;● la durabilité des opérations (les mises à jour perdurent même si une panne se produit après

la transaction).

Figure 6-5 Transaction

TRANSFERT(500€) Début Transaction … UPDATE Codevi( -500€) UPDATE CompteCourant(+500€) Fin Transaction

État cohérent

TEMPS

PANNE !

État cohérent




Début et fin d’une transactionDeux instructions sont disponibles pour marquer le d ébut d’une transaction : START TRAN-SACTION ou BEGIN. Ainsi, entre BEGIN et END d’un programme MySQL, il est possibled’écrire plusieurs transactions. Le fait de commencer une transaction termine implicitementcelle qui précédait ladite transaction.

Une transaction se termine explicitement par les instructions SQL COMMIT ou ROLLBACK. Ellese termine implicitement :

• à la première commande SQL du LDD ou du LCD rencontrée (CREATE, ALTER, DROP…) ;

• à la fin normale d’une session utilisateur avec déconnexion ;

• à la fin anormale d’une session utilisateur (sans déconnexion).

Nous détaillons ici les principes de base d’une transaction MySQL sans entrer dans des détailsplus techniques (vérouillages, accès concurrents et transactions réparties) qui sortent du cadrede cet ouvrage.

Mode de validationDeux modes de fonctionnement sont possibles : celui par d éfaut ( autocommit) qui validesystématiquement toutes les instructions reçues par la base. Dans ce mode point de salut, car ilvous sera impossible de revenir en arri ère afin d ’annuler une instruction. Le mode à utiliserpour programmer des transactions est celui inverse (autocommit off) qui se déclare à l’aide duparamètre 0 dans l’instruction suivante :

SET AUTOCOMMIT = {0 | 1}

Le tableau suivant précise la validité de la transaction en fonction des événements possibles :

Votre première transactionVous pouvez tester rapidement une transaction en écrivant le bloc suivant qui insère une lignedans une de vos tables.

Tableau 6-23 Validité d’une transaction

Événement Validité

COMMIT Transaction validée.

ROLLBACKCommande SQL (LDD ou LCD).Fin anormale d’une session.

Transaction non validée.




delimiter $

DROP PROCEDURE sp1$

CREATE PROCEDURE sp1()

BEGIN

SET AUTOCOMMIT = 0;

INSERT INTO TableaVous VALUES (...);

END;

$

--appel de la transaction

CALL sp1()$

SELECT * FROM TableaVous$

Exécutez ce bloc dans l ’interface, puis déconnectez-vous soit en cassant la fen être (icône enhaut à droite), soit proprement avec exit. Reconnectez-vous et constatez que l ’enregistre-ment n’est pas présent dans votre table. Même quand la fin du programme est normale, la tran-saction n ’est pas valid ée (car il manque COMMIT). Relancez le bloc en ajoutant cetteinstruction apr ès l ’insertion. Notez que l ’enregistrement est pr ésent d ésormais dans votretable, même après une déconnexion douce ou dure.

Contrôle des transactionsIl est int éressant de pouvoir d écouper une transaction en ins érant des points de validation(savepoints) qui rendent possible l ’annulation de tout ou partie des op érations composantladite transaction.La figure suivante illustre une transaction découpée en trois parties. L’instruction ROLLBACKpeut s’écrire sous différentes formes. Ainsi ROLLBACK TO SAVEPOINT Pointvalidation1invalidera les UPDATE et le DELETE tout en laissant la possibilit é de confirmer l ’instructionINSERT (en fonction des commandes se trouvant après ce ROLLBACK restreint et de la manièredont la session se terminera).

Web

Figure 6-6 Points de validation

Début Transaction INSERT…Point validation 1 UPDATE… UPDATE…Point validation 2 DELETE … …

Fin Transaction

ROLLBACK

ROLLBACK TO SAVEPOINTPoint validation1

ROLLBACK TO SAVEPOINTPoint validation2




Le tableau suivant décrit une transaction MySQL découpée en trois parties. Le programmeuraura le choix entre les instructions ROLLBACK TO SAVEPOINT indiqu ées en commentairepour valider tout ou partie de la transaction. Il faudra finalement se d écider entre COMMIT etROLLBACK.

L’instruction SAVEPOINT déclare un point de validation.

Écourtez la dur ée de vos transactions et programmez-les c ôté serveur (par des proc édurescataloguées). En effet, une transaction nécessite d’accéder souvent et exclusivement aux don-nées et des verrous seront mis en œuvre automatiquement. Ces derniers induisent des tempsd’attente pour les utilisateurs concurrents. Si votre code n’est pas optimisé ou s’il s’exécute ducôté du client, la contention deviendra inévitable.

Transactions imbriquées

Il n ’est pas possible d ’imbriquer plusieurs transactions au sein de diff érents blocs ( BEGIN…END).

Vous ne pouvez pas invalider par ROLLBACK une commande SQL du langage de définition dedonnées ou de contrôle (CREATE, ALTER, RENAME, DROP, GRANT…).

Tableau 6-24 Transaction découpée


BEGINSET AUTOCOMMIT = 0; INSERT INTO Compagnie VALUES('C2',2, 'Place Brassens', 'Blagnac', 'Easy Jet');

Première partie de la transaction.

; UPDATE Compagnie SET nrue = 125 WHERE comp = 'AF'; UPDATE Compagnie SET ville = 'Castanet' WHERE comp = 'C1';

Deuxième partie de la transaction.

; DELETE FROM Compagnie WHERE comp = 'C1';

Troisième partie de la transaction.

-- ; Première partie à valider.

-- ; Deuxième partie à valider.

-- Troisième partie à valider.

; Tout à invalider.

; END;

Valide la ou les sous-parties.

Web

SAVEPOINT P1

SAVEPOINT P2

ROLLBACK TO SAVEPOINT P1



ROLLBACK

COMMIT




Modes d’exécution SQL

Le mode SQL permet de r égir, dans une certaine mesure, le comportement du serveur face àtelle ou telle instruction SQL. Ce comportement correspond principalement à la syntaxe et auxvérifications que doit assurer (ou pas) le serveur.Cette fonctionnalité peut être intéressante pour tester le contexte de production sur des envi-ronnements différents ou avec des bases de données hétérogènes. Préparer une migration versun autre SGBD peut aussi être consid éré comme un objectif des modes SQL. Un autredomaine d ’application concerne le traitement de donn ées en masse o ù il peut être utile dedésactiver des contrôles basiques pour améliorer les performances.

Le contexteLe mode peut se définir pour toutes les sessions au démarrage du serveur par mysqld --sql-mode="mode1,mode2…". Vous pouvez également agir au niveau du fichier d ’initialisation(my.cnf sous Unix et my.ini sous Windows) à l ’aide de l ’étiquette sql-mode="mode1,mode2…". Le mode par défaut est le mode vide.Au niveau d ’une ou de plusieurs sessions, il est possible de d éfinir le mode d ’exécutioncourant à l’aide de la commande SET [GLOBAL|SESSION] sql_mode='mode1,mode2…'.Vous devrez d étenir le privil ège SUPER pour b énéficier de l ’option GLOBAL (qui affecteratoutes les sessions qui d émarreront après). Dans une session, pour conna ître le mode SQLcourant de la session et le mode plus global, il suffit d’interroger cette variable système.mysql> SELECT "Global", "Session";+----------------------------------------------------------------------------+| Global | Session |+----------------------------------------------------------------------------+| STRICT_TRANS_TABLES, | STRICT_TRANS_TABLES, || NO_AUTO_CREATE_USER, | NO_AUTO_CREATE_USER, || NO_ENGINE_SUBSTITUTION | NO_ENGINE_SUBSTITUTION |+----------------------------------------------------------------------------+

Présentons quelques-unes des options disponibles.

L’expression « mode SQL strict » s’applique aux modes qui sont TRADITIONAL, STRICT_TRANS_TABLES, ou STRICT_ALL_TABLES. Ce mode est à préconiser surtout pour les transactions.

Programmation transactionnelleLes modes STRICT_TRANS_TABLES et STRICT_ALL_TABLES déclenchent l’invalidation del’instruction dès lors qu ’une mise à jour d ’une table transactionnelle (moteur InnoDB) poseproblème (mauvais type de données, mauvaise valeur, valeur manquante ou nulle, etc.). Pourles tables non transactionnelles, l’instruction est invalidée si une seule ligne est concernée ousi c’est la première ligne modifiée d’une instruction multiple (multiple-row statement).

@@GLOBAL.sql_mode @@SESSION.sql_mode




● Lorsque l ’erreur survient d ès la deuxi ème ligne, le comportement STRICT_ALL_TABLESretournera une erreur et ignorera le reste des modifications (tout en validant les premi ères).Pour éviter ceci, essayez de ne pas utiliser les instructions multiples (du type INSERT …VALUES (…),(…),(…), etc., et UPDATE ou DELETE dont le WHERE ramène plusieurs lignes).

● Lorsque l ’erreur survient d ès la deuxi ème ligne, le comportement STRICT_TRANS_TABLES convertit la valeur problématique à la valeur de la colonne la plus proche accepta-ble. Si une valeur est manquante, une valeur implicite par défaut est substituée. Dans tousles cas, seul un warning est généré et le traitement se poursuit.

Si vous n’adoptez pas un mode strict (ni STRICT_TRANS_TABLES ni STRICT_ALL_TABLES),MySQL ajustera au mieux les valeurs aux colonnes posant probl ème, tout en produisant deswarnings. Ce comportement peut être explicitement mis en œuvre avec le mode en utilisant ladirective IGNORE pour chaque INSERT et UPDATE.Le mode strict n’a rien à voir avec les contraintes d’intégrité référentielles. C’est au niveau dela variable système foreign_key_checks (à 0 les contraintes sont désactivées, à 1 elles sontactives) qu’il faudra agir.

Les datesCe paragraphe concerne les colonnes DATE et DATETIME, et ne s ’applique pas aux colonnesTIMESTAMP qui correspondent toujours à des dates valides.Les modes stricts n’autorisent pas les dates invalides comme le 31 avril et les dates où l’annéeest différente de 0 avec un mois ou un jour nul. En revanche, ils laissent passer les dates ayantzéro dans l’année, quel que soit le mois ou le jour…Pour d ébusquer certaines erreurs, vous devrez ajouter NO_ZERO_IN_DATE (qui interdit lesjours ou les mois à zéro) et NO_ZERO_DATE (qui interdit '0000-00-00') à votre mode strict.Si vous ne désirez opérer aucun contrôle mis à part le fait que le mois soit compris entre 1 et12 et le jour entre 1 et 31, le mode ALLOW_INVALID_DATES peut vous intéresser. Combiné ounon à un mode strict, le contrôle complet ne sera pas réalisé (et le 31 avril devient valide). Enrevanche, en l ’absence de mode, une date invalide se transforme en '0000-00-00' et unwarning est généré.Le tableau suivant présente un scénario illustrant ces différents cas d’utilisation.

Tableau 6-25 Modes SQL pour les dates

Commandes SQL Commentaires

SET SESSION sql_mode='STRICT_TRANS_TABLES';CREATE TABLE Vols (num_vol CHAR(6), date_vol DATE, num_client INT, date_resa TIMESTAMP);

Initialisation du mode strict SQL.

Création de la table Vols.

INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143', ,5);INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143', ,4);

Deux insertions valides (même si je doute qu’à l’année 0, il existait des aéronefs…)

'2010-05-02'

'0000-04-10'




INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143', ,170);

Insertion invalide : ERROR 1292 (22007): Incorrect date value … for column 'date_vol'…

SET SESSION sql_mode= 'STRICT_TRANS_TABLES,NO_ZERO_DATE';INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143', ,0);

Enrichissement du mode strict SQL.Insertion invalide (la date nulle est inter-dite du fait du mode SQL) : ERROR 1292 (22007): Incorrect date value … for column 'date_vol'…

SET SESSION sql_mode= 'STRICT_TRANS_TABLES,NO_ZERO_DATE, NO_ZERO_IN_DATE';INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143', ,70);INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143', ,165);INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143', ,1);

Enrichissement du mode strict SQL.

Insertions invalides (le jour ou le mois nul est interdit du fait du mode SQL) : ERROR 1292 (22007): Incor-rect date value … for column 'date_vol'…

mysql> SELECT * FROM Vols;+---------+------------+------------+---------------------+| num_vol | date_vol | num_client | date_resa |+---------+------------+------------+---------------------+| AF6143 | 2010-05-02 | 5 | 2010-10-05 16:38:49 || AF6143 | 0000-04-10 | 4 | 2010-10-05 16:38:49 |+---------+------------+------------+---------------------+

SET SESSION sql_mode= 'STRICT_TRANS_TABLES,ALLOW_INVALID_DATES';INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143','2011-04-31',170);

Enrichissement du mode strict SQL.

Insertion valide du fait du mode SQL.

SET SESSION sql_mode='';INSERT INTO Vols (num_vol,date_vol,num_client) VALUES ('AF6143','2012-04-31',367);

Annulation du mode strict SQL.Insertion valide (plus aucun contrôle). Un warning est toutefois généré : 1265 Data truncated for column 'date_vol'...

mysql> SELECT * FROM Vols;+---------+------------+------------+---------------------+| num_vol | date_vol | num_client | date_resa |+---------+------------+------------+---------------------+| AF6143 | 2010-05-02 | 5 | 2010-10-05 16:38:49 || AF6143 | 0000-04-10 | 4 | 2010-10-05 16:38:49 || AF6143 | 2011-04-31 | 170 | 2010-10-05 17:36:38 || AF6143 | 0000-00-00 | 367 | 2010-10-05 17:36:38 |+---------+------------+------------+---------------------+

Tableau 6-25 Modes SQL pour les dates (suite)


'2011-04-31'

'0000-00-00'

'2011-04-00'

'2011-00-10'

'2011-00-00'




Les séquencesCe paragraphe concerne les colonnes AUTO_INCREMENT (séquence). Dans un mode strict oupar défaut, la valeur d ’une séquence est g énérée séquentiellement même pour les colonnesdont la valeur est NULL ou 0 (voir chapitre 2, section « Séquences »).Le mode NO_AUTO_VALUE_ON_ZERO inhibe ce comportement pour la valeur 0 qui ne déclen-che pas une nouvelle séquence (alors que NULL le fait toujours). Ce mécanisme peut être inté-ressant lors d’un dump d’une table suivie d’un rechargement (la commande mysqldump utiliseautomatiquement ce mode).Le tableau suivant présente quelques insertions illustrant ce mode d’utilisation.

Tableau 6-26 Mode SQL pour les séquences


CREATE TABLE Affreter (numAff SMALLINT AUTO_INCREMENT, comp CHAR(4),immat CHAR(6), dateAff DATE, nbPax SMALLINT(3), CONSTRAINT pk_Affreter PRIMARY KEY (numAff));

SET SESSION sql_mode='';INSERT INTO Affreter (comp,immat,dateAff,nbPax) VALUES ('AF', 'F-WTSS', '2012-05-13', 85);INSERT INTO Affreter (numAff,comp,immat,dateAff,nbPax) VALUES (NULL, 'EJ','N-23DS', '2012-09-11', 90);INSERT INTO Affreter (numAff,comp,immat,dateAff,nbPax) VALUES (0, 'AF','F-FPJY', '2012-09-11', 95);

Création de la table.

Initialisation du mode vide (non strict).Insertion de trois lignes et séquen-ces (les deux premières avec NULL, la troisième avec 0).

mysql> SELECT * FROM Affreter;+--------+------+--------+------------+-------+| numAff | comp | immat | dateAff | nbPax |+--------+------+--------+------------+-------+| 1 | AF | F-WTSS | 2012-05-13 | 85 || 2 | EJ | N-23DS | 2012-09-11 | 90 || 3 | AF | F-FPJY | 2012-09-11 | 95 |+--------+------+--------+------------+-------+

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';INSERT INTO Affreter (numAff,comp,immat,dateAff,nbPax) VALUES (NULL, 'AF','F-FRSS', '2012-09-11', 127);INSERT INTO Affreter (numAff,comp,immat,dateAff,nbPax) VALUES (0, 'AF','F-GLFS', '2012-09-11', 75);

Enrichissement du mode SQL.Insertion de deux lignes et séquen-ces (la première avec NULL, la deuxième avec 0).




Chaînes de caractèresDans tous les cas (mode strict ou non), les éventuels espaces qui suivent une valeur d’unecolonne CHAR ne sont pas restitu és à l ’extraction (ces caract ères sont en revanche pr ésentsdans la base). Le mode PAD_CHAR_TO_FULL_LENGTH inhibe ce comportement et ne restituepas les valeurs de ces colonnes telles qu’elles sont stockées mais avec leur taille maximale. Cemode ne s’applique pas aux colonnes VARCHAR pour lesquelles les valeurs sont toujours resti-tuées telles qu’elles sont stockées.Le tableau suivant présente quelques insertions illustrant ce mode d’utilisation.

mysql> SELECT * FROM Affreter ;+--------+------+--------+------------+-------+| numAff | comp | immat | dateAff | nbPax |+--------+------+--------+------------+-------+| 0 | AF | F-GLFS | 2012-09-11 | 75 || 1 | AF | F-WTSS | 2012-05-13 | 85 || 2 | EJ | N-23DS | 2012-09-11 | 90 || 3 | AF | F-FPJY | 2012-09-11 | 95 || 4 | AF | F-FRSS | 2012-09-11 | 127 |+--------+------+--------+------------+-------+

Tableau 6-26 Mode SQL pour les séquences (suite)


Tableau 6-27 Mode SQL pour les chaînes de caractères


CREATE TABLE Passager (num_pax SMALLINT AUTO_INCREMENT PRIMARY KEY, prenom CHAR(20), nom CHAR(20), mail VARCHAR(30));

SET sql_mode = '';INSERT INTO Passager (prenom,nom,mail) VALUES ('Fred','Brouard','[email protected]');

Création de la table.

Annulation du mode strict.Insertion d’une ligne.

SELECT CONCAT(prenom,LENGTH(prenom)) "Prenom", CONCAT(nom,LENGTH(nom)) "Nom", CONCAT(mail,LENGTH(mail)) "Mail" FROM Passager;+--------+----------+----------------------+| Prenom | Nom | Mail |+--------+----------+----------------------+| Fred4 | Brouard7 | [email protected] |+--------+----------+----------------------+

Les colonnes CHAR sont resti-tuées comme elles sont sto-ckées.

SET sql_mode='PAD_CHAR_TO_FULL_LENGTH';INSERT INTO Passager (prenom,nom,mail) VALUES ('Chris','Codd','[email protected]');

Enrichissement du mode SQL.Insertion de la deuxième ligne.




Les moteursIl est possible d ’affecter un moteur de stockage à une table lors de sa cr éation par CREATETABLE ou une fois créée par ALTER TABLE. Suivant votre configuration, ce moteur peut êtreindisponible (pas encore compilé ou désactivé). Le cas échéant, le mode NO_ENGINE_SUBS-TITUTION provoque une erreur en retour et annule la cr éation ou la modification de la table.Si ce mode n ’est pas actif, la table est quand m ême créée avec le moteur de stockage pardéfaut (et un warning est retourn é). En revanche, la table ne sera pas modifi ée par ALTERTABLE et un warning sera également retourné.Le tableau suivant présente quelques cas d’utilisation, dans lesquels on suppose que le moteurFEDERATED n’est pas implémenté. La première table est associ ée au moteur de stockage pardéfaut (ici InnoDB, voir votre variable default-storage-engine dans le fichier de confi-guration). La deuxième table n’est pas créée et une erreur est retournée.

SELECT CONCAT(prenom,LENGTH(prenom)) "Prenom", CONCAT(nom,LENGTH(nom)) "Nom", CONCAT(mail,LENGTH(mail)) "Mail" FROM Passager;+------------------------+------------------------+----------------------+| Prenom | Nom | Mail |+------------------------+------------------------+----------------------+| Fred 20 | Brouard 20 | [email protected] || Chris 20 | Codd 20 | [email protected] |+------------------------+------------------------+----------------------+

Tableau 6-27 Mode SQL pour les chaînes de caractères (suite)


Tableau 6-28 Mode SQL pour les moteurs

Création de tables

SET sql_mode = '';CREATE TABLE Clients (num_cli SMALLINT AUTO_INCREMENT PRIMARY KEY, prenom CHAR(20), nom CHAR(20)) ENGINE=FEDERATED;Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show warnings;+---------+------+-------------------------------------------------+| Level | Code | Message |+---------+------+-------------------------------------------------+| Warning | 1286 | Unknown storage engine 'FEDERATED' || Warning | 1266 | Using storage engine InnoDB for table 'clients' |+---------+------+-------------------------------------------------+

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';CREATE TABLE Avions (immat VARCHAR(8) PRIMARY KEY, typeav CHAR(20), mise_service DATE) ENGINE=FEDERATED;ERROR 1286 (42000): Unknown storage engine 'FEDERATED'




PortabilitéLes modes NO_KEY_OPTIONS et NO_TABLE_OPTIONS masquent les options relatives aux tablesspécifiques à MySQL en termes d’index, de commentaires et de moteur. Ces modes sont utiliséspar la commande mysqldump. À titre d’exemple, supposons qu’un index soit défini sur la tableClients. Le tableau suivant présente l’utilisation de ces modes.

CREATE INDEX idx_nomcli USING BTREE

ON Clients (nom DESC)

COMMENT 'plus rapide pour les recherches nominatives';

Le mode IGNORE_SPACE permet de placer un espace entre le nom d’une fonction MySQL (nes’applique pas aux fonctions cataloguées) et les paramètres (devant le caractère « ( »). Lenom d’une fonction est alors consid éré comme un mot r éservé du vocable MySQL. Il n ’estjamais possible, quel que soit le mode SQL, de s éparer le nom d’une fonction utilisateur (oud’une fonction stockée) de ses paramètres.

Tableau 6-29 Modes SQL pour la portabilité

Sans le mode portabilité Avec le mode portabilitéSET sql_mode = '';mysql> SHOW CREATE TABLE Clients;+---------+---------------------------------| Table | Create Table+---------+---------------------------------| Clients | CREATE TABLE `clients` ( `num_cli` smallint(6) NOT NULL AUTO_INCREMENT, `prenom` char(20) DEFAULT NULL, `nom` char(20) DEFAULT NULL, PRIMARY KEY (`num_cli`), KEY `idx_nomcli` (`nom`) USING BTREE COMMENT 'plus rapide pour les recherches nominatives') ENGINE=InnoDB DEFAULT CHARSET=latin1

SET sql_mode = 'NO_KEY_OPTIONS, NO_TABLE_OPTIONS';mysql> SHOW CREATE TABLE Clients;+---------+------------------------------| Table | Create Table+---------+------------------------------| Clients | CREATE TABLE `clients` ( `num_cli` smallint(6) NOT NULL AUTO_INCREMENT, `prenom` char(20) DEFAULT NULL, `nom` char(20) DEFAULT NULL, PRIMARY KEY (`num_cli`), KEY `idx_nomcli` (`nom`))

Tableau 6-30 Mode SQL IGNORE_SPACE

Sans le mode IGNORE_SPACE Avec le mode IGNORE_SPACEmysql> SET sql_mode = '';mysql> SELECT MAX(nom) FROM Passager;

+----------+| MAX(nom) |+----------+| Codd |+----------+

mysql> SELECT MIN (nom) FROM Passager;ERROR 1630 (42000): FUNCTION bdsoutou.MIN does not exist…

mysql> SET sql_mode = 'IGNORE_SPACE';mysql> SELECT MAX(nom), MIN (nom) FROM Passager;

+----------+-----------+| MAX(nom) | MIN (nom) |+----------+-----------+| Codd | Brouard |+----------+-----------+




Les combinaisonsCertains modes sont composés de plusieurs modes élémentaires, parmi lesquels nous pouvonsciter :● ANSI combine REAL_AS_FLOAT (consid ère les colonnes REAL comme FLOAT et non

comme DOUBLE), PIPES_AS_CONCAT (|| permet de concaténer deux chaînes de caractères),ANSI_QUOTES (considère les caractères " et ̀ pour délimiter un identifiant et non unechaîne de caractères) et IGNORE_SPACE. Ce mode se rappoche le plus du standard SQL.

● TRADITIONAL combine STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER etNO_ENGINE_SUBSTITUTION. Ce mode ajoute à un mode strict des restrictions sur lesdonnées en entrée.

● ORACLE combine PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS et NO_AUTO_CREATE_USER. Cemode se rapproche le plus de la syntaxe d’Oracle.

● POSTGRESQL combine PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS et NO_FIELD_OPTIONS. Ce mode se rapproche le plus de lasyntaxe PostgreSQL.

● DB2 combine PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS,NO_TABLE_OPTIONS et NO_FIELD_OPTIONS. Ce mode se rapproche le plus de la syntaxed’IBM DB2.

● MSSQL combine PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS,NO_TABLE_OPTIONS et NO_FIELD_OPTIONS. Ce mode se rapproche le plus de la syntaxede Microsoft SQL Server.

Prudence si vous utilisez un tel mode en exécutant des instructions dont la syntaxe est proprié-taire MySQL (les énumérations ENUM et SET par exemple).




Exercices

L’objectif de ces exercices est d ’écrire des blocs puis des transactions manipulant des tablesdu schéma Parc Informatique. Vous utiliserez une procédure pour tester vos blocs, comme ilest indiqué dans la section « Test des exemples ».

6.1 Extraction de donnéesÉcrire le bloc MySQL qui affiche les détails de la dernière installation de logiciel sous la forme suivante(les champs en gras sont à extraire) :+------------------------------------------------+

| Resultat 1 exo 1 |

+------------------------------------------------+

| Derniere installation en salle : numérodeSalle |+------------------------------------------------+

+--------------------------------------------------------------------+

| Resultat 2 exo 1 |

+--------------------------------------------------------------------+

| Poste : numéroPoste Logiciel : nomLogiciel en date du dateInstallation |+--------------------------------------------------------------------+

Vous utiliserez SELECT … INTO pour extraire ces valeurs. Ne tenez pas compte, pour le moment, deserreurs qui pourraient éventuellement se produire (aucune installation de logiciel, poste ou logiciel nonréférencés dans la base, etc.).

6.2 Variables de sessionÉcrire le bloc MySQL qui affecte hors d’un bloc, par des variables session, un n uméro de salle et untype de poste, et qui retourne des variables session permettant de composer un message indiquant lesnombres de postes et d’installations de logiciels correspondants :+--------------------------------------------------------------------+

| Resultat exo2 |

+--------------------------------------------------------------------+

| x poste(s) installe(s) en salle y, z installation(s) de type t |+--------------------------------------------------------------------+

Essayez pour la salle s01 et le type UNIX. Vous devez extraire 1 poste et 3 installations. Ne tenez pascompte pour le moment d’éventuelles erreurs (aucun poste trouvé ou aucune installation réalisée, etc.).

6.3 Transaction

Écrire une tr ansaction per mettant d ’insérer un nouv eau logiciel dans la base apr ès avoir passé enparamètres, par des variables de session, toutes ses caractéristiques (numéro, nom, version et type dulogiciel). La date d ’achat doit être celle du jour . Tracer l ’insertion du logiciel (message Logicielinséré dans la base).




Il faut ensuite procéder à l’installation de ce logiciel sur le poste de code p7 (utiliser une variable pourpouvoir plus facilement modifier ce paramètre). L’installation doit se faire aussi à la date du jour. Penserà actualiser correctement la colonne delai qui mesure le d élai (TIME) entre l ’achat et l ’installation.Pour ne pas que ce d élai soit nul (les deux inser tions se feraient dans la m ême seconde dans cettetransaction), placer une attente de 5 secondes entre l’ajout dans la tab le Logiciel et celui dans latable Installer à l’aide de l ’instruction SELECT SLEEP(5). Utiliser la f onction TIMEDIFF pourcalculer ce délai.

Insérer par exemple le logiciel log15, de nom MySQL Query, version 1.4, typePCWS coûtant 95 €.Tracer la transaction comme suit :

+------------------------------+

| message1 |

+------------------------------+

| Logiciel insere dans la base |

+------------------------------+

1 row in set (0.01 sec)

+----------------------------------+

| message2 |

+----------------------------------+

| Date achat : 2005-11-23 19:16:04 |

+----------------------------------+

+----------+

| SLEEP(5) |

+----------+

| 0 |

+----------+

+-----------------------------------------+

| message3 |

+-----------------------------------------+

| Date installation : 2005-11-23 19:16:10 |

+-----------------------------------------+

+--------------------------------+

| message4 |

+--------------------------------+

| Logiciel installe sur le poste |

+--------------------------------+

Vérifiez l ’état des tab les mises à jour apr ès la tr ansaction. Ne tenez pas compte pour le momentd’éventuelles erreurs (n uméro du logiciel d éjà référencé, type du logiciel incorrect, installation d éjàréalisée, etc.).

Attente de 5 secondes à ce niveau


Documents

Programmer avec MySQL - eyrolles.com€¦© Groupe Eyrolles, 2006, 2011, ISBN : 978-2-212-12869-7 © Éditions Eyrolles 223 Chapitre 6 ... Partie II Programmation procédurale 224