Oracle 11g PL/SQL, еще несколько особенностей

Я уже делился тем, что недавно (вновь или впервые) привлекло мое внимание в языке PL/SQL. Сегодня - продолжу.

Когда %TYPE и %ROWTYPE берут с собой ограничения

Переменные, объявленные с помощью %TYPE, наследуют от "прототипа" не только тип и размер, но также ограничения NOT NULL:

SQL> SQL> declare
  2      l_one number not null := 0;
  3      l_two l_one%type;
  4  begin
  5      null;
  6  end;
  7  /
ORA-06550: line 3, column 11:
PLS-00218: a variable declared NOT NULL must have an initialization assignment

А переменные, объявленные с помощью %ROWTYPE, наследуют не только типы, размеры и ограничения полей записи, но и их значения по умолчанию (эти значения - часть определения типа):

SQL> declare
  2      type t_rec is record (
  3          id number,
  4          name varchar2(50) not null default 'Qwerty'
  5      );
  6      l_rec1 t_rec;
  7      l_rec2 l_rec1%type;
  8  begin
  9      dbms_output.put_line(
 10          nvl(to_char(l_rec1.id), 'NULL') || ', ' ||
 11          nvl(to_char(l_rec1.name), 'NULL')
 12      );
 13      dbms_output.put_line(
 14          nvl(to_char(l_rec2.id), 'NULL') || ', ' ||
 15          nvl(to_char(l_rec2.name), 'NULL')
 16      );
 17      l_rec2.name := to_char(NULL);
 18  exception
 19  when others then
 20      dbms_output.put_line(sqlerrm);
 21  end;
 22  /
NULL, Qwerty
NULL, Qwerty
ORA-06502: PL/SQL: numeric or value error

PL/SQL procedure successfully completed

Вышесказанное справедливо только в случаях, когда %TYPE и %ROWTYPE используют как прототип переменную PL/SQL, а не столбец или строку таблицы БД. От столбцов таблицы наследуются только тип и размер, и не наследуются ограничения и значения по умолчанию:

SQL> create table test_tab (
  2      msg varchar2(50) default 'Hello' not null
  3  );
Table created

SQL> declare
  2      l_rec test_tab%ROWTYPE;
  3  begin
  4      dbms_output.put_line(nvl(to_char(l_rec.msg), 'NULL'));
  5      l_rec.msg := NULL;
  6  end;
  7  /
NULL
PL/SQL procedure successfully completed

Таблица test_tab нам еще пригодится для дальнейших примеров.

Когда (не)уместны COMMIT и ROLLBACK

В общем случае, нет органичений на количество транзакций, которые можно начинать и завершать внутри процедуры или функции PL/SQL, так же как нет требования обязательно использовать (или не использовать) в процедуре или функции COMMIT или ROLLBACK. Но есть несколько специальных случаев.

* Автономную транзакцию в процедуре необходимо завершать - или она будет отменена и мы получим исключение:

SQL> create or replace procedure test_proc as
  2      pragma autonomous_transaction;
  3  begin
  4      insert into test_tab values ('Привет, мир');
  5      commit;
  6      insert into test_tab values ('Hello, world');
  7  end test_proc;
  8  /
Procedure created

SQL> exec test_proc
begin test_proc; end;
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "AY.TEST_PROC", line 7
ORA-06512: at line 1

SQL> select * from test_tab;
MSG
--------------------------------------------------------------------------------
Привет, мир

SQL> drop procedure test_proc;
Procedure dropped

* Нельзя завершать транзакцию в функции, которая вызывается из команды DML или SELECT, или в табличном триггере:

SQL> create or replace function test_func
  2  return varchar2 as
  3  begin
  4      commit;
  5      return 'commited';
  6  end test_func;
  7  /
Function created

SQL> select test_func from dual;
select test_func from dual
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML 
ORA-06512: at "AY.TEST_FUNC", line 4

SQL> update test_tab set msg = test_func;
update test_tab set msg = test_func
ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML 
ORA-06512: at "AY.TEST_FUNC", line 4

