Хранимые процедуры
| Определение: |
| Хранимая процедура — фрагмент кода, хранящийся в БД. |
Достоинства и мотивация
Чем отличаются от хранимых функций.
Достоинства кода, хранящегося в БД:
- Обработка данных производится непосредственно на стороне БД.
- БД много знает про код — можно скомпилировать, построить оптимальный план исполнения.
- Дает возможность работать с недоступными данными с помощью заданного нами интерфейса.
- Более удобная работа с изменением данных — предварительные проверки, изменение сразу в нескольких местах.
Объявление
Синтаксис
create procedure имя(параметры)
[language {sql|...}]
[[not] deterministic]
{contains sql|reads sql data|modifies sql data|no sql}
[{returns null|called} on null input]
[sql security {definer|invoker}]
тело
- Параметры — список параметров через запятую. Объявление параметра: {in | out | inout} имя тип.
- in (умолч.) — внешний мир не узнает об изменении параметра.
- out — запись результата, но читать изначальное значение нельзя.
- inout — запись результата, но читать изначальное значение можно.
- language — язык, на котором написала процедура. По умолчанию sql. Также поддерживаются Ada, C, COBOL, Fortran, MUMPS, Pascal, PLI.
- [not] deterministic — является ли процедура детерминированной. По умолчанию — нет.
- Отношение к данным. Соответственно: выполняет произвольные SQL операторы (умолч.), только читает, только изменяет, с данными не работает.
- Отношение к null-агрументам. Соответственно: процедура не вызовется и вернет null, процедура вызовется (умолч.).
- Управление доступом. Соответственно: запускается с правами того, кто создал процедуру, с правами того, кто процедуру вызвал (умолч.).
- Можно позволить пользователь работать с недоступными для него данными, но через предоставленный нами интерфейс. Для этого делаем процедуру, работающую с этими данными, даем ей права definer и разрешаем выполнять пользователю.
- Права definer наследуются при вызове других функций (иначе может не хватить прав при вызове процедуры, объявленной с правами invoker).
- Тело может быть как одним оператором (к примеру, select или insert), так и произвольным количеством операторов, заключенных в begin/end. См. разделители.
Примеры
create procedure s() language sql reads sql data select * from Students;
create procedure t(id int, in id1 int, inout i int, out ans int) language sql deterministic modifies sql data called on null input sql security invoker begin -- some operators end
Вызов
Синтаксис
exec имя(аргументы)
Доступ
Чтобы вызывать процедуру, нужно иметь привилегию execute. Может выполняться с разными правами доступа, указанными в объявлении.
Пример
exec ChangeName('Иванов', 'Петров');
Разделители
Рассмотрим пример. Подробнее про содержимое begin/end см. [операторы].
create procedure foo() begin declare x int; set x = 42; end;
Жадный парсер прочитает до первого разделителя (а он по умолчанию ";") и получится синтаксическая ошибка, так как объявление функции уже закончилось и он встречает строку "set x = 42;".
create procedure foo() begin declare x int; ———————- set x = 42; end;
Выход — объявить на время другой разделитель. Так мы не сможем "разорвать" содержимое begin/end.
DELIMITER // create procedure foo() begin declare x int; set x = 42; end; // DELIMITER ;
NB: Некоторые СУБД умеют парсить так, как нам хочется.
См. также
Литература
- Дейт К. Введение в системы баз данных (главы 4 и 17)
- Уидом Д., Ульман Д. Основы реляционных баз данных (раздел 7.4)
- Gulutzan P., Pelzer T. SQL-99 complete, really
