Главная > MySQL > Хранимый код MySQL: Процедуры и Функции

Хранимый код MySQL: Процедуры и Функции

3 апреля 2009 Написать комментарий К комментариям

Хранимые процедуры в частях:
Часть 1. Переменные.
Часть 2. Prepared Statements.
Часть 3. Синтаксис хранимого кода
Часть 4. Функции и Процедуры
Часть 5. Триггеры

Рассмотрим как объявляются функции и процедуры, как они работают, какие характеристики имеют.

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

CREATE
   [DEFINER = { user | CURRENT_USER }]
   PROCEDURE sp_name ([sp_parameter[,...]])
   [characteristic ...]
   routine_body


DEFINER
Необязательное предложение DEFINER определяет логин MySQL, который нужно использовать при проверке привилегий доступа для хранимого кода, которые имеют характеристику SQL SECURITY DEFINER (смотри characteristic). Предложение DEFINER было добавлено в MySQL 5.0.20.
Если дано значение user, это должно быть логином MySQL в формате ‘user_name’@'host_name’ (тот же самый формат используется в инструкции GRANT). Параметры user_name и host_name обязательны. CURRENT_USER также может быть дан как CURRENT_USER().

По умолчанию DEFINER=CURRENT_USER.

Если мы определили предложение DEFINER, мы не можем устанавливать значение к любому логину (только к нашему собственному), если мы не имеем привилегию SUPER. Эти правила определяют допустимые значения пользователя DEFINER:

  • Если мы НЕ имеем привилегию SUPER, единственное допустимое значение user: наш собственный логин, определенный буквально или используя CURRENT_USER. Мы не можем устанавливать DEFINER к некоторому другому логину.
  • Если мы таки имеем привилегию SUPER, мы можем определять любой синтаксически допустимый логин. Если он фактически не существует, будет сгенерировано предупреждение.

Хотя возможно создать хранимый код с несуществующим значением DEFINER, ошибка происходит, если хранимый код выполняется с привилегиями DEFINER, но сам DEFINER не существует во время выполнения.

sp_name
Название хранимого кода. Символы допускаются такие же как и у переменных.


sp_parameter

[IN | OUT | INOUT] param_name type

Параметр IN передает значение в процедуру. Процедура может изменять значение, но модификация видна только самой процедуре, то есть вне процедуры она остаётся неизменной. Параметр OUT передает значение из процедуры во вне, то есть процедура придаёт значение параметру, значение которго может использоватся вне процедуры, после завершения процедуры. Начальное значение внутри процедуры NULL. Параметр INOUT передает значение в процедуру, как и IN, но процедура может изменять значение, которое будет видно и вне прицедуры, как OUT. Стоит заметить что всё это верно только для PROCEDURE, параметры FUNCTION всегда расцениваются как параметры IN.
param_name – название параметра, а type – его любой допустимый тип данных MySQL.


characteristic

