Язык PL/SQL

В случае ошибки преобразования символа к числу (третий столбец таблицы, l_var='a') исключение ошибки преобразования не ловится в разделе EXCEPTION блока 2 и PL/SQL передает управление в родительский блок 1, сразу после END блока 2 и исключение пытаются поймать в разделе EXCEPTION блока 1. В разделе обработки исключений блока 1 есть два обработчика (ZERO_DIVIDE и OTHERS). «Примерка» обработчиков к прилетевшему исключению начинается в той последовательности, как они записаны в коде (сверху вниз). ZERO_DIVIDE для этого исключения не подходит при «примерке» уже второй раз, а OTHERS-обработчик ловит все исключения, поэтому управление передается ему и выполняются две его команды. После успешного выполнения команд обработчика исключение получает статус «обработано».

Передача исключений в вызывающую среду

При разработке клиентских приложений при любом обращении к базе данных нужно предусмотреть обработку ошибок, которые могут произойти как при вызове хранимых программ PL/SQL, так и при выполнении предложений SQL. В коде клиентских программ для этого следует использовать конструкции try/catch, имеющиеся в Java и C, или try/except – в Python.

Если в ходе работы программы PL/SQL произошло так никем и не обработанное исключение, то оно вылетит «наружу», то есть будет передано вызывавшей среде. Например, в SQL*Plus или в прикладное клиентское приложение. В SQL*Plus это выглядит вот так:

Без вылета исключения «наружу»

С вылетом исключения «наружу»

SQL> DECLARE

2 i int;

3 BEGIN

4 i := 1/0;

5 EXCEPTION

6 WHEN OTHERS THEN

7 DBMS_OUTPUT.PUT_LINE('/0');

8 END;

9 /

/0

PL/SQL procedure successfully

completed.

SQL> DECLARE

2 i int;

3 BEGIN

4 i := 1/0;

5 END;

6 /

DECLARE

*

ERROR at line 1:

ORA-01476: divisor is equal to zero

ORA-06512: at line 4

Если же обработка исключений не предусмотрена ни в хранимых программах PL/SQL, ни в клиентском приложении, то исключение PL/SQL пролетит через все вложенные блоки PL/SQL, а потом через весь вызывающий код клиентского приложения. В итоге клиентское приложение покажет пользователю MessageBox с красным кругом или желтым восклицательным знаком, под которым будет написано что-то вроде

En error was encountered performing the requested operation

ORA-00604: error occurred at recursive SQL level 1

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

ORA-06512: at line 7

View program sources of error stack?

Как-то раз один из авторов книги был свидетелем того, как характерная «оракловая» ошибка вида ORA-123456 выскочила в сообщении приложения банковской информационной системы, когда операционистка оформляла вклад. Девушка сначала некоторое время пыталась понять, что же ей программа хочет сказать на английском языке, потом подозвала более опытного коллегу, который со словами «Это нормально, бывает» закрыл сообщение, и оформление продолжилось.

Это не нормально. Все возможные исключения в программах PL/SQL должны обрабатываться, причем продуманным унифицированным способом. В книге Стивена Фейерштейна «PL/SQL для профессионалов» теме обработки исключений посвящена отдельная глава объемом 34 страницы, что больше, чем написано в этой книге про условные команды и циклы вместе взятые.

Диагностические функции

В PL/SQL имеется несколько диагностических функций для получения информации об исключениях:

SQLCODE – возвращает код ошибки последнего исключения, инициированного в блоке PL/SQL;

SQLERRM – возвращает сообщение об ошибке последнего исключения, инициированного в блоке PL/SQL;

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE – возвращает отформатированную строку с содержимым стека программ и номеров строк кода.

Максимальная длина строки, возвращаемой функцией SQLERRM, составляет 512 байт. Из-за этого ограничения рекомендуется использовать вместо SQLERRM функцию встроенного пакета DBMS_UTILITY.FORMAT_ERROR_STACK, которая выводит строку с отформатированным стеком сообщений. Приведем несколько примеров использования диагностических функций:

SQL> CREATE OR REPLACE PROCEDURE error_proc IS

2 i INTEGER;

3 BEGIN

4 i := 1/0;

5 i := 15;

6 END;

7 /

Procedure created.

SQL> CREATE OR REPLACE PROCEDURE parent_proc IS

2 BEGIN

3 error_proc;

4 END;

5 /

Procedure created.

SQL> BEGIN

2 parent_proc;

3 EXCEPTION

4 WHEN OTHERS THEN

