Достоинства использования 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 будет завершаться ошибкой. При параллельной работе большого числа сессий это весьма вероятно, поэтому чтобы не сталкиваться с ошибками такого вида, настоятельно рекомендуется аккуратно закрывать курсоры.