DeepEdit!

Автор: Стивен Ферстайн, член-директор Oracle ACE

Я только что вернулся из мира Java в PL/SQL. Одна из возможностей Java, отсутствие которых я реально почувствовал в PL/SQL, это секция метода FINALLY. Как получить подобную функциональность при выходе из PL/SQL?

В отличие от Java, PL/SQL не поддерживает секцию FINALLY. Однако многое из того, что она делает, можно эмулировать с помощью внимательного и упорядоченного использования локальных подпрограмм.

Сначала посмотрим, как FINALLY работает в Java, затем я объясню, почему она была бы полезна в PL/SQL, и, наконец, покажу, как её эмулировать.

В Java секция FINALLY всегда выполняется при завершении секции TRY - даже если возникает необработанное исключение. Секция FINALLY гарантирует, что cleanup-логика не пропущена и не проигнорирована, где бы и как ни завершилась программа. Программист не должен специально включать эту секцию или вызывать её код. Java-машина автоматически выполняет её перед тем, как вернуть управление из метода.

Cleanup-логика, необходимая при выполнении PL/SQL

В PL/SQL есть несколько действий, которые требуют явных cleanup-предложений, включая следующие:

• Открытие файла с помощью UTL_FILE.FOPEN. Я должен позже закрыть файл, используя UTL_FILE.FCLOSE; иначе он останется открытым до тех пор, пока соединение не завершится или пока не будет вызван UTL_FILE.FCLOSE_ALL, чтобы закрыть все файлы, открытые сессией.
• Открытие курсора с помощью DBMS_SQL.OPEN_CURSOR. Я должен закрыть курсор, используя DBMS_SQL.CLOSE_CURSOR, или этот курсор останется открытым до тех пор, пока соединение не завершится.
• Выделение памяти для пакетных переменных. Переменные, объявленные на уровне пакета, сохраняют значения (и память, выделенную для этих значений) на протяжении сессии, даже если блок, в котором значение было присвоено, завершится. Если я не хочу, чтобы эта память продолжала быть занятой значениями переменных, необходимо явно освободить память.

Давайте посмотрим на программу, которая работает с файлами и динамическим SQL, - и проблемы, которые могут возникнуть, если не выполнить после себя полную зачистку. Я буду использовать типичную распространённую поверхностную методологию для небрежной программы (exec_sql_from_file), которая читает файл и выполняет его содержимое, как единичное SQL-предложение, используя DBMS_SQL. Я предполагаю, что это метод только с динамическим SQL-предложением (DDL или DML и без каких-либо bind-переменных).

Вот описание процедуры exec_sql_from_file из Листинга 1:

Листинг 1: exec_sql_from_file (до эмуляции FINALLY)

  1  PROCEDURE exec_sql_from_file (
  2         dir_in    IN     VARCHAR2
  3       , file_in   IN     VARCHAR2
  4  )
  5  IS
  6     l_file         UTL_FILE.file_type;
  7     l_lines       DBMS_SQL.varchar2a;
  8     l_cur         PLS_INTEGER;
  9     l_exec       PLS_INTEGER;
 10  BEGIN
 11     BEGIN
 12        l_file := UTL_FILE.fopen (dir_in, file_in, 'R');
 13
 14        LOOP
 15           UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));
 16        END LOOP;
 17     EXCEPTION
 18        WHEN NO_DATA_FOUND
 19        THEN
 20             /* Все данные из файла прочитаны. */
 21             NULL;
 22     END;
 23
 24     l_cur := DBMS_SQL.open_cursor;
 25     DBMS_SQL.parse (l_cur
 26                            , l_lines
 27                            , l_lines.FIRST
 28                           , l_lines.LAST
 29                            , TRUE
 30                           , DBMS_SQL.native
 31                             );
 32     l_exec := DBMS_SQL.EXECUTE (l_cur);
 33 END exec_sql_from_file;

Строки 12-22. Использование UTL_FILE для открытия указанного файла, и чтение его содержимого в массив, который объявлен как тип DBMS_SQL.

Строки 18-21. Когда UTL_FILE.GET_LINE считывает конец файла, возникает исключение NO_DATA_FOUND. Это исключение отлавливается и затем используется предложение NULL для того, чтобы сообщить программе о необходимости продолжения.

Строки 24-32. Использование перегрузки DBMS_SQL.PARSE (которая принимает массив строк) для разбора всего содержимого файла и последующего выполнения курсора. Эти строки выполняют динамическую SQL-операцию. Такое применение SQL и перегрузки с массивами будет работать во всех версиях Oracle Database, но заметьте, что в Oracle Database 11g, как DBMS_SQL.PARSE, так и EXECUTE IMMEDIATE, есть и CLOB, поэтому больше не надо будет использовать перегрузку с массивом для очень больших (больше 32K) SQL-предложений.

Итак, в PL/SQL нужно только 33 строки кода для реализации процедуры, которая читает содержимое файла и выполняет его как SQL-предложение. К сожалению, это очень грязный код. Я пренебрёг реализацией этапа зачистки: закрытием файла и закрытием курсора. Как результат, файл остаётся открытым на протяжении моей сессии (или до тех пор, пока я не вызову UTL_FILE.FCLOSE_ALL). Курсор также остаётся открытым до тех пор, пока я не отключусь.

Эмуляция Finally

Теперь я покажу, как самым похожим образом эмулировать поведение выражения FINALLY в PL/SQL с помощью локальных cleanup-подпрограмм.

