Utilisation avancée des procédures stockées MySQL

D'abord un récapitulatif des bases pour ceux qui n'auraient pas lu mon article original. Les procédures stockées sont des bouts de code SQL stockés sur le serveur de bases de données qui peuvent être appelés par leurs noms depuis les clients. Les procédures stockées peuvent traitées de multiples commandes SQL, prendre des paramètres et retourner des DataSets. Les procédures stockées sont présentes dans d'autres systèmes de bases de données depuis plusieurs années jusqu'à ce que récemment cette fonctionnalité soit accessible dans MySQL. Le format d'une requête SQL pour créer une procédure stockée ressemble à ceci :

DELIMITER $$ 

CREATE PROCEDURE `sdpExampleProcedure` 
   ( 
      paramètre 1 VARCHAR(255), 
      paramètre 2 INTEGER 
   ) 
   BEGIN 
      /* 
      Requêtes SQL séparées par un point-virgule (;)
      Utilisant paramètre 1 et paramètre 2      */ 
   END$$ 

DELIMITER ;

Notez la commande DELIMITER au début et à la fin qui change la manière de séparer les requêtes de MySQL. Ensuite pour appeler la procédure, vous pouvez utiliser la commande suivante :

CALL `sdpExampleProcedure`('testdata', '11');

Les procédures stockées peuvent être utilisées pour grouper les commandes SQL et ajouter une logique dans vos états SQL, tout en réduisant la quantité de données transférées depuis le serveur de bases de données et votre application. Par exemple, en administrant des sessions dans une application PHP. Si vous voulez mettre à jour un enregistrement session quand une requête est faite pour une session existante, ou créer une nouvelle session en tout vous pourriez faire tout cela avec des contrôles dans une procédure stockée. Cela devrait normalement consister à utiliser une commande SELECT pour récupérer toutes les données voulues depuis le serveur de données en rapport avec l'id de session donné, ensuite en programmant votre code pour exécuter une commande INSERT ou UPDATE qui dépend du nombre de lignes retournées. Avec les procédures stockées vous pouvez inclure tout ceci dans une seule commande SQL simple qui retourne simplement vrai quand une session est créée, et faux lorsque une session est mise à jour.

En addition des paramètres définis qui sont passés durant l'appel, il est possible de définir des variables sur lesquelles travailler durant l'exécution. Pour faire ça vous devez utiliser la commande DECLARE, faisant état du nom et du type de la variable. Dans l'exemple suivant, je définis trois variables appelées « session_count », « session_id » et « session_content », de type INTEGER, VARCHAR (de taille 32) et TEXT respectivement.

DECLARE session_count INTEGER; 
DECLARE session_id VARCHAR(32); 
DECLARE session_content TEXT;

Maintenant que vous avez déclaré vos variables, vous pouvez configurer leur valeur en utilisant la commande SET. Une fois que ces variables ont une valeur, vous pouvez les utiliser plus tard dans votre procédure.

SET session_count = 1; 
SET session_id = '699d571326815e40b8e1ae99af04563c'; 
SET session_content = 'Du contenu de session ici';

Si vous connaissez la valeur de vos variables au moment de les déclarer, vous pouvez utiliser la commande DEFAULT pour les configurer. Ci-dessous j'ai condensé les six commandes en trois utilisant cette technique :

DECLARE session_count INTEGER DEFAULT 1; 
DECLARE session_id VARCHAR(32) DEFAULT '699d571326815e40b8e1ae99af04563c'; 
DECLARE session_content TEXT DEFAULT ''Du contenu de session ici;

Une utilisation excellente des variables dans vos procédures est d'utiliser le résultat d'une commande SELECT pour configurer la valeur d'une variable existante. Dans d'autres systèmes de bases de données, ceci est vraiment simple, cependant dans MySQL bien que ce soit simple, la syntaxe est quelque peu inattendue.

Dans le but de transmettre une valeur depuis une requête SELECT vers une variable, il y a une ligne supplémentaire que nous devons ajouter à la requête SELECT juste avant la définition de la table dans laquelle les données seront extraites. La commande INTO met les données dans une variable définie. Voici un exemple pour illustrer son utilisation :

DECLARE session_count INTEGER; 

SELECT COUNT(*) 
INTO session_count 
FROM `tblSessions`;

La variable session_count contient maintenant la valeur renvoyée par la requête SELECT qui compte le nombre de lignes dans la table des sessions. Il est aussi possible de mettre les valeurs dans de multiples variables comme dans l'exemple suivant :

DECLARE user VARCHAR(15); 
DECLARE pass VARCHAR(32); 

SELECT `strUserName`, `strPassword` 
INTO user, pass 
FROM `tblUsers` 
LIMIT 0, 1;

Maintenant que nous avons configuré des variables et leur avons assigné des valeurs, nous pouvons contrôler le flux de notre procédure pour décider de ce que nous allons faire par la suite. MySQL supporte une flopée de contrôleurs de flux que vous allez utiliser comme vous pourrez le voir dans d'autres langages de programmation.

V-A. La commande IF▲

IF session_count > 0 THEN
   SELECT 1;
ELSE
   SELECT 0;
END IF;

V-B. La commande CASE▲

DECLARE catchme INTEGER DEFAULT 10; 

CASE catchme 
   WHEN 2 THEN SET catchme = catchme + 2; 
   WHEN 5 THEN SET catchme = catchme + 5; 
   ELSE 
      SET catchme = 50; 
END CASE

V-C. La boucle WHILE▲

DECLARE iter INTEGER DEFAULT 0; 

iterwhile: WHILE iter < 9 DO 
   SET iter = iter + 1; 
END WHILE iterwhile;

V-D. La boucle LOOP▲

DECLARE iter INTEGER DEFAULT 0; 

iterloop: LOOP 
   IF iter < 9 THEN 
      SET iter = iter + 1; 
   ELSE 
      LEAVE iterloop; 
   END IF; 
END LOOP iterloop;

V-E. La boucle REPEAT▲

DECLARE iter INTEGER DEFAULT 0; 

iterrepeat: REPEAT 
   SET iter = iter + 1; 
UNTIL iter < 9 
END REPEAT iterrepeat;

V-F. Les commandes LEAVE et ITERATE▲

DECLARE iter INTEGER DEFAULT 0; 

testloop: LOOP 
   IF iter = 10 THEN 
      SET iter = iter + 12; 
      ITERATE testloop; 
   END IF; 
   IF iter > 126 THEN 
      LEAVE testloop; 
   END IF; 
   SET iter = iter + 1; 
END LOOP testloop;

Un mix de ces commandes peut vraiment ajouter de la puissance à vos codes SQL. Pas seulement, car elles peuvent réduire le trafic réseau, mais parce que vous pouvez réduire le nombre d'appels à votre base de données significativement, rendant les choses un peu plus organisées. Si vous commencez seulement à utiliser ces techniques et avez des difficultés avec les erreurs de syntaxe SQL, veillez à bien vérifier vos commandes individuellement avant de les mettre ensemble dans une procédure. Gardez un œil sur le point-virgule aussi, il peut causer une belle somme de problèmes quand il n'est pas utilisé au bon endroit donc assurez-vous de pouvoir différencier une commande d'une autre.

Vous pouvez aussi aller voir mes autres traductions.