MySQL
Zapisane procedury (procedury i funkcje)

Parametry

Uwagi

Utwórz funkcję

Poniższa (trywialna) przykładowa funkcja po prostu zwraca stałą wartość INT 12 .

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

Pierwszy wiersz określa, na jaki znak separatora ( DELIMITER || ) ma zostać zmieniony, należy to ustawić przed utworzeniem funkcji, jeśli pozostawi ją domyślną ; potem pierwszy ; znalezione w treści funkcji będą traktowane jako koniec instrukcji CREATE , co zwykle nie jest pożądane.

Po uruchomieniu funkcji CREATE FUNCTION powinieneś ustawić domyślny ogranicznik ; jak widać po kodzie funkcji w powyższym przykładzie ( DELIMITER ; ).

Wykonanie tej funkcji jest następujące:

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

Nieco bardziej złożony (ale wciąż trywialny) przykład przyjmuje parametr i dodaje do niego stałą:

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

Zwróć uwagę na użycie innego argumentu niż dyrektywa DELIMITER . W rzeczywistości można użyć dowolnej sekwencji znaków, która nie pojawia się w treści instrukcji CREATE , ale zwykłą praktyką jest stosowanie podwójnego znaku niealfanumerycznego, takiego jak \\ , || lub $$ .

Dobrą praktyką jest zawsze zmiana parametru przed i po utworzeniu funkcji, procedury lub wyzwalacza lub aktualizacji, ponieważ niektóre GUI nie wymagają zmiany ogranicznika, podczas gdy uruchamianie zapytań za pomocą wiersza poleceń zawsze wymaga ustawienia ogranicznika.

Utwórz procedurę za pomocą przygotowanego przygotowania

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 ;

Tworzenie procedury składowanej pokazuje opakowanie z DELIMITER niezbędnym w wielu narzędziach klienta.

Przykład wywołania:

call displayNext100WithName(1,"questions_mysql");

Wyjściowe próbki z separatorem %3B (średnik):

Procedura składowana z parametrami 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 ;

Wywołuje ( CALL ) procedurę przechowywaną:

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

Wynik:

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

Parametr IN przekazuje wartość do procedury. Procedura może zmodyfikować wartość, ale modyfikacja nie jest widoczna dla dzwoniącego, gdy procedura powróci.

Parametr OUT przekazuje wartość z procedury z powrotem do programu wywołującego. Jego wartość początkowa wynosi NULL w ramach procedury, a jej wartość jest widoczna dla dzwoniącego, gdy procedura powróci.

Parametr INOUT jest inicjowany przez wywołującego, może być modyfikowany przez procedurę, a wszelkie zmiany wprowadzone przez procedurę są widoczne dla wywołującego po powrocie procedury.

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

Kursory

Kursory umożliwiają iterację wyników zapytania jeden po wierszu. Polecenie DECLARE służy do inicjowania kursora i powiązania go z określonym zapytaniem SQL:

DECLARE student CURSOR FOR SELECT name FROM studend;

Załóżmy, że sprzedajemy produkty niektórych typów. Chcemy policzyć, ile istnieje produktów każdego typu.

Nasze dane:

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

Możemy osiągnąć cel za pomocą procedury składowanej za pomocą kursora:

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 ;

Kiedy możesz wywołać procedurę z:

CALL product_count();

Wynik będzie w tabeli product_type_count :

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

Chociaż jest to dobry przykład CURSOR , zwróć uwagę, w jaki sposób całą procedurę można zastąpić przez just

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

To będzie działać znacznie szybciej.

Wiele zestawów wyników

W przeciwieństwie do instrukcji SELECT Stored Procedure zwraca wiele zestawów wyników. Wymaga użycia innego kodu do zbierania wyników CALL w Perlu, PHP itp.

(Potrzebujesz określonego kodu tutaj lub gdzie indziej!)

Utwórz funkcję

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 ;

Typ danych RETURNS jest dowolnym typem danych MySQL.