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

Хранимые процедуры выполняются на стороне сервера БД.

Синтаксис языка описания хранимых процедур и функций сделан максимально похожим на язык VIP. Типы поддерживаемых данных те же, что и для VIP, но ограничиваются простыми типами. Однако тут есть некоторые особенности. Поскольку язык хранимых процедур и функций транслируется в соответствующий код на языке конкретной СУБД, то и типы данных будут соответствовать этой СУБД. Например, если мы говорим о строках, то это будут не классические для Атлантис строки с максимальной длиной в 255 символов, а строки, имеющие ограничения на длину, соответствующие конкретной СУБД.

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

символы '//' в любой позиции строки означают комментарий от данной позиции до конца строки;
текст, заключенный в скобки '/*' (открывающая скобка) и '*/' (закрывающая скобка), рассматривается как комментарий.

Описание хранимой процедуры работает по принципу: если есть, то удалить и создать, иначе просто создать. Имена типов параметров и синтаксис описания соответствуют таковым в Атлантис. В процедурах поддерживаются var-параметры.

<procedure> = PROCEDURE <proc-name> ([<parameters>])
  { <declare-var> <declare-cursor> } <begin-end>

Описание хранимой функции работает по принципу: если есть, то удалить и создать, иначе просто создать. Имена типов параметров и синтаксис описания соответствуют таковым в Атлантис. В функциях не поддерживаются var-параметры.

<function> = FUNCTION <func-name> ([<parameters>]) : <type>
  { <declare-var> <declare-cursor> } <begin-end>

<proc-name> - имя хранимой процедуры.

<func-name> - имя хранимой функции.

<parameters> - необязательный список параметров процедуры или функции. Функции и процедуры прямого SQL без параметров следует создавать (и вызывать), явно указывая пустые скобки в конце идентификатора:

sql procedure aaa3();
{
  ...
}
  ...
SqlExecStmt(stmt, 'aaa3()');

<type> - тип значения, возвращаемого функцией.

<declare-var> - описание локальных переменных:

<declare-var> = VAR <var-name>:<var-type> {, <var-name>:<var-type> } [ ; ]

<var-name> - идентификатор, имя переменной.

<var-type> - тип переменной. Имена типов соответствуют простым типам в Атлантис (см. "Простые типы данных. ").

<declare-cursor> - оператор объявления курсора:

<declare-cursor> = DECLARE <cursor-name> CURSOR FOR <select>
  [ FOR UPDATE | FOR READ ONLY ] [ ; ]

<cursor-name> - идентификатор, имя курсора.

<select> - оператор выборки для курсора (см. "Оператор выборки данных SELECT. ").

<begin-end> - блок операторов:

<begin-end> = ( BEGIN | { ) { <statement> } ( END | } )

<statement> - оператор языка:

<statement> = (<sql-statement> | <begin-end> | <sql-operator>)[;]

<sql-statement> - SQL-утверждение:

<sql-statement> = <insert> | <update> | <delete>
| TRANSACTION | <commit> | <rollback> | <savepoint>

<insert> - см. "Оператор INSERT. ".

<update> - см. "Оператор UPDATE. ".

<delete> - см. "Оператор DELETE. ".

TRANSACTION - открыть транзакцию. Оператор доступен начиная с Атлантис 5.3.15.

<commit> - зафиксировать изменения. Оператор доступен начиная с Атлантис 5.3.15.

<commit> = COMMIT [ WORK ]

WORK - необязательное слово.

<rollback> - откатить изменения. Оператор доступен начиная с Атлантис 5.3.15.

<rollback> = ROLLBACK [WORK] [TO [SAVEPOINT] <имя>]

WORK - необязательное слово.

TO <имя> - откатить изменения до точки сохранения <имя>.

SAVEPOINT - необязательное слово.

<savepoint> - установить точку сохранения транзакции. Оператор доступен начиная с Атлантис 5.3.15.

<savepoint> = SAVEPOINT <имя>

<имя> - имя точки сохранения транзакции.

<sql-operator> - SQL оператор:

<sql-operator> = <assigment> | <while-do> | <do-while> 
 | <if> | BREAK | RETURN | <procedure-call> | <cursor>

<assigment> - оператор присваивания:

<assigment> = <var-name> := <expression>

<while-do> - оператор цикла с предпроверкой условия продолжения:

<while-do> = WHILE <expression> DO <statement>

