MySQL
Lagrade rutiner (procedurer och funktioner)

parametrar

Anmärkningar

Skapa en funktion

Följande (triviala) exempelfunktion returnerar helt enkelt det konstanta INT värdet 12 .

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

Den första raden definierar vad avgränsningstecknet ( DELIMITER || ) ska ändras till, detta behövs för att ställas in innan en funktion skapas annars om den lämnas som standard ; sedan den första ; som finns i funktionskroppen kommer att tas som slutet på CREATE uttalandet, vilket vanligtvis inte är vad som önskas.

När CREATE FUNCTION har körts bör du ställa in avgränsaren till dess standard av ; som ses efter funktionskoden i exemplet ovan ( DELIMITER ; ).

Utför denna funktion är följande:

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

Ett lite mer komplext (men fortfarande trivialt) exempel tar en parameter och lägger till en konstant till det:

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

Observera användningen av ett annat argument än DELIMITER direktivet. Du kan faktiskt använda valfri teckensekvens som inte visas i CREATE uttalarkroppen, men den vanliga praxisen är att använda ett fördubblat icke-alfanumeriskt tecken som \\ , || eller $$ .

Det är god praxis att alltid ändra parametern före och efter en funktion, procedur eller utlösa skapande eller uppdatering eftersom vissa GUI inte kräver att avgränsaren ändras medan körfrågor via kommandoraden alltid kräver att avgränsaren ska ställas in.

Skapa procedur med en konstruerad förberedelse

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 ;

Skapandet av den lagrade proceduren visar inslagning med en DELIMITER som krävs i många klientverktyg.

Exempel:

call displayNext100WithName(1,"questions_mysql");

Provutgång med %3B (semi-colon) separator:

Lagrad procedur med parametrarna 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 ;

Åkallar ( CALL ) den lagrade proceduren:

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

Resultat:

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

En IN parameter överför ett värde till en procedur. Proceduren kan ändra värdet, men ändringen är inte synlig för den som ringer när proceduren återgår.

En OUT parameter överför ett värde från proceduren tillbaka till den som ringer. Dess initialvärde är NULL inom proceduren, och dess värde är synligt för den som ringer när proceduren återgår.

En INOUT parameter initialiseras av den som ringer, kan ändras genom proceduren och alla ändringar som görs av proceduren är synliga för den som ringer när proceduren återgår.

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

markörer

Med markörerna kan du upprätta resultat från frågan en för rad. DECLARE kommando används för att initiera markören och associera den med en specifik SQL-fråga:

DECLARE student CURSOR FOR SELECT name FROM studend;

Låt oss säga att vi säljer produkter av vissa typer. Vi vill räkna hur många produkter av varje typ som finns.

Våra data:

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

Vi kan uppnå målet med lagrad procedur med markören:

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 ;

När du kan ringa procedur med:

CALL product_count();

Resultatet skulle vara i tabellen product_type_count :

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

Även om det är ett bra exempel på en CURSOR , märker du hur hela proceduren kan ersättas med bara

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

Detta kommer att köras mycket snabbare.

Flera resultatuppsättningar

Till skillnad från ett SELECT uttalande returnerar en Stored Procedure flera resultatuppsättningar. Det kräver olika kod för att samla resultaten av en CALL i Perl, PHP, etc.

(Behöver specifik kod här eller någon annanstans!)

Skapa en funktion

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 ;

RETURNS data_typ är vilken MySQL-datatyp som helst.