MySQL
Routine memorizzate (procedure e funzioni)

Parametri

Osservazioni

Crea una funzione

La seguente (banale) funzione di esempio restituisce semplicemente il valore INT costante 12 .

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

La prima riga definisce a cosa deve essere modificato il carattere delimitatore ( DELIMITER || ), questo è necessario per essere impostato prima che venga creata una funzione, altrimenti se lo si lascia al suo valore predefinito ; poi il primo ; quello che si trova nella funzione corpo sarà preso come la fine CREATE , che di solito non è ciò che si desidera.

Dopo aver eseguito CREATE FUNCTION è necessario impostare il delimitatore sul valore predefinito di ; come si vede dopo il codice funzione nell'esempio precedente ( DELIMITER ; ).

L'esecuzione di questa funzione è la seguente:

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

Un esempio leggermente più complesso (ma ancora banale) prende un parametro e aggiunge una costante ad esso:

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 |
+-----------+

Notare l'uso di un argomento diverso per la direttiva DELIMITER . Puoi effettivamente usare qualsiasi sequenza di caratteri che non appare nel corpo CREATE , ma la solita pratica è usare un carattere non alfanumerico raddoppiato come \\ , || o $$ .

È buona norma modificare sempre il parametro prima e dopo una funzione, procedura o creazione o aggiornamento del trigger poiché alcune GUI non richiedono la modifica del delimitatore mentre le query in esecuzione tramite la riga di comando richiedono sempre il delimitatore da impostare.

Creare una procedura con una preparazione costruita

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 creazione della stored procedure mostra il wrapping con DELIMITER necessario in molti strumenti client.

Esempio di chiamata:

call displayNext100WithName(1,"questions_mysql");

Esempio di output con separatore %3B (punto e virgola):

Procedura memorizzata con i parametri 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 ( CALL ) la stored procedure:

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

Risultato:

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

Un parametro IN passa un valore in una procedura. La procedura potrebbe modificare il valore, ma la modifica non è visibile al chiamante al ritorno della procedura.

Un parametro OUT passa un valore dalla procedura al chiamante. Il suo valore iniziale è NULL all'interno della procedura e il suo valore è visibile al chiamante quando viene restituita la procedura.

Un parametro INOUT viene inizializzato dal chiamante, può essere modificato dalla procedura e qualsiasi modifica apportata dalla procedura è visibile al chiamante al ritorno della procedura.

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

Cursori

I cursori ti consentono di itterare i risultati della query uno per riga. DECLARE comando DECLARE viene utilizzato per avviare il cursore e associarlo a una query SQL specifica:

DECLARE student CURSOR FOR SELECT name FROM studend;

Diciamo che vendiamo prodotti di alcuni tipi. Vogliamo contare quanti prodotti di ciascun tipo esistono.

I nostri dati:

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

Possiamo raggiungere l'obiettivo usando la stored procedure usando il cursore:

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 ;

Quando puoi chiamare la procedura con:

CALL product_count();

Il risultato sarebbe nella tabella product_type_count :

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

Mentre quello è un buon esempio di CURSOR , notate come l'intero corpo della procedura può essere sostituito semplicemente

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

Questo funzionerà molto più velocemente.

Multiple ResultSets

A differenza di un'istruzione SELECT , una Stored Procedure restituisce più set di risultati. Richiede un codice diverso da utilizzare per raccogliere i risultati di una CALL in Perl, PHP, ecc.

(Hai bisogno di un codice specifico qui o altrove!)

Crea una funzione

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 ;

Il tipo_di dati RETURNS è qualsiasi tipo di dati MySQL.