Автор: Стивен Ферстайн, член-директор 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.