5 DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);

6 DBMS_OUTPUT.PUT_LINE('SQLERRM: '||SQLERRM);

7 DBMS_OUTPUT.PUT_LINE('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:');

8 DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

9 END;

10 /

SQLCODE: -1476

SQLERRM: ORA-01476: divisor is equal to zero

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE:

ORA-06512: at "U1.ERROR_PROC", line 4

ORA-06512: at "U2.PARENT_PROC", line 3

ORA-06512: at line 2

PL/SQL procedure successfully completed.

По строке, которую вернула FORMAT_ERROR_BACKTRACE, видно, как пролетало системное исключение по строкам кода: сначала она возникла в процедуре error_proc на четвертой строке, управление из error_proc сразу вернулось в родительский для error_proc блок – процедуру parent_proc (на третью строку, где вызывалась error_proc). Далее выводятся сведения о второй строке анонимного блока, в котором вызывалась parent_proc. При этом в стеке появились три ошибки ORA-06512.

Также видно, что функция DBMS_UTILITY.FORMAT_ERROR_BACKTRACE не выдает сообщение о самой исходной ошибке, поэтому совместно с этой функцией следует использовать функции SQLERRM или DBMS_UTILITY.FORMAT_ERROR_STACK.

Пользовательские исключения

Пользовательские исключения объявляются следующим образом:

имя исключения EXCEPTION;

Пользовательские исключения инициируются командой RAISE, у которой есть две формы:

RAISE имя исключения (исключение должно быть предопределенным в пакете STANDARD или объявленным в области видимости);

RAISE (может быть вызвана только внутри обработчиков исключений, когда в обработчике нужно повторно инициировать то же самое исключение).

Приведем пример работы с пользовательскими исключениями:

DECLARE

l_amount INTEGER := -100;

l_crncy VARCHAR2(3) := 'RUR';

ex_negative_payment_amount EXCEPTION;

ex_non_rur_payment EXCEPTION;

BEGIN

IF l_amount < 0 THEN

RAISE ex_negative_payment_amount;

END IF;

IF l_crncy <> 'RUR' THEN

RAISE ex_non_rur_payment;

END IF;

… все проверки пройдены, обрабатываем платеж

EXCEPTION

WHEN ex_negative_payment_amount THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: сумма отрицательная: '||l_amount);

WHEN ex_non_rur_payment THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: платеж не в рублях');

END;

Видно, что код имеет линейный вид: проверки записаны одна за одной и если платеж не проходит проверку, то управление сразу переходит в раздел обработки исключений. Без исключений код выглядит нелинейно – как несколько ветвей команды IF:

DECLARE

l_amount INTEGER := -100;

l_crncy VARCHAR2(3) := 'RUR';

ex_negative_payment_amount EXCEPTION;

ex_non_rur_payment EXCEPTION;

BEGIN

IF l_amount < 0 THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: сумма отрицательная: '||l_amount);

ELSE

– второй уровень вложенности IF

IF l_crncy <> 'RUR' THEN

DBMS_OUTPUT.PUT_LINE('Ошибка: платеж не в рублях');

ELSE

– потом будет третий уровень вложенности IF

… наконец все проверки пройдены, обрабатываем платеж

END IF;

END IF;

END;

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

Посмотрим на поведение диагностических функций при инициировании пользовательских исключений:

SQL> DECLARE

2 exception1 EXCEPTION;

3 BEGIN

4 RAISE exception1;

5 EXCEPTION

6 WHEN OTHERS THEN

7 DBMS_OUTPUT.PUT_LINE('SQLCODE print: '||SQLCODE);

8 DBMS_OUTPUT.PUT_LINE('SQLERRM print: '||SQLERRM);

9 END;

10 /

SQLCODE print: 1

SQLERRM print: User-Defined Exception

PL/SQL procedure successfully completed.

Видно, что пользовательские исключения имеют код ошибки 1, а сообщение об ошибке малоинформативно. Поэтому при работе над кодом нужно следить, чтобы пользовательские исключения обрабатывались соответствующими им обработчиками, а не OTHERS-обработчиком, внутри которого нельзя узнать, какое именно пользовательское исключение прилетело. И уж тем более пользовательские исключения PL/SQL не должны вылетать «наружу» в вызывающую среду.

Процедура RAISE_APPLICATION_ERROR

Если пользовательское исключение все-таки вылетит «наружу» в вызывающую среду, то независимо от того, какое исключение вылетело, «снаружи» выглядеть это будет одинаково – как ошибка ORA-06510.

