Одной из важнейших характеристик PL/SQL
является тесная интеграция с базой данных Oracle в отношении как изменения данных в таблицах, так и выборки данных из таблиц. В этом блоге рассматриваются элементы PL/SQL
, связанные с выборкой информации из базы данных и ее обработкой в программах PL/SQL
.
При выполнении команды SQL
из PL/SQL РСУБД
Oracle назначает ей приватную рабочую область, а некоторые данные записывает в системную глобальную область (SGA, System Global Area). В приватной рабочей области содержится информация о команде SQL и набор данных, возвращаемых или обрабатываемых этой командой. PL/SQL предоставляет программистам несколько механизмов доступа к этой рабочей области и содержащейся в ней информации; все они так или иначе связаны с определением курсоров и выполнением операций с ними.
- Неявные курсоры. Команда
SELECT
.. .INTO
считывает одну строку данных и присваивает ее в качестве значения локальной переменной программы. Это простейший (и зачастую наиболее эффективный) способ доступа к данным, но он часто ведет к написанию сходных и даже одинаковыхSQL
-командSELECT
во многих местах программы. - Явные курсоры. Запрос можно явно объявить как курсор в разделе объявлений локального блока или пакета. После этого такой курсор можно будет открывать и выбирать из него данные в одной или нескольких программах, причем возможности управления явным курсором шире, чем у неявного.
- Курсорные переменные. Курсорные переменные (в объявлении которых задается тип
REF CURSOR
) позволяют передавать из программы в программу указатель на результирующий набор строк запроса. Любая программа, для которой доступна такая переменная, может открыть курсор, извлечь из него необходимые данные и закрыть его. - Курсорные выражения. Ключевое слово
CURSOR
превращает командуSELECT
в набор REF CURSOR, который может использоваться совместно с табличными функциями для повышения производительности приложения. - Динамические
SQL
-запросы. Oracle позволяет динамически конструировать и выполнять запросы с использованием либо встроенного динамическогоSQL
либо программ пакетаDMBS_SQL
. Этот встроенный пакет описывается в документации Oracle, а также в книге Oracle Built-in Packages (O’Reilly).
Основные принципы работы с курсорами
Курсор проще всего представить себе как указатель на таблицу в базе данных. Например, следующее объявление связывает всю таблицу employee с курсором employee_cur
:
CURSOR employee_cur IS SELECT * FROM employee;
Объявленный курсор можно открыть:
OPEN employee_cur;
Далее из него можно выбирать строки:
FETCH employee_cur INTO employee_rec;
Завершив работу с курсором, его следует закрыть:
CLOSE employee_cur;
В этом случае каждая выбранная из курсора запись представляет строку таблицы employee. Однако с курсором можно связать любую допустимую команду SELECT
. В следующем примере в объявлении курсора объединяются три таблицы:
DECLARE
CURSOR joke_feedback_cur IS
SELECT J.name, R.laugh_volume, C.name FROM Joke J, response R, comedian C WHERE J.joke_id = R.joke_id AND R.joker_id = C.joker_id;
BEGIN
END;
В данном случае курсор действует не как указатель на конкретную таблицу базы данных — он указывает на виртуальную таблицу или неявное представление, определяемое командой SELECT
. (Такая таблица называется виртуальной, потому что команда SELECT
генерирует данные с табличной структурой, но эта таблица существует только временно, пока программа работает с возвращенными командой данными.) Если тройное объединение возвращает таблицу из 20 строк и 3 столбцов, то курсор действует как указатель на эти 20 строк.
Терминология
В PL/SQL
имеется множество возможностей выполнения команд SQL
, и все они реализованы в программах как курсоры того или иного типа. Прежде чем приступить к их освоению, необходимо познакомиться с методами выборки данных и используемой при этом терминологией.
- Статический
SQL
. КомандаSQL
называется статической, если она полностью определяется во время компиляции программы. - Динамический
SQL
. КомандаSQL
называется динамической, если она строится и выполняется на стадии выполнения программы, так что в программном коде нет ее фиксированного объявления. Для динамического выполнения командSQL
могут использоваться программы встроенного пакетаDBMS_SQL
(имеющегося во всех версиях Oracle) или встроенный динамическийSQL
. - Результирующий набор строк. Набор строк с результирующими данными, удовлетворяющими критериям, определяемым командой
SQL.
Результирующий набор кэшируется в системной глобальной области с целью ускорения чтения и модификации его данных. - Неявный курсор. При каждом выполнении команды
DML
(INSERT, UPDATE, MERGE
или delete) или команды select into, возвращающей строку из базы данных прямо в структуру данных программы,PL/SQL
создает неявный курсор. Курсор этого типа называется неявным, посколькуOracle
автоматически выполняет многие связанные с ним операции, такие как открытие, выборка данных и даже закрытие. - Явный курсор. Команда
SELECT
, явно определенная в программе как курсор. Все операции с явным курсором (открытие, выборка данных, закрытие и т. д.) в программе должны выполняться явно. Как правило, явные курсоры используются для выборки из базы данных набора строк с использованием статического SQL. - Курсорная переменная. Объявленная программистом переменная, указывающая на объект курсора в базе данных. Ее значение (то есть указатель на курсор или результирующий набор строк) во время выполнения программы может меняться, как у всех остальных переменных. В разные моменты времени курсорная переменная может указывать на разные объекты курсора. Курсорную переменную можно передать в качестве параметра процедуре или функции. Такие переменные очень полезны для передачи результирующих наборов из программ
PL/SQL
в другие среды (например, Java или Visual Basic). - Атрибут курсора. Атрибут курсора имеет форму %имя_атрибута и добавляется к имени курсора или курсорной переменной. Это что-то вроде внутренней переменной Oracle, возвращающей информацию о состоянии курсора — например о том, открыт ли курсор, или сколько строк из курсора вернул запрос. У явных и неявных курсоров и в динамическом SQL в атрибутах курсоров существуют некоторые различия, которые рассматриваются в этой статье.
- SELECT FOR UPDATE. Разновидность обычной команды
SELECT
, устанавливающая блокировку на каждую возвращаемую запросом строку данных. Пользоваться ею следует только в тех случаях, когда нужно «зарезервировать» запрошенные данные, чтобы никто другой не мог изменить их, пока с ними работаете вы. - Пакетная обработка. В
Oracle8i
и вышеPL/SQL
поддерживает запросы с секциейBULK COLLECT
, позволяющей за один раз выбрать из базы данных более одной строки.
Типичные операции с запросами и курсорами
Независимо от типа курсора процесс выполнения команд SQL
всегда состоит из одних и тех же действий. В одних случаях PL/SQL
производит их автоматически, а в других, как, например, при использовании явного курсора, они явно организуются программистом.
- Разбор. Первым шагом при обработке команды
SQL
должен быть ее разбор (синтаксический анализ), то есть проверка ее корректности и формирование плана выполнения (с применением оптимизации по синтаксису или по стоимости в зависимости от того, какое значение параметра0PTIMIZER_M0DE
задал администратор базы данных). - Привязка. Привязкой называется установление соответствия между значениями программы и параметрами команды
SQL.
Для статическогоSQL
привязка производится ядромPL/SQL
. Привязка параметров в динамическомSQL
выполняется явно с использованием переменных привязки. - Открытие. При открытии курсора определяется результирующий набор строк команд SQL, для чего используются переменные привязки. Указатель активной или текущей строки указывает на первую строку результирующего набора. Иногда явное открытие курсора не требуется; ядро
PL/SQL
выполняет эту операцию автоматически (так происходит в случае применения неявных курсоров и встроенного динамическогоSQL
). - Выполнение. На этой стадии команда выполняется ядром
SQL
. - Выборка. Выборка очередной строки из результирующего набора строк курсора осуществляется командой
FETCH
. После каждой выборкиPL/SQL
перемещает указатель на одну строку вперед. Работая с явными курсорами, помните, что и после завершения перебора всех строк можно снова и снова выполнять командуFETCH
, ноPL/SQL
ничего не будет делать (и не станет инициировать исключение) — для выявления этого условия следует использовать атрибуты курсора. - Закрытие. Операция закрывает курсор и освобождает используемую им память. Закрытый курсор уже не содержит результирующий набор строк. Иногда явное закрытие курсора не требуется, последовательность
PL/SQL
делает это автоматически (для неявных курсоров и встроенного динамическогоSQL
).
На рис. 1 показано, как некоторые из этих операций используются для выборки информации из базы данных в программу PL/SQL
.
Рис. 1. Упрощенная схема выборки данных с использованием курсора
Знакомство с атрибутами курсоров
В этом разделе перечисляются и вкратце описываются атрибуты курсоров.
PL/SQL
поддерживает шесть атрибутов курсоров, перечисленных в табл. 1.
Таблица 1. Атрибуты курсоров
Чтобы обратиться к атрибуту курсора, укажите в виде его префикса имя курсора или курсорной переменной и символ %:
имя_курсора%имя_атрибута
В качестве имен неявных курсоров используется префикс SQL
, например SQL%NOTFOUND
. Ниже приведены краткие описания всех атрибутов курсоров.
Атрибут %FOUND
Атрибут %FOUND возвращает информацию о состоянии последней операции FETCH
с курсором. Если последний вызов FETCH
для курсора вернул строку, то возвращается значение TRUE
, а если строка не была получена, возвращается FALSE
.
Если курсор еще не был открыт, база данных инициирует исключение INVALID_CURS0R
. В следующем примере я перебираю все строки курсора caller_cur, присваиваю все звонки, введенные до сегодняшнего дня для конкретного позвонившего, после чего перехожу к следующей записи. При достижении последней записи атрибут %FOUND
явного курсора возвращает FALSE
, и выполнение простого цикла прерывается. Атрибут %FOUND
неявного курсора также проверяется после команды UPDATE
:
FOR caller_rec IN caller_cur LOOP
UPDATE call
SET caller_id = caller_rec.caller_id WHERE call_timestamp < SYSDATE;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE ('Calls updated for ' || caller_rec.caller_id);
END IF;
END LOOP;
Атрибут %NOTFOUND
Атрибут %NOTFOUND
по смыслу противоположен %FOUND
: он возвращает TRUE
, если последняя операция FETCH
с курсором не вернула строки (как правило, из-за того, что последняя строка уже была прочитана). Если курсор не может вернуть строку из-за ошибки, инициируется соответствующее исключение.
Если курсор еще не был открыт, база данных инициирует исключение INVALID_CURSOR
. В каких случаях следует использовать %FOUND
, а когда предпочтение отдается %NOTFOUND
? Используйте ту формулировку, которая более естественно подходит для вашего кода. В предыдущем примере для выхода из цикла использовалась следующая команда:
EXIT WHEN NOT caller_cur%FOUND;
Альтернативная — и возможно, более понятная — формулировка могла бы использовать
%NOTFOUND:
EXIT WHEN caller_cur%NOTFOUND;
Атрибут %ROWCOUNT
Атрибут %ROWCOUNT
возвращает количество записей, прочитанных из курсора к моменту запроса атрибута. При исходном открытии курсора атрибут %ROWCOUNT
равен 0. При обращении к атрибуту %ROWCOUNT
курсора, который еще не был открыт, инициируется исключение INVALID_CURSOR
. После выборки каждой записи %ROWCOUNT
увеличивается на единицу. Используйте атрибут %ROWCOUNT
для проверки того, что программа прочитала (или обновила в случае DML
) нужное количество строк, или для прерывания выполнения после заданного числа итераций. Пример:
BEGIN
UPDATE employees SET last_name = 'FEUERSTEIN';
DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT);
END;
Атрибут %ISOPEN
Атрибут %ISOPEN
возвращает TRUE
, если курсор открыт; в противном случае возвращается FALSE
.
Приведем типичный пример использования этого атрибута, который гарантирует, что курсор не остается открытым, если в программе произойдет что-то непредвиденное:
DECLARE
CURSOR happiness_cur IS SELECT simple_delights FROM ...;
BEGIN
OPEN happiness_cur;
IF happiness_cur%ISOPEN THEN ...
EXCEPTION
WHEN OTHERS THEN
IF happiness_cur%ISOPEN THEN close happiness_cur;
END IF;
END;
Атрибут %BULK_ROWCOUNT
Атрибут %BULK_ROWCOUNT
, предназначенный для использования с командой FORALL, возвращает количество строк, обработанных при каждом выполнении DML. Этот атрибут обладает семантикой ассоциативного массива.
Атрибут %BULK_EXCEPTIONS
Атрибут %BULK_EXCEPTIONS
, также предназначенный для использования с командой FORALL, возвращает информацию об исключении, которое может быть инициировано при каждом выполнении DML. Этот атрибут обладает семантикой ассоциативного массива записей.
На атрибуты курсоров можно ссылаться в коде PL/SQL, как показано в приведенных примерах, но вы не сможете напрямую обращаться к ним в команде SQL. Например, при попытке использовать атрибут %ROWCOUNT
в секции WHERE
команды SELECT
:
SELECT caller_id, company_id FROM caller
WHERE company_id = company_cur%ROWCOUNT;
компилятор выдает ошибку PLS-00229: Attribute expression within SQL expression
.
Ссылки на переменные PL/SQL в курсорах
Поскольку курсор должен быть связан с командой SELECT
, в нем всегда присутствует хотя бы одна ссылка на таблицу базы данных; по ней (и по содержимому условия WHERE
) Oracle определяет, какие строки будут возвращены в составе активного набора. Однако это не означает, что команда SELECT
может возвращать информацию только из базы данных.
Список выражений, задаваемых между ключевыми словами SELECT
и FROM
, называется списком выборки. Во встроенном SQL
список выборки может содержать столбцы и выражения (вызовы SQL
-функций для этих столбцов, константы и т. д.). В PL/SQL
список выборки обычно содержит переменные PL/SQL
и сложные выражения.
На локальные данные программы PL/SQL
(переменные и константы), а также на переменные привязки хост-среды можно ссылаться в предложениях WHERE
, GROUP BY
и HAVING
команды SELECT
курсора. Ссылки на переменные PL/SQL
можно (и должно) уточнять именем ее области видимости (именем процедуры, именем пакета и т. д.), особенно в командах SQL
.
Выбор между явным и неявным курсорами
Все последние годы знатоки Oracle (включая и авторов данной книги) убежденно доказывали, что для однострочной выборки данных никогда не следует использовать неявные курсоры. Это мотивировалось тем, что неявные курсоры, соответствуя стандарту ISO
, всегда выполняют две выборки, из-за чего они уступают по эффективности явным курсорам.
Так утверждалось и в первых двух изданиях этой книги, но пришло время нарушить эту традицию (вместе со многими другими). Начиная с Oracle8, в результате целенаправленных оптимизаций неявные курсоры выполняются даже эффективнее эквивалентных явных курсоров.
Означает ли это, что теперь всегда лучше пользоваться неявными курсорами? Вовсе нет. В пользу применения явных курсоров существуют убедительные доводы.
- В некоторых случаях явные курсоры эффективнее неявных. Часто выполняемые критические запросы лучше протестировать в обеих формах, чтобы точно выяснить, как лучше выполнять каждый из них в каждом конкретном случае.
- Явными курсорами проще управлять из программы. Например, если строка не найдена, Oracle не инициирует исключение, а просто принудительно завершает выполняемый блок.
Поэтому вместо формулировки «явный или неявный?» лучше спросить: «инкапсулированный или открытый?» И ответ будет таким: всегда инкапсулируйте однострочные запросы, скрывая их за интерфейсом функции (желательно пакетной) и возвращая данные через RETURN
.
Не жалейте времени на инкапсуляцию запросов в функциях, желательно пакетных. Это позволит вам и всем остальным разработчикам вашей группы просто вызвать функцию, когда появится необходимость в данных. Если Oracle изменит правила обработки запросов, а ваши предыдущие наработки станут бесполезными, достаточно будет изменить реализацию всего одной функции.