MySQL
Сохраненные процедуры (процедуры и функции)

параметры

замечания

Создать функцию

Следующая (тривиальная) примерная функция просто возвращает значение константы INT 12 .

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

Первая строка определяет, для чего должен быть изменен символ разделителя ( DELIMITER || ), это необходимо установить до того, как функция будет создана иначе, если оставить ее по умолчанию ; затем первый ; который находится в теле функции, будет приниматься за конец инструкции CREATE , которая обычно не является желаемой.

После запуска CREATE FUNCTION вы должны установить для разделителя значение по умолчанию ; как видно из кода функции в приведенном выше примере ( DELIMITER ; ).

Выполнение этой функции выполняется следующим образом:

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

Несколько более сложный (но все же тривиальный) пример принимает параметр и добавляет к нему константу:

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

Обратите внимание на использование другого аргумента в директиве DELIMITER . Фактически вы можете использовать любую последовательность символов, которая не появляется в CREATE , но обычной практикой является использование удвоенного не-алфавитно-цифрового символа, такого как \\ , || или $$ .

Рекомендуется всегда изменять параметр до и после создания или обновления функции, процедуры или триггера, поскольку некоторые графические интерфейсы не требуют изменения разделителя, тогда как выполнение запросов через командную строку всегда требует установки разделителя.

Создание процедуры с созданной подготовкой

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 ;

Создание хранимой процедуры показывает упаковку с DELIMITER, необходимой во многих клиентских инструментах.

Пример вызова:

call displayNext100WithName(1,"questions_mysql");

Выход образца с разделителем %3B (с запятой):

Сохраненная процедура с параметрами 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 ;

Вызывает ( CALL ) хранимую процедуру:

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

Результат:

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

Параметр IN передает значение в процедуру. Процедура может изменить значение, но модификация не отображается вызывающему, когда процедура возвращается.

Параметр OUT передает значение из процедуры обратно вызывающему абоненту. Его начальное значение равно NULL в пределах процедуры, и его значение отображается вызывающему, когда процедура возвращается.

Параметр INOUT инициализируется вызывающим абонентом, может быть изменен процедурой, и любое изменение, сделанное процедурой, отображается вызывающему абоненту при возврате процедуры.

Ссылка: http://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

курсоры

Курсоры позволяют повторять результаты запроса по очереди. Команда DECLARE используется для инициализации курсора и связывания его с конкретным SQL-запросом:

DECLARE student CURSOR FOR SELECT name FROM studend;

Предположим, мы продаем продукты некоторых типов. Мы хотим подсчитать, сколько продуктов каждого типа существует.

Наши данные:

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

Мы можем достичь цели с помощью хранимой процедуры с помощью курсора:

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 ;

Когда вы можете вызвать процедуру с помощью:

CALL product_count();

Результат будет в таблице product_type_count :

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

Хотя это хороший пример CURSOR , обратите внимание на то, как весь орган процедуры может быть заменен просто

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

Это будет работать намного быстрее.

Множественные ResultSets

В отличие от SELECT , Stored Procedure возвращает несколько наборов результатов. Требуется использовать другой код для сбора результатов CALL в Perl, PHP и т. Д.

(Нужен конкретный код здесь или в другом месте!)

Создать функцию

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_type - любой тип данных MySQL.