Создание хранимой процедуры

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

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

                                 
< создание хранимой процедуры >::=
CREATE [IF NOT EXISTS | OR REPLACE] PROCEDURE
[имя схемы.]имя хранимой процедуры([параметр [; …]])
[AUTHID {CURRENT_USER | DEFINER}]
[RESULT {простой тип данных | курсор | тип данных объекта}] [FOR DEBUG] процедурный блок
< модификатор >::=
{IN | OUT | INOUT}
< инициализатор >::=

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

  1. Опция IF NOT EXISTS отменяет выполнение оператора, если указанная хранимая процедура уже существует в БД.

  2. Опция OR REPLACE заставляет удалять существующую в БД хранимую процедуру и создавать её под тем же именем, но с указанными параметрами.

  3. Одновременное использование опций IF NOT EXISTS и OR REPLACE запрещено.

  4. Если < имя схемы > не указано, то процедура будет создана в текущей схеме.

  5. Допустимы следующие значения < модификатора > параметра:

    • IN – передается в процедуру (используется только как входной параметр);

    • OUT – возвращается процедурой (используется только как выходной параметр);

    • INOUT – передается процедуре и возвращается ею (используется как входной/выходной параметр).

  6. Тип параметра CURSOR можно использовать только при вызове процедуры внутри процедуры.

  7. Список параметров может быть пустым или содержать до 128 параметров.

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

    create procedure prc_test (in v1, v2, v3 int; in c1, c2 char(10)) result int …
  9. При указании списка однотипных параметров допускается указывать соответствующий им список инициализаторов, задаваемый в виде списка выражений, разделенных запятыми.

    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
  10. Количество выражений в < инициализаторе > может быть меньше количества параметров в списке параметров. В этом случае параметры, для которых нет соответствующих выражений в < инициализаторе >, инициализируются так же, как при отсутствии фразы DEFAULT, т.е. NULL-значениями (отсутствие DEFAULT равносильно явной записи DEFAULT NULL).

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

  12. При указании опции 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 (нарушение привилегий).

  13. Все процедуры возвращают некоторое значение (код завершения или возвращаемое значение). Тип этого значения определяется во фразе RESULT. Если она не задана, по умолчанию предполагается NULL.

  14. Если указана опция FOR DEBUG, процедура транслируется с отладочной информацией, иначе отладочная информация не включается в оттранслированный код процедуры, и процедуру нельзя будет отлаживать отладчиком хранимых процедур.

  15. Опция 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

Общие правила

  1. Создавать процедуру от имени другого пользователя недопустимо.

  2. При трансляции некорректной процедуры ее исходный код будет сохранен или перезаписан в БД для возможной последующей правки.