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 :

1/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 « */ »

2/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

3/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

4/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.

5/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

6/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.

7/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.
8/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

9/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

10/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 des transactions

Il est possible de découper une transaction en insérant des points de validation (savepoints) qui rendent
possible l’annulation de tout ou partie des opérations composant la dite transaction.

11/19
L’instruction SAVEPOINT déclare un point de validation :
Syntaxe : SAVEPOINT Label ;

Exemple : Le tableau suivant décrit une transaction MySQL découpée en trois parties. Le programmeur aura le choix
entre les instructions ROLLBACK TO SAVEPOINT indiquées en commentaire pour valider tout ou partie de la
transaction. Il faudra finalement choisir entre COMMIT et ROLLBACK.

Note : Il n’est pas possible d’invalider par ROLLBACK une commande SQL du LDD rencontrée (CREATE,
ALTER, DROP…).

12/19
 Partie 2 : Programmation avancée
Cette partie est consacrée à des caractéristiques avancées du langage procédural de MySQL Stored

1. Procédures stockées : ( stored procedures ou stored routines)

Ce sont des fonctions ou procédures « cataloguées » (ou « stockées »)

1.1. Généralités
Une procédure peut être appelée à l’aide :
- de l’interface de commande (par CALL),
- dans un programme externe (Java, PHP, C…),
- par d’autres procédures ou fonctions,
- dans le corps d’un déclencheur.

Les fonctions peuvent être invoquées

- dans une instruction SQL (SELECT, INSERT, et UPDATE),
- dans une expression (affectation de variable ou calcul).

1.2. Les principaux avantages (stockées côté serveur) :

- La modularité : un sous-programme peut être composé d’autres blocs d’instructions.
- Réutilisabilité : 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 pour parcourir des résultats de
requêtes (curseurs), pour traiter des erreurs (handlers) et pour programmer 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 sur les agissements des utilisateurs.

1.3. Syntaxe de création de procédure et fonction

CREATE { PROCEDURE | FUNCTION }
nomSousProgramme [(...) ] [RETURNS typeMSQL ]
BEGIN
[DECLARE déclaration ]; ...
instructions MySQL;
BEGIN
[DECLARE déclaration ];...
...
...
instructions MySQL;
END;
…
END;
délimiteur

où :
Délimiteur : délimiteur de commandes différent de « ; » (symbole utilisé obligatoirement en fin de chaque
déclaration et instruction du langage procédural de MySQL

1.4. Compilation et appel de sous-programme

1.4.1. Compilation :
Pour compiler ces sous-programmes à partir de l’interface de commande, il faut procéder comme suit :

delimiter $
Sous programme ;

13/19
 $

1.4.2. Appel
La procédure s’appelle toujours par CALL, la fonction par son nom.

Exemple :
a)
delimiter ;
SET @vs_compa = 'AF';
SET @vs_nompil = '';
SET @vs_heures = '';
CALL PlusExperimente (@vs_compa, @vs_nompil, @vs_heures);
b)
delimiter ;
SELECT EffectifsHeure ('AF',300) ;
c)
SET @vs_compa = NULL$
SET @vs_nompil = ''$
SET @vs_heures = ''$
CREATE PROCEDURE test.sp1()
BEGIN
CALL PlusExperimente (@vs_compa,@vs_nompil,@vs_heures);
END;
$

CALL test.sp1()$

1.5. Destruction d’un sous-programme

Pour supprimer un sous-programme, si vous n’êtes pas son créateur, le privilège ALTER ROUTINE est requis
sur la base de données.

Syntaxe : DROP {PROCEDURE | FUNCTION} [IF EXISTS] [nomBase.]nomSousProg

2. Programmation des curseurs

Un curseur est une zone mémoire qui est générée côté serveur (mise en cache) et qui permet de traiter
individuellement chaque ligne renvoyée par un SELECT.
 Le curseur est décrit après les variables et avant les exceptions
 Il est ouvert dans le code du programme et va ensuite se charger en extrayant les données de la base
 Le programme parcour tout le curseur en récupérant les lignes une par une dans des variables locales
 Le curseur est ensuite fermé.

2.1. Instructions
Les instructions disponibles pour travailler avec des curseurs sont définies dans le tableau suivant :

1. Déclaration de curseur : Cursor For

Exemple : DECLARE curs1 CURSOR FOR
SELECT brevet,nbHVol,comp
FROM Pilote
WHERE comp = 'AF';
2. Ouverture de curseur (chargement des lignes) : Open
Exemple : OPEN curs1;

Note : Aucune exception n’est levée si la requête ne ramène aucune ligne.

3. Recherche d’un ligne du curseur : Fetch

Exemple: FETCH curs1 INTO var1, var2, var3;

Note : Positionnement sur la ligne suivante et chargement de l’enregistrement courant dans une ou
plusieurs variables.
4. Fermeture de curseur : Close
Exemple : CLOSE curs1;

