MySQL
Opgeslagen routines (procedures en functies)

parameters

Opmerkingen

Creëer een functie

De volgende (triviale) voorbeeldfunctie retourneert gewoon de constante INT waarde 12 .

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

De eerste regel definieert waarnaar het scheidingsteken ( DELIMITER || ) moet worden gewijzigd, dit moet worden ingesteld voordat een functie wordt gemaakt, anders als deze standaard wordt ingesteld ; dan de eerste ; die wordt gevonden in het functielichaam, wordt beschouwd als het einde van de instructie CREATE , wat meestal niet is wat gewenst is.

Nadat de CREATE FUNCTION is uitgevoerd, moet u het scheidingsteken terugzetten op de standaardwaarde van ; zoals te zien is na de functiecode in het bovenstaande voorbeeld ( DELIMITER ; ).

De uitvoering van deze functie is als volgt:

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

Een iets complexer (maar toch triviaal) voorbeeld neemt een parameter en voegt er een constante aan toe:

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

Let op het gebruik van een ander argument dan de DELIMITER richtlijn. U kunt eigenlijk elke tekenreeks gebruiken die niet in de hoofdtekst CREATE verschijnt, maar de gebruikelijke praktijk is om een dubbel niet-alfanumeriek teken te gebruiken, zoals \\ , || of $$ .

Het is een goede gewoonte om de parameter altijd voor en na een functie, procedure of trigger-creatie of update te wijzigen, omdat sommige GUI's niet vereisen dat het scheidingsteken verandert, terwijl het uitvoeren van query's via de opdrachtregel altijd vereist dat het scheidingsteken wordt ingesteld.

Maak een procedure met een geconstrueerde voorbereiding

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 ;

Bij het maken van de opgeslagen procedure wordt ingepakt met een DELIMITER die nodig is in veel clienttools.

Bellen voorbeeld:

call displayNext100WithName(1,"questions_mysql");

Voorbeelduitvoer met scheidingsteken %3B (puntkomma):

Opgeslagen procedure met IN, UIT, INOUT parameters

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 ;

Roept ( CALL ) de opgeslagen procedure op:

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

Resultaat:

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

Een IN parameter geeft een waarde door aan een procedure. De procedure kan de waarde wijzigen, maar de wijziging is niet zichtbaar voor de beller wanneer de procedure terugkeert.

Een OUT parameter geeft een waarde uit de procedure terug aan de beller. De beginwaarde is NULL binnen de procedure en de waarde is zichtbaar voor de beller wanneer de procedure terugkeert.

Een INOUT parameter wordt geïnitialiseerd door de beller, kan worden gewijzigd door de procedure en elke wijziging die door de procedure wordt aangebracht, is zichtbaar voor de beller wanneer de procedure terugkeert.

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

cursors

Met cursors kunt u de resultaten van de zoekopdracht één voor regel herhalen. DECLARE opdracht wordt gebruikt om de cursor te initiëren en te koppelen aan een specifieke SQL-query:

DECLARE student CURSOR FOR SELECT name FROM studend;

Laten we zeggen dat we sommige soorten producten verkopen. We willen tellen hoeveel producten van elk type er zijn.

Onze gegevens:

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

We kunnen het doel bereiken met behulp van de opgeslagen procedure met behulp van de 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 ;

Wanneer u de procedure kunt bellen met:

CALL product_count();

Het resultaat zou in de tabel product_type_count :

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

Hoewel dat een goed voorbeeld is van een CURSOR , merk je op hoe de hele procedure kan worden vervangen door gewoon

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

Dit gaat een stuk sneller.

Meerdere resultatensets

In tegenstelling tot een SELECT instructie, retourneert een Stored Procedure meerdere resultatensets. Voor het verzamelen van de resultaten van een CALL in Perl, PHP, enz. Is een andere code vereist.

(Specifieke code hier of elders nodig!)

Creëer een functie

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 ;

Het RETURNS-gegevenstype is elk MySQL-gegevenstype.