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

Как и любой язык программирования, PL/SQL имеет свои особенности. Одни особенности делают язык программирования тем, что он есть, и программист использует их постоянно. А без других он может обходиться годами и даже вовсе не подозревать (или забыть) об их существовании.

Например, особенностью Java является необходимость объявлять исключения, которые выбрасывает метод. Без этого не обойтись. Другая особенность Java - невозможность использования одноименных переменных во вложенных блоках. Об этом вспоминаешь не часто. Очень разные вещи, но характерные для Java. А особенностью PL/SQL до версии Oracle 11g было отсутствие предложения CONTINUE - и для его имитации приходилось использовать безусловный переход на метку в конце тела цикла. Теперь эта особенность осталась в прошлом.

Недавно я предпринял систематический обзор PL/SQL по книге Study Guide for 1Z0-144: Oracle Database 11g: Program with PL/SQL: Oracle Certification Prep by Matthew Morris. Про некоторые особенности PL/SQL, которые привлекли мое внимание в процессе чтения, я сегодня и расскажу.

Доступ к переменной при помощи метки

В PL/SQL метки нужны не только для того, чтобы выполнять переход на помеченное предложение при помощи GOTO. Другое их назначение - давать имена блокам кода ([DECLARE] BEGIN .. END) и циклам ([FOR | WHILE] LOOP .. END LOOP), которые в отсутствие метки были бы анонимны. Имя, присвоенное блоку, может быть использовано как квалификатор для доступа к переменным этого блока.

Следующий пример демонстрирует доступ к одноименным переменным, определенным во вложенных блоках PL/SQL, из самого внутреннего блока. Доступ к переменной, определенной в секции объявлений процедуры, дает использование имени процедуры как квалификатора:

SQL> CREATE OR REPLACE PROCEDURE процедура AS
    x NUMBER := 0;
BEGIN
    <<цикл>>
    FOR x IN 1 .. 3 LOOP
        <<внешний>>
        DECLARE
            x NUMBER := 2;
        BEGIN
            DECLARE
                x number := 3;
            BEGIN
                dbms_output.put_line(x);
                dbms_output.put_line(внешний.x);
                dbms_output.put_line(цикл.x);
                dbms_output.put_line(процедура.x);
            END;
            EXIT;
        END;
    END LOOP;
END процедура;
/
Procedure created

SQL> set serveroutput on

SQL> exec процедура
3
2
1
0
PL/SQL procedure successfully completed

Русскоязычные имена меток и процедуры здесь напоминают о еще одной особенности PL/SQL (и SQL): в идентификаторах можно использовать не только латиницу, но все буквы из database character set - набора символов, установленных для базы данных Oracle при ее создании.

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

SQL> DROP PROCEDURE процедура;
Procedure dropped

Непойманное исключение отменяет ранее сделанные изменения

Когда мы имеем дело с SQL, то ошибка при выполнении команды DML отменяет все изменения, которые данная команда успела сделать до того, как возникла ошибка. А результаты предыдущих команд DML, которые успешно выполнились в текущей транзакции, остаются в силе:

SQL> CREATE TABLE my_books (
    ID NUMBER(9) PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL
);
Table created

SQL> INSERT INTO my_books VALUES (1, 'Атлас мира');
1 row inserted

SQL> INSERT INTO my_books VALUES (2, 'Иллиада');
1 row inserted

SQL> UPDATE my_books SET name = UPPER(NAME) WHERE ROWNUM = 1 / 0;
UPDATE my_books SET name = UPPER(NAME) WHERE ROWNUM = 1 / 0
ORA-01476: divisor is equal to zero

SQL> SELECT * FROM my_books;
        ID NAME
---------- ------------------------------
         1 Атлас мира
         2 Иллиада

Все, что потеряно в результате ошибки команды UPDATE - это результат самой UPDATE. В текущей ситуации можно поступить по-разному: исправить команду UPDATE и выполнить ее, или применить ранее сделанные изменения командой COMMIT, или отменить изменения командой ROLLBACK.

Отменю сделанные изменения и выполню те же три команды DML внутри анонимного PL/SQL блока:

SQL> rollback;
Rollback complete

SQL> SELECT * FROM my_books;
        ID NAME
---------- ------------------------------

SQL> BEGIN
  2      INSERT INTO my_books VALUES (1, 'Атлас мира');
  3      INSERT INTO my_books VALUES (2, 'Иллиада');
  4      UPDATE my_books SET name = UPPER(NAME) WHERE ROWNUM = 1 / 0;
  5  END;
  6  /
BEGIN
    INSERT INTO my_books VALUES (1, 'Атлас мира');
    INSERT INTO my_books VALUES (2, 'Иллиада');
    UPDATE my_books SET name = UPPER(NAME) WHERE ROWNUM = 1 / 0;
END;
ORA-01476: divisor is equal to zero
ORA-06512: at line 4

SQL> SELECT * FROM my_books;
        ID NAME
---------- ------------------------------