При этом нет никаких препятствий для вызова test_func из PL/SQL:

SQL> var v varchar2(50)
SQL> exec :v := test_func
PL/SQL procedure successfully completed
SQL> print v
v
---------
commited

А вот триггер, завершающий транзакцию, и к чему это приводит:

SQL> create or replace trigger test_tab_trg
  2  after insert or update or delete on test_tab
  3  begin
  4      commit;
  5  end;
  6  /
Trigger created

SQL> insert into test_tab values ('Hello, world');
insert into test_tab values ('Hello, world')
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "AY.TEST_TAB_TRG", line 2
ORA-04088: error during execution of trigger 'AY.TEST_TAB_TRG'

Удаляю следы экспериментов:

SQL> drop trigger test_tab_trg;
Trigger dropped

SQL> drop function test_func;
Function dropped

* Завершение транзакции при открытом курсоре FOR UPDATE приводит к немедленному закрытию курсора:

SQL> DECLARE
  2      CURSOR c_test_tab IS SELECT * FROM test_tab FOR UPDATE;
  3  BEGIN
  4      FOR x IN c_test_tab LOOP
  5          UPDATE test_tab
  6          SET msg = LOWER(msg)
  7          WHERE CURRENT OF c_test_tab;
  8          COMMIT; -- !!!
  9      END LOOP;
 10  END;
 11  /
ORA-01002: fetch out of sequence
ORA-06512: at line 4

SQL> select * from test_tab;
MSG
--------------------------------------------------------------------------------
привет, мир

Сиквенсы доступны в PL/SQL напрямую

Когда-то получить значения сиквенса можно было только в команде DML. Если требовалось присвоить значение сиквенса переменной PL/SQL, то требовалось выполнить SELECT INTO... В Oracle 11g можно сделать так:

SQL> create sequence test_seq;
Sequence created

SQL> 
SQL> declare
  2      l_seq number;
  3  begin
  4      l_seq := test_seq.nextval;
  5      l_seq := test_seq.currval;
  6  end;
  7  /
PL/SQL procedure successfully completed

SQL> drop sequence test_seq;
Sequence dropped

Режимы IN, OUT и IN OUT для связанных переменных

В PL/SQL режимы IN, OUT и IN OUT используются не только для параметров процедур, но и для связанных переменных в части USING команды EXECUTE IMMEDIATE.

По умолчанию переменные USING имеют режим IN. В динамическом коде, исполняемом с помощью EXECUTE IMMEDIATE,

• IN переменные доступны только на чтение,
• OUT переменные доступны только на запись.

SQL> SQL> DECLARE
  2      x VARCHAR2(50) := 'на входе';
  3      y VARCHAR2(50) := 'на входе';
  4      z VARCHAR2(50) := 'на входе';
  5  BEGIN
  6      EXECUTE IMMEDIATE '
  7          begin
  8              dbms_output.put_line(''1 '' || :x);
  9              --:x := ''новое'';
 10              --ORA-06536: IN bind variable bound to an OUT position
 11  
 12              --dbms_output.put_line(''1 '' || :y);
 13              --ORA-06537: OUT bind variable bound to an IN position
 14  
 15              :y := ''новое'';
 16              dbms_output.put_line(''1 '' || :z);
 17              :z := ''новое'';
 18          end;'
 19      USING x, OUT y, IN OUT z;
 20      dbms_output.put_line('2 ' || x);
 21      dbms_output.put_line('2 ' || y);
 22      dbms_output.put_line('2 ' || z);
 23  END;
 24  /
1 на входе
1 на входе
2 на входе
2 новое
2 новое
PL/SQL procedure successfully completed

Переменная OUT var в части USING и часть RETURNING INTO var взаимозаменяемы в предложении EXECUTE IMMEDIATE:

