Chapitre 19 Procédures stockées et fonctions

Table des matières

19.1 Procédures stockées et tables de droits
19.2 Syntaxe des procédures stockées
19.2.1 CREATE PROCEDURE et CREATE FUNCTION
19.2.2 ALTER PROCEDURE et ALTER FUNCTION
19.2.3 DROP PROCEDURE et DROP FUNCTION
19.2.4 SHOW CREATE PROCEDURE et SHOW CREATE FUNCTION
19.2.5 SHOW PROCEDURE STATUS et SHOW FUNCTION STATUS
19.2.6 CALL
19.2.7 La commande composée BEGIN ... END
19.2.8 La commande DECLARE
19.2.9 Les variables dans les procédures stockées
19.2.10 Conditions et gestionnaires
19.2.11 Curseurs
19.2.12 Instructions de contrôle

Les procédures stockées et les fonctions sont de nouvelles fonctionnalités de MySQL version 5.0. 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 :

MySQL suit la syntaxe de la norme SQL:2003 pour les procédures stockées, qui est aussi utilisée par IBM dans DB2. La compatibilité avec les autres langages de procédures stockées, comme PL/SQL ou T-SQL sera ajouté ultérieurement.

L'implémentation des procédures stockées de MySQL est en cours de développement. Toutes les syntaxes décrites dans ce chapitre sont supportées, et les limitations ou extensions sont documentés lorsque c'est nécessaire.

Les procédures stockées requièrent la table proc dans la base mysql. Cette table est créée durant l'installation de MySQL 5.0. Si vous passez à MySQL 5.0 depuis une ancienne version, assurez-vous de bien mettre à jour vos tables de droits, et que la table proc existe. See Section 2.6.7, « Mise à jour des tables de droits ».

19.1 Procédures stockées et tables de droits

Les procédures stockées requièrent la table proc dans la base mysql. Cette table est créée durant la procédure d'installation de MySQL 5.0. Si vous faîtes la mise à jour vers MySQL 5.0 depuis une ancienne installation, pensez à mettre à jour les tables de droits pour que la table proc existe. See Section 2.6.7, « Mise à jour des tables de droits ».

Depuis MySQL 5.0.3, le système de droits a été modifié pour prendre en compte les procédures stockées comme ceci :

  • Le droit de CREATE ROUTINE est nécessaire pour créer une procédure stockée.

  • Le droit de ALTER ROUTINE est nécessaire pour pouvoir modifier ou effacer une procédure stockée. Le droit est fourni automatiquement au créateur d'une routine.

  • Le droit de EXECUTE est requis pour exécuter une procédure stockée. Cependant, ce droit est fourni automatiquement au créateur d'une routine. De plus, la caractéristique par défaut SQL SECURITY est définie (DEFINER), ce qui fait que les utilisateurs qui ont accès à une base de données associée à une routine ont le droit d'exécutier la routine.

19.2 Syntaxe des procédures stockées

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.

Actuellement, MySQL préserve uniquement le contexte de la base par défaut. C'est à dire que si vous utilisez la commande USE dbname dans une procédure, le contexte initial sera restauré à la fin de la procédure.

Une routine hérite de la base de données par défaut de l'utilisateur appelant, et donc, il est recommande de commencer les routines avec la commande USE dbname, ou de spécifier explicitement les tables et les bases de données qui sont utilisées : i.e. base.table.

MySQL supporte une extension très pratique qui permet d'utiliser des expressions régulières dans les commandes SELECT (c'est à dire, sans utiliser de curseur ou de variable locales). Le résultat d'une telle requête est simplement envoyé directement au client.

Plusieurs commandes SELECT génèreront plusieurs jeux de résultats, et le client doit utiliser la bibliothèques qui supporte les résultats multiples. Cela signifie que vous devez utiliser une bibliothèque cliente de version 4.1 ou plus récente.

La section suivante décrit la syntaxe à utiliser pour créer, modifier, détruire et appeler des procédures stockées.

19.2.1 CREATE PROCEDURE et CREATE FUNCTION

CREATE PROCEDURE sp_name ([parameter[,...]])
[characteristic ...] routine_body

