Chapitre 19 Procédures stockées et fonctions

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)

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.

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.

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,'!')

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 ».

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

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

[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.

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.

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

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

SET variable = expression [,...]

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

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

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

DECLARE condition_name CONDITION FOR condition_value

condition_value:
    SQLSTATE [VALUE] sqlstate_value
  | mysql_error_code

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

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)

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

DECLARE cursor_name CURSOR FOR sql_statement

OPEN cursor_name

FETCH cursor_name

CLOSE cursor_name

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.

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

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

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

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

LEAVE label

ITERATE label

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

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

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)

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

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

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