Неявный курсор, реализуемый оператором SELECT, - это некое подобие «черного ящика». Вы передаете команду SQL в базу данных и получаете назад одну строку данных. Об отдельных операциях, таких как открытие, извлечение данных и закрытие, вы ничего не знаете. Вам также придется иметь в виду, что Oracle автоматически инициирует исключения для неявного курсора SELECT в следующих двух случаях:

Запрос не находит ни одной строки, соответствующей его условиям. В этом случае Oracle инициирует исключение NO_DATA_FOUND.

Команда SELECT возвращает несколько строк. В этом случае Oracle инициирует исключение TOO_MANY_ROWS.

Атрибуты неявного курсора SQL

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

Таблица 1.6. Атрибуты неявных курсоров и их значения

Имя	Описание
SQL%FOUND	Возвращает TRUE, если была выбрана или изменена одна строка (или несколько строк при работе с BULK COLLECT INTO), и FALSE - в противном случае (в этом случае Oracle также инициирует исключение NO_DATA_FOUND).
SQL%NOTFOUND	Возвращает TRUE, если оператором DML не было выбрано или изменено ни одной строки, и FALSE - в противном случае.
SQL%ROWCOUNT	Возвращает количество строк, выбранных или измененных курсором. Для курсора SELECT INTO будет иметь значение 1, если строка найдена, и 0, если Oracle инициирует исключение NO_DATA_FOUND.
SQL%ISOPEN	Всегда возвращает FALSE для неявных курсоров, так как Oracle открывает и закрывает неявные курсоры автоматически.

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

Явные курсоры

Явный курсор - это оператор SELECT, который явно определен в секции объявлений кода; такому курсору присваивается имя. Явные курсоры для операторов INSERT, UPDATE и DELETE не создаются.

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

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

Давайте рассмотрим в качестве примера функцию, которая определяет (и возвращает) ту степень зависти, которую я испытываю к своим друзьям в зависимости от их места жительства:

Этот блок PL/SQL выполняет следующие действия над курсором:

Строки	Описание
4-7	Объявление курсора.
9	Объявление записи на основе этого курсора.
12	Открытие курсора.
14	Выборка из курсора одной строки данных.
16	Проверка атрибута курсора для определения того, найдена ли строка.

Строки	Описание
18-22	Анализ содержимого выбранной строки для определения уровня за
	висти.
25	Закрытие курсора.

Для использования явного курсора необходимо сначала объявить его в разделе объявлений вашего PL/SQL-блока или пакета:

где имя_курсора - имя курсора, спецификация_возврата - необязательное предложение RETURN для курсора, а SELECT_оператор - любой корректный SQL-оператор SELECT. Вы также можете передавать в курсор параметры, используя необязательный список параметров. Как только курсор объявлен, вы можете открывать его и выбирать из него данные.

Oracle поддерживает для явных курсоров тот же набор атрибутов, что и для неявных курсоров. Значения, которые атрибуты явных курсоров могут приобретать до и после выполнения указанных операций над курсорами, приведены в табл. 1.7.

Таблица 1.7. Значения атрибутов явных курсоров «до и после» выполнения операций над курсорами

	%FOUND	% NOTFOUN D	%ISOPEN	% RO WCOUNT
Перед OPEN	Инициируется ORA-01001	Инициируется ORA-01001	FALSE	Инициируется ORA-01001
После OPEN	NULL	NULL	TRUE	0
Перед первой FETCH	NULL	NULL	TRUE	0
После первой FETCH	TRUE	FALSE	TRUE	1
Перед последующими FETCH	TRUE	FALSE	TRUE	1
После последующих FETCH	TRUE	FALSE	TRUE	Зависит от данных
Перед последней FETCH	TRUE	FALSE	TRUE	Зависит от данных
После последней FETCH	FALSE	TRUE	TRUE	Зависит от данных
Перед CLOSE	FALSE	TRUE	TRUE	Зависит от данных
После CLOSE	Исключение	Исключение	FALSE	Исключение

BULK COLLECT

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

В Oracle8i Database появилось новое мощное средство, повышающее эффективность запросов в PL/SQL: предложение BULK COLLECT. При помощи BULK COLLECT вы можете извлекать в явном или неявном запросе несколько строк данных за одно обращение к базе данных. Использование BULK COLLECT уменьшает количество переключений контекста между PL/SQL и SQL, тем самым снижая издержки на извлечение данных. Предложение имеет следующий синтаксис:

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

В версиях, предшествующих Oracle9/ Database, BULK COLLECT может использоваться только со статическим SQL. В Oracle9/ Database и Oracle Database 10g BULK COLLECT может применяться как для статического, так и для динамического SQL.

Ключевые слова BULK COLLECT могут быть использованы в любом из следующих предложений: SELECT INTO, FETCH INTO и RETURNING INTO.

Коллекции, которые указываются в предложении BULK COLLECT, могут хранить только скалярные значения (строки, числа и даты). Другими словами, невозможно извлечение строки данных в запись, являющуюся элементом другой коллекции.

Ядро SQL автоматически инициализирует и расширяет коллекции, которые задаются в предложении BULK COLLECT. Заполнение начинается с индекса 1, элементы вставляются последовательно (плотно), любые определенные ранее элементы перезаписываются.

Использование пакетной выборки SELECT. .. BULK COLLECT в операторе FORALL недопустимо.

В случае, если не возвращено ни одной строки, SELECT.. .BULK COLLECT не инициирует исключения NO_DATA_FOUND. Вам необходимо проверить содержимое коллекции на предмет наличия в ней данных.

Операция BULK COLLECT перед исполнением запроса делает пустой коллекцию, заданную в предложении INTO. Если запрос не возвращает строк, то метод COUNT этой коллекции будет возвращать 0.

Ограничение количества строк, извлекаемых при помощи BULK COLLECT

Для ограничения количества строк, извлекаемых предложением BULK COLLECT из базы данных, Oracle поддерживает предложение LIMIT, которое имеет такой синтаксис:

Использование LIMIT для BULK COLLECT чрезвычайно полезно, так как позволяет регулировать объем памяти, используемый программой для обработки данных. Предположим, например, что необходимо выбрать и обработать 10000 строк данных. Вы можете использовать BULK COLLECT для извлечения всех этих строк и заполнения большой коллекции. Однако такой подход потребует использования большого объема памяти в глобальной области процесса (PGA) данного сеанса. Если этот код исполняется несколькими разными пользователями Oracle, то производительность приложения может быть значительно снижена из-за свопирования PGA.

Рассмотрим фрагмент кода, в котором предложение LIMIT используется в операторе FETCH, исполняемом внутри простого цикла. Обратите внимание, что после выборки данных производится проверка атрибута %NOTFOUND (с тем чтобы определить, удалось ли в этот раз выбрать строки).

< Предыдущая		Следующая >

DeepEdit!

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

Явные курсоры

Популярные