CREATE FUNCTION sp_name ([parameter[,...]])
[RETURNS type]
[characteristic ...] routine_body

paramètre :
  [ IN | OUT | INOUT ] param_name type

type :
  Any valid MySQL data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT string

routine_body :
  Commande(s) SQL valide(s)

La clause RETURNS peut être spécifiée uniquement pour une FUNCTION. Elle sert à indiquer le type de retour de la fonction, et le corps de la fonction doit contenir une instruction RETURN value.

La liste de paramètre entre parenthèses est obligatoire. S'il n'y a pas de paramètre, une liste vide sous la forme () doit être utilisée. Chaque paramètre est un paramètre de type IN par défaut. Pour spécifier un autre type, utilisez les mots OUT ou INOUT avant le nom du paramètre. Spécifier IN, OUT ou INOUT n'est valable que pour une PROCEDURE.

L'instruction CREATE FUNCTION est utilisée dans les anciennes versions de MySQL pour créer des UDF (User Defined Functions, fonctions utilisateur). See Section 27.2, « Ajouter des fonctions à MySQL ». Les UDF sont toujours supportées, même avec la présence des procédures stockées. Une UDF peut être considérée comme une fonction stockée. Cependant, notez que les UDF et les fonctions stockées partagent le même espace de noms.

Un framework pour développer des procédures stockées externes sera prochainement présenté. Il permettra d'écrire des procédures stockées dans d'autres langages que SQL. Il est probable que l'un des premiers langages supportés sera PHP, car le moteur PHP est compact, compatible avec les threads et peut être facilement intégré. Comme ce framework sera public, il est probable que bien d'autres langages soient supportés.

Une fonction est considérée comme ``déterministe'' si elle retourne toujours le même résultat pour les mêmes paramètres d'entrée. Sinon, elle est considérée comme ``non déterministe''. L'optimiseur peut utiliser cette propriété. Actuellement, l'attribut DETERMINISTIC est accepté, mais il n'est pas encore utilisé.

L'attribut SQL SECURITY peut être utilisé pour spécifier si la routine doit être exécutée avec les droits de l'utilisateur qui l'a créé ou avec ceux de celui qui appelle la fonction. La valeur par défaut est DEFINER. Cette fonctionnalité est nouvelle en SQL:2003.

MySQL n'utilise pas le droit GRANT EXECUTE. Pour le moment, si une procédure p1() utilise la table t1, l'appelant doit avoir les droits sur la table t1 afin que la procédure p1() puisse réussir.

MySQL stocke la configuration SQL_MODE en effet au moment de la création de la procédure, et l'utilisera toujours lors de l'exécution de la procédure.

La clause COMMENT est une extension MySQL, et peut servir à décrire la procédure stockée. Cette information est affichée par les commandes SHOW CREATE PROCEDURE et SHOW CREATE FUNCTION.

MySQL permet aux routines de contenir des commandes DDL, telle que CREATE et DROP, et des transactions SQL, comme COMMIT. Ce n'est pas obligatoire selon le standard et c'est donc une extension spécifique.

Note : Actuellement, les fonctions stockées FUNCTIONs ne doivent pas contenir de références aux tables. Notez que cela inclut aussi les commandes SET, mais pas les commandes SELECT. Cette limitation sera supprimée aussitôt que possible.

L'exemple suivant est une procédure stockée simple, qui utilise un paramètre de sortie OUT. L'exemple utilise la commande delimiter du client mysql pour modifier le délimiteur de commande avant de définir la procédure. Cela permet au délimiteur ; d'être utilisé dans le corps de la procédure, plutôt que d'être interprété par le client mysql.

mysql> delimiter |

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> CALL simpleproc(@a)|
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a|
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

L'exemple suivant est une fonction qui prend un paramètre, effectue une opération avec une fonction SQL, et retourne le résultat :

mysql> delimiter |

