MySQL
Routines stockées (procédures et fonctions)

Paramètres

Remarques

Créer une fonction

La fonction exemple (triviale) suivante renvoie simplement la valeur INT constante 12 .

DELIMITER ||
CREATE FUNCTION functionname()
RETURNS INT
BEGIN
    RETURN 12;
END;
||
DELIMITER ;

La première ligne définit ce que le caractère de délimiteur ( DELIMITER || ) doit être changé, il faut le définir avant de créer une fonction, sinon si elle est laissée à sa valeur par défaut ; puis le premier ; ce qui se trouve dans le corps de la fonction sera considéré comme la fin de l'instruction CREATE , ce qui n'est généralement pas ce que l'on souhaite.

Après l'exécution de CREATE FUNCTION , vous devez redéfinir le délimiteur sur sa valeur par défaut ; comme on le voit après le code de fonction dans l'exemple ci-dessus ( DELIMITER ; ).

Exécution cette fonction est la suivante:

SELECT functionname();
+----------------+
| functionname() |
+----------------+
|             12 |
+----------------+

Un exemple légèrement plus complexe (mais néanmoins trivial) prend un paramètre et lui ajoute une constante:

DELIMITER $$
CREATE FUNCTION add_2 ( my_arg INT )
  RETURNS INT
BEGIN
  RETURN (my_arg + 2);
END;
$$
DELIMITER ;

SELECT add_2(12);
+-----------+
| add_2(12) |
+-----------+
|        14 |
+-----------+

Notez l'utilisation d'un argument différent pour la directive DELIMITER . Vous pouvez réellement utiliser n'importe quelle séquence de caractères n'apparaissant pas dans le corps de l'instruction CREATE , mais la pratique habituelle consiste à utiliser un caractère non alphanumérique doublé tel que \\ , || ou $$ .

Il est recommandé de toujours modifier le paramètre avant et après une fonction, une procédure ou une création ou une mise à jour de déclencheur car certaines interfaces graphiques ne nécessitent pas de modification du délimiteur alors que l'exécution de requêtes via la ligne de commande nécessite toujours la définition du délimiteur.

Créer une procédure avec une préparation construite

DROP PROCEDURE if exists displayNext100WithName;
DELIMITER $$
CREATE PROCEDURE displayNext100WithName
(    nStart int,
    tblName varchar(100)
)
BEGIN
    DECLARE thesql varchar(500); -- holds the constructed sql string to execute

    -- expands the sizing of the output buffer to accomodate the output (Max value is at least 4GB)
    SET session group_concat_max_len = 4096; -- prevents group_concat from barfing with error 1160 or whatever it is

    SET @thesql=CONCAT("select group_concat(qid order by qid SEPARATOR '%3B') as nums ","from (    select qid from ");
    SET @thesql=CONCAT(@thesql,tblName," where qid>? order by qid limit 100 )xDerived");
    PREPARE stmt1 FROM @thesql; -- create a statement object from the construct sql string to execute
    SET @p1 = nStart; -- transfers parameter passed into a User Variable compatible with the below EXECUTE
    EXECUTE stmt1 USING @p1;
     
    DEALLOCATE PREPARE stmt1; -- deallocate the statement object when finished
END$$
DELIMITER ;

La création de la procédure stockée montre l'encapsulation avec un DELIMITER nécessaire dans de nombreux outils clients.

Exemple d'appel:

call displayNext100WithName(1,"questions_mysql");

Exemple de sortie avec le séparateur %3B (point-virgule):

Procédure stockée avec les paramètres IN, OUT, INOUT

DELIMITER $$
 
DROP PROCEDURE IF EXISTS sp_nested_loop$$
CREATE PROCEDURE sp_nested_loop(IN i INT, IN j INT, OUT x INT, OUT y INT, INOUT z INT)
BEGIN
    DECLARE a INTEGER DEFAULT 0;
    DECLARE b INTEGER DEFAULT 0;
    DECLARE c INTEGER DEFAULT 0;
    WHILE a < i DO
        WHILE b < j DO
            SET c = c + 1;
            SET b = b + 1;
        END WHILE;    
        SET a = a + 1;
        SET b = 0;        
    END WHILE;
    SET x = a, y = c;
    SET z = x + y + z;