Исключение exception1

Исключение exception2

SQL> DECLARE

2 exception1 EXCEPTION;

3 BEGIN

4 RAISE exception1;

5 END;

6 /

DECLARE

*

ERROR at line 1:

ORA-06510: PL/SQL:

unhandled user-defined exception

ORA-06512: at line 4

SQL> DECLARE

2 exception2 EXCEPTION;

3 BEGIN

4 RAISE exception2;

5 END;

6 /

DECLARE

*

ERROR at line 1:

ORA-06510: PL/SQL:

unhandled user-defined exception

ORA-06512: at line 4

С системными исключениями дело обстоит иначе – разные системные исключения вылетают «наружу» с разными кодами и сообщениями, что позволяет их обрабатывать в вызывающей среде, так как по коду понятно, какая ошибка произошла.

Как отмечалось выше, системные исключения автоматически инициируются виртуальной машиной PL/SQL при возникновении программных ошибок этапа выполнения. В то же время есть возможность инициировать системные исключения и вручную. Для этого используется процедура RAISE_APPLICATION_ERROR, которая инициирует системные исключения с задаваемыми программистом сообщениями и номерами ошибок из диапазона [-20999,-20000].

SQL> DECLARE

2 l_error_number INTEGER := -20187;

3 l_error_message VARCHAR2(100) := 'Отрицательная сумма платежа';

4 l_amount INTEGER := -10;

5 BEGIN

6 IF l_amount < 0 THEN

7 RAISE_APPLICATION_ERROR(l_error_number,l_error_message);

8 END IF;

9 END;

10 /

DECLARE

*

ERROR at line 1:

ORA-20187: Отрицательная сумма платежа

ORA-06512: at line 7

Привязка пользовательских исключений к ошибкам

В рассмотренных примерах работы с исключениями часто использовались предопределенные исключения, например, исключение ZERO_DIVIDE, которое соответствует ошибке ORA-01476 Divisor is equal to zero. В PL/SQL есть возможность практически к любой ошибке сервера привязать пользовательское исключение и ловить ошибки по именам таких исключений, а не в OTHERS-обработчике.

Для привязки ошибки к пользовательскому исключению достаточно узнать номер ошибки и записать соответствующую директиву компилятору:

PRAGMA EXCEPTION_INIT (имя пользовательского исключения, номер ошибки)

Обработаем ошибку преобразования символьного значения в дату по заданной маске с использованием привязанного исключения:

– сначала специально получаем ошибку и узнаем ее номер,

– это вспомогательный шаг

SQL> DECLARE

2 v1 DATE;

3 BEGIN

4 v1:=TO_DATE('abc','dd.mm.yyyy'); – ожидается не abc, а дата по маске

5 END;

6 /

DECLARE

*

ERROR at line 1:

ORA-01858: a non-numeric character was found where a numeric was expected

ORA-06512: at line 4

– объявляем исключение и привязываем его к ORA-01858

SQL> DECLARE

2 v1 DATE;

3 to_date_convert_error EXCEPTION;

4 PRAGMA EXCEPTION_INIT (to_date_convert_error, -01858);

5 BEGIN

6 v1:=TO_DATE('abc','dd.mm.yyyy');

7 EXCEPTION

8 WHEN to_date_convert_error THEN

9 DBMS_OUTPUT.PUT_LINE('Ошибка преобразования строки в дату');

10 END;

11 /

Ошибка преобразования строки в дату

PL/SQL procedure successfully completed.

Если бы возможности привязки к ошибкам пользовательских исключений не было, то пришлось бы все ошибки обрабатывать в OTHERS-обработчике примерно так:

BEGIN

v1:=TO_DATE('abc','dd.mm.yyyy');

EXCEPTION

WHEN OTHERS THEN

IF SQLCODE = -01858 THEN

DBMS_OUTPUT.PUT_LINE('Ошибка преобразования строки в дату');

END IF;

IF SQLCODE = -… THEN

…

END IF;

… и так для каждой ожидаемой ошибки

… (в конце ELSE-ветвь – для неожидаемых ошибок)

END;

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

Хорошим стилем является объявление в одном месте кода всех пользовательских исключений с привязкой к ошибкам. Далее эти ошибки можно ловить по именам исключений и иметь отдельные обработчики с обозримым объемом кода для каждой ошибки. При необходимости можно разделить ошибки на критичные и некритичные. Некритичные ошибки следует ловить и обрабатывать с продолжением вычислений (например, в циклах обычно обрабатывается ошибка на сбойной итерации и происходит переход на следующую итерацию цикла). Критичные ошибки «катапультировать» через все вложенные блоки, вызывая во всех обработчиках исключений команду RAISE до тех пор, пока критичная ошибка не долетит до раздела обработки критичных ошибок.