LANGUAGE SQL | [NOT] DETERMINISTIC
   | {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
   | SQL SECURITY {DEFINER | INVOKER}
   | COMMENT 'string'

Характеристики DETERMINISTIC и NOT DETERMINISTIC указывают, производит ли функция всегда тот же самый результат для входных данных. Значение по умолчанию: NOT DETERMINISTIC, если никакая характеристика не дана.
Характеристики CONTAINS SQL, NO SQL, READS SQL DATA и MODIFIES SQL DATA обеспечивают информацию относительно того, читает ли функция или записывает данные. NO SQL или READS SQL DATA указывают, что функция не изменяет данные. Значение по умолчанию: CONTAINS SQL, если никакая характеристика не дана.
Характеристика SQL SECURITY может применяться для определения, должна ли процедура выполняться с использованием привилегий пользователя, создающего эту процедуру, или привилегий пользователя, ее вызывающего. Значение по умолчанию – DEFINER.
Конструкция COMMENT является расширением MySQL и может использоваться для описания хранимой процедуры. Такая информация отображается операторами SHOW CREATE PROCEDURE И SHOW CREATE FUNCTION.
По умолчению характеристика имеет следующие значение:

NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

routine_body
Состоит из допустимой инструкций хранимого кода SQL, которые мы рассмотрели в предыдущей главе. Это может быть простая инструкция типа SELECT или INSERT , либо это может быть составная инструкция, использующая BEGIN и END. Составные инструкции могут содержать объявления, циклы и другие инструкции управляющей структуры. Некоторые инструкции не допускаются в хранимых процедурах.

Вызов процедуры

CALL sp_name([parameter[,...]])

Инструкция CALL вызывает процедуру, которая была определена предварительно, с помощью команды CREATE PROCEDURE.
Чтобы вернуть значение из процедуры, использующей параметр OUT или INOUT, необходимо передать параметр посредством переменной пользователя, и затем можно использовать новое значение переменной после возврата из процедуры. Если вызывать процедуру изнутри другой сохраненной процедуры или функции, можно также передавать стандартный параметр или локальную стандартную переменную как параметр IN или INOUT. Для параметра INOUT инициализируется значение перед его передачей процедуре. Следующая процедура имеет параметр OUT, который процедура устанавливает в текущую (актуальную) версию сервера, и значение INOUT, которое процедура увеличивает:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
    BEGIN
      # Set value of OUT parameter
      SELECT VERSION() INTO ver_param;
      # Increment value of INOUT parameter
      SET incr_param = incr_param + 1;
    END;

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;

+------------+------------+
| @version   | @increment |
+------------+------------+
| 5.0.25-log | 11         |
+------------+------------+

Пример хранимой процедуры:

CREATE
DEFINER=`root`@`localhost`
PROCEDURE curdemo(INOUT dm INT)
    NOT DETERMINISTIC
    SQL SECURITY INVOKER
    COMMENT 'Пример'
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE a CHAR(16);
  DECLARE b,c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
  OPEN cur1;
  OPEN cur2;
  REPEAT
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
   dm = dm + 1;
    IF NOT done THEN
       IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
          ELSE INSERT INTO test.t3 VALUES (a,c);
       END IF;
    END IF;
  UNTIL done END REPEAT;
  SELECT "done";
  CLOSE cur1;
  CLOSE cur2;
END
# ...
SET @dm = 5;
CALL curdemo(@dm); # выведет 'done'
SELECT @dm; # выведет 6

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

CREATE
   [DEFINER = { user | CURRENT_USER }]
   FUNCTION func_name ([func_parameter[,...]])
   RETURNS type
   [characteristic ...]
   routine_body

DEFINER
Предложение DEFINER аналогично DEFINER в процедуре.
sp_name
Название хранимого кода. Символы допускаются такие же как и у переменных.

sp_parameter

[IN] param_name type

В отличии от процедуры у функции параметры всегда принимаются как IN. IN здесь действует аналогично как у процедуры. param_name – название параметра, а type – его любой допустимый тип данных MySQL.

RETURNS
Предложение RETURNS указывает какой тип данных возвращает функция. type – любой допустимый тип данных MySQL. Возможность возвращать значения есть только у функции, но в отличии от процедуры, функция не может возвращать выборки (например ‘SELECT 1′, в функции нельзя сделать, но не запрещается использовать SELECT для заполнения переменных).

characteristic
characteristic аналогична процедурной.

routine_body
routine_body аналогично процедурной за исключением того что там можно, даже нужно, использовать инструкцию RETURN variable, возвещающая значение.

Пример функции:

Если а < b возвращает 1, иначе 0
CREATE FUNCTION eq(IN a INT, IN b INT) RETURNS TINYINT(1)
  NOT DETERMINISTIC
  SQL SECURITY INVOKER
COMMENT 'Пример'
BEGIN
  IF a < b
    THEN RETURN 1;
    ELSE RETURN 0;
  END IF;
END
# или проще
CREATE FUNCTION eq(IN a INT, IN b INT) RETURNS TINYINT(1)
BEGIN
  IF a < b
    THEN RETURN 1;
    ELSE RETURN 0;
  END IF;
END
# ...
SELECT eq(1,3); # выведет 1

ALTER PROCEDURE и ALTER FUNCTION

ALTER {PROCEDURE | FUNCTION} sp_name
          [characteristic ...]

characteristic:
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

Эта инструкция используется, чтобы изменить характеристики сохраненной процедуры или функции. Изменить аргументы или тело функции и процедуры нельзя. По этому используйте конструкцию

DROP {PROCEDURE | FUNCTION} IF EXISTS `routine`$$

Информация о хранимом коде, в том числе и body, хранится в `INFORMATION_SCHEMA`.`ROUTINES`.

DROP PROCEDURE и DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

Эта инструкция используется, чтобы удалить сохраненную процедуру или функцию. То есть определенная подпрограмма будет удалена с сервера.
Предложение IF EXISTS является расширением MySQL. Это предотвращает ошибку, если процедура или функция не существует.

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

Если стандартное имя совпадает с именем встроенной функции SQL, мы должны использовать пробел между именем и следующей круглой скобкой при определении подпрограммы или происходит синтаксическая ошибка. Это также верно, когда мы вызываем функцию или процедуру позже. По этой причине, предлагаю избегать многократно использовать имена существующих функций SQL для ваших функций или процедур.

Список параметров, включенный внутри круглых скобок, должен всегда присутствовать. Если не имеется никаких параметров, должен использоваться пустой список параметров ().

Каждый параметр может быть объявлен, чтобы использовать любой тип достоверных данных, за исключением того, что не может использоваться атрибут COLLATE.

Внутри тела хранимого кода (процедуры или функции) или триггера значение LAST_INSERT_ID() меняется по обычным правилам. Эффект от воздействие хранимой подпрограммы или триггера на значение LAST_INSERT_ID(), который замечен следующими инструкциями, зависит от вида подпрограммы:

  • Если хранимая процедура выполняет инструкции, которые изменяют значение LAST_INSERT_ID(), измененное значение будет замечено инструкциями, которые следуют за вызовом процедуры
  • Для хранимой функций и триггеров, которые меняют значение, оно восстанавливается, когда функция или триггер завершат работу, так что последующие инструкции не будут видеть измененное значение.

Для использования множественных операторов необходимо, чтобы у клиента была возможность посылать строки запросов, содержащие разделитель операторов ;. Добить­ся этого можно путем применения команды delimiter в командной строке клиента mysql. Замена завершающего запрос разделителя ; (например, на разделитель // или $$) позволяет использовать ; в теле процедуры. Тем самым мы разбиваем запросы на блоки инструкций.

Структура для внешних хранимых процедур будет представлена в ближайшем буду­щем. Это позволит записывать хранимые процедуры на языках, отличных от SQL. Ско­рее всего, одним из первых поддерживаемых языков станет РНР, потому что базовый механизм РНР невелик по размерам, безопасен в отношении потоков и легко встраива­ется. Поскольку структура будет общедоступной, ожидается поддержка и многих других языков.

Примеры
Зададимся целью взорвать мозг и создать процедуры и функции для работы с простейшим иерархическим деревом(хотя я сторонник Nested Sets, но не будем усложнять). Для начала создадим и заполним таблицу:

CREATE TABLE `tree` (
          `item_id` int(11) unsigned NOT NULL auto_increment,
          `parent_id` int(11) default NULL,
          `descr` varchar(64) default NULL,
          `some_data` tinytext,
          PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM

#
# Заполним её
#

INSERT INTO `tree` (`item_id`,`parent_id`,`descr`,`some_data`) VALUES
(1,'0','root','The start'),
(2,'1','level 1','2'),
(3,'1','level 1','3'),
(4,'3','level 2','4'),
(5,'4','level 3','5'),
(6,'2','level 2','6'),
(7,'3','level 2','7'),
(8,'2','level 2','8'),
(9,'8','level 3','9'),
(10,'1','level 1','10'),
(11,'10','level 2','11'),
(12,'6','level 3','12'),
(13,'11','level 3','13'),
(14,'4','level 3','14'),
(15,'7','level 3','15'),
(16,'12','appendix','The end');

Создадим процедуру которая выводит все элементы между указанным элементом и root.

DELIMITER $$
DROP PROCEDURE IF EXISTS `fromItemToRoot` $$
CREATE PROCEDURE `fromItemToRoot` (IN _item_id INT)
BEGIN
WHILE _item_id > 0 DO
  SELECT * FROM `tree` WHERE `item_id` = _item_id;
  SELECT parent_id INTO _item_id FROM `tree` WHERE `item_id` = _item_id;
END WHILE;
END $$
DELIMITER ;

Время её работы 45ms (при пустом кэше).
Разберём процедуру. Создаём цикл WHILE пока _item_id больше нуля. _item_id изменяется в теле WHILE на значение родительского id. И каждую итерацию WHILE выбираем строку соответсвующую _item_id. Вроде, всё. Казалось бы, всё должно работать. Однако, если использовать стандартную библиотеку mysql

$res = mysql_query("CALL fromItemToRoot(16)");
// ...

мы получим только одну строчку. Причина проста. Вызвав из MySQL консоли нашу процедуру, можно заметить,

mysql> CALL fromItemToRoot(16);
+---------+-----------+----------+-----------+
| item_id | parent_id | descr    | some_data |
+---------+-----------+----------+-----------+
|      16 |        12 | appendix | The end   |
+---------+-----------+----------+-----------+
1 row in set (0.00 sec)

+---------+-----------+---------+-----------+
| item_id | parent_id | descr   | some_data |
+---------+-----------+---------+-----------+
|      12 |         6 | level 3 | 12        |
+---------+-----------+---------+-----------+
1 row in set (0.00 sec)

+---------+-----------+---------+-----------+
| item_id | parent_id | descr   | some_data |
+---------+-----------+---------+-----------+
|       6 |         2 | level 2 | 6         |
+---------+-----------+---------+-----------+
1 row in set (0.00 sec)

+---------+-----------+---------+-----------+
| item_id | parent_id | descr   | some_data |
+---------+-----------+---------+-----------+
|       2 |         1 | level 1 | 2         |
+---------+-----------+---------+-----------+
1 row in set (0.00 sec)

+---------+-----------+-------+-----------+
| item_id | parent_id | descr | some_data |
+---------+-----------+-------+-----------+
|       1 |         0 | root  | The start |
+---------+-----------+-------+-----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

что тут обработано 5 запросов. Объясняется это тем, что каждый SELECT в теле WHILE обрабатывается отдельно. Тем самым, процедура потянула за собой 5 отдельных запросов. В то время, как библиотека ожидала ответ(ресурс) только на один запрос, она получает несколько и берёт первый, как и ожидалось. Что бы не сталкиваться с такой ситуацией используйте более совершенные библиотеки такие, как mysqli, PDO. Однако рассмотрим варианты процедур, возвращающий только один ресурс. Переделаем процедуру через готовые инструкции:

DELIMITER $$
DROP PROCEDURE IF EXISTS `fromItemToRoot` $$
CREATE PROCEDURE `fromItemToRoot` (IN _item_id INT)
BEGIN
SET @items := _item_id;
WHILE _item_id > 0 DO
  SELECT parent_id INTO _item_id FROM `tree` WHERE `item_id` = _item_id;
  SET @items := CONCAT(@items,",",_item_id);
END WHILE;
SET @s := CONCAT("SELECT * FROM `tree` WHERE `item_id` IN (",@items,") ORDER BY FIELD (`item_id`,",@items,")");
PREPARE stp FROM @s;
EXECUTE stp;
END $$
DELIMITER ;

Время работы процедуры 64ms (при пустом кэше).
Здесть создаётся в цикле запрос в виде строки, который реализуется при помощи конструкции PREPARE EXECUTE.
А вот ещё один вариант реализации, через временную таблицу:

DELIMITER $$
DROP PROCEDURE IF EXISTS `fromItemToRoot` $$
CREATE PROCEDURE `fromItemToRoot` (IN _item_id INT)
BEGIN
DROP TABLE IF EXISTS temp;
CREATE TEMPORARY TABLE temp
(
    `item_id` int(11) unsigned NOT NULL,
    `parent_id` int(11) default NULL,
    `descr` varchar(64) default NULL,
    `some_data` tinytext
) CHARSET=cp1251;
WHILE _item_id > 0 DO
  INSERT INTO temp SELECT * FROM `tree` WHERE `item_id` = _item_id;
  SELECT parent_id INTO _item_id FROM `tree` WHERE `item_id` = _item_id;
END WHILE;
SELECT * FROM temp ORDER BY item_id DESC;
END $$
DELIMITER ;

Время её работы 232ms (при пустом кэше).
Суть этогой реализации: создаем временную таблицу и в цикле заполняем нужными нам элементами. Он имеет минусы: создание таблицы точно соответствующей нашим требованиям и в следствии чего очень долгая реализация.
Пример функции, подсчитывающая количество элементов между item_id и root:

DELIMITER $$
DROP FUNCTION IF EXISTS `fromItemToRootCount` $$
CREATE FUNCTION `fromItemToRootCount` (_item_id INT UNSIGNED) RETURNS INT
BEGIN
DECLARE cnt INT DEFAULT 0;
WHILE _item_id > 0 DO
  SET cnt = cnt + 1;
  SELECT parent_id INTO _item_id FROM `tree` WHERE `item_id` = _item_id;
END WHILE;
RETURN cnt;
END $$
DELIMITER ;

Шаблоны
Процедура:

DELIMITER $$
DROP PROCEDURE IF EXISTS `proc`$$
CREATE PROCEDURE proc(IN a INT, OUT b CHAR(3), INOUT c BIGINT)
  BEGIN
   # stmt
  END$$
DELIMITER ;

Функция:

DELIMITER $$
DROP FUNCTION IF EXISTS `func`$$
CREATE FUNCTION func(IN a INT) RETURNS TINYINT(1)
  BEGIN
    # stmt
  END $$
DELIMITER ;
Categories: MySQL Tags:
  1. Вячеслав
    13 Март 2010 в 18:18 | #1
    Ответить | Цитата

    Полезная статья. Спасибо. Долго искал.

  1. Пока что нет уведомлений.