Как видим, неперехваченное исключение приводит к отмене всех изменений, сделанных в блоке PL/SQL до того, как возникло исключение.

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

Похожее поведение - все или ничего - демонстрирует PL/SQL и в случае возвращения результатов процедуры через параметы OUT.

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

SQL> DROP TABLE my_books;
Table dropped

Передача значений через параметры OUT и IN OUT

Параметры с модификаторами OUT и IN OUT обычно передаются в вызываемую процедуру и обратно по значению, то есть,

• значения фактических параметров, с которыми вызывается процедура, копируются при вызове в параметры процедуры, и
• значения параметров процедуры, установленные в ходе ее работы, копируются обратно в фактические параметры при успешном завершении работы процедуры.

А если в ходе выполнения процедуры возникает исключение, то фактические параметры остаются без изменений:

SQL> DECLARE
    l_out VARCHAR2(50) := 'не важно';
    l_inout VARCHAR2(50) := 'не важно';
    
    PROCEDURE x(p_out OUT VARCHAR2, p_inout IN OUT VARCHAR2) IS
    BEGIN
        dbms_output.put_line('1   p_out: ' || p_out);
        dbms_output.put_line('1 p_inout: ' || p_inout);
        p_out := 'результат';
        p_inout := 'результат';
        dbms_output.put_line('2   l_out: ' || l_out);
        dbms_output.put_line('2 l_inout: ' || l_inout);
        RAISE no_data_found;
    END x;
BEGIN
    x(l_out, l_inout);
EXCEPTION
WHEN no_data_found THEN
    dbms_output.put_line('3   l_out: ' || l_out);
    dbms_output.put_line('3 l_inout: ' || l_inout);
END;
/
1   p_out: 
1 p_inout: не важно
2   l_out: не важно
2 l_inout: не важно
3   l_out: не важно
3 l_inout: не важно
PL/SQL procedure successfully completed

Из примера видно следующее:

1. Параметр p_out OUT в процедуре x инициализирован значением NULL перед выполнением кода процедуры - так всегда происходит с OUT параметрами. Значение фактического параметра l_out в вызванной процедуре недоступно через "внутренюю переменную" p_out. Тогда как значение переменной l_inout доступно в процедуре через параметр p_inout IN OUT.
2. При аварийном завершении процедуры (в результате исключения) значения формальных параметров p_out и p_inout не копируются в формальные параметры l_out и l_inout.

Итак, PL/SQL заботливо предохраняет внешние по отношению к процедуре переменные от изменений вплоть до момента успешного завершения процедуры.

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

Подсказка (hint) NOCOPY рекомендует виртуальной машине PL/SQL передавать параметры в процедуру по ссылке, а не по значению, что не требует копирования. При этом код процедуры напрямую изменяет значения фактических параметров:

SQL> DECLARE
    l_out VARCHAR2(50) := 'не важно';
    l_inout VARCHAR2(50) := 'не важно';
    
    PROCEDURE x(p_out OUT NOCOPY VARCHAR2, p_inout IN OUT NOCOPY VARCHAR2) IS
    BEGIN
        dbms_output.put_line('1   p_out: ' || p_out);
        dbms_output.put_line('1 p_inout: ' || p_inout);
        p_out := 'результат';
        p_inout := 'результат';
        dbms_output.put_line('2   l_out: ' || l_out);
        dbms_output.put_line('2 l_inout: ' || l_inout);
        RAISE no_data_found;
    END x;
BEGIN
    x(l_out, l_inout);
EXCEPTION
WHEN no_data_found THEN
    dbms_output.put_line('3   l_out: ' || l_out);
    dbms_output.put_line('3 l_inout: ' || l_inout);
END;
/
1   p_out: 
1 p_inout: не важно
2   l_out: результат
2 l_inout: результат
3   l_out: результат
3 l_inout: результат
PL/SQL procedure successfully completed

Как видим, подсказка NOCOPY была принята и процедура x, прежде чем завершилась аварийно, изменила значения переменных l_out и l_inout.

Инициализация глобальных переменных пакета

Как известно, глобальные, публичные и частные, переменные пакета, в т.ч. курсоры, сохраняют состояние в течение сеанса работы с СУБД Oracle. Однако, такое поведение можно отменить с помощью директивы компилятора PRAGMA SERIALLY_REUSABLE.

Вначале приведу пример с сохранением значений глобальных переменных. Виртуальная машина PL/SQL хранит их в области памяти PGA, связанной с текущим сеансом.

SQL> CREATE OR REPLACE PACKAGE test_pkg AS
    -- public package variables
    g_ts1 TIMESTAMP := SYSTIMESTAMP;
    g_ts2 TIMESTAMP;

    FUNCTION get_ts3 RETURN TIMESTAMP;
    FUNCTION get_ts4 RETURN TIMESTAMP;
END test_pkg;
/

