Tutoriel vidéo MySQL : Programmation MySQL

Tout comme Oracle possède son langage PL/SQL et MS SQL Server son Transact-SQL, MySQL possède aussi un langage de programmation.

la programmation sous MySQL regroupe

les triggers
les procédures stockées
les fonctions

Les Procédures Stockées

Une procédure stockée est un petit programme stocké dans la base de données et appelable à partir d'un client comme on peut le faire pour une requête. Une procédure est executée par le serveur de base de données.

Tous les exemples de ce tuto ont été tapés en utilisant le client mySQL dans une console. Vous pouvez si vous le souhaitez, utiliser MySQL Workbench ou PhpMyadmin (sauf pour les triggers).

Lorsque l'on crée une procédure, un des premiers soucis à contourner et tout bête : comment puis-je écrire une instruction qui se termine par un point-virgule alors que ma procédure contient un point-virgule à chaque ligne ?

La réponse est : changeons le caractère délimiteur par un autre qui ne risque pas d'apparaitre dans nos intructions, prenons par exemple le pipe '|'

$: DELIMITER |

Créons notre procédure avec la commande CREATE PROCEDURE name ([param[,param ...]])
On peut utiliser des paramètres IN, OUT ou INOUT c'est à dire en Entrée, en Sortie et à la fois en Entrée et en Sortie.
Chaque paramètre est défini par son sens, son nom et son type.

Voici un premier exemple d'une procedure qui met à jour les prix en leur appliquant un coefficient

$: CREATE PROCEDURE maj_prix (IN coef FLOAT) 
      BEGIN
          UPDATE T1 SET PRICE = PRICE * coef;
      END
      |
$:

Notez le | final qui nous fait sortir de l'édition
Chaque bloc d'instructions doit être encadré par BEGIN et END comme en Pascal, mais peut être ignoré dans le cas d'une seule instruction.

Pour lancer notre procédure, on pense à remettre le délimiteur standard, c'est à dire le point-virgule.

On lance une procédure par une commande call en SQL

$: DELIMITER ;
$: CALL maj_prix(1.05);

en php avec PDO cela donnerait :

$pdo->exec("call maj_prix($coef)");

Utilisation d'un paramètre de retour

Pour affecter un résultat SQL à une variable, on utilise le mot clé INTO

$: DELIMITER |
$: CREATE PROCEDURE get_count (OUT nb INT) 
    BEGIN
        SELECT COUNT(*) INTO nb FROM T1;
    END
    |
$:

Pour récupérer le résultat, on doit initialiser la variable qui va recevoir le résultat

$: SET @n=0;
$: CALL get_count(@n);
$: SELECT @n;

Pour modifier une procédure, il faut la supprimer avec DROP puis la recréer, il n'existe pas de commande ALTER PROCEDURE

$: DROP PROCEDURE maproc;

Pour afficher le code d'une procédure

$: SHOW CREATE PROCEDURE maproc;

Pour voir les procédures existantes

$: SHOW PROCEDURE STATUS LIKE '%%'

Déclaration des variables

Les variables sont déclarées par le mot clé DECLARE et les types sont les types SQL de MySQL

On peut déclarer plusieurs variables du même type sur la même ligne

On peut fixer une valeur d'initialisation.

$: DECLARE myvar CHAR(10);
$: DECLARE i, j INT DEFAULT 0;

Les commentaires

/*
* une ligne
* deuxieme ligne
*/
-- une seule ligne (il y a un espace après les 2 tirets)

Affectation directe de variables

On utilise le mot clé SET

DECLARE x INT;
DECLARE nom VARCHAR(50);
SET x=10;
SET nom='toto';

Retouner des enregistrements

Si une procédure execute une requête SELECT, les enregistrements résultant sont retournés.

$: DELIMITER |
$: CREATE PROCEDURE mesenreg()
    SELECT * FROM matable;
    |

Du coup on peut l'appeler comme une requête SELECT mis à part le 'call'
Cela donne en php :

$stmt = $pdo->query("call mesenreg()");
$arrAll = $stmt->fetchAll();
foreach($arrAll as $row) {
    echo $row[1];
}

Notez qu'il n'est pas possible d'appeler la procédure à partir d'une autre Requête

SELECT * FROM mesenreg();  // Ne fonctionne pas, il faut plutot faire une Vue

Les Fonctions

Une fonction comme une procédure s'exécute sur le serveur, mais une fonction retourne un résultat et peut être utilisée directement dans une requête SQL .

Création de fonction

On utilise la commande CREATE FUNCTION name (params) RETURNS returnType

$: DELIMITER |
$: CREATE FUNCTION getlib (param_id INT) RETURNS CHAR(50)
BEGIN
    DECLARE lib CHAR(50);
    SELECT Libelle INTO lib FROM T1 WHERE id=param_id;
    RETURN lib;
