MySQL
Rutinas almacenadas (procedimientos y funciones)

Parámetros

Observaciones

Crear una función

La siguiente función de ejemplo (trivial) simplemente devuelve el valor constante de INT 12 .

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

La primera línea define a qué se DELIMITER || carácter delimitador ( DELIMITER || ), esto se debe configurar antes de que se cree una función, de lo contrario, si se deja en su valor predeterminado ; entonces el primero ; que se encuentra en el cuerpo de la función se tomará como el final de la CREATE , que generalmente no es lo que se desea.

Una vez que se haya ejecutado CREATE FUNCTION , debe configurar el delimitador a su valor predeterminado de ; como se ve después del código de función en el ejemplo anterior ( DELIMITER ; ).

La ejecución de esta función es la siguiente:

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

Un ejemplo ligeramente más complejo (pero aún trivial) toma un parámetro y le agrega una 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 |
+-----------+

Tenga en cuenta el uso de un argumento diferente a la directiva DELIMITER . En realidad, puede usar cualquier secuencia de caracteres que no aparezca en el cuerpo de la sentencia CREATE , pero la práctica habitual es usar un carácter no alfanumérico duplicado, como \\ , || o $$ .

Es una buena práctica cambiar siempre el parámetro antes y después de la creación, actualización o activación de una función, procedimiento o disparador, ya que algunas GUI no requieren que el delimitador cambie, mientras que las consultas en ejecución a través de la línea de comandos siempre requieren que se establezca el delimitador.

Crear procedimiento con una preparación construida

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 creación del procedimiento almacenado muestra el ajuste con un DELIMITER necesario en muchas herramientas del cliente.

Ejemplo de llamada:

call displayNext100WithName(1,"questions_mysql");

Salida de muestra con separador %3B (punto y coma):

Procedimiento almacenado con parámetros 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 ;

Invoca ( LLAMAR ) el procedimiento almacenado:

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

Resultado:

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

Un parámetro IN pasa un valor a un procedimiento. El procedimiento puede modificar el valor, pero la modificación no es visible para la persona que llama cuando el procedimiento vuelve.

Un parámetro OUT pasa un valor del procedimiento a la persona que llama. Su valor inicial es NULL dentro del procedimiento, y su valor es visible para la persona que llama cuando el procedimiento vuelve.

Un parámetro INOUT es inicializado por la persona que llama, puede ser modificado por el procedimiento, y cualquier cambio realizado por el procedimiento es visible para la persona que llama cuando el procedimiento regresa.

Ref: http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

Cursores

Los cursores le permiten iterar los resultados de la consulta uno por línea. DECLARE comando DECLARE se usa para iniciar el cursor y asociarlo con una consulta SQL específica:

DECLARE student CURSOR FOR SELECT name FROM studend;

Digamos que vendemos productos de algunos tipos. Queremos contar cuántos productos de cada tipo existen.

Nuestros datos:

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');

Podemos lograr el objetivo usando el procedimiento almacenado usando el cursor:

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 ;

Cuando puede llamar al procedimiento con:

CALL product_count();

El resultado estaría en la tabla product_type_count :

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

Si bien ese es un buen ejemplo de un CURSOR , observe cómo todo el cuerpo del procedimiento puede ser reemplazado por solo

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

Esto se ejecutará mucho más rápido.

Conjuntos de resultados múltiples

A diferencia de una instrucción SELECT , un Stored Procedure devuelve varios conjuntos de resultados. El código requiere que se use un código diferente para recopilar los resultados de una CALL en Perl, PHP, etc.

(¡Necesitas un código específico aquí o en otra parte!)

Crear una función

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 ;

El RETURNS data_type es cualquier tipo de datos MySQL.