66
правок
Изменения
init
{{Определение|definition = '''Хранимая процедура''' — фрагмент кода, хранящийся в БД.}}
В отличие от [хранимых функций] может возвращать таблицу или не возвращать ничего.
Достоинства кода, хранящегося в БД:
*Обработка данных производится непосредственно на стороне БД.
*БД много знает про код -- можно скомпилировать, построить оптимальный план исполнения.
*Дает возможность работать с недоступными данными с помощью заданного нами интерфейса.
*Более удобная работа с изменением данных -- предварительные проверки, изменение сразу в нескольких местах.
== Объявление ==
===Синтаксис===
<font color = blue>create procedure</font> имя(<font color = red>параметры</font>)
[<font color = blue>language</font> {<font color = blue>sql</font>|...}]
[[<font color = blue>not</font>] <font color = blue>deterministic</font>]
{<font color = blue>contains sql</font>|<font color = blue>reads sql data</font>|<font color = blue>modifies sql data</font>|<font color = blue>no sql</font>}
[{<font color = blue>returns</font> <font color = red>null</font>|called} <font color = blue>on</font> <font color = red>null</font> input]
[<font color = blue>sql</font> security {<font color = blue>definer</font>|<font color = blue>invoker</font>}]
<font color = red>тело</font>
*<font color = red>Параметры</font> -- список параметров через запятую. Объявление параметра: {<font color = blue>in</font> | <font color = blue>out</font> | <font color = blue>inout</font>} имя тип.
**<font color = blue>in</font> (умолч.) -- внешний мир не узнает об изменении параметра.
**<font color = blue>out</font> -- запись результата, но читать изначальное значение нельзя.
**<font color = blue>inout</font> -- запись результата, но читать изначальное значение можно.
*<font color = blue>language</font> -- язык, на котором написала процедура. По умолчанию <font color = blue>sql</font>. Также поддерживаются <font color = blue>Ada</font>, <font color = blue>C</font>, <font color = blue>COBOL</font>, <font color = blue>Fortran</font>, <font color = blue>MUMPS</font>, <font color = blue>Pascal</font>, <font color = blue>PLI</font>.
*[<font color = blue>not</font>] <font color = blue>deterministic</font> -- является ли процедура детерминированной. По умолчанию -- нет.
*Отношение к данным. Соответственно: выполняет произвольные SQL операторы (умолч.), только читает, только изменяет, с данными не работает.
*Отношение к null-агрументам. Соответственно: процедура не вызовется и вернет <font color = red>null</font>, процедура вызовется (умолч.).
*Управление доступом. Соответственно: запускается с правами того, кто создал процедуру, с правами того, кто процедуру вызвал (умолч.).
**Можно позволить пользователь работать с недоступными для него данными, но через предоставленный нами интерфейс. Для этого делаем процедуру, работающую с этими данными, даем ей права <font color = blue>definer</font> и разрешаем выполнять пользователю.
**Права <font color = blue>definer</font> наследуются при вызове других функций (иначе может не хватить прав при вызове процедуры, объявленной с правами <font color = blue>invoker</font>).
*Тело может быть как одним оператором (к примеру, <font color = blue>select</font> или <font color = blue>insert</font>), так и произвольным количеством операторов, заключенных в <font color = blue>begin</font>/<font color = blue>end</font>. См [разделители]
=== Примеры ===
<font color = blue>create procedure</font> s()
<font color = blue>language sql reads sql data
select</font> * <font color = blue>from</font> Students;
<font color = blue>create procedure</font> t(id <font color = blue>int</font>, <font color = blue>in</font> id1 <font color = blue>int</font>, <font color = blue>inout</font> i <font color = blue>int</font>, <font color = blue>out</font> ans <font color = blue>int</font>)
<font color = blue>language sql
deterministic
modifies sql data</font>
called <font color = blue>on</font> <font color = red>null</font> input
<font color = blue>sql</font> security <font color = blue>invoker
begin</font>
<font color = green>-- some operators</font>
<font color = blue>end</font>
== Вызов ==
===Синтаксис===
<font color = blue>exec</font> <font color = red>имя</font>(<font color = red>аргументы</font>)
===Доступ===
Чтобы вызывать процедуру, нужно иметь привилегию <font color = blue>execute</font>.
Может выполняться с разными правами доступа, указанными в [объявлении].
=== Пример ===
<font color = blue>exec</font> ChangeName(<font color = green>'Иванов'</font>, <font color = green>'Петров'</font>);
== Разделители ==
Рассмотрим пример. Подробнее про содержимое <font color = blue>begin/end</font> см. [операторы].
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;
Выход -- объявить на время другой разделитель. Так мы не сможем "разорвать" содержимое <font color = blue>begin/end</font>.
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''
** [https://crate.io/docs/sql-99/en/latest/chapters/25.html Chapter 25 – SQL-Invoked Routine]
** [https://crate.io/docs/sql-99/en/latest/chapters/26.html Chapter 26 – PSM: Not Just Persistent Stored Modules]
[[Категория: Базы данных]]
В отличие от [хранимых функций] может возвращать таблицу или не возвращать ничего.
Достоинства кода, хранящегося в БД:
*Обработка данных производится непосредственно на стороне БД.
*БД много знает про код -- можно скомпилировать, построить оптимальный план исполнения.
*Дает возможность работать с недоступными данными с помощью заданного нами интерфейса.
*Более удобная работа с изменением данных -- предварительные проверки, изменение сразу в нескольких местах.
== Объявление ==
===Синтаксис===
<font color = blue>create procedure</font> имя(<font color = red>параметры</font>)
[<font color = blue>language</font> {<font color = blue>sql</font>|...}]
[[<font color = blue>not</font>] <font color = blue>deterministic</font>]
{<font color = blue>contains sql</font>|<font color = blue>reads sql data</font>|<font color = blue>modifies sql data</font>|<font color = blue>no sql</font>}
[{<font color = blue>returns</font> <font color = red>null</font>|called} <font color = blue>on</font> <font color = red>null</font> input]
[<font color = blue>sql</font> security {<font color = blue>definer</font>|<font color = blue>invoker</font>}]
<font color = red>тело</font>
*<font color = red>Параметры</font> -- список параметров через запятую. Объявление параметра: {<font color = blue>in</font> | <font color = blue>out</font> | <font color = blue>inout</font>} имя тип.
**<font color = blue>in</font> (умолч.) -- внешний мир не узнает об изменении параметра.
**<font color = blue>out</font> -- запись результата, но читать изначальное значение нельзя.
**<font color = blue>inout</font> -- запись результата, но читать изначальное значение можно.
*<font color = blue>language</font> -- язык, на котором написала процедура. По умолчанию <font color = blue>sql</font>. Также поддерживаются <font color = blue>Ada</font>, <font color = blue>C</font>, <font color = blue>COBOL</font>, <font color = blue>Fortran</font>, <font color = blue>MUMPS</font>, <font color = blue>Pascal</font>, <font color = blue>PLI</font>.
*[<font color = blue>not</font>] <font color = blue>deterministic</font> -- является ли процедура детерминированной. По умолчанию -- нет.
*Отношение к данным. Соответственно: выполняет произвольные SQL операторы (умолч.), только читает, только изменяет, с данными не работает.
*Отношение к null-агрументам. Соответственно: процедура не вызовется и вернет <font color = red>null</font>, процедура вызовется (умолч.).
*Управление доступом. Соответственно: запускается с правами того, кто создал процедуру, с правами того, кто процедуру вызвал (умолч.).
**Можно позволить пользователь работать с недоступными для него данными, но через предоставленный нами интерфейс. Для этого делаем процедуру, работающую с этими данными, даем ей права <font color = blue>definer</font> и разрешаем выполнять пользователю.
**Права <font color = blue>definer</font> наследуются при вызове других функций (иначе может не хватить прав при вызове процедуры, объявленной с правами <font color = blue>invoker</font>).
*Тело может быть как одним оператором (к примеру, <font color = blue>select</font> или <font color = blue>insert</font>), так и произвольным количеством операторов, заключенных в <font color = blue>begin</font>/<font color = blue>end</font>. См [разделители]
=== Примеры ===
<font color = blue>create procedure</font> s()
<font color = blue>language sql reads sql data
select</font> * <font color = blue>from</font> Students;
<font color = blue>create procedure</font> t(id <font color = blue>int</font>, <font color = blue>in</font> id1 <font color = blue>int</font>, <font color = blue>inout</font> i <font color = blue>int</font>, <font color = blue>out</font> ans <font color = blue>int</font>)
<font color = blue>language sql
deterministic
modifies sql data</font>
called <font color = blue>on</font> <font color = red>null</font> input
<font color = blue>sql</font> security <font color = blue>invoker
begin</font>
<font color = green>-- some operators</font>
<font color = blue>end</font>
== Вызов ==
===Синтаксис===
<font color = blue>exec</font> <font color = red>имя</font>(<font color = red>аргументы</font>)
===Доступ===
Чтобы вызывать процедуру, нужно иметь привилегию <font color = blue>execute</font>.
Может выполняться с разными правами доступа, указанными в [объявлении].
=== Пример ===
<font color = blue>exec</font> ChangeName(<font color = green>'Иванов'</font>, <font color = green>'Петров'</font>);
== Разделители ==
Рассмотрим пример. Подробнее про содержимое <font color = blue>begin/end</font> см. [операторы].
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;
Выход -- объявить на время другой разделитель. Так мы не сможем "разорвать" содержимое <font color = blue>begin/end</font>.
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''
** [https://crate.io/docs/sql-99/en/latest/chapters/25.html Chapter 25 – SQL-Invoked Routine]
** [https://crate.io/docs/sql-99/en/latest/chapters/26.html Chapter 26 – PSM: Not Just Persistent Stored Modules]
[[Категория: Базы данных]]
