MySQL
Gespeicherte Routinen (Prozeduren und Funktionen)

Parameter

Bemerkungen

Erstellen Sie eine Funktion

Die folgende (triviale) Beispielfunktion gibt einfach den konstanten INT Wert 12 .

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

In der ersten Zeile wird festgelegt, in was das Trennzeichen ( DELIMITER || ) geändert werden soll. Dies muss festgelegt werden, bevor eine Funktion erstellt wird. Andernfalls, wenn die Standardeinstellung DELIMITER || wird ; dann der erste ; Dies wird im Funktionshauptteil als Ende der CREATE Anweisung betrachtet, was normalerweise nicht erwünscht ist.

Nachdem die CREATE FUNCTION ausgeführt wurde, sollten Sie das Trennzeichen auf den Standardwert von setzen ; wie im obigen Beispiel nach dem Funktionscode ( DELIMITER ; ).

Ausführung dieser Funktion ist wie folgt:

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

Ein etwas komplexeres (aber immer noch triviales) Beispiel nimmt einen Parameter und fügt ihm eine Konstante hinzu:

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

Beachten Sie die Verwendung eines anderen Arguments als die DELIMITER Direktive. Sie können tatsächlich eine beliebige Zeichenfolge verwenden, die nicht im Hauptteil der CREATE Anweisung enthalten ist. In der Regel wird jedoch ein doppeltes, nicht alphanumerisches Zeichen wie \\ , || oder $$ .

Es ist empfehlenswert, den Parameter immer vor und nach einer Funktion, Prozedur oder Triggererstellung oder -aktualisierung zu ändern, da für einige GUI keine Änderungen des Trennzeichens erforderlich sind, während für Abfragen über die Befehlszeile immer das Setzen des Trennzeichens erforderlich ist.

Prozedur mit einer konstruierten Vorbereitung erstellen

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 ;

Die Erstellung der gespeicherten Prozedur zeigt das Umschließen mit einem DELIMITER, der in vielen Client-Tools erforderlich ist.

Aufrufbeispiel:

call displayNext100WithName(1,"questions_mysql");

Beispielausgabe mit dem Trennzeichen %3B (Semikolon):

Gespeicherte Prozedur mit den Parametern 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 ;

Ruft die gespeicherte Prozedur auf ( CALL ):

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

Ergebnis:

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

Ein IN Parameter übergibt einen Wert an eine Prozedur. Die Prozedur kann den Wert ändern, die Änderung ist jedoch für den Aufrufer nicht sichtbar, wenn die Prozedur zurückkehrt.

Ein OUT Parameter übergibt einen Wert aus der Prozedur an den Aufrufer. Sein Anfangswert ist NULL innerhalb der Prozedur und sein Wert ist für den Aufrufer sichtbar, wenn die Prozedur zurückkehrt.

Ein INOUT Parameter wird vom Aufrufer initialisiert und kann von der Prozedur geändert werden. Jede durch die Prozedur vorgenommene Änderung ist für den Aufrufer sichtbar, wenn die Prozedur zurückkehrt.

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

Cursor

Mithilfe von Cursorn können Sie die Abfrageergebnisse einer nach dem anderen durchlaufen. DECLARE Befehl DECLARE wird verwendet, um den Cursor zu DECLARE und einer bestimmten SQL-Abfrage DECLARE :

DECLARE student CURSOR FOR SELECT name FROM studend;

Angenommen, wir verkaufen Produkte einiger Arten. Wir möchten zählen, wie viele Produkte von jedem Typ vorhanden sind.

Unsere Daten:

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

Wir können das Ziel mithilfe der gespeicherten Prozedur mit dem Cursor erreichen:

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 ;

Wann können Sie die Prozedur aufrufen mit:

CALL product_count();

Ergebnis wäre in der Tabelle product_type_count :

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

Während dies ein gutes Beispiel für einen CURSOR , beachten Sie, wie der gesamte Körper des Verfahrens durch nur ersetzt werden kann

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

Das wird viel schneller laufen.

Mehrere ResultSets

Im Gegensatz zu einer SELECT Anweisung gibt eine Stored Procedure mehrere Ergebnismengen zurück. Dies erfordert, dass ein anderer Code verwendet wird, um die Ergebnisse eines CALL in Perl, PHP usw. zu erfassen.

(Benötigen Sie hier oder anderswo speziellen Code!)

Erstellen Sie eine 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 ;

Der RETURNS-Datentyp ist ein beliebiger MySQL-Datentyp.