Использование обработчика OTHERS

Как было сказано ранее, обработчик OTHERS указывается последним в разделе обработки исключений. Он ловит все исключения, которые не поймали обработчики, перечисленные раньше него. Чаще всего обработчик OTHERS используется для того, чтобы обрабатывать системные исключения, инициируемые из-за возникновения ошибок.

При неправильном использовании наличие обработчика OTHERS может стать причиной «потери» для клиентских приложений ошибок в программах PL/SQL. Все вызовы программы PL/SQL с обработчиком WHEN OTHERS в разделе обработки исключений самого внешнего блока будут завершаться успешно, никакие ошибки «наружу» вылетать не будут. При этом в программе на P/SQL могут происходить и фатальные ошибки, но о них никто сразу не узнает, они будут «подавлены» в разделе обработки исключений.

Особенно плохой практикой является использование обработчиков вида

BEGIN

…

EXCEPTION

WHEN OTHERS THEN NULL;

END;

В этом случае исключение даже никак не регистрируется и просто теряется. Один из авторов в унаследованной системе столкнулся с тем, что при загрузке с помощью SQL*Loader некоторые строки то загружались, то не загружались. После нескольких часов жизни, напрасно потерянных на проверку различных гипотез, случайно был обнаружен триггер, который срабатывал на вставку строк предложением INSERT. В этом триггере осуществлялось преобразование строки в дату, которое то было успешным, то завершалось ошибкой. Ошибки эти обрабатывались так, как показано выше, то есть в новых записях то проставлялись даты, то нет. В результате в логах SQL*Loader появлялись сообщения об ошибках нарушения ограничения целостности для столбца с датами, а причина этого была неясна.

В том памятном случае разработчик триггера сделал три ошибки сразу:

понадеялся на неявное преобразование строки в дату без указания маски (плохая практика) и в этом была причина ошибки, которая то была, то нет;

с помощью изменения псевдозаписей :NEW тихо подменял значения столбцов добавляемых в таблицу строк в BEFORE-триггере (очень плохая практика);

подавлял происходящие ошибки в обработчике OTHERS (очень-очень плохая практика).

SQL в программах PL/SQL

Выполнение SQL в программах PL/SQL

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

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

При подключении к серверу Oracle клиентской программы создается выделенный серверный процесс, который обрабатывает поступающие предложения SQL и вызовы программ PL/SQL. Эта обработка происходит на нескольких уровнях ядра Oracle, при этом собственно выполнение и SQL и PL/SQL осуществляется на одном и том же уровне – уровне выполнения (Execution Layer (KX)). Когда выполняется какое-то предложение SQL, то действия процесса осуществляются в контексте SQL, а когда происходит вызов программы PL/SQL, то действия процесса осуществляются в контексте PL/SQL. Когда же в ходе работы программы PL/SQL потребуется выполнить какое-нибудь предложение SQL из ее байт-кода, то произойдет переключение контекста PL/SQL-SQL и это предложение SQL будет выполнено серверным процессом на уровне KX точно так же, как будто бы оно поступило не из программы PL/SQL, а из любой другой программы. После обработки предложения SQL произойдет обратное переключение контекста SQL-PL/SQL.

Достоинства использования PL/SQL для выполнения предложений SQL заключаются в следующем

в PL/SQL есть удобные и лаконичные языковые конструкции обработки результирующих выборок SQL-запросов;

компилятор PL/SQL по исходному коду программы PL/SQL формирует предложения SQL со связываемыми переменными, использование которых позволяет избежать многих проблем с сервером Oracle;

PL/SQL автоматически оптимально управляет курсорами – важнейшими внутренними механизмами Oracle для выполнения предложений SQL;

в PL/SQL есть средства дополнительной оптимизации для массовой обработки (bulk collect) данных.

Можно сказать, что PL/SQL – это такой своеобразный движок (engine) для отправки предложений SQL на выполнение и работы с возвращаемыми ими результатами. Движок этот работает в ядре сервера Oracle и написан сотрудниками самой компании Oracle, поэтому он является очень эффективным средством реализации бизнес-логики с использованием языка SQL.

Выборка данных c использованием курсоров

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

