Курсор (Cursor)
Cursor это структурная переменная, позволяющая вам обрабатывать данные с несколькими строками. Количество строк зависит от команды запроса данных после нее. В процессе обработки, вы можете манипулировать с Cursor через каждую строку данных. Эта строка данных определена курсором. Перемещая курсор, вы можете получить все данные текущей строки.
Терминология
В PL/SQL имеется множество возможностей выполнения команд SQL, и все они реализованы в программах как курсоры того или иного типа. Прежде чем приступить к их освоению, необходимо познакомиться с методами выборки данных и используемой при этом терминологией.
- Статический SQL. Команда SQL называется статической, если она полностью определяется во время компиляции программы.
- Динамический SQL. Команда SQL называется динамической, если она строится и выполняется на стадии выполнения программы, так что в программном коде нет ее фиксированного объявления. Для динамического выполнения команд SQL могут использоваться программы встроенного пакета DBMS_SQL (имеющегося во всех версиях Oracle) или встроенный динамический SQL.
- Результирующий набор строк. Набор строк с результирующими данными, удовлетворяющими критериям, определяемым командой SQL. Результирующий набор кэшируется в системной глобальной области с целью ускорения чтения и модификации его данных.
- Неявный курсор. При каждом выполнении команды DML (INSERT, UPDATE, MERGE или DELETE) или команды SELECT INTO, возвращающей строку из базы данных прямо
- SELECT FOR UPDATE. Разновидность обычной команды SELECT, устанавливающая блокировку на каждую возвращаемую запросом строку данных. Пользоваться ею следует только в тех случаях, когда нужно «зарезервировать» запрошенные данные, чтобы никто другой не мог изменить их, пока с ними работаете вы.
Типичные операции с запросами и курсорами
Независимо от типа курсора процесс выполнения команд SQL всегда состоит из одних и тех же действий. В одних случаях PL/SQL производит их автоматически, а в других, как, например, при использовании явного курсора, они явно организуются программистом.
- Разбор. Первым шагом при обработке команды SQL должен быть ее разбор (синтаксический анализ), то есть проверка ее корректности и формирование плана выполнения (с применением оптимизации по синтаксису или по стоимости в зависимости от того, какое значение параметра OPTIMIZER_MODE задал администратор базы данных).
- Привязка. Приязкой называется установление соответствия между значениями программы и параметрами команды SQL. Для статического SQL привязка производится ядром PL/SQL. Привязка параметров в динамическом SQL выполняется явно с использованием переменных привязки.
- Открытие. При открытии курсора определяется результирующий набор строк команд SQL, для чего используются переменные привязки. Указатель активной или текущей строки указывает на первую строку результирующего набора. Иногда явное открытие курсора не требуется; ядро PL/SQL выполняет эту операцию автоматически (так происходит в случае применения неявных курсоров и встроенного динамического SQL).
- Выполнение. На этой стадии команда выполняется ядром SQL.
- Выборка. Выборка очередной строки из результирующего набора строк курсора осуществляется командой FETCH. После каждой выборки PL/SQL перемещает указатель на одну строку вперед. Работая с явными курсорами, помните, что и после завершения перебора всех строк можно снова и снова выполнять команду FETCH, но PL/SQL ничего не будет делать (и не станет инициировать исключение) — для выявления этого условия следует использовать атрибуты курсора.
- Закрытие. Операция закрывает курсор и освобождает используемую им память. Закрытый курсор уже не содержит результирующий набор строк. Иногда явное закрытие курсора не требуется, последовательность PL/SQL делает это автоматически (для неявных курсоров и встроенного динамического SQL).
Знакомство с атрибутами курсоров
- %FOUND - TRUE, если успешно выбрана хотя бы одна строка; в противном случае возвращает FALSE
- %NOTFOUND - TRUE, если команда не выбрала ни одной строки; в противном случае возвращает FALSE
- %ROWCOUNT - Количество строк, выбранных из курсора на данный момент времени
- %ISOPEN - TRUE, если курсор открыт; в противном случае возвращает FALSE
- %BULK_ROWCOUNT - Количество измененных записей для каждого элемента исходной коллекции, заданной в команде FORALL
- %BULK_EXCEPTIONS - Информация об исключении для каждого элемента исходной коллекции, заданной в команде FORALL
Выбор между явным и неявным курсорами
Все последние годы знатоки Oracle (включая и авторов данной книги) убежденно доказывали, что для однострочной выборки данных никогда не следует использовать неявные курсоры. Это мотивировалось тем, что неявные курсоры, соответствуя стандарту ISO, всегда выполняют две выборки, из-за чего они уступают по эффективности явным курсорам.
Начиная с Oracle8, в результате целенаправленных оптимизаций неявные курсоры выполняются даже эффективнее эквивалентных явных курсоров. Означает ли это, что теперь всегда лучше пользоваться неявными курсорами? Вовсе нет. В пользу применения явных курсоров существуют убедительные доводы.
- В некоторых случаях явные курсоры эффективнее неявных. Часто выполняемые критические запросы лучше протестировать в обеих формах, чтобы точно выяснить, как лучше выполнять каждый из них в каждом конкретном случае.
- Явными курсорами проще управлять из программы. Например, если строка не найдена, Oracle не инициирует исключение, а просто принудительно завершает выполняемый блок.
Поэтому вместо формулировки «явный или неявный?» лучше спросить: «инкапсулированный или открытый?» И ответ будет таким: всегда инкапсулируйте однострочные запросы, скрывая их за интерфейсом функции (желательно пакетной) и возвращая данные через RETURN.
Не жалейте времени на инкапсуляцию запросов в функциях, желательно пакетных. Это позволит вам и всем остальным разработчикам вашей группы просто вызвать функцию, когда появится необходимость в данных. Если Oracle изменит правила обработки запросов, а ваши предыдущие наработки станут бесполезными, достаточно будет изменить реализацию всего одной функции
Синтаксис объявления курсора:
-- Куросор без параметров
CURSOR <Cursor_Name>
IS
<Select_Statement>
-- Курсор с параметрами
CURSOR <Cursor_Name>(<Parameter_List>)
IS
<Select_Statement>
Пример:
-- Объявляем куросор без параметров
Cursor Emp_Cur Is
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
From Employee Emp;
-- Объявляем курсор с параметрами
Cursor Emp_Cur(p_Dept_Id Number
,p_Branch_Id Number)
Is
Select Emp.Emp_Id
,Emp.First_Name
,Emp.Last_Name
,Emp.Assigned_Branch_Id
,Emp.Dept_Id
From Employee Emp
Where (Emp.Dept_Id = p_Dept_Id Or p_Dept_Id Is Null)
And (Emp.Assigned_Branch_Id = p_Branch_Id Or p_Branch_Id Is Null);
Есть 2 вида курсора (Cursor):
Явный курсор
Неявный курсор.
Понятие явный означает, что при использовании нужно написать команду открытия курсора (open), и написать команду закрыть курсор после использования.
При каждом выполнении команды DML (INSERT, UPDATE, MERGE или delete) или команды SELECT INTO, возвращающей строку из базы данных в структуру данных программы, PL/SQL автоматически создает для нее курсор. Курсор этого типа называется неявным, поскольку Oracle автоматически выполняет многие связанные с ним операции, такие как выделение курсора, его открытие, выборку строк и т.д.
Атрибуты явного курсора:
%isopen возвращает значение True если cursor открыт;
%notfound возвращает значение true если отстутствует следующая строка;
%found возвращает значение true если присутствует следующая строка;
%rowcount возвращает число просмотренных row.
Вопросы для самопроверки
- Что такое курсор?
- Назовите методы выборки данных
- Назовите типичные операции с запросами и курсорами
- Перечислите атрибуты курсоров
- Какие виды курсоров существуют?
Задания
- Напишите явный курсор
- Напишите неявный курсор
- Снова напишите явный курсор и с помощью определенного атрибута подсчитатьте количество строк, выбранных из курсора.