mysql> CREATE FUNCTION bonjour (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Bonjour, ',s,'!');
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT bonjour('le monde')|
+---------------------+
| bonjour('le monde') |
+---------------------+
| Bonjour, le monde!  |
+---------------------+
1 row in set (0.00 sec)

19.2.2 ALTER PROCEDURE et ALTER FUNCTION

ALTER PROCEDURE | FUNCTION sp_name [characteristic ...]

characteristic:
    NAME newname
  | SQL SECURITY {DEFINER | INVOKER}
  | COMMENT string

Cette commande peut être utilisée pour renommer une procédure stockée ou une fonction, et pour en changer les caractéristiques. Plusieurs modifications peut être spécifiées dans une commande ALTER PROCEDURE et ALTER FUNCTION.

19.2.3 DROP PROCEDURE et DROP FUNCTION

DROP PROCEDURE | FUNCTION [IF EXISTS] sp_name

Cette commande sert à effacer une procédure stockée ou une fonction. C'est à dire que la routine spécifiée est supprimée du serveur.

La clause IF EXISTS est une extension de MySQL. Elle permet d'éviter une erreur si la routine n'existe pas. Une alerte est alors produite, et peut être lue avec SHOW WARNINGS.

19.2.4 SHOW CREATE PROCEDURE et SHOW CREATE FUNCTION

SHOW CREATE PROCEDURE | FUNCTION sp_name

Cette commande est une extension MySQL. Similaire à SHOW CREATE TABLE, elle retourne la chaîne exacte qui permet de recréer la procédure.

mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
       Function: hello
       sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')

19.2.5 SHOW PROCEDURE STATUS et SHOW FUNCTION STATUS

SHOW PROCEDURE | FUNCTION STATUS [LIKE pattern]

Cette commande est une extension MySQL. Elle retourne les caractéristiques de la routine, comme son nom, type, créateur, ainsi que les dates de création et de modification. Si le paramètre pattern n'est pas fourni, les informations sur toutes les procédures stockées ou fonctions sont listées, en fonction de la commande exacte que vous utilisez.

mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
           Db: test
         Name: hello
         Type: FUNCTION
      Definer: testuser@localhost
     Modified: 2004-08-03 15:29:37
      Created: 2004-08-03 15:29:37
Security_type: DEFINER
      Comment:

Vous pouvez aussi vous reporter aux informations sur les procédures stockées dans la table ROUTINES, de la base INFORMATION_SCHEMA. See Section 22.1.14, « La table INFORMATION_SCHEMA ROUTINES ».

19.2.6 CALL

CALL sp_name([parameter[,...]])

La commande CALL sert à appeler une routine qui a été définie précédemment avec CREATE PROCEDURE.

19.2.7 La commande composée BEGIN ... END

[begin_label:] BEGIN
 statement(s)
END [end_label]

Les routines peuvent contenir des commandes multiples, en utilisant le bloc de commande BEGIN ... END.

begin_label et end_label doivent être identiques, s'ils sont spécifiés.

Notez bien que la clause optionnelle [NOT] ATOMIC n'est pas encore supportée. Cela signifie qu'il n'y a pas de début de transaction au début du bloc, et que la clause BEGIN sera utilisé sans affecté la transaction courante.

Les commandes multiples requièrent un client capable d'envoyer des requêtes contenant le caractère ‘;’. C'est géré dans le client en ligne de commande mysql, avec la commande delimiter. En changeant le caractère de fin de requête ‘;’ pour le remplacer par ‘|’ permet à ‘;’ d'être utilisé dans le corps de la routine.

19.2.8 La commande DECLARE

La commande DECLARE sert à définir différents objets locaux dans une routine : variables locales (see Section 19.2.9, « Les variables dans les procédures stockées »), conditions et gestionnaires (see Section 19.2.10, « Conditions et gestionnaires »), curseurs (see Section 19.2.11, « Curseurs »). Les commandes SIGNAL et RESIGNAL ne sont pas supportées pour le moment.

DECLARE ne peut être utilisé dans un bloc BEGIN ... END, et doit intervenir au début de la routine, avant tout autre commande.

19.2.9 Les variables dans les procédures stockées

Vous pouvez déclarer et utiliser des variables dans une routine.

19.2.9.1 DECLARE : déclarer une variable locale

DECLARE var_name[,...] type [DEFAULT value]

Cette commande sert à déclarer des variables locales. Le scope des variables est le bloc BEGIN ... END.

19.2.9.2 Commande d'affectation de variables SET

SET variable = expression [,...]

La commande SET des procédures stockées est une version étendue de la commande SET classique. Les variables référencées peuvent être déclarées dans le contexte de la routine ou comme variables globales.

La commande SET des procédures stockées est implémentée comme une sous-partie de la syntaxe SET. Cela permet la syntaxe étendue SET a=x, b=y, ..., où plusieurs types de variables (locales, serveur, globale ou session) sont mélangées. Cela permet aussi la combinaison de variables locales et d'option système qui n'ont de sens qu'au niveau global du serveur : dans ce cas, les options sont acceptées mais ignorées.

19.2.9.3 Syntaxe de SELECT ... INTO

SELECT column[,...] INTO variable[,...] table_expression

Cette syntaxe de SELECT stocke les colonnes sélectionnées dans des variables. Par conséquent, une seule ligne doit être lue. Cette commande est aussi extrêmement utile lorsqu'elle est utilisée avec des curseurs.

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

19.2.10 Conditions et gestionnaires

Certaines conditions peuvent exiger des gestions spécifiques. Ces conditions peuvent être liées à des erreurs, ou au déroulement de la routine.

19.2.10.1 DECLARE une condition

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

Cette commande spécifie les conditions qui demandent une gestion spécifique. Elle associe un nom avec une erreur spécifique. Ce nom peut être utilisé ultérieurement dans une commande DECLARE HANDLER. See Section 19.2.10.2, « DECLARE un gestionnaire ».

En plus des valeurs SQLSTATE, les codes d'erreur MySQL sont aussi supportés.

19.2.10.2 DECLARE un gestionnaire

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement

handler_type:
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
  | mysql_error_code

Cette commande spécifie les gestionnaires qui vont traiter une ou plusieurs conditions. Si une de ces conditions survient, le gestionnaire associé est appelé.

Pour un gestionnaire CONTINUE, l'exécution de la routine courante continue après l'exécution du gestionnaire. Pour un gestionnaire EXIT, l'exécution de la routine est terminée. Le gestionnaire UNDO n'est pas encore supporté. Actuellement, UNDO se comporte comme CONTINUE.

  • SQLWARNING est un raccourci pour toutes les codes SQLSTATE qui commencent par 01.

  • NOT FOUND est un raccourci pour toutes les codes SQLSTATE qui commencent par 02.

  • EXCEPTION est un raccourci pour toutes les codes SQLSTATE qui ne sont pas représenté par SQLWARNING ou NOT FOUND.

En plus des valeurs SQLSTATE, les codes d'erreur MySQL sont aussi supportés.

Par exemple :

mysql> CREATE TABLE test.t (s1 int,primary key (s1));
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter |

mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    ->   DECLARE CONTINUE HANDLER FOR '23000' SET @x2 = 1;
    ->   set @x = 1;
    ->   INSERT INTO test.t VALUES (1);
    ->   set @x = 2;
    ->   INSERT INTO test.t VALUES (1);
    ->   SET @x = 3;
    -> END;
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> CALL handlerdemo()|
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x|
    +------+
    | @x   |
    +------+
    | 3    |
    +------+
    1 row in set (0.00 sec)

Notez que @x vaut 3, ce qui signifie que MySQL a exécuté la procédure jusqu'à la fin. Si la ligne DECLARE CONTINUE HANDLER FOR '23000' SET @x2 = 1; était absente, MySQL aurait pris le chemin par défaut (EXIT) après l'échec du second INSERT, dû à la contrainte de PRIMARY KEY, et SELECT @x aurait retourné 2.

19.2.11 Curseurs

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.

Par exemple :

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

19.2.11.1 Déclaration des curseurs

DECLARE cursor_name CURSOR FOR sql_statement

Plusieurs curseurs peuvent être définit dans une routine, mais chacun doit avoir un nom distinct.

19.2.11.2 Commande de curseur OPEN

OPEN cursor_name

Cette commande ouvre un curseur déclaré précédemment.

19.2.11.3 Commande de curseur FETCH

FETCH cursor_name

Cette commande lit la prochaine ligne (si elle existe), en utilisant un curseur ouvert, et avance le pointeur de curseur.

19.2.11.4 Commande de curseur CLOSE

CLOSE cursor_name

Cette commande clôt le curseur précédemment ouvert.

19.2.12 Instructions de contrôle

Les instructions IF, CASE, LOOP, WHILE, ITERATE et LEAVE sont toutes supportées.

Ces instructions peuvent contenir des commandes simples, ou des blocs de commandes BEGIN ... END. Les instructions peuvent être imbriquées.

Les boucles FOR ne sont pas supportées actuellement.

19.2.12.1 Commande IF

IF search_condition THEN statement(s)
[ELSEIF search_condition THEN statement(s)]
...
[ELSE statement(s)]
END IF

IF implémente une instruction de condition simple. Si search_condition est vrai, la commande SQL correspondante est exécutée. Si search_condition est faux, la commande dans la clause ELSE est exécutée.

Notez aussi qu'il y a une fonction IF(). See Section 12.2, « Les fonctions de contrôle ».

19.2.12.2 Commande CASE

CASE case_value
    WHEN when_value THEN statement
    [WHEN when_value THEN statement ...]
    [ELSE statement]
END CASE

ou :

CASE
    WHEN search_condition THEN statement
    [WHEN search_condition THEN statement ...]
    [ELSE statement]
END CASE

CASE implémente une structure conditionnelle complexe. Si un des conditions search_condition est vraie, la commande SQL correspondante est exécutée. Si aucune condition n'est vérifiée, la commande SQL de la clause ELSE est exécuté.

Note : la syntaxe de la commande CASE à l'intérieure d'une procédure stockée diffère légèrement de l'expression SQL CASE. La commande CASE ne peut pas avoir de clause ELSE NULL, et l'instruction se termine avec END CASE au lieu de END. See Section 12.2, « Les fonctions de contrôle ».

19.2.12.3 Commande LOOP

[begin_label:] LOOP
  statement(s)
END LOOP [end_label]

LOOP implémente une boucle, permettant l'exécution répétée d'un groupe de commande. Les commandes à l'intérieure de la boucle sont exécutée jusqu'à ce que la boucle se termine, généralement lorsqu'elle atteint la commande LEAVE.

begin_label et end_label doivent être identiques, si les deux sont spécifiés.

19.2.12.4 Commande LEAVE

LEAVE label

Cette commande sert à sortir d'une instruction de contrôle.

19.2.12.5 Commande ITERATE

ITERATE label

ITERATE ne peut être utilisée qu'à l'intérieur d'une boucle LOOP, REPEAT ou WHILE. ITERATE signifie ``exécute encore une fois la boucle.''

Par exemple :

CREATE PROCEDURE doiterate(p1 INT)
BEGIN
  label1: LOOP
    SET p1 = p1 + 1;
    IF p1 < 10 THEN ITERATE label1; END IF;
    LEAVE label1;
  END LOOP label1;
  SET @x = p1;
END

19.2.12.6 Commande REPEAT

[begin_label:] REPEAT
  statement(s)
UNTIL search_condition
END REPEAT [end_label]

Les commandese à l'intérieur d'une commande REPEAT sont répétées jusqu'à ce que la condition search_condition soit vraie.

begin_label et end_label doivent être identiques, s'ils sont fournis.

Par exemple :

mysql> delimiter |

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> |
Query OK, 0 rows affected (0.00 sec)

mysql> CALL dorepeat(1000)|
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x|
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

19.2.12.7 Syntaxe WHILE

[begin_label:] WHILE search_condition DO
  statement(s)
END WHILE [end_label]

Les commandes dans l'instruction WHILE sont répétées tant que la condition search_condition est vraie.

begin_label et end_label doivent être identiques, s'ils sont spécifiés.

Par exemple :

CREATE PROCEDURE dowhile()
BEGIN
  DECLARE v1 INT DEFAULT 5;

  WHILE v1 > 0 DO
    ...
    SET v1 = v1 - 1;
  END WHILE;
END