END;
    |

en cas d'erreur 1418

$: set global log_bin_trust_routine_creators=1;

Utilisation d'une fonction stockée dans une requête SQL

$: SELECT getlib(id) FROM T WHERE ...

Pour la création de routines telles que procedures ou fonctions, il faut posséder le droit ALTER ROUTINE
Exemple de fonction qui arrondit un montant à 50 centimes prés

DELIMITER |
CREATE FUNCTION arrondi50(v DECIMAL(8,2)) RETURNS DECIMAL(8,2)
    RETURN ROUND((v * 2) + 0.49999)/2;
    |
DELIMITER ;

Conditions IF THEN ELSE

IF var = 2 THEN
    ...
ELSE
    ...
ELSEIF
    ...
END IF;

Conditions CASE

Suivant la valeur de la variable qui suit CASE, le programme va traiter tel ou tel cas

CASE var
WHEN 1 THEN ...;
WHEN 2 THEN ...;
ELSE  ...;  // autres cas
END CASE;

Boucles LOOP

LOOP
    ...
END LOOP

Si on souhaite sortir de la boucle on doit rajouter une étiquette. L'appel à LEAVE suivi de l'étiquette provoque la sortie de la boucle.

LOOP 
    ...
    IF myvar = 0 THEN LEAVE unlabel;
END LOOP unlabel;

Boucles REPEAT UNTIL

REPEAT 
    ...
UNTIL var = 5 END REPEAT;

Si on veut recommencer l'itération

un_label: REPEAT
        ...
    IF i = 3 THEN ITERATE un_label; END IF; 
UNTIL i < 100 END REPEAT un_label;

Boucles WHILE

WHILE i < 100 DO
    ...
END WHILE

Utilisation des curseurs (CURSOR)

Un CURSOR, c'est ce qui va nous permettre de parcourir un jeu d'enregistrements.
On doit commencer par déclarer le curseur et l'associer à une requête de type SELECT, celle qui va fournir les enregistrements.
Pour cela on doit le déclarer et lui donner un nom :

DECLARE mycursor CURSOR FOR SELECT id, nom FROM matable;

Avant de l'exécuter, il faut déclarer des variables qui récupéreront les valeurs des champs

DECLARE var_id INT;
DECLARE var_nom VARCHAR(50);

On doit ensuite ouvrir le curseur avec OPEN pour exécuter la requête.
A chaque boucle on 'FETCH' les valeurs dans les variables :

OPEN mycursor;

boucle: LOOP
    FETCH mycursor INTO var_id, var_nom;
    ...
    IF done THEN
      LEAVE boucle;
    END IF;
   END LOOP;

ensuite il faut fermer le curseur pour libérer les ressources

CLOSE mycursor;

Les Triggers

Les Triggers ou déclencheurs en français, sont des procédures attachées directementà un évênement d'une table, par exemple sur chaque insertion ou chaque suppression d'enregistrement.
Un Trigger est donc rattaché à une table et à un évênement, mais on doit aussi indiquer si notre code sera déclenché avant ou après l'évênement.

CREATE TRIGGER <nomtrigger> <action_time> <event> ON <table>

L' action_time précise si l'action a lieu avant (BEFORE) ou après (AFTER) l'évênement.
L' event c'est l'action sur laquelle on se rattache : INSERT, DELETE, UPDATE
Prenons un exemple cher à Grafikart, celui des messages et topics de forum :

$: CREATE TABLE topic (id_topic INT NOT NULL AUTO_INCREMENT, description VARCHAR(255), message_at DATETIME);
$: CREATE TABLE message (id_message INT NOT NULL AUTO_INCREMENT, id_topic INT, msg TEXT, created_at DATETIME DEFAULT CURRENT_TIMESTAMP);

Nous voulons qu'à chaque nouveau message, le champ message_at du topic soit mis à jour à la date courante.
Dans le trigger, on accède aux données de l'enregistrement à insérer avec le préfixe NEW

 DELIMITER |
 CREATE TRIGGER insMsg BEFORE INSERT ON message

    FOR EACH ROW
     BEGIN
        UPDATE topic SET message_at = NOW() WHERE id_topic = NEW.id_topic;
     END;
    |

Le FOR EACH ROW est obligatoire même si dans notre cas nous ne traitons qu'un seul enregistrement.
Supposons que nous voulions supprimer un message, la date du topic doit être mise à jour avec :

Soit la date de l'avant-dernier message s'il existe
Soit null s'il n'existe pas