Приведем цитату из интервью с Майклом Стоунбрейкером.

«…Сейчас мы общаемся с базами данных, используя ODBC и JDBC, встроенные в языки программирования. Это наихудшие интерфейсы на нашей планете. Я имею в виду, что они настолько ужасны, что их не пожелаешь даже злейшему врагу.

Взгляните на такой язык, как Ruby on Rails (www.rubyonrails.org). Этот язык расширен встроенными средствами доступа к базам данных. Не нужно обращаться к SQL; достаточно сказать: «for E in employee do», и для доступа к базе данных используются языковые конструкции и переменные. Это существенно облегчает работу программиста…».

Каркас приложений (framework) «Ruby на рельсах» для модного языка Ruby появился в 2004 году, а еще за 15 лет до этого в языке PL/SQL уже имелся курсорный цикл FOR и достаточно было написать «FOR E in (SELECT * FROM employee) LOOP». Простой и элегантный код.

Понятие курсора

Напомним, что к DML-предложениям языка SQL относятся предложения INSERT, UPDATE, DELETE и предложение SELECT, которое дальше будет также называться SQL-запросом. Курсором (cursor) в Oracle называется именованный указатель на приватную рабочую область в памяти, используемую в ходе обработки DML-предложений. Выполняя действия с курсором, можно получить доступ к результирующей выборке связанного в текущий момент времени с этим курсором SQL-запроса и к другим сведениям о ходе обработки SQL, например, получить число обработанных строк для предложений INSERT, UPDATE, DELETE.

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

В PL/SQL есть явные и неявные курсоры (explicit and implicit cursors):

явные курсоры объявляются с указанием текстов SQL-запросов в разделах объявлений блоков PL/SQL;

неявные курсоры используются при выполнении команд SELECT INTO и команд INSERT, UPDATE и DELETE.

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

Явный курсор имеет имя, указываемое при объявлении курсора, и все действия с таким курсором должны быть явно указаны в исходном коде.

Код программы на языке PL/SQL состоит из команд PL/SQL. Отметим, что рассматриваемые далее INSERT, DELETE, UPDATE и SELECT INTO – это именно команды PL/SQL, а не предложения SQL, хотя и очень на них похожие. Для текста таких команд PL/SQL компилятором осуществляется препроцессинг, то есть обработка исходного кода для передачи на следующий шаг компиляции. Эта обработка заключается в подготовке предложений SQL для последующего их размещения в байт-коде программ PL/SQL, причем текст SQL будет отличаться от того текста, который был в соответствующих командах PL/SQL. Например, все переменные PL/SQL будут заменены на связываемые переменные SQL, а текст сформированных предложений SQL приведен к верхнему регистру.

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

Для неявных курсоров компилятор эти вызовы разместит в байт-коде автоматически, для явных курсоров – по командам PL/SQL, явно заданным программистом в исходном коде. Ответственность за правильное расположение этих команд лежит на программисте. Нарушение последовательности действий с явным курсором приводит к ошибкам этапа выполнения. Если, например, попытаться считать запись из неоткрытого курсора, то будет инициировано системное исключение.

Неявные курсоры для выборки данных

Неявный курсор для выборки данных используется для команды PL/SQL SELECT INTO, обладающей следующими свойствами:

результирующая выборка SQL-запроса должна содержать ровно одну строку (не ноль строк, не две, не три строки, а ровно одну);

конструкция INTO представляет собой механизм передачи значений столбцов строки выборки в переменные программы PL/SQL.

Рассмотрим пример.

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

CREATE TABLE tab1 (at1 NUMBER, at2 VARCHAR2(1));

INSERT INTO tab1 VALUES (1, 'A');

INSERT INTO tab1 VALUES (2, 'B');

INSERT INTO tab1 VALUES (3, 'C');

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

SQL> DECLARE

2 l_at1 NUMBER;

3 l_at2 VARCHAR2(1);

4 BEGIN

5 SELECT at1,at2 INTO l_at1,l_at2

6 FROM tab1 WHERE at1=1;

7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_at1)||''||l_at2);

8 END;

9 /

1 A

SQL> DECLARE

2 l_at1 NUMBER;

3 l_at2 VARCHAR2(1);

4 BEGIN

5 SELECT at1,at2 INTO l_at1,l_at2

6 FROM tab1 WHERE at1=4;

7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_at1)||''||l_at2);

8 END;

9 /

DECLARE

*

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 5

SQL> DECLARE

2 l_at1 NUMBER;