<do-while> - оператор цикла с постпроверкой условия продолжения:

<do-while> = DO <statement> WHILE <expression>

<if> - условный оператор:

<if> = IF <expression> [ THEN ] <statement>
  [ ELSE <statement> ]

BREAK - оператор прерывания выполнения цикла.

RETURN - оператор возврата значения функции.

<procedure-call> - вызов процедуры:

<procedure-call> = <proc-name> (<parameters>)

<cursor> - оператор работы с курсором:

<cursor> = OPEN <cursor-name> | <fetch-cursor>
  | CLOSE <cursor-name>

OPEN - оператор открытия курсора.

<fetch-cursor> - оператор выборки значений из курсора:

<fetch-cursor> = FETCH [NEXT] [FROM] <cursor-name>
  INTO <var-name> {, <var-name> }

CLOSE - оператор закрытия курсора

В хранимых функциях и процедурах доступны глобальные переменные:

<global-variables> = #ROWCOUNT | #OK | #NOTFOUND

#ROWCOUNT - количество записей в курсоре. Можно применять для получения количества записей в неявных курсорах. Тип : целое число.

#OK - статус операции FETCH, когда данные были успешно выбраны. Тип : булевский.

#NOTFOUND - статус операции FETCH, когда больше нет записей в выборке. Тип : булевский.

Особенности использования транзакций в хранимых процедурах

Транзакции в хранимых процедурах на MS SQL Server и Oracle работают по разному.

На Oracle:

по умолчанию код хранимой процедуры всегда работает в транзакции;
оператора открытия транзакции нет (в силу предыдущего пункта);
вложенных транзакций нет (по той же причине);
в конце процедуры срабатывает autocommit.

На MS SQL Server:

по умолчанию код хранимой процедуры работает в режиме autocommit;
транзакция открывается оператором BEGIN TRANSACTION;
допускаются вложенные транзакции;
транзакция по отношению к хранимой процедуре может быть "внешней", например из кода на vip.

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

В случае "внешней" транзакции код хранимых процедур должен (см. пример 1 в конце раздела):

не содержать SQL-утверждений работы с транзакциями;
не открывать и не закрывать транзации в коде на vip;
не открывать и не закрывать транзации при вызове хранимой процедуры.

В случае использования транзакции в самих хранимых процедурахВ случае использования транзакции в самих хранимых процедурах (см. пример 2 в конце раздела):

не использовать "внешнюю" транзакцию. Разработчик должен отслеживать, что если он вызывает хранимую процедуру, которая внутри открывает транзакцию, то снаружи, в том числе и в других хранимых процедурах, вызывающих эту, транзакции открывать уже нельзя;
явно открывать транзакцию оператором TRANSACTION;
не использовать вложенные транзакции, вместо этого использовать SAVEPOINT.

На платформе MS SQL Server замечены следующие ограничения:

1. var-параметры в функциях не доступны;

2. процедуры из функций вызывать нельзя;

3. в функциях не допустимо использование SQL-утверждений (update, delete, insert);

4. в хранимых процедурах не будут устанавливаться права доступа на множества записей, на поля (колонки) и филиальные фильтры.

Примеры

Пример 1. Внешняя транзакция

Текст хранимой процедуры:

sql procedure aaa3(s : string; c : word);
{
  insert into events(typeevent, event) values(c, s);
  insert into events(typeevent, event) values(c, s||' | '||s);
}

Вызов ее на vip:

  BeginTransaction(0);
  n := 'eee!!!';
  stmt := SqlAllocStmt;
  SqlBindParam(stmt, 1, n);
  SqlExecStmt(stmt, 'aaa3(?,12)');
  SqlFreeStmt(stmt);
  EndTransaction;

Пример 2. Транзакция в хранимой процедуре

Текст хранимой процедуры:

sql procedure aaa3(s : string; c : word);
{
  transaction;
  insert into events(typeevent, event) values(c, s);
  savepoint s_a3;
  insert into events(typeevent, event) values(c, s||' | '||s);
  rollback to savepoint s_a3;
  commit;
}

Вызов ее на vip:

  SqlExecDirect('aaa3(''eee!!!'', 12)');

Версия

Начиная с Атлантис 5.3.15 доступны операторы TRANSACTION, COMMIT, ROLLBACK и SAVEPOINT.

Атлантис 5.3.10