DELIMITER |
CREATE TRIGGER delMsg BEFORE DELETE ON message
FOR EACH ROW
BEGIN
    DECLARE date_topic DATETIME;
    DECLARE date_av_dernier DATETIME;
    DECLARE id_av_dernier INT DEFAULT 0;
    /* on modifie le topic si seulement le message supprime est le dernier */
    SELECT message_at INTO date_topic FROM topic WHERE id_topic = OLD.id_topic;
    IF date_topic <= OLD.created_at THEN
        BEGIN
            /* on recherche l'avant dernier message du meme topic*/
            SELECT created_at, id_topic INTO date_av_dernier, id_av_dernier
            FROM message
            WHERE id_message <> OLD.id_message AND id_topic = OLD.id_topic ORDER BY Created_at DESC LIMIT 1;

            /* on met a jour la date dans le topic ou NULL si pas de message anterieur */
            IF id_av_dernier = 0 THEN
                UPDATE topic SET message_at = NULL WHERE id_topic = OLD.id_topic;
            ELSE
                UPDATE topic SET message_at = date_av_dernier WHERE id_topic = OLD.id_topic;
            END IF;
        END;
    END IF;
END;
|

Autre solution en plaçant le trigger après la suppression.
Dans ce cas le message est déja supprimé, on recherche donc la date du dernier message s'il existe.

DELIMITER |
CREATE TRIGGER delMsg AFTER DELETE ON message
FOR EACH ROW
BEGIN
    DECLARE date_topic DATETIME;
    DECLARE date_dernier DATETIME;
    DECLARE id_dernier INT DEFAULT 0;

    /* on modifie le topic si seulement le message supprime etait le dernier */
    SELECT message_at INTO date_topic FROM topic WHERE id_topic = OLD.id_topic;
    IF date_topic <= OLD.created_at THEN
        BEGIN
            /* on recherche le dernier message existant du meme topic*/
            SELECT created_at, id_topic INTO date_dernier, id_dernier
            FROM message
            WHERE id_topic = OLD.id_topic ORDER BY Created_at DESC LIMIT 1;

            /* on met a jour la date dans le topic ou NULL si pas de message anterieur */
            IF id_dernier = 0 THEN
                UPDATE topic SET message_at = NULL WHERE id_topic = OLD.id_topic;
            ELSE
                UPDATE topic SET message_at = date_av_dernier WHERE id_topic = OLD.id_topic;
            END IF;
        END;
    END IF;
END;
|

Utilisation de trigger pour gérer un compteur de messages

On suppose que la table topic contient le nombre de messages
Pour cela on rajoute un champ nb_messages initialisé à zéro.

$: CREATE TABLE topic (id_topic INT NOT NULL AUTO_INCREMENT, description VARCHAR(255), message_at DATETIME, nb_messages INT DEFAULT '0');

On crée un trigger sur l'ajout de message et un autre sur la suppression

CREATE TRIGGER cnt_add_msg AFTER INSERT ON message
FOR EACH ROW    
    UPDATE topic SET nb_messages = nb_messages + 1 WHERE id_topic = NEW.id_topic;
END;

On crée un trigger sur la suppression de message

CREATE TRIGGER cnt_del_msg AFTER DELETE ON message
FOR EACH ROW    
    UPDATE topic SET nb_messages = nb_messages - 1 WHERE id_topic = OLD.id_topic;
END;

Comportement avec les Auto-increment

Supposons que lorsque l'on crée un topic, on crée automatiquement un premier message qui reprend la description du topic

DELIMITER |
CREATE TRIGGER instopic AFTER INSERT ON topic
FOR EACH ROW
    INSERT INTO message (id_topic,  msg) VALUES (NEW.id_topic, NEW.description);
|

Que nous donne le lastInsertId, celui du message ou celui du topic ?
Réponse : celui du topic

$pdo->exec("INSERT INTO topic (description) VALUES (\"La vie des bêtes\");");
echo $pdo->lastInsertId();

Suppression en cascade

Lorsque l'on crée une liaison entre deux tables, on a la possiblité de définir une suppession en cascade
Cela signifie que si on supprime un topic, tous ses messages sont supprimés dans la foulée (sans avoir besoin de trigger).
Le problème c'est que la suppression dans la table mère désactive le trigger DELETE de la table fille !!!
Il faut choisir l'un ou l'autre.

Quelques commandes utiles

Voir les triggers en place

$: SHOW TRIGGERS LIKE '%';

Avec MySQL Workbench
On a la possibilité d'éditer un trigger existant.
Dans l'explorateur d'objets, faire un clic doit sur le nom de la table puis 'alter table', ensuite sélectionner l'onglet Trigger.
Le trigger sera automatiquement Droppé s'il existe puis recréé.

Voilà pour ce tutoriel qui je l'espère vous donnera des idées pour vos prochains développements.

Publié

Technologies utilisées

MySQL

Auteur :
Huggy

Proposer une correction

Tutoriel MySQL : Programmation MySQL