SQL> DECLARE
  2      l_msg test_tab.msg%type;
  3  BEGIN
  4      EXECUTE IMMEDIATE '
  5          update test_tab
  6          set msg = upper(msg)
  7          where rownum = :x
  8          returning msg into :y'
  9      USING 1, OUT l_msg
 10      ;
 11      dbms_output.put_line(l_msg);
 12  
 13      EXECUTE IMMEDIATE '
 14          update test_tab
 15          set msg = initcap(msg)
 16          where rownum = :x
 17          returning msg into :y'
 18      USING 1
 19      RETURNING INTO l_msg
 20      ;
 21      dbms_output.put_line(l_msg);
 22  END;
 23  /
ПРИВЕТ, МИР
Привет, Мир
PL/SQL procedure successfully completed

Это разные звери

Вы проводите различие между функциями SQL и PL/SQL? А ведь это разные звери.

Попробую вызвать функцию NVL2 в PL/SQL коде, а затем в SQL:

SQL> exec :n := NVL2(NULL, 1, 2)
begin :n := NVL2(NULL, 1, 2); end;
ORA-06550: line 1, column 13:
PLS-00201: identifier 'NVL2' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select NVL2(NULL, 1, 2) from dual;
NVL2(NULL,1,2)
--------------
             2

NVL2 - это функция языка SQL, но не языка PL/SQL. А вот COALESCE работает в обеих средах, потому что эта функция реализована и в SQL и в PL/SQL:

SQL> select coalesce(null, null, 'hi') from dual;
COALESCE(NULL,NULL,'HI')
------------------------
hi

SQL> var x varchar2(5)
SQL> exec :x := coalesce(null, null, 'hi')
PL/SQL procedure successfully completed

SQL> print x
x
---------
hi

Другой пример, демонстрирующий, что функции SQL и функции PL/SQL - это разные звери, использует тот факт, что в Oracle 11g стало возможным при вызове PL/SQL функций из SQL передавать параметры по именам. Создам собственную функцию, чтобы продемонстрировать это:

SQL> CREATE OR REPLACE FUNCTION hello(x VARCHAR2) RETURN VARCHAR2
AS
BEGIN
    RETURN 'hello '||x;
END;
/
Function created

SQL> select hello(x => 'world') from dual;
HELLO(X=>'WORLD')
-------------------------
hello world

Но попробуем вызвать с именованными параметрами функции, которые есть и в SQL и в PL/SQL (имена параметров посмотрите в пакете SYS.STANDARD):

SQL> select upper(ch => 'hello') from dual;
select upper(ch => 'hello') from dual
ORA-00907: missing right parenthesis

SQL> select concat(LEFT => 'hello ', RIGHT => 'world') from dual;
select concat(LEFT => 'hello ', RIGHT => 'world') from dual
ORA-00909: invalid number of arguments

Дело в том, что в пакете SYS.STANDARD описаны идентификаторы PL/SQL, а в приведенных командах SELECT вызываются функции SQL! Вызовем функции PL/SQL, указав их полные имена:

SQL> select sys.standard.upper(ch => 'hello') from dual;
SYS.STANDARD.UPPER(CH=>'HELLO'
--------------------------------------------------------------------------------
HELLO

SQL> select sys.standard.concat(LEFT => 'hello ', RIGHT => 'world') from dual;
SYS.STANDARD.CONCAT(LEFT=>'HEL
--------------------------------------------------------------------------------
hello world

В заключение, замечание по поводу DECODE. Многие сталкивались с невозможностью использовать DECODE напрямую в PL/SQL:

SQL> var n number
SQL> exec :n := decode('a', 'a', 1, 'b', 2, 3)
begin :n := decode('a', 'a', 1, 'b', 2, 3); end;
ORA-06550: line 1, column 13:
PLS-00204: function or pseudo-column 'DECODE' may be used inside a SQL statement only
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

При этом функция DECODE определена в пакете SYS.STANDARD (в отличие от уже упоминавшейся NVL2). Выходит, DECODE стоит совсем особняком. Возможно, ее использование в PL/SQL запрещено из-за ее необычного обращения с NULL, которое идет в разрез с общей практикой?

Зачищаю следы экспериментов:

SQL> drop function hello;
Function dropped

SQL> drop table test_tab;
Table dropped