Достоинства использования 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, если из курсора считана очередная строка
%NOTFOUND
FALSE, если из курсора считана очередная строка
%ROWCOUNT
количество считанных до настоящего момента строк
%ISOPEN
TRUE, если курсор открыт
Основная нагрузка при считывании всех строк результирующей выборки ложится на атрибуты курсора %NOTFOUND и %FOUND, которые всегда находятся в связкелибо принимают противоположные логические значения TRUE и FALSE, либо оба UNKNOWN.
Атрибут %FOUND равен TRUE и атрибут %FOUND равен FALSE в то время, пока команда FETCH считывает из курсора все новые и новые (очередные) строки. После того, как последняя строка результирующей выборки будет считана дважды (второй раз, выходит, уже не как очередная), атрибут курсора %FOUND станет FALSE, а %NOTFOUND станет TRUE. На этом поведении атрибутов курсора обычно и формируется условие выхода из циклов, предназначенных для считывания из курсора всех строк результирующей выборки.
Еще одним важным фактом является то, что после открытия курсора, но до выполнения первой команды FETCH, атрибуты %FOUND и %NOTFOUND имеют неопределенное логическое значение (UNKNOWN). Если это не учитывать, то можно совершить одну из распространенных ошибокв цикле WHILE с условием на истинность атрибута %FOUND цикл не будет выполнен ни разу, несмотря на то, что в результирующей выборке есть строки. Выполнить команду FETCH первый раз надо еще до входа в цикл, тем самым проинициализировав атрибуты курсора.
Приведем пример использования атрибутов курсора в цикле WHILE для считывания всех строк результирующей выборки.
SQL> DECLARE
2 CURSOR cur1 IS SELECT * FROM tab1;
3 rec cur1%ROWTYPE;
4 BEGIN
5 OPEN cur1;
6 FETCH cur1 INTO rec;
7 WHILE cur1%FOUND LOOP
8 DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.at1)||' '||rec.at2);
9 FETCH cur1 INTO rec;
10 END LOOP;
11 CLOSE cur1;
12 END;
13 /
1 A
2 B
3 C
PL/SQL procedure successfully completed.
Еще один пример показывает использование атрибутов курсора для считывания всех строк выборки в простом цикле LOOP END LOOP с условием выхода EXIT WHEN.
SQL> DECLARE
2 TYPE tab1_rec_type IS RECORD
3 (arg1 tab1.at1%TYPE,
4 arg2 tab1.at2%TYPE);
5 tab1_rec tab1_rec_type;
6 CURSOR cur1 IS SELECT * FROM tab1;
7 BEGIN
8 OPEN cur1;
9 LOOP
10 EXIT WHEN (cur1%NOTFOUND);
11 FETCH cur1 INTO tab1_rec;
12 DBMS_OUTPUT.PUT_LINE(cur1%ROWCOUNT||' '||tab1_rec.arg2);
13 END LOOP;
14 CLOSE cur1;
15 END;
/
1 A
2 B
3 C
3 C
PL/SQL procedure successfully completed.
Обратите внимание на повторный вывод последней строки (3 С). Это еще одна распространенная ошибка. В ходе проведения занятий со студентами авторы десятки раз видели считывание и обработку последней строки выборки дважды. Системное исключение при повторном считывании последней строки выборки, напомним, не инициируется, поэтому такие ошибки в коде трудно обнаруживаются.
Рекомендуется после написания кода, реализующего считывание и обработку всех строк выборки, проверить его с помощью небольших тестов на отсутствие двух распространенных ошибок:
цикл считывания не выполняется ни разу;
последняя строка выборки в цикле обрабатывается дважды.
В приведенном выше примере показано, что значение %ROWCOUNT увеличивается на единицу с каждой считанной строкой, а не отражает общее число отобранных SQL-запросом строк. Видно и что повторное считывание последней строки выборки не влияет на значение атрибута %ROWCOUNT: оно остается равным значению, присвоенному при первом считывании последней строки. В примере значение атрибута %ROWCOUNT как стало равным трем при первом считывании последней строки, так и осталось без изменений после еще одного считывания.
Курсорный цикл FOR
Курсорный цикл FOR позволяет в цикле обработать все строки результирующей выборки SQL-запроса.
SQL> DECLARE
2 CURSOR cur1 IS SELECT at1,at2 FROM tab1;
3 v1 VARCHAR2(4000);
4 BEGIN
5 FOR rec IN cur1 LOOP
6 v1:=LTRIM(v1||' '||rec.at2);
7 END LOOP;
8 DBMS_OUTPUT.PUT_LINE(v1);
9 END;
10 /
A B C
PL/SQL procedure successfully completed.
Обратите внимание, переменная rec, в которую в цикле считываются данные, не требует объявления. Она будет являться записью PL/SQL, такой же, как записи PL/SQL, объявленные с помощью атрибута %ROWTYPE на основе курсора.
Все очень просто. Не нужно явно открывать и закрывать курсор. Вместо команды FETCH просто следует обратиться к текущему значению записи PL/SQL, которая здесь является своеобразной управляющей переменной цикла. Для выхода из цикла больше не нужно проверять атрибуты курсора %NOTFOUND и %FOUND. Если SQL-запрос не отберет ни одной строки, тело цикла просто не выполнится ни разу, если же результирующая выборка непустая, то после перебора всех строк цикл завершится автоматически.
По сути, программист тремя строчками кода говорит компилятору PL/SQL «Мне нужна каждая строка результирующей выборки, и я хочу, чтобы она была помещена в запись PL/SQL, соответствующую курсору». Компилятор PL/SQL формирует соответствующий байт-код со всеми низкоуровневыми вызовами сервера.
Простейший вариант курсорного цикла FOR имеет SQL-запрос, встроенный прямо в описание цикла:
SQL> DECLARE
2 v1 VARCHAR2(4000);