Хранимые процедуры

Содержание

Хранимая процедура — это такой же объект базы данных, как и таблица. Это набор действий, который может выполняться по вашей команде.

Для большинства серверов хранимые процедуры работают намного быстрее, чем просто sql запросы. Поэтому их часто используют. Кроме того, хранимые процедуры безопаснее.

Создание хранимых процедур

Хранимая процедура создается с помощью команды CREATE PROCEDURE.

delimiter $$$ //способ определить что будет являться концовкой запроса
CREATE PROCEDURE sp_example
BEGIN
SELECT * FROM table;
END;
$$$ //концовка

Конструкция delimiter нужна, чтобы изменить концовку запроса. Т.к. внутри процедуры пишутся sql запросы с окончанием точка с запятой, то возникнет ошибка при обработке (окончание команды по умолчанию обозначается точкой с запятой), процессор не увидит окончание процедуры END.

После создания процедуры вызовем ее:

CALL sp_example();

У хранимой процедуры есть несколько типов параметров:

IN — входной параметр. Указывается при вызове процедуры.
OUT — выходной параметр. В него будет производиться запись. В PHP есть подобное — это передача данных по ссылке.

При работе процедур могут возникать ошибки. Для этого придумали обработчики ошибок в sql запросах.

Обработчик ошибок

Это способ повлиять на выполнение процедуры при возниконовении какой-то внеплановой ситуации. Таких обработчиков различают 3 вида:

CONTINUE — продолжить работу.
EXIT — бросить все и выйти из процедуры.
UNDO — попытка отката назад.

Обработчик прописывается в начале процедуры. Пример:

delimiter |||
CREATE PROCEDURE demo()
BEGIN
DECLARE EXIT HANDLER FOR SQLSTATE '42000'; //выход, если такая ошибка
//здесь идет какой-то запрос;
END;
|||

Использование курсора

Курсор — это текущий набор записей. В хранимых процедурах его редко используют. По сути курсором является любая выборка оператором SELECT. Пример:

CREATE PROCEDURE cursor()
BEGIN
   DECLARE cur1 CURSOR FOR SELECT id,data FROM demo.d1; //объявили курсор для этого запроса
   DECLARE cur2 CURSOR FOR SELECT i FROM demo.d2; //еще один курсор
   OPEN cur1; //здесь курсор наполняется текущими данными
   OPEN cur2;
//дальше можно считать данные из курсоров с помощью FETCH
CLOSE cur1;
CLOSE cur2;
END;

Управляющие конструкции в хранимых процедурах

Управляющие конструкции — это циклы. Рассмотрим кратко:

IF — выполняет какой-то кусок код в зависимости от условия. Бывает конструкция: IF — THEN — ELSE — END IF, либо — IF — THEN — END IF.
CASE — аналог оператора swith в php.
LOOP — безусловный цикл, т.е. цикл без условия. Может крутиться вечно, пока явно егоне остановить.
LEAVE — прекращает работу LOOP, REPEAT и WHILE.
ITERATE — прекращает замкнутый цикл на следующий шаг. Т.е. действует так же, как LEAVE, только пропускает еще одну итерацию.
REPEAT — цикл с постусловием. Т.е. в конце каждой итерации происходит проверка условия. Пока условие ложно — цикл работает. Как только условие станет true — цикл прекратится. Из программирования поход на оператор do-while.
WHILE — цикл с предусловием. Сначала проверяет условие, потом идет итерация. Пока условие true — работает, условие false — прекращает работу.