END $$
DELIMITER ;

Invoque ( CALL ) la procédure stockée:

SET @z = 30;
call sp_nested_loop(10, 20, @x, @y, @z);
SELECT @x, @y, @z;

Résultat:

+------+------+------+
|  @x  |  @y  |  @z  |
+------+------+------+
|  10  |  200 |  240 |
+------+------+------+

Un paramètre IN transmet une valeur à une procédure. La procédure peut modifier la valeur, mais la modification n'est pas visible pour l'appelant au retour de la procédure.

Un paramètre OUT renvoie une valeur de la procédure à l'appelant. Sa valeur initiale est NULL dans la procédure et sa valeur est visible pour l'appelant lors du retour de la procédure.

Un paramètre INOUT est initialisé par l'appelant, peut être modifié par la procédure et toute modification apportée par la procédure est visible par l'appelant au retour de la procédure.

Réf: http://dev.mysql.com/doc/refman/5.7/fr/create-procedure.html

Des curseurs

Les curseurs vous permettent d'itérer les résultats de la requête une par une. DECLARE commande DECLARE est utilisée pour initialiser le curseur et l'associer à une requête SQL spécifique:

DECLARE student CURSOR FOR SELECT name FROM studend;

Disons que nous vendons des produits de certains types. Nous voulons compter le nombre de produits de chaque type.

Nos données:

CREATE TABLE product
(
  id   INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(50)      NOT NULL,
  name VARCHAR(255)     NOT NULL

);
CREATE TABLE product_type
(
  name VARCHAR(50) NOT NULL PRIMARY KEY
);
CREATE TABLE product_type_count
(
  type  VARCHAR(50)      NOT NULL PRIMARY KEY,
  count INT(10) UNSIGNED NOT NULL DEFAULT 0
);

INSERT INTO product_type (name) VALUES
  ('dress'),
  ('food');

INSERT INTO product (type, name) VALUES
  ('dress', 'T-shirt'),
  ('dress', 'Trousers'),
  ('food', 'Apple'),
  ('food', 'Tomatoes'),
  ('food', 'Meat');

Nous pouvons atteindre l'objectif en utilisant la procédure stockée à l'aide du curseur:

DELIMITER //
DROP PROCEDURE IF EXISTS product_count;
CREATE PROCEDURE product_count()
  BEGIN
    DECLARE p_type VARCHAR(255);
    DECLARE p_count INT(10) UNSIGNED;
    DECLARE done INT DEFAULT 0;
    DECLARE product CURSOR FOR
      SELECT
        type,
        COUNT(*)
      FROM product
      GROUP BY type;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    TRUNCATE product_type;

    OPEN product;
    
    REPEAT
      FETCH product
      INTO p_type, p_count;
      IF NOT done
      THEN
        INSERT INTO product_type_count
        SET
          type  = p_type,
          count = p_count;
      END IF;
    UNTIL done
    END REPEAT;
    
    CLOSE product;
  END //
DELIMITER ;

Quand vous pouvez appeler la procédure avec:

CALL product_count();

Le résultat serait dans la table product_type_count :

type   | count
----------------
dress  |   2
food   |   3

Bien que ce soit un bon exemple de CURSOR , notez comment le corps entier de la procédure peut être remplacé par un simple

INSERT INTO product_type_count
        (type, count)
    SELECT type, COUNT(*)
        FROM product
        GROUP BY type;

Cela va courir beaucoup plus vite.

Plusieurs ResultSets

Contrairement à une SELECT , une Stored Procedure renvoie plusieurs jeux de résultats. Le code différent requis pour être utilisé pour rassembler les résultats d'un CALL en Perl, PHP, etc.

(Besoin d'un code spécifique ici ou ailleurs!)

Créer une fonction

DELIMITER $$
CREATE
    DEFINER=`db_username`@`hostname_or_IP`
    FUNCTION `function_name`(optional_param data_type(length_if_applicable))
    RETURNS data_type
BEGIN
    /*
    SQL Statements goes here
    */
END$$
DELIMITER ;

Le type de données RETURNS est tout type de données MySQL.