Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 3 sur 12
Maîtriser les instructions de base
Les variables locales et de session, la portée des objets et le verrouillage des tables.
- ≈ 90 min
- 2 leçons
- 1 vidéo
- 3 exercices
Module 1Maîtriser le langage de programmation procédurale sous MySQL
Chapitre 3 sur 12
Les variables locales et de session, la portée des objets et le verrouillage des tables.
Objectifs
Avec ce chapitre, tu sauras…
DECLARE et leur donner une valeur ;@variable ;LOCK TABLES, UNLOCK TABLES).Leçon 1
Les variables scalaires (locales)
Une variable locale se déclare avec DECLARE, au début d'un bloc BEGIN … END :
DECLARE nomVariable1[,nomVariable2...] typeMySQL [DEFAULT expression];DECLARE v_dateNaissance DATE;
DECLARE v_trouve BOOLEAN DEFAULT TRUE;
DECLARE v_Dans2jours DATE DEFAULT ADDDATE(SYSDATE(), 2);
DECLARE i, j, k INT;DEFAULT donne une valeur de départ ; sans DEFAULT, la variable vaut NULL ;i, j, k) ;SET (SET i = 1;) ou avec SELECT … INTO (chapitre 2).Les variables de session (externes)
Les variables de session (user-defined variables) commencent par @. Elles sont créées par un client et visibles seulement par lui : elles sont propres à la session (la connexion) et disparaissent à la déconnexion. Elles n'ont pas besoin d'être déclarées.
SET @nom_variable := valeur;
-- ou
SELECT @nom_variable := valeur;SELECT @max_prix := MAX(pu) -- initialiser la variable de session @max_prix avec le prix maximum
FROM produit;
SELECT id, designation, qtstock, pu
FROM produit
WHERE pu = @max_prix; -- utiliser @max_prix pour lister les produits dont le prix est le maximum| Variable locale | Variable de session | |
|---|---|---|
| écriture | v_total | @total |
| déclaration | DECLARE obligatoire, au début d'un bloc | aucune : SET @total = 0; |
| où ? | seulement dans un programme stocké, dans son bloc | partout : console, requêtes, procédures |
| durée de vie | la fin du bloc | la fin de la session |
| usage typique | calculs internes | récupérer un paramètre OUT (CALL p(@total)) |
Vidéo · À regarder
FRBase de données : Procédures stockées structurées
Algomius · YouTube
Leçon 2
| Verrou | La session qui verrouille | Les autres sessions |
|---|---|---|
| READ | peut lire, ne peut pas écrire | peuvent lire, mais leurs écritures attendent |
| WRITE | peut lire et écrire | attendent pour lire comme pour écrire |
1Une variable locale se déclare avec…
2Une variable sans DEFAULT vaut au départ…
3Une variable de session commence par…
4Une variable de session est visible…
5Une variable déclarée dans un sous-bloc est visible dans le bloc parent.
6Avec LOCK TABLES produit READ, la session qui a le verrou peut…
7Une session peut libérer le verrou d'une autre session.
L'essentiel du chapitre
DECLARE v_x type [DEFAULT valeur]; au début d'un bloc, NULL par défaut ; SET v_x = … ou SELECT … INTO v_x.v_ et p_.@x, sans déclaration, SET @x = … ou SELECT … INTO @x, privée à la session, sert pour les paramètres OUT.LOCK TABLES t READ | WRITE; … UNLOCK TABLES; ; une session ne gère que ses propres verrous.Tester avec deux sessions
La portée des objets
La portée d'un objet (variable, curseur, exception) est limitée au bloc dans lequel il est déclaré :
DELIMITER $$
CREATE PROCEDURE demo_portee()
BEGIN
DECLARE a INT DEFAULT 1;
BEGIN
DECLARE b INT DEFAULT 2;
SELECT a, b; -- 1, 2 : a est visible dans le sous-bloc
END;
SELECT a; -- 1 ; SELECT b ici provoquerait l'erreur « Unknown column 'b' »
END $$
DELIMITER ;Ce que tu vas voir (6 min) : les chapitres « Portée des variables locales ». Le formateur montre la portée des variables locales dans des blocs imbriqués : ce qui est visible dans un sous-bloc et ce qui ne l'est pas.
Un verrou est un drapeau associé à une table. MySQL permet à une session client d'acquérir explicitement un verrou de table pour empêcher les autres sessions d'y accéder pendant une période donnée. Une session ne peut acquérir ou libérer des verrous que pour elle-même, jamais pour une autre session.
LOCK TABLES nom_table [READ | WRITE]; -- verrouiller
UNLOCK TABLES; -- déverrouillerÉcris la procédure sp_fiche_produit(IN p_id INT) : elle déclare les variables locales v_designation, v_pu, v_stock et v_valeur_stock (pu × stock), les remplit avec SELECT … INTO et SET, puis affiche une seule ligne « PC Portable HP : 10 en stock, valeur 74900.00 DH » avec CONCAT.
Avec des variables de session : (1) mets dans @prix_moyen le prix moyen des produits ; (2) affiche les produits dont le prix est supérieur à @prix_moyen ; (3) mets dans @nb_agadir le nombre de clients d'Agadir en appelant la fonction uf_nombre_clients_par_ville du chapitre 2.
Cette procédure doit compter les clients d'une ville. Pourquoi renvoie-t-elle toujours le nombre total de clients ? Corrige-la.
DELIMITER $$
CREATE PROCEDURE sp_compter(IN adresse VARCHAR(50))
BEGIN
SELECT COUNT(*) FROM clients WHERE adresse = adresse;
END $$
DELIMITER ;Ouvre deux onglets de requêtes dans Workbench (deux connexions). Dans le premier : LOCK TABLES produit WRITE;. Dans le second : SELECT * FROM produit; reste bloqué. Reviens au premier : UNLOCK TABLES; : le second affiche aussitôt le résultat.