SQL> CREATE OR REPLACE PACKAGE BODY test_pkg AS
    -- private package variables
    g_ts3 TIMESTAMP := SYSTIMESTAMP;
    g_ts4 TIMESTAMP;

    FUNCTION get_ts3 RETURN TIMESTAMP IS BEGIN RETURN g_ts3; END;
    FUNCTION get_ts4 RETURN TIMESTAMP IS BEGIN RETURN g_ts4; END;
BEGIN
    -- initialize public and rivate package variables
    g_ts2 := SYSTIMESTAMP;
    g_ts4 := SYSTIMESTAMP;
END test_pkg;
/

SQL> set serveroutput on
SQL> 
SQL> EXEC dbms_output.put_line(test_pkg.g_ts1)
01-MAR-15 01.09.25.041334 PM
PL/SQL procedure successfully completed

SQL> EXEC dbms_lock.sleep(1)
PL/SQL procedure successfully completed
SQL> EXEC dbms_output.put_line(test_pkg.g_ts2)
01-MAR-15 01.09.25.041393 PM
PL/SQL procedure successfully completed

SQL> EXEC dbms_lock.sleep(1)
PL/SQL procedure successfully completed
SQL> EXEC dbms_output.put_line(test_pkg.get_ts3)
01-MAR-15 01.09.25.041388 PM
PL/SQL procedure successfully completed

SQL> EXEC dbms_lock.sleep(1)
PL/SQL procedure successfully completed
SQL> EXEC dbms_output.put_line(test_pkg.get_ts4)
01-MAR-15 01.09.25.041396 PM
PL/SQL procedure successfully completed

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

Директива PRAGMA SERIALLY_REUSABLE инструктирует витруальную машину PL/SQL хранить глобальные переменные пакета не в PGA, а в специальной области SGA, где время жизни этих переменных ограничивается одним вызовом, а не длительностью сеанса:

SQL> CREATE OR REPLACE PACKAGE test_pkg AS
    PRAGMA SERIALLY_REUSABLE;
    
    -- public package variables
    g_ts1 TIMESTAMP := SYSTIMESTAMP;
    g_ts2 TIMESTAMP;

    FUNCTION get_ts3 RETURN TIMESTAMP;
    FUNCTION get_ts4 RETURN TIMESTAMP;
END test_pkg;
/

SQL> CREATE OR REPLACE PACKAGE BODY test_pkg AS
    PRAGMA SERIALLY_REUSABLE;
    
    -- private package variables
    g_ts3 TIMESTAMP := SYSTIMESTAMP;
    g_ts4 TIMESTAMP;

    FUNCTION get_ts3 RETURN TIMESTAMP IS BEGIN RETURN g_ts3; END;
    FUNCTION get_ts4 RETURN TIMESTAMP IS BEGIN RETURN g_ts4; END;
BEGIN
    -- initialize public and rivate package variables
    g_ts2 := SYSTIMESTAMP;
    g_ts4 := SYSTIMESTAMP;
END test_pkg;
/

SQL> EXEC dbms_output.put_line(test_pkg.g_ts1)
01-MAR-15 01.37.58.172515 PM
PL/SQL procedure successfully completed

SQL> EXEC dbms_lock.sleep(1)
PL/SQL procedure successfully completed
SQL> EXEC dbms_output.put_line(test_pkg.g_ts2)
01-MAR-15 01.37.59.330465 PM
PL/SQL procedure successfully completed

SQL> EXEC dbms_lock.sleep(1)
PL/SQL procedure successfully completed
SQL> EXEC dbms_output.put_line(test_pkg.get_ts3)
01-MAR-15 01.38.00.471975 PM
PL/SQL procedure successfully completed

SQL> EXEC dbms_lock.sleep(1)
PL/SQL procedure successfully completed
SQL> EXEC dbms_output.put_line(test_pkg.get_ts4)
01-MAR-15 01.38.01.614801 PM
PL/SQL procedure successfully completed

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

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

SQL> DROP PACKAGE test_pkg;
Package dropped

Перегрузка процедур по имени параметра

И последнее. Мелочь, а приятно:

SQL> CREATE OR REPLACE PACKAGE test_pkg AS
    -- only parameter names differ!!!
    PROCEDURE hello(name VARCHAR2);
    PROCEDURE hello(surname VARCHAR2);
END test_pkg;
/

SQL> CREATE OR REPLACE PACKAGE BODY test_pkg AS

    PROCEDURE hello(name VARCHAR2) IS
    BEGIN
        dbms_output.put_line('Hello, ' || name);
    END;

    PROCEDURE hello(surname VARCHAR2) IS
    BEGIN
        dbms_output.put_line('Hi, ' || surname);
    END;
END test_pkg;
/

SQL> set serveroutput on

SQL> exec test_pkg.hello('Andrey')
...
PLS-00307: too many declarations of 'HELLO' match this call

SQL> exec test_pkg.hello(name => 'Andrey')
Hello, Andrey
PL/SQL procedure successfully completed

SQL> exec test_pkg.hello(surname => 'Andrey')
Hi, Andrey
PL/SQL procedure successfully completed

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

SQL> DROP PACKAGE test_pkg;
Package dropped