Aller au contenu | Aller au menu | Aller à la recherche


Procédures stockées, fonctions SQL, curseurs et triggers

Par PlaceOweb le dimanche, octobre 3 2010, 18:51 - SQL - Lien permanent

Procédures stockées (PROCEDURE) et fonctions (FUNCTION) en SQL, et bien ce n'est pas si simple à comprendre ! Et les fameux déclencheurs, les Triggers !

MySQL

Procédures stockées et fonctions

Une procédure stockées est un jeu de commandes SQL qui réside sur le serveur. Une fois qu'elle sont enregistrées, les clients n'ont pas besoin de soumettre chaque commande individuellement, mais peuvent les lancer d'un seul coup.

Les procédures stockées fournissent un gain de performances, car moins d'informations sont échangées entre le serveur et le client. En échange, cela augmente la charge du serveur, car ce dernier doit réaliser plus de travail. Souvent, il y a de nombreux clients, mais peut de serveurs.

Les procédures stockées permettent aussi l'utilisation de bibliothèques et de fonctions sur le serveur. Les langages de programmation modernes ont déjà intégré ce type de concept, et l'utilisation de ces langages de programmation externes reste valable et utile, en dehors de la base de données.

Quelques situations où les procédures stockées sont utiles :

  • Lorsque plusieurs applications clientes sont écrites dans différents langages sur différentes plates-formes, et utilisent le serveur comme point d'interaction.
  • Lorsque la sécurité est prioritaire. Les banques, par exemple, utilisent les procédures stockées pour toutes les opérations standards. Cela conduit à un environnement cohérent et sécurisé, car les procédures assurent que les opérations sont correctement faîtes et enregistrées. Dans une telle configuration, les applications et les utilisateurs n'ont aucun accès direct aux tables, mais passent par des procédures stockées pre-définies.

Les procédures stockées et les fonctions sont créées avec les commandes CREATE PROCEDURE et CREATE FUNCTION. Une procédure est appelée avec la commande CALL, et ne peut retourner de valeur que via les variables de retour. Les fonctions peuvent retourner une valeur scalaire, et être appelée depuis une commande, tout comme toute autre fonction. Les procédures stockées peuvent appeler une autre routine stockée. Une routine est une procédure stockée ou une fonction.

Des curseurs simples sont supportés dans les routines. La syntaxe est la même que dans le SQL intégré. Les curseurs sont actuellement assensible, sans scroll et en lecture seule. Les curseurs assensibles signifie que le curseur peut ou pas faire une copie de la table de résultat.

CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,DATA FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
 
  OPEN cur1;
  OPEN cur2;
 
  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
       ELSE
          INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;
 
  CLOSE cur1;
  CLOSE cur2;
END

Les procédures stockées peuvent traiter de multiples commandes SQL, prendre des paramètres et retourner des ResultSets.

Pour écrire vos procédures ou fonctions (surtout depuis des outils externes à la console MySQL, phpMyAdmin par exemple), n'oubliez pas changer le délimiteur par défaut ";" en "//" ou "$$" par exemple :

DELIMITER $$ 
 
CREATE PROCEDURE `procedureExemple` 
   ( 
      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 ;

Erreur de création, mise à jour de procédures stockées et fonctions :

DELIMITER //
DROP PROCEDURE IF EXISTS simpleproc//
CREATE PROCEDURE simpleproc (IN param1 INT)
BEGIN
  SELECT COUNT(*) INTO param1 FROM t1;
  RETURN param1;
END//
 
#1313 - RETURN IS only allowed IN a FUNCTION

Pour un exemple de fonction, regardez la fonction MySQL pour calculer la distance en mètres entre 2 points : get_distance_metres

Tips double tabs autocompletion : désactiver l'auto complétion pour le client MySQL en ligne de commande

Afin de faire des copier coller de vos procédures/fonctions sans être embêter par vos multiples tabulations qui initialement appelent la complétion en ligne de commandes MySQL, rajoutez l'option --no-auto-rehash lors de votre connexion :

# Se connecter à MySQL comme suit : 
mysql -u <user_name> -p --no-auto-rehash

Ressources

Extraits du bouquin : Livre MySQL 5: Installation, mise en œuvre, administration et programmation

Les procédures stockées et les fonctions font partie des nouveautés les plus importantes de la version 5 de MySQL. Leur nom génériques est le terme routine.

Une routine est un jeu de commandes SQL qui est enregistré sur le serveur MySQL. Cet ensemble est identifié par un nom et éventuellement des paramètres d'entrée/sortie.

Une procédure stockée permet de renvoyer un jeu d'enregistrement tel que le résultat d'une commande select par exemple alors qu'une fonction renvoie un scalaire.

characteristic:
   COMMENT 'string'
 | LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }

COMMENT 'string'

