Выполнение хранимой процедуры

Определение оператора выполнения хранимой процедуры.

Спецификация

               
< выполнение хранимой процедуры >::=
{EXECUTE | CALL} [имя схемы.]имя хранимой процедуры
[([параметр [, …]])] [AS OWNER] [INTO < имя переменной >[, …]]

Синтаксические правила

  1. Конструкция < выполнение хранимой процедуры > исполняет предварительно оттранслированную и хранящуюся в БД процедуру. Любые виды рекурсивных вызовов разрешаются.

  2. < Список параметров > – это список выражений, имен переменных или SQL-параметров, разделенных запятыми (может быть и пустым). Фактические параметры ставятся в соответствие формальным по порядку следования.

    Примечание

    SQL-параметр нельзя устанавливать в соответствие параметру хранимой процедуры с типом данных CURSOR.

  3. Количество параметров процедуры в вызове не должно превышать количество параметров в описании процедуры.

  4. Если необходимо передать параметр по умолчанию, можно пропустить фактический параметр и сразу поставить запятую. Таким образом, чтобы пропустить несколько параметров, необходимо подряд поставить несколько запятых. Запятые не обязательно ставить в конце списка. Все недостающие параметры всегда получают свои значения по умолчанию, в том числе, если список пустой.

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

  6. Если формальные параметры вызываемой процедуры имеют модификатор OUT или INOUT, в качестве фактических параметров указывать выражения недопустимо. Здесь используются только имена переменных, в которые будут записаны выходные параметры. Если имя переменной опущено, выходное значение никуда записано не будет.

  7. При указании опции AS OWNER и наличии прав на выполнение процедуры с правами владельца, процедура будет исполняться с правами владельца, все выполняемые запросы и вызовы процедур внутри процедуры при отсутствии указания схемы будут выполняться в схеме владельца. Для получения имени и идентификатора пользователя, запустившего её на выполнение, необходимо использовать функции username() и userid() соответственно, а для получения имени и идентификатора пользователя, от имени которого выполняется процедура, необходимо использовать функции effective_username() и effective_userid() соответственно.

  8. Если в операторе CALL задана фраза INTO, возвращаемое значение процедуры присвоится указанной переменной (такое использование недопустимо для процедур, возвращающих курсор; для передачи курсора в вызывающую процедуру существует специальная конструкция – см. пункт Открытие курсора).

  9. Разрешение ссылки на вызываемую процедуру происходит на этапе выполнения данной процедуры – процедура ищется по имени. Если такой процедуры нет, происходит исключение UNDEFPROC. Соответственно, на этапе выполнения так же проверяются типы, количество параметров и тип возвращаемого значения. В случае ошибок происходят исключения BADPARAM или BADRETVAL.

  10. Оператор используется для вызова хранимой процедуры внутри другого объекта процедурного языка (хранимой процедуры, триггера или временной процедуры).

  11. < Параметр > представляет собой совокупность литералов и/или значений параметров, разделенных запятой.

    create or replace procedure test (in i int) result int 
    code 
      return i;
    end;
    !Недопустимая конструкция
    execute test(1+2);
  12. При наличии у параметров опции DEFAULT, они могут опускаться, в этом случае поле ввода параметра необходимо оставить пустым и ввод следующего параметра выполнить после запятой.

    create procedure test_proc (in k int, in m int DEFAULT 2, in n int) ...
    call test_proc(1,,3);
  13. Для передачи логических значений параметров используются целые числа (0 интерпретируется как FALSE, 1 – как TRUE) или символьные литералы 'true' и 'false' (в любом регистре).

  14. В рамках одной транзакции первая запущенная процедура для выполнения оператора EXECUTE открывает от основного канала свой дочерний канал. Все последующие процедуры, вызываемые в рамках той же транзакции, переиспользуют тот же самый канал, не открывая новых. Поэтому COMMIT/ROLLBACK в процедуре влияет не только на изменения, сделанные данной процедурой, но и на все изменения, сделанные всеми вызванными ранее процедурами. Чтобы избежать этого, процедура должна использовать точку сохранения (SAVEPOINT), установив ее в начале транзакции, и подавать команды COMMIT/ROLLBACK до нее.

    Примечание

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

  15. Для выполнения чужой процедуры необходимо явно указывать < имя схемы > и иметь соответствующее право на вызов процедуры, назначенное владельцем процедуры.

  16. Если опция AS OWNER не задана, то вызывающему пользователю должна быть назначена привилегия EXECUTE на данную процедуру и обращение к таблицам внутри тела процедуры будет выполняться от имени вызывающего пользователя и соответственно у вызывающего пользователя должны быть назначены соответствующие привилегии на таблицы.

  17. Если опция AS OWNER задана, то вызывающему пользователю должна быть назначена привилегия EXECUTE AS OWNER на данную процедуру и обращение к таблицам будет выполняться от имени владельца процедуры и права доступа к таблицам, используемым в процедуре, не требуются.

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

1)
call myproc("auto",,1,aa) into bb;
2)
create or replace procedure tst_param (in id int; in ch char(10); out answ
  char(20)) result int for debug
code
  answ:= tochar(id) + "  " + ch; //
  return 0; //
end;

call tst_param(?,:arg2);
235
abcd

output parameters ( 235  abcd )
3) 
create or replace table T1 (id int primary key, s int);
insert into T1 values (2, 300);
insert into T1 values (7, 200);
insert into T1 values (4, 600);

create or replace procedure procedure1() result int for debug
declare
  var a int;//
code
  execute "select max(s) from SYSTEM.T1;" into a;//
  return a;//
end;

create or replace procedure procedure0() result int for debug
declare
  var a int;//
code
  call SYSTEM.procedure1() as owner into a;//
  return a;//
exceptions
  when all then resignal;//
end;

create or replace user U1 identified by '12345678';
grant resource to U1;
grant execute as owner on procedure1 to U1;
grant execute as owner on procedure0 to U1;

username U1/12345678

execute SYSTEM.procedure0() as owner;
|600|