Что возвращает команда sql isopen для неявных курсоров
Работа с неявными курсорами в PL/SQL на примерах
Общая структура неявного запроса выглядит так:
Примеры неявных курсоров
Неявные курсоры часто используются для поиска данных на основе значений первичного ключа. В следующем примере выполняется поиск названия книги по ее коду ISBN:
Из запроса также можно получить информацию уровня групп. Например, следующий запрос вычисляет и возвращает сумму окладов по отделу. И снова PL/SQL создает для него неявный курсор:
Благодаря тесной интеграции PL/SQL с базой данных Oracle с помощью запросов из базы данных можно извлекать и сложные типы данных, такие как объекты и коллекции. Все эти примеры демонстрируют применение неявных запросов для выборки данных одной строки. Если вы хотите получить более одной строки, используйте либо явный курсор, либо конструкцию bulk collect into.
Как упоминалось ранее, я рекомендую всегда «скрывать» однострочные запросы за функциональным интерфейсом. Эта концепция подробно рассматривается в разделе «Выбор между явным и неявным курсорами» вот этого блога.
Обработка ошибок при использовании неявных курсоров
При работе с неявным курсором программист может сделать некоторые рискованные предположения об извлекаемых данных. Приведем пару примеров:
Последствия таких предположений часто оказываются плачевными, поскольку программисты не включают в программы соответствующие обработчики исключений для неявных запросов.
Конечно, в настоящий момент и при текущем состоянии данных запрос может вернуть ровно одну строку. Но если данные изменятся, запрос, к примеру, команда SELECT может вернуть две строки вместо одной, программа выдаст исключение, которое не будет обработано — и это может создать проблемы в коде.
Почему эти два обработчика ведут себя по-разному? Дело в том, что функция должна вернуть название книги, которое никогда не может быть представлено значением NULL. Для проверки используется код ISBN («существует ли книга с данным кодом ISBN?»), поэтому если книга по ISBN не найдена, функция не должна инициировать исключение. В этом нет ничего плохого. Например, логика программы может быть такой: «если книги с заданным ISBN не существует, используем его для новой книги», а возможная реализация может выглядеть так:
Иначе говоря, то, что запрос не вернул ни одной строки, не всегда свидетельствует об ошибке.
Атрибуты неявных курсоров
Таблица 1. Атрибуты неявных курсоров
Если вам нужна гарантия того, что значения атрибутов относятся к заданной команде SQL, сохраняйте эти атрибуты в локальных переменных сразу же после ее выполнения:
Связь объектов PL/SQL с таблицами базы данных (Курсоры)
Чтобы программа PL/SQL могла работать с информацией, содержащейся в базах данных, необходимо организовать обмен между значениями столбцов таблиц баз данных и переменными PL/SQL.
Известно, что для выбора информации из таблиц используется SQL предложение SELECT. При его выполнении Oracle создает специальную рабочую область, содержащую информацию о самом SELECT, данные, которые требуются для его выполнения (например, результаты подзапросов), и, наконец, окончательный результат выполнения SELECT. PL/SQL имеет несколько механизмов доступа к этой рабочей области. Одним из них является курсор, с помощью которого можно присвоить имя этой рабочей области и манипулировать содержащейся в ней информацией, последовательно выбирая строки результата и пересылая значения столбцов текущей строки в переменные PL/SQL. Существуют и другие механизмы, не требующее создания явного курсора.
Явный курсор
Курсор — это средство языка SQL, позволяющее с помощью команд OPEN, FETCH и CLOSE получить построчный доступ к результату запроса к базе данных. (Будем также называть курсором и сам набор строк, полученный в результате выполнения запроса.) Для использования курсора его надо сначала объявить, т.е. дать ему имя и указать (с помощью предложения SELECT), какие столбцы и строки базовых таблиц должны быть помещены в набор строк, названный этим именем. Команда OPEN инициализирует получение указанного набора и установку перед первой его строкой указателя текущей строки. Команда FETCH служит для установки указателя текущей строки на следующую запись (первый раз на строку с номером 1) и выборки из текущей строки курсора значений указанных столбцов с пересылкой их в переменные PL/SQL. (Выполнением FETCH в цикле можно последовательно выбрать информацию из всех строк курсора.) Наконец, команда CLOSE позволяет закрыть (удалить из памяти) набор строк (при этом описание курсора сохраняется и его можно снова открыть командой OPEN).
Существует модификация («Курсор в цикле FOR«), позволяющая организовать последовательный выбор строк объявленного курсора без явного использования команд OPEN, FETCH и CLOSE.
Объявление курсора
Перед работой с курсором его следует объявить в разделе DECLARE или другом допустимом разделе, используя синтаксис:
где
cursor_name — имя курсора;
SELECT … — предложение SELECT, определяющее строки курсора;
parametr — имеет следующий синтаксис:
а type_name — любой тип (подтип) данных PL/SQL без указания ограничений (например, длины символьных значений).
Формальные параметры курсора используются только для передачи значений в WHERE фразу предложения SELECT с целью отбора нужных строк запроса. Передача таких значений производится во время открытия курсора командой OPEN. Если значения формальных параметров отсутствуют в команде OPEN и не заданы по умолчанию (:= value или DEFAULT value), то выдается ошибка. При наличии тех и других используются параметры из команды OPEN.
В следующем примере использованы оба способа задания значений по умолчанию параметрам курсора:
Кроме того, в нем выражению «(TO_CHAR(razr)||’ ‘||imya_dolg)» дан псевдоним «razr_dolg», использованный во фразе ORDER BY. Oracle рекомендует создавать псевдонимы для всех выражений фразы SELECT с целью ссылки на них при работе с курсором.
Открытие курсора (OPEN)
Команда OPEN имеет следующий синтаксис
где список значений («value») используется для передачи параметров курсора и должен по числу и типу данных совпадать с описанием этих параметров.
будет создан набор:
где использовались значения параметров, заданные при описании, а по команде:
будет создан другой набор:
Выборка строк из курсора (FETCH)
Команда FETCH, используемая для продвижения на один шаг указателя текущей строки курсора и пересылки ее значений в переменные или запись, имеет следующий синтаксис:
Для каждого значения столбца, возвращенного запросом, в списке INTO должна иметься переменная или поле записи соответствующего типа данных. Такие переменные или записи должны быть заранее описаны в декларативной части блока PL/SQL. Например:
Внутри цикла можно использовать значения переменных sh_raz и sh_stav, которые после открытия курсора равны «17 Профессор» и «1.75», после первого прохождения цикла – «16 Профессор» и «3.», после второго – «15 Доцент» и «9.75» и т.д.
При выборке значений текущей строки в запись, например, с именем ShRec надо немного изменить как описание, так и тело блока процедуры:
Теперь значения, получаемые ранее из sh_raz и sh_stav, можно получать из полей ShRec.raz_dol и ShRec.stav записи ShRec.
Целесообразность использования записей возрастает с увеличением количества ее полей или возможности создания описания по типу уже существующей записи с помощью атрибута %ROWTYPE.
Наконец, если при выполнении цикла осуществлен переход за последнюю строку набора, то значения переменных FETCH-списка будут неопределены.
Закрытие курсора (CLOSE)
Команда CLOSE используется для освобождения всех ресурсов, которые поддерживались открытым курсором (при этом описание курсора сохраняется и его
можно снова открыть командой OPEN). Синтаксис команды CLOSE имеет вид:
Использование курсора в цикле FOR
В большинстве ситуаций, которые требуют явного курсора, текст программы может быть упрощен при использовании «курсора в цикле FOR«, заменяющего команды OPEN, FETCH и CLOSE. Курсор в цикле FOR:
— неявно объявляет индекс цикла записью, поля которой соответствуют столбцам (псевдонимам) предложения SELECT … из описания курсора;
– передает параметры курсора (если они есть) и открывает курсор;
– выбирает в цикле строки из полученного набора в индекс цикла (поля записи);
– закрывает курсор после обработки всех строк набора или досрочному выходу из него с помощью команд EXIT или GOTO.
Синтаксис курсора в цикле FOR имеет вид:
где – var_rec_name индекс цикла, в котором при первом прохождении цикла хранится первая строка набора, при втором прохождении цикла – вторая строка и т.д.;
– список значений («value») используется для передачи параметров курсора (он заменяет в данном случае список из команды OPEN);
– ТЕЛО ЦИКЛА содержит нужные строки повторяющейся части программы, в которых используются переменные с именами var_rec_name.column_name, а column_name имя столбца из перечня столбцов предложения SELECT в описании курсора.
Например:
Атрибуты явного курсора
Для анализа состояния курсора используются специальные переменные, имена которых составляются из имени курсора и суффиксов %FOUND, %NOTFOUND, %ROWCOUNT и %ISOPEN, называемых атрибутами курсора. Если курсор назван «cursor_name», то эти переменные имеют имена:
Значения таких переменных анализируются при выполнении программы с помощью различных операторов управления (IF…THEN, EXIT WHEN и т.п.), которые изменяют (при необходимости) ход выполнения программы. Следует отметить, что ссылка на эти переменные до открытия курсора приводит к появлению сообщения INVALID_CURSOR.
Переменная с атрибутом %ISOPEN позволяет определить, открыт ли курсор. Если он открыт то эта переменная возвращает TRUE, иначе – FALSE. Например:
Переменные с %NOTFOUND и %FOUND атрибутами показывают состояние текущей позиции курсора (перед первой выборкой строки курсора обе переменных имеют значение NULL). Переменная с %NOTFOUND принимает значение FALSE тогда, когда выборка возвратила строку (при этом переменная с %FOUND принимает значение TRUE). Если же в результате выборки строка не возвращается, то переменные с %NOTFOUND и %FOUND принимают значения TRUE и FALSE, соответственно.
Переменная с атрибутом %ROWCOUNT содержит количество строк, выбранных из курсора на текущий момент (при открытии курсора эта переменная содержит ноль).
В следующем примере переменная s1%ROWCOUNT ограничивает выборку из курсора s1 десятью строками:
Изменение или удаление текущей строки курсора
Существует два предложения, позволяющие изменить или удалить ту строку таблицы базы данных, на которую позиционирована текущая строка курсора:
Для этого необходимо, чтобы при объявлении курсора предложение SELECT … содержало фразу
в которой следует привести список обновляемых столбцов.
Неявный курсор (SQL курсор)
Для всех команд языка SQL, не связанных с объявлением курсора («явным курсором»), PL/SQL открывает курсор («неявный курсор»), на который можно ссылаться по курсорному имени SQL%. При работе с таким курсором нельзя использовать команды OPEN, FETCH и CLOSE, но можно использовать атрибуты курсора, чтобы получить информацию о текущем его состоянии.
SELECT … INTO
В тех случаях, когда программе необходимо иметь значения столбцов из одной строки таблицы, можно воспользоваться предложением SELECT … INTO, формат которого имеет вид:
Практически это обычный SELECT, выполняющий присвоение выбираемых значений столбцов переменным, перечисленным во фразе INTO. Однако такое присвоение происходит только в том случае, если «WHERE condition» обеспечивает возвращение по запросу лишь одной строки и переменные заранее описаны в декларативной части блока PL/SQL.
UPDATE, DELETE и INSERT
Эти предложения отличаются от аналогичных предложений интерактивного SQL лишь тем, что в их выражениях (expr) могут использоваться переменные PL/SQL.
Атрибуты неявного курсора (SQL курсора)
Для анализа результата выполнения предложений SELECT…INTO, INSERT, UPDATE и DELETE используются три переменные: SQL%NOTFOUND, SQL%FOUND и SQL%ROWCOUNT (Oracle закрывает курсор SQL автоматически после выполнения SQL предложения, что делает бессмысленным использование переменной SQL%ISOPEN, так как ее значение всегда равно FALSE).
Перед выполнением предложений SELECT…INTO, INSERT, UPDATE и DELETE переменные SQL%NOTFOUND и SQL%FOUND имеют значение NULL. Переменная SQL%NOTFOUND принимает значение TRUE, если INSERT, UPDATE и DELETE не произвели изменений таблиц базы данных или SELECT…INTO не возвратил строк (при этом переменная SQL%FOUND принимает значение FALSE). В противном случае переменная SQL%NOTFOUND принимает значение FALSE, а переменная SQL%FOUND – TRUE. Вот один из примеров использования SQL%NOTFOUND для добавления новой строки в таблицу temp при сбое модификации:
Запись опубликована 13.01.2010 в 6:12 дп и размещена в рубрике Oracle7 краткий справочник. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.
Русские Блоги
Подробное использование курсора в oracle
[Обучение 1] Используйте атрибуты неявного курсора, чтобы определить, является ли изменение зарплаты сотрудника успешным.
Шаг 1. Войдите и запустите следующую программу:
Шаг 2: Измените номер сотрудника с 1234 на 7788 и повторите описанную выше процедуру:
Результат:
[Обучение 1] Используйте курсор, чтобы извлечь имя и должность 7788 сотрудников в таблице emp.
Объяснение: Эта программа извлекает и отображает имя и должность сотрудника 7788, определяя курсор emp_cursor.
В качестве улучшения приведенного выше примера переменные записи используются в следующем обучении.
[Обучение 2] Используйте курсор, чтобы извлечь имя, должность и зарплату 7788 сотрудников в таблице emp.
Объяснение: В примере переменные записи используются для получения данных.Переменные записи определяются переменными курсора и должны появляться после определения курсора.
Примечание. Содержимое записанных переменных можно получить в следующем виде:
Запишите имя переменной и имя поля.
[Тренинг 3] Показать имена и зарплаты трех лучших сотрудников с самой высокой заработной платой.
Объяснение: Программа использует предложение ORDER BY в определении курсора для сортировки и использует оператор цикла для извлечения нескольких строк данных.
Цикл курсора
[Обучение 1] Используйте специальный цикл FOR для отображения номеров и имен всех сотрудников.
Примечание. Эта форма проще, определение курсора опущено, а оператор запроса SELECT для курсора появляется непосредственно в цикле.
Явные атрибуты курсора
Несмотря на то, что вы можете использовать предыдущую форму для получения данных курсора, это более гибкий метод использования некоторых его свойств для структурного контроля после определения курсора. Свойства явного курсора показаны ниже.
Объяснение: В этом примере используется emp_cursor% ISOPEN, чтобы определить, открыт ли курсор, используется emp_cursor% ROWCOUNT для получения количества строк данных, возвращенных до сих пор оператором FETCH, и выводится; используется цикл для получения данных; используется оператор FETCH в теле цикла; используется emp_cursor% NOTFOUND Независимо от того, был ли успешно выполнен оператор FETCH. Когда оператор FETCH завершается неудачно, это указывает на то, что данные были получены и цикл завершен.
[Упражнение 1] Удалите оператор OPEN emp_cursor; и повторно запустите вышеуказанную программу.
Передача параметров курсора
[Training 1] Курсоры с параметрами.
Объяснение: используется двойной цикл. Во внешнем теле цикла оператор SELECT курсора генерируется динамически, а затем открывается. Используйте инструкцию letter: = chr (ascii (letter) +1), чтобы получить следующую букву в алфавите.
Объяснение: В приведенном выше запросе, поскольку сотрудник с номером 1234 не существует, будет указан тип «NO_DATA_»
FOUND «исключение.» NO_DATA_FOUND «является предопределенным системой типом ошибки. Оператор WHEN в разделе EXCEPTION будет перехватывать исключение и выполнять соответствующий раздел кода. В этом примере выходные данные Определяемое пользователем сообщение об ошибке «Ошибка числа, соответствующий сотрудник не найден!». Если возникают другие типы ошибок, будет выполнен раздел кода с условием «ДРУГОЕ» с сообщением «Произошли другие ошибки!»
[Training 2] Системные ошибки отображаются кодом программы.
Объяснение: Ошибка деления на ноль произошла во время работы программы и была перехвачена WHEN OTHERS. При выполнении собственного оператора вывода, отображаемого в сообщении, отобразилось сообщение об ошибке, а затем он завершился нормально. В части обработки ошибок предопределенные функции SQLCODE () и SQLERRM () используются для дальнейшего получения кода ошибки и информации о типе.
предопределенная ошибка
В Oracle много системных ошибок, но в стандартном пакете определены только некоторые распространенные ошибки. Об определенной ошибке можно судить по стандартному имени ошибки в части EXCEPTION, и выполняется обработка исключений. Общие системные предопределенные исключения показаны ниже.
Например, если программа вставит повторяющиеся значения в столбец первичного ключа таблицы, произойдет ошибка DUP_VAL_ON_INDEX.
Если системная ошибка не определена в стандартном пакете, ее необходимо определить в разделе описания, синтаксис следующий:
Имя ошибки ИСКЛЮЧЕНИЕ;
После определения PRAGMA EXCEPTION_INIT используется, чтобы связать определенную ошибку со специальным кодом ошибки Oracle, и ее можно использовать как системную ошибку. Синтаксис выглядит следующим образом:
PRAGMA EXCEPTION_INIT (имя ошибки, код ошибки);
[Training 1] Определите новый тип системной ошибки.
Объяснение: NULL_INSERT_ERROR является пользовательским исключением и связано с системной ошибкой 1400.
пользовательское исключение
Программисты могут использовать механизм, который вызывает исключения для разработки программ, и определять свои собственные типы исключений. Вы можете определить новый тип исключения в разделе объявлений. Синтаксис определения:
Имя ошибки ИСКЛЮЧЕНИЕ;
Определяемые пользователем ошибки не могут быть вызваны системой. Они должны запускаться явно программой. Синтаксис триггера:
RAISE имя ошибки;
RAISE также может быть использован для вызова симулируемых системных ошибок. Например, RAISE ZERO_DIVIDE вызовет симулированное деление на ноль ошибок.
Вы также можете вызвать исключение, используя функцию RAISE_APPLICATION_ERROR. Этой функции передаются два параметра, первый из которых представляет собой определяемый пользователем номер ошибки, а второй параметр представляет собой определяемое пользователем сообщение об ошибке. Количество исключений, возникающих при использовании этой функции, следует выбирать между 20 000 и 20 999.
Пользовательская обработка исключений такая же, как и раньше.
[Обучение 1] Вставьте новых сотрудников и ограничьте число сотрудников, которые будут вставлены, между 7000 и 8000.
Результат выполнения:
Номер сотрудника меньше нижнего предела в 7000!
Процедура PL / SQL успешно завершена.
Примечание. В этом примере определены два исключения: new_excp1 и new_excp2, которые представляют ошибки с числами меньше 7000 и числами больше 8000 соответственно. Исходя из размера числа в программе, генерируется соответствующее исключение, и операция вставки откатывается в части обработки исключения, а затем отображается соответствующее сообщение об ошибке.
[Training 2] Используйте функцию RAISE_APPLICATION_ERROR, чтобы вызвать системное исключение.
Результат выполнения:
Сотрудник успешно скопирован!
Процедура PL / SQL успешно завершена.
Шаг 2: Показать результат копирования:
SELECT empno,ename,job FROM emp1;
Результат выполнения:
Примечание: первая функция rpad производит эффект выравнивания, а вторая функция rpad генерирует различное число * в зависимости от зарплаты. Программа использует неявный цикл курсора курсора.
[Обучение 3] Напишите программу для форматирования и вывода информации об отделе.
Введите и выполните следующую программу:
Примечание. Содержимое поля в этой программе расположено вертикально. Переменная V_count записывает количество циклов, то есть количество отделов.
[Обучение 4] Известно, что в каждом отделе есть менеджер, он пишет программу и статистически выводит название отдела, общее количество отделов, общую зарплату и руководителя отдела.
Введите и выполните следующую программу:
Объяснение: Оператор SELECT с функцией группировки используется в курсоре для подсчета общего числа людей и общей заработной платы каждого отдела. Затем найдите менеджера отдела по номеру отдела и названию. Процедура предполагает наличие менеджера для каждого отдела.
[Обучение 5] Повышение заработной платы для работников, начиная с низкооплачиваемых работников, увеличение 10% первоначальной заработной платы для каждого человека, ограничение общего повышения заработной платы до 800 юаней, что свидетельствует о том, что увеличение заработной платы Количество и баланс.
Введите и отладьте следующую программу:
Курсоры (CURSOR) в PL/SQL: основные принципы программирования
При выполнении команды SQL из PL/SQL РСУБД Oracle назначает ей приватную рабочую область, а некоторые данные записывает в системную глобальную область (SGA, System Global Area). В приватной рабочей области содержится информация о команде SQL и набор данных, возвращаемых или обрабатываемых этой командой. PL/SQL предоставляет программистам несколько механизмов доступа к этой рабочей области и содержащейся в ней информации; все они так или иначе связаны с определением курсоров и выполнением операций с ними.
Основные принципы работы с курсорами
Курсор проще всего представить себе как указатель на таблицу в базе данных. Например, следующее объявление связывает всю таблицу employee с курсором employee_cur :
Объявленный курсор можно открыть:
Далее из него можно выбирать строки:
Завершив работу с курсором, его следует закрыть:
Терминология
Типичные операции с запросами и курсорами
Независимо от типа курсора процесс выполнения команд SQL всегда состоит из одних и тех же действий. В одних случаях PL/SQL производит их автоматически, а в других, как, например, при использовании явного курсора, они явно организуются программистом.
Рис. 1. Упрощенная схема выборки данных с использованием курсора
Знакомство с атрибутами курсоров
В этом разделе перечисляются и вкратце описываются атрибуты курсоров.