14/19
3. Gestion des exceptions
Afin d’éviter qu’un programme ne s’arrête dès la première erreur suite à une instruction SQL, il est
indispensable de prévoir les cas potentiels d’erreurs et d’associer à chacun de ces cas la programmation d’une
exception (handler dans le vocabulaire de MySQL).

- Une exception MySQL correspond à une condition d’erreur

- Les exceptions peuvent se paramétrer dans un sous-programme (fonction ou procédure cataloguée) ou
un déclencheur
- Une exception est détectée (ou « levée ») si elle est prévue dans un handler au cours de l’exécution d’un
bloc (entre BEGIN et END)
- Une fois levée, elle fait continuer ou sortir du bloc le programme après avoir réalisé une ou plusieurs
instructions que le programmeur aura explicitement spécifiées.

3.1. Syntaxe
DECLARE { CONTINUE | EXIT }
HANDLER FOR
{ SQLSTATE [VALUE] ‘valeur_sqlstate’ | nomException | SQLWARNING
| NOT FOUND | SQLEXCEPTION | code_erreur_mysql }
instructions_MySQL;
où :
- CONTINUE est une directive (appelée handler) qui force la poursuite de l’exécution de programme
lorsqu’il se passe un événement prévu dans la clause FOR.
- EXIT est une directive qui fait sortir l’exécution du bloc courant (entre BEGIN et END).

Et Les événements sont :

- SQLSTATE permet de couvrir toutes les erreurs d’un état donné.
- nomException s’applique à la gestion des exceptions nommées (étudiées plus loin).
- SQLWARNING permet de couvrir toutes les erreurs d’état SQLSTATE débutant par 01.
- NOT FOUND permet de couvrir toutes les erreurs d’état SQLSTATE débutant par 02.
- SQLEXCEPTION gère toutes les erreurs qui ne sont ni gérées par SQLWARNING ni par NOT FOUND.
- code_erreur_mysql désigne un code erreur Mysql

instructions_MySQL : une ou plusieurs instructions du langage de MySQL (bloc, appel possibles par CALL
d’une fonction ou d’une procédure stockée).

Exemple :

15/19
4. Mise en place de déclencheurs (Triggers)

4.1. Qu’est ce qu’un déclencheur ?

Un déclencheur peut être vu comme des sous-programmes résidents associés à un événement particulier :
- une instruction INSERT, UPDATE, ou DELETE sur une table ou vue; (déclencheurs LMD)
- une instruction CREATE, ALTER, ou DROP sur un objet (table, vue, index, etc.); (déclencheurs LDD)
- le démarrage ou l’arrêt de la base, une erreur spécifique (not found, duplicate key, etc.), une connexion
ou une déconnexion d’un utilisateur. On parle de déclencheurs d’instances.

À la différence des sous-programmes, l’exécution d’un déclencheur n’est pas explicite (par CALL par
exemple), c’est l’événement lui même qui déclenche automatiquement le code programmé dans le
déclencheur.

4.2. À quoi sert un déclencheur ?

En théorie, un déclencheur permet de :
- Programmer toutes les règles de gestion qui n’ont pas pu être mises en place par des contraintes au
niveau des tables.
- Déporter des contraintes au niveau du serveur et alléger ainsi la programmation client.
- Renforcer des aspects de sécurité et d’audit.
- Programmer l’intégrité référentielle et la réplication dans des architectures distribuées, avec
l’utilisation de liens de bases de données.

4.3. Déclencheur dans MySQL :

- les déclencheurs n’existent que depuis la version 5.
- Ils sont encore limitatifs en termes de fonctionnalités et relativement instables. En page d’accueil du
site de MySQL, on peut lire dans MySQL 5.0 Triggers : « Triggers are very new. There are bugs. … Do
not try triggers with a database that has important data in it… ».
- Prudence donc avec vos données. A partir de la version 5, il faudrait suivre de près toutes les
limitations qui seront sans doute résolues au fur et à mesure des prochaines versions majeures du

16/19
 serveur. Rappelons qu’avant la version 5.0.10, les déclencheurs ne pouvaient même pas accéder à la
base !
- Mysql dans sa version 5, ne prend en charge que les déclencheurs de type LMD.

4.4. Syntaxe
CREATE TRIGGER nomDéclencheur
{ BEFORE | AFTER } { DELETE | INSERT | UPDATE }
ON nomTable
FOR EACH ROW
{ instruction; |
[etiquette:] BEGIN
instructions;
END [etiquette];
}

Note :
- Chaque enregistrement qui tente d’être supprimé d’une table, qui inclut un déclencheur de type
DELETE FOR EACH ROW, est désigné par OLD au niveau du code du déclencheur. L’accès aux
colonnes de ce pseudo-enregistrement dans le corps du déclencheur se fait par la notation pointée.
- Chaque enregistrement qui tente d’être ajouté dans une table est désigné par NEW au niveau du code
du déclencheur. L’accès aux colonnes de ce pseudo-enregistrement dans le corps du déclencheur se
fait par la notation pointée.