Чтобы убедиться в том, что очистка выполнена правильно и закрыты все открытые ресурсы, необходимо добавить две строки в конце процедуры (между строками 32 и 33 на Листинге 1):

UTL_FILE.fclose (l_file);
DBMS_SQL.close_cursor (l_cur);

Они выполнятся? Только если никогда не будет проблем с выполнением этой программы.

В реальности весьма вероятно, что если что-то может случиться, то это непременно случится. Поэтому в случае с exec_sql_from_file необходимо добавить обработку исключения для ловушки ошибок, записать информацию об ошибке в журнал, как принято в стандартах моего приложения, очистить последствия программы, и снова инициировать исключение.

Нижеследующий код добавляет описанную ранее cleanup-логику в секцию exception в конце процедуры exec_sql_from_file (между строками 32 и 33 на Листинге 1):

   UTL_FILE.fclose (l_file);
   DBMS_SQL.close_cursor (l_cur);
EXCEPTION
   WHEN OTHERS 
   THEN
      log_error ();
      UTL_FILE.fclose (l_file);
      DBMS_SQL.close_cursor (l_cur);
   RAISE;

Теперь у меня есть весьма умная процедура, которая убирает за собой, независимо от того, завершилась ли она успешно или с ошибкой. Однако лучше было бы не дублировать cleanup-код в нескольких местах.

Далее, код секции exception предполагает, что и файл, и курсор открыты. Если проблема возникает при чтении файла, я никогда не получу даже динамической SQL-части моей программы (строки с 24 по 32). Таким образом, можно попытаться закрыть курсор, который не был открыт, и получить исключение. Ошибка, которая будет инициирована, зависит от версии Oracle Database. (Если применяется Oracle Database 11g, это действие отключит использование DBMS_SQL для всей моей сессии и потребует переподсоединения.)

На самом деле закрыть ресурс необходимо только в том случае, если он открыт, и это усложняет cleanup-код, который необходимо написать. Я мог бы просто добавить этот код в секцию exception, но что будет, если потребуется инициировать это исключение? Необходимо будет и там выполнять зачистку, продублировав ещё больше кода. Моя программа будет намного более изящной и простой в сопровождении, если собрать всю cleanup-логику в одной многократно используемой подпрограмме.

Поэтому я реализую в exec_sql_from_file маленькую локальную подпрограмму, которая выполняет все мои операции по зачистке:

PROCEDURE exec_sql_from_file (
   dir_in    IN   VARCHAR2
 , file_in   IN   VARCHAR2
)
IS
   ... объявления до ...

   PROCEDURE cleanup
   IS
   BEGIN
      IF SQLCODE <> 0
      THEN
         log_error ();
      END IF;

      IF UTL_FILE.is_open (l_file) 
      THEN
         UTL_FILE.fclose (l_file);
      END IF;

      IF DBMS_SQL.is_open (l_cur) 
      THEN
         DBMS_SQL.close_cursor (l_cur);
      END IF;
   END cleanup;

Эта cleanup-программа вызывается в обеих точках выхода из процедуры exec_sql_from_file: успешное завершение (конец исполняемой секции) и возникновение какой-нибудь ошибки (в выражении WHEN OTHERS). Следующий код предполагает, что cleanup-процедура добавлена в процедуру exec_sql_from_file и заменяет последнюю строку exec_sql_from_file Листинга 1 на:

   cleanup ();
EXCEPTION
   WHEN OTHERS
   THEN
      cleanup ();
      RAISE;
END exec_sql_from_file;

Листинг 2 показывает изменённую процедуру exec_sql_from_file с эмуляцией FINALLY.

Листинг 2: exec_sql_from_file (с эмуляцией finally)

PROCEDURE exec_sql_from_file (
   dir_in    IN   VARCHAR2
 , file_in   IN   VARCHAR2
  )
  IS
     l_file    UTL_FILE.file_type;
     l_lines   DBMS_SQL.varchar2a;
     l_cur     PLS_INTEGER;
     l_exec    PLS_INTEGER;

PROCEDURE cleanup
IS
BEGIN
   IF SQLCODE <> 0
   THEN
      log_error ();
   END IF;

   IF UTL_FILE.is_open (l_file) 
   THEN
      UTL_FILE.fclose (l_file);
   END IF;

   IF DBMS_SQL.is_open (l_cur) 
   THEN
       DBMS_SQL.close_cursor (l_cur);
   END IF;
END cleanup;

BEGIN
    l_file := UTL_FILE.fopen (dir_in, file_in, 'R');

    LOOP
       UTL_FILE.get_line (l_file, l_lines (l_lines.COUNT + 1));
    END LOOP;

EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
         /* Все данные из файла прочитаны. */
         NULL;
END;

BEGIN
   l_cur := DBMS_SQL.open_cursor;

   DBMS_SQL.parse (l_cur
                       , l_lines
                       , l_lines.FIRST
                       , l_lines.LAST
                       , TRUE
                       , DBMS_SQL.native
                         );

   l_exec := DBMS_SQL.EXECUTE (l_cur); 

cleanup ();

EXCEPTION
    WHEN OTHERS
    THEN
         cleanup ();
         RAISE;

END exec_sql_from_file;

Такой метод группировки всей cleanup-логики в единственной подпрограмме и затем её вызова в конце исполняемой секции и в каждом обработчике исключений - самый близкий из возможных для эмуляции на PL/SQL выражения FINALLY из Java.