MySQL Avancé

Variables

En plus des variables de session qui commencent par @@, MySQL nous permet de stocker des variables, commençant par @. Les variables ne sont stockées en mémoire que pour la durée de la session.

SET @var := 'toto';
SET @'ma var' := 3;
SET @var := 3 + 1;

SELECT attr FROM ... WHERE ... INTO @var;
SELECT @var := attr, ... FROM ... WHERE ...;

Attention l'affectation se fait sur := et non pas sur =, qui teste une égalité booléenne. La variable stockée est réutilisable partout dans les requêtes (SELECT @var;).

Prepared Statements

Les "commandes préparées" servent à stocker non pas une valeur mais une requête. Il garde la requête qu'on lui donne en cache. Quand il appelle cette requête elle sera lancée plus rapidement car il aura déjà analysé et optimisé la syntaxe.

PREPARE nom FROM 'requete';
EXECUTE nom;

On peut également donner des arguments aux commandes préparées, en gardant la même structure de requête. Exemple :

PREPARE solde FROM 'SELECT solde FROM compte WHERE idCompte = ?';
SET @num := 37;
EXECUTE solde USING @num;

Utile quand une requête prends beaucoup de temps à s'éxécuter et que l'on doit l'éxécuter plusieurs fois à la suite. Par contre une commande préparée ne peut pas contenir plusieurs requêtes.

Procédures stockées

Permet de stocker une suite d'opérations, par exemple une transaction bancaire.

Avantages

Syntaxe

CREATE PROCEDURE nom (parametres)
    requête;
CALL nom(...);

Les paramètres sont au format [IN|OUT|INOUT] nom type, par défaut IN.

Exemple

Procédure solde qui prends en entrée un numéro de compte et affiche le solde du compte :

CREATE PROCEDURE solde (IN numcompte INT)
    SELECT solde
    FROM compte
    WHERE idCompte = numcompte;

Blocs de requêtes

DELIMITER //
CREATE PROCEDURE nom (parametres)
BEGIN
    requête1;
    requête2;
    ...
END; //
DELIMITER ;

DELIMITER permet de modifier le caractère de fin de requête ; pour qu'il évite d'arrêter trop tôt la suite de requêtes. On lui donne les caratères que l'on veut, ici //. DELIMITER ; permet de remetre le délimiteur normal.

Pour définir une variable qui ne servira que dans le bloc :

DECLARE nomvariable type;

Dans une procédure on peut utiliser toutes les fonctions de transactions. On peut également appeler une procédure, mais pas en créer.

Blocs de contrôle

IF test THEN ...;
ELSE ...;
END IF;

Exemple de la mort qui tue

Procédure virement qui prends en entrée le numéro du compte émetteur, le num du compte récepteur et le montant. La procédure vérifie que le solde est suffisant. Si oui : virement, si non : on affiche un message.

DELIMITER //
CREATE PROCEDURE virement (emetteur INT, recepteur INT, montant FLOAT)
BEGIN
    START TRANSACTION;
        DECLARE soldeemetteur INT;
        SELECT solde
            FROM compte
            WHERE idCompte = emetteur
            INTO soldeemetteur;

        IF (soldeemetteur < montant) THEN
            SELECT "Solde insuffisant pour effectuer le virement." AS message;
        ELSE
            BEGIN
                UPDATE compte SET solde = solde + montant WHERE idCompte = recepteur;
                UPDATE compte SET solde = solde - montant WHERE idCompte = emetteur;
                INSERT INTO ...;
                INSERT INTO ...;
            END;
        END IF;
    COMMIT;
END; //
DELIMITER ;

Triggers / Déclencheurs

L'inconvénient majeur des procédures est qu'elle doivent être lancée. Les triggers, eux, s'activent sur une action. Mais un trigger ne peut pas avoir de transactions et ne peux rien afficher.

CREATE TRIGGER nom [AFTER|BEFORE] [INSERT|UPDATE|DELETE] on table
    ...;

Un traitement BEFORE permet de vérifier la cohérence et peut donc encore empêcher l'action qui a déclanché le déclencheur.