4.5. Invalidation dans le déclencheur

Une fonctionnalité importante des déclencheurs consiste à pouvoir invalider l’événement qui a déclenché
l’action. En d’autres termes, c’est pouvoir faire dire au déclencheur non à une insertion, une modification
ou à une suppression.

Dans tout déclencheur (de type BEFORE ou AFTER), une erreur lors de l’exécution et toutes les
instructions du bloc sont invalidées.

L’invalidation dans un déclencheur se traduit en général par le déclenchement d’une exception (qui fait
avorter l’instruction LMD), et par le retour d’un message d’erreur personnalisé.
Les procédures et déclencheurs MySQL ne permettent pour l’instant ni de provoquer une exception
système ni de retourner un code SQL personnalisé. Il n’est pas non plus possible d’utiliser ROLLBACK
dans un déclencheur

Une seule solution, qui n’est pas du tout satisfaisante, comme nous allons le voir, consisterait à provoquer
artificiellement une erreur (mais pas une erreur système, par exemple accéder à une table inexistante). Il
faut une erreur sémantiquement correcte qui pose problème à l’exécution (NULL dans une clé primaire).

Exemple :
Considérons la contrainte que « tout pilote ne peut être qualifié sur plus de trois types d’appareils ». Ici, il s’agit
d’assurer la cohérence entre la valeur de la colonne nbQualif de la table Pilote et les enregistrements de la
table Qualifications.

On crée d’abord la table trace :

CREATE TABLE Trace(col VARCHAR(80) PRIMARY KEY).

17/19
 Ensuite:

CREATE TRIGGER TrigInsQualif

BEFORE INSERT ON Qualifications
FOR EACH ROW
BEGIN
DECLARE v_compteur TINYINT(1);
DECLARE v_nom VARCHAR(30);
SELECT nbQualif, nom INTO v_compteur, v_nom
FROM Pilote WHERE brevet = NEW.brevet;

IF v_compteur < 3 THEN

UPDATE Pilote
SET nbQualif = nbQualif + 1
WHERE brevet = NEW.brevet;
ELSE
INSERT INTO TRACE VALUES
(CONCAT('Le pilote ',v_nom, ' a déjà 3 qualifications!'));
INSERT INTO TRACE VALUES (NULL);
END IF;
END;

Et si on fait un ajout incorrect :

INSERT INTO Qualifications VALUES ('PL-1','A380', SYSDATE())$

ERROR 1048 (23000): Column 'col' cannot be null

-- ne fait pas l'INSERT dans Qualifications
-- ni dans Trace !

4.6. Suppression d’un déclencheur

Syntaxe :
DROP TRIGGER [nomBase.]nomDéclencheur;

Note : Le fait de détruire une table a pour conséquence d’effacer aussi tous les déclencheurs qui lui sont
associés.

5. Utilisation du SQL dynamique

Une instruction SQL dynamique est stockée en tant que chaîne de caractères qui sera évaluée à l’exécution
et non à la compilation (en opposition aux instructions SQL statiques qui peuplent la majorité des sous-
programmes).

Commandes (Directives) associées :

1. Déclaration de l’instruction dynamique :

PREPARE nomEtat FROM étatPréparé;

- La commande PREPARE associe un nom à une instruction dynamique

- étatPréparé est une chaîne ou une variable de session contenant le texte de l’instruction SQL construite
- Dans cette chaîne, le caractère « ? » permet de se substituer à un paramètre
2. Exécution d’une instruction dynamique :

EXECUTE nomEtat [USING @var1 [, @var2] ...];

- L’instruction EXECUTE lance l’ordre paramétré avec éventuellement la clause USING qui reliera les
paramètres aux variables de session.

3. Terminer une instruction dynamique :

DEALLOCATE PREPARE nomEtat

- supprime le contenu de l’ordre (une fin de session désalloue tous les ordres ouverts)

18/19
 Exemple 1 : Considérons la table Avion suivante :

Immatriculation TypeAv NbHvol Compagnie

F-GLFS A320 1000 AH
F-WOWW A380 1500 AH

La procédure suivante utilise la construction dynamique de l’extraction des avions dont le nombre d’heures de vol
est égal à un paramètre spécifié par une variable de session (ici évaluée à 1 000). La requête est elle-même stockée
dans une variable de session.

Delimiter $
-- Déclaration des variables de session
SET @vs_chaine = ‘SELECT * FROM Avion WHERE nbHVol=?’ $
SET @vs_nbhVol = 1000$

-- Déclaration de la procédure
CREATE PROCEDURE sousProg()
BEGIN
-- Préparation de l’ordre.
PREPARE etat FROM @vs_chaine;

-- Exécution de l’ordre
EXECUTE etat USING @vs_nbhVol;

DEALLOCATE PREPARE etat;

END ;
$

CALL sousProg() -- Cet appel extrait l’avion immatriculé 'F-GLFS'.

----------------------------------------------------------- FIN ---------------------------------------------------------

19/19