Создание хранимой процедуры
Определение оператора создания хранимой процедуры.
Спецификация
::=
[имя схемы.]имя хранимой процедуры([параметр [; …]])
[AUTHID {CURRENT_USER | DEFINER}]
[RESULT {простой тип данных | курсор | тип данных объекта}] [FOR DEBUG] процедурный блок
::=
[DEFAULT инициализатор]
::=
::=
Синтаксические правила
-
Опция
IF NOT EXISTS
отменяет выполнение оператора, если указанная хранимая процедура уже существует в БД. -
Опция
OR REPLACE
заставляет удалять существующую в БД хранимую процедуру и создавать её под тем же именем, но с указанными параметрами. -
Одновременное использование опций
IF NOT EXISTS
иOR REPLACE
запрещено. -
Если
< имя схемы >
не указано, то процедура будет создана в текущей схеме. -
Допустимы следующие значения
< модификатора >
параметра:-
IN – передается в процедуру (используется только как входной параметр);
-
OUT – возвращается процедурой (используется только как выходной параметр);
-
INOUT – передается процедуре и возвращается ею (используется как входной/выходной параметр).
-
-
Тип параметра
CURSOR
можно использовать только при вызове процедуры внутри процедуры. -
Список параметров может быть пустым или содержать до 128 параметров.
-
Допускается при указании однотипных параметров перечислять их через запятую в общей спецификации параметра, например,
create procedure prc_test (in v1, v2, v3 int; in c1, c2 char(10)) result int …
-
При указании списка однотипных параметров допускается указывать соответствующий им список инициализаторов, задаваемый в виде списка выражений, разделенных запятыми.
create procedure prc_test (in v1, v2, v3 int default 1, abs(-2), 3) result int … create or replace procedure SUN ( in v varbyte(10) default hextoraw("AABBCCDD"); out v1 varbyte(10)) result varbyte(10) for debug code v1:=v; // return v1; // end; call sun(); return AABBCCDD create or replace procedure SUN ( in nch nchar(10) default tonchar("alpha"); out v1 nchar(10)) result nchar(10) for debug code v1:=nch; return v1; end; call sun(); return alpha
-
Количество выражений в
< инициализаторе >
может быть меньше количества параметров в списке параметров. В этом случае параметры, для которых нет соответствующих выражений в< инициализаторе >
, инициализируются так же, как при отсутствии фразы DEFAULT, т.е. NULL-значениями (отсутствие DEFAULT равносильно явной записи DEFAULT NULL). -
< Инициализатор >
представляет выражение, которое может быть вычислено на этапе трансляции процедуры, т.е. должно содержать константы/константные функции и ранее определенные в процедуре параметры и/или переменные (в последнем случае в качестве значений переменных берутся их значения по умолчанию). -
При указании опции
AUTHID DEFINER
доступ к объектам, используемым в коде процедуры, осуществляется от имени владельца процедуры. При указании опцииAUTHID CURRENT_USER
проверяется доступ вызывающего процедуру пользователя к объектам, используемым в коде процедуры.При вызове процедуры, созданной с опцией
AUTHID DEFINER
, пользователю необходима привилегия EXECUTE AS OWNER на вызываемую процедуру. При вызове процедуры, созданной с опциейAUTHID CURRENT_USER
, пользователю необходима привилегия EXECUTE на вызываемую процедуру.Пример
drop user USER1 cascade; create user USER1 identified by '12345678'; grant dba to USER1; create user USER2 identified by '12345678'; grant dba to USER2; username USER1/12345678 create or replace table "TEST" ("ID" int, "TEXT" char(10)); insert into TEST values( 1, 'aaa'); create or replace procedure TEST_PROC(IN id INTEGER; IN text CHAR(10)) AUTHID DEFINER result integer code EXECUTE "INSERT INTO USER1.TEST values(?, ?)" using id, text;// return errcode(); // end; create or replace procedure TEST_UFN(in i int ) AUTHID DEFINER result cursor( i int ) declare var b typeof(result); // code open b for "select ID from USER1.TEST where ID = ?;" using i; // return b; // end; grant execute as owner on USER1.TEST_PROC to USER2; grant execute as owner on USER1.TEST_UFN to USER2; username USER2/12345678 select i from USER1.TEST_UFN(1); execute USER1.TEST_PROC(2,'bbb');
Предпоследний запрос возвращает одну запись, последний – успешно выполняется. Если же убрать
AUTHID DEFINER
из текста процедур, то оба запроса возвращают код завершения 1022 (нарушение привилегий). -
Все процедуры возвращают некоторое значение (код завершения или возвращаемое значение). Тип этого значения определяется во фразе
RESULT
. Если она не задана, по умолчанию предполагается NULL. -
Если указана опция
FOR DEBUG
, процедура транслируется с отладочной информацией, иначе отладочная информация не включается в оттранслированный код процедуры, и процедуру нельзя будет отлаживать отладчиком хранимых процедур. -
Опция
DEFAULT
используется для пропущенных параметров в конце списка (если при вызове список параметров короче, чем в объявлении процедуры) или в любом месте списка (в этом случае пропущенные параметры заменяются запятыми).Пример заголовка хранимой процедуры
procedure retcur(in name char(20) default "AUTO"; out success bool) result cursor(i int, a char(20), s smallint, d date, n numeric, r real ) for debug
Общие правила
-
Создавать процедуру от имени другого пользователя недопустимо.
-
При трансляции некорректной процедуры ее исходный код будет сохранен или перезаписан в БД для возможной последующей правки.