Иван Сергеевич Задворьев
Язык PL/SQL
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;
3 l_at2 VARCHAR2(1);
4 BEGIN
5 SELECT at1,at2 INTO l_at1,l_at2 FROM tab1
6 WHERE at1 IN (1,2);
7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_at1)||''||l_at2);
8 END;
9 /
DECLARE
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 5
Если SQL-запрос команды SELECT INTO возвращает больше одной строки, то инициируется предопределенное исключение TOO_MANY_ROWS. Если возвращается пустая выборка, то инициируется другое предопределенное исключение – NO_DATA_FOUND. По этой причине команду SELECT INTO рекомендуется помещать в отдельный блок с обработкой этих исключений:
BEGIN
…
BEGIN
SELECT INTO …
EXCEPTION
WHEN TOO_MANY_ROWS THEN …
WHEN NO_DATA_FOUND THEN …
END;
…
END;
Команда SELECT INTO обычно используется тогда, когда есть уверенность, что ее SQL-запрос вернет ровно одну строку, например, для запроса строк таблицы с условием на значение ее первичного ключа.
Явные курсоры
Объявив SQL-запрос с помощью явного курсора, программист получает полный контроль над этапами его выполнения. Можно определить, когда открыть курсор (OPEN), когда считывать из него строки (FETCH) и когда закрыть курсор (CLOSE).
Объявим курсор cur1:
CURSOR cur1 IS SELECT at1,at2 FROM tab1;
Первым шагом работы с курсором является его открытие:
OPEN cur1;
Считывание строк результирующей выборки из курсора выполняется командой FETCH в набор переменных PL/SQL подходящих типов (число переменных должно совпадать с числом столбцов выборки):
FETCH cur1 INTO l_at1, l_at2;
Полностью код для получения трех строк из tab1 выглядит так:
SQL> DECLARE
2 CURSOR cur1 IS SELECT * FROM tab1;
3 rec tab1%ROWTYPE;
4 BEGIN
5 OPEN cur1;
6 FOR i IN 1..3 LOOP
7 FETCH cur1 INTO rec;
8 DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.at1)||' '||rec.at2);
9 END LOOP;
10 END;
11 /
1 A
2 B
3 C
PL/SQL procedure successfully completed.
После того, как курсор стал ненужным, его следует закрыть:
CLOSE cur1;
Если забыть закрыть явный курсор, как в приведенном выше примере, то можно считать, что запрограммирована утечка памяти в сервере Oracle. Вообще говоря, виртуальная машина PL/SQL автоматически сама закрывает и уничтожает открытые курсоры, как только они оказываются вне области видимости для выполняющегося в настоящий момент блока. Однако делается это не сразу, какое-то время такой курсор существует и остается открытым. У экземпляра Oracle есть ограничение на число одновременно открытых курсоров, которое задается параметром экземпляра open_cursors (по умолчанию параметр выставлен в 300). Если превысить значение этого параметра, то выполнение любого предложения SQL будет завершаться ошибкой. При параллельной работе большого числа сессий это весьма вероятно, поэтому чтобы не сталкиваться с ошибками такого вида, настоятельно рекомендуется аккуратно закрывать курсоры.
Программа, представленная выше, неудачна еще и тем, что цикл FOR со счетчиком предусматривает считывание конкретного числа строк, которых, вообще говоря, может и не быть в результирующей выборке. Строк в выборке может быть больше, чем указано (в данном случае у цикла FOR счетчик изменяется до 3) и тогда какие-то строки останутся несчитанными. Также возможна ситуация, когда число строк в выборке меньше значения счетчика – тогда произойдет повторное считывание последней строки.
Отметим, что повторное считывание из курсора последней строки выборки не приводит к ошибкам. Если в выборке, например, n строк, а команда FETCH выполнена k раз (k>n), то повторные считывания последней (n-й) строки не приведут к инициированию системных исключений, просто последняя строка выборки будет считана и выведена на экран несколько (k-n+1) раз. Для организации перебора строк результирующей выборки предназначены атрибуты явных курсоров, которые рассматриваются далее.
Объявление записей PL/SQL на основе курсоров
Переменная rec, в которую считывались строки результирующей выборки, была объявлена с помощью атрибута %ROWTYPE как запись PL/SQL на основе таблицы tab1. В данном случае это оправдано, потому что в SQL-запросе осуществляется выборка всех столбцов одной таблицы tab1 (SELECT * FROM tab1). Число атрибутов записи PL/SQL будет соответствовать числу столбцов строк выборки и считывание строк пройдет без ошибок.
Однако столбцы результирующей выборки могут быть не из одной, а из нескольких таблиц или вовсе могут являться выражениями:
CURSOR cur_short_person IS
SELECT born,
surname||' '||SUBSTR(name,1,1)||'.'||SUBSTR(secname,1,1)||'.' AS fio,
passport.seria||' '||passport.num AS passport_data
FROM person, passport
WHERE person.id=13243297
AND person.id=passport.r$person
В столбце fio результирующей выборки для каждой строки таблицы person будет результат выражения – фамилия и инициалы (например, Кислов Виктор Михайлович – Кислов В.М.). В столбце passport_data будут паспортные данные из таблицы passport и тоже выражением – серия и номер паспорта, «склеенные» через пробел.
Самый правильный способ определить то, во что будем «принимать» результирующую выборку SQL-запроса курсора – это объявить с помощью атрибута %ROWTYPE переменную-запись PL/SQL, основанную не на схеме одной таблицы, а прямо на курсоре. В этом случае список атрибутов записи PL/SQL по числу столбцов результирующей выборки будет сформирован автоматически.
l_short_person cur_short_person%ROWTYPE;
OPEN cur_short_person;
FETCH cur_short_person INTO l_short_person;
DBMS_OUTPUT.PUT_LINE('ФИО: '||l_short_person.fio);
DBMS_OUTPUT.PUT_LINE('Дата рождения: '||TO_CHAR(l_short_person.born));
DBMS_OUTPUT.PUT_LINE('Паспорт: '||l_short_person.passport_data);
В приведенном коде считывание строки результирующей выборки в запись PL/SQL осуществляется одной короткой командой FETCH без указания столбцов. При появлении новых столбцов во фразе SELECT запроса курсора новые атрибуты также автоматически появятся в записях PL/SQL, объявленных на основе курсора. Таким образом, объявление записей PL/SQL на основе курсоров позволяет писать компактный, поддерживаемый и расширяемый код.
Атрибуты явного курсора
Для управления считыванием строк из явных курсоров используются их атрибуты. В частности, они позволяют выполнить считывание для последующей обработки в программе всех строк результирующей выборки.
Таблица 3. Атрибуты явного курсора.
Атрибут курсора
Описание атрибута
%FOUND
TRUE, если из курсора считана очередная строка