COMMENT permet de préciser un commentaire, par exemple les fonctionnalités de la procédure. Le commentaire est affiché via la commande SHOW CREATE PROCEDURE.

LANGUAGE SQL

Il n'est pas nécessaire de spécifier LANGUAGE SQL car c'est pour l'instant le seul langage disponible.

[NOT] DETERMINISTIC

Si une procédure est DETERMINISTIC, elle renvoie toujours le même résultat si les paramètre d'entrée sont les mêmes. Par exemple, une procédure stockée qui renvoi le nombre de lignes de commande d'une facture est DETERMINISTIC puisque le nombre de lignes de commandes n'évolue pas une fois la facture émise. Par contre, une procédure qui renvoi le montant moye, des 100 dernières factures est NOT DETERMINISTIC puisque malgré le paramètre d'entrée 100, suivant l'instant où nous exécutons la procédure, le résultat est différent. Ce paramètre, bien qu'existant, n'est actuellement pas prise en compte par le serveur. A terme, il servira à optimiser les requêtes, en particulier la gestion du cache. Par défaut si ce paramètre est omis, MySQL considère la procédure NOT DETERMINISCTIC.

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

Si ce n'est pas précisé, MySQL utilise CONTAINS SQL qui précise que la routine contient des requêtes SQL. READS SQL DATA permet de préciser qu'aucun accès en écriture n'est effectué par la routine, seulement en lecture. MODIFIES SQL DATA permet de préciser que la routine contient des requêtes en en écriture et NO SQL précise que la routine ne contient pas de requêtes SQL. Ces paramètres sont utilisés pour améliorer les performances.

SQL SECURITY { DEFINER | INVOKER }

SQL SECURITY permet de définir le cadr d'execution de la procédure stockée. Si DEFINER est précisé, la procédure sera exécutée avec les privilèges du créateur de la procédure et si INVOKER est précisé, elle sera alors exécutée avec les privilèges de l'utilisateur qui apelle la procédure. Ce point est important car avec l'option DEFINER, un utilisateur qui lance la routine peut accéder à des données alors qu'il ne possède pas les privilèges sur les tables qui contiennent ces données. Cette option est un moyen pratique de de permettre l'accès aux routines sans donner directement l'accès aux données.

Ensuite, penchez vous sur les CONDITION, HANDLER, CURSOR

Un HANDLER est un bloc d'instruction SQL exécuté lorsqu'une CONDITION est vérifiée suite à une exception générée parl e serveur MySQL.

Les curseurs sont principalement utilisés dans les procédures stockées, les déclencheurs et les scripts, où ils permettent aux autres instructions SQL d'accéder au contenu d'un jeu de résultats.

Imaginons que nous voulions effectuer un ensemble d'instructions SQL sur un même jeu d'enregistrement. L'idéal serait de pouvoir effectuer une boucle sur le jeu d'enregistrements source dans laquelle nous exécutions l'ensemble des instructions SQL. C'est la possibilité offerte par les curseurs.

Déclencheurs (Triggers)

Pour palier au bug MySQL InnoDB, j'ai du utiliser les triggers

SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE table_name='the_table_you_want';
SET @auto = 1234;
ALTER TABLE `the_table_you_want` AUTO_INCREMENT = @auto;

Et utiliser les Variables utilisateur

SET @variable = 'toto';
SET @variable = (SELECT MAX(`ID`) FROM `MA_TABLE`);
DROP TRIGGER create_auto_inc;
 
DELIMITER //
CREATE TRIGGER create_auto_inc BEFORE INSERT ON `MA_TABLE_IN`
FOR EACH ROW 
BEGIN
 
SET @nbr = (SELECT COUNT(*) FROM `MA_TABLE_ID_SAVE`);
IF @nbr <> 1 THEN
  SET @id_auto = (SELECT MAX(`ID`) FROM `MA_TABLE_ID_SAVE`);
  DELETE FROM `MA_TABLE_ID_SAVE`;
  SET @id_auto = IFNULL(@id_auto+1, (SELECT MAX(`ID`) FROM `MA_TABLE_EXTERNE`)+1);  -- Attention si la table MA_TABLE_EXTERNE est vide erreur !
  INSERT INTO `MA_TABLE_ID_SAVE` (`ID`) VALUES (@id_auto);
ELSE
  SET @id_auto = (SELECT `ID`+1 FROM `MA_TABLE_ID_SAVE`);
  UPDATE `MA_TABLE_ID_SAVE` SET `ID` = (@id_auto);
END IF;
 
SET NEW.ID = @id_auto;  -- Remplacement de la valeur ID insérée à la base
END
//
DELIMITER ;
 
TRUNCATE TABLE `MA_TABLE_IN`;
INSERT INTO `MA_TABLE_IN` (`ID`) VALUES (0);

Ressources

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

Fil des commentaires de ce billet