Курсоры

По сути, курсор представляет собой указатель на результирующее множество в БД. Но курсор – это не просто указатель на данные в таблице. Он указывает на область памяти в глобальной области процесса (PGA), которая называется контекстной областью и в которой храниться следующее:

1. Строки, возвращенные запросом;

2. Число строк, обработанных запросом;

3. Указатель на разобранный запрос в общем пуле.

Таким образом, создается указатель на память, а не на сами данные. И так как данные выбираются в память при открытии курсора, то пользователю гарантируется согласованное представление данных на все время транзакции. Если после того как был открыт курсор, данные были добавлены, удалены или обновлены, новые данные не отражаются в результирующем множестве курсора. Открытие курсора подобно созданию моментальной копии для данных, существующих в момент этого открытия.

Курсоры применяются для построчного доступа к данным результирующего множества. Существует четыре различных вида курсоров:

Явные курсоры - команды SELECT, явно объявленные в секции объявлений текущего блока или спецификации пакета.

Неявные курсоры – автоматически создаются в ответ на любые команды DML (SELECT, INSERT, UPDATE, DELETE, MERGE, SELECT FOR UPDATE) или SELECT … INTO в секции исполнения блока PL/SQL.

Курсорные переменные – объявленные типы, которые в одном и том же блоке PL/SQL могут быть ассоциированы с несколькими запросами.

Курсорные подзапросы или вложенные курсорные выражения – механизм встраивания курсоров в операторы SQL.

Явные курсоры. Обеспечивают самый высокий уровень управления обработкой курсоров. Явный курсор является статическим; команда SQL определяется в момент компиляции. Считается, что они работают с оператором SELECT, который единовременно возвращает более одной строки. Для того, чтобы использовать явный курсор, его необходимо объявить, открыть, выбрать данные из него и закрыть.

Объявление явных курсоров. Для объявления явного курсора в качестве курсорной переменной в секции объявлений блока или пакета используется следующий синтаксис:

CURSOR имя_курсора [(список_параметров)]

[RETURN возвращаемый_ тип]

IS запрос

[FOR UPDATE [OF (список_столбцов)] [NOWAIT]];

где имя_курсора – любой разрешенный идентификатор;

в списке_параметров указываются любые допустимые параметры, используемые для выполнения запроса;

необязательная фраза RETURN определяет тип данных, который должен быть возвращен курсором, определенный в возвращаемом_ типе;

запрос – это любой допустимый синтаксис оператора SELECT;

необязательная фраза FOR UPDATE блокирует записи при открытии курсора, т.е. они будут доступны другим пользователям только в режиме READ ONLY.

Использование FOR UPDATE служит гарантией того, что:

• при выполнение цикла по записям курсора все они будут доступны для обновления и не будут заблокированы другим сеансом;

• данные будут соответствовать данным из контекстной области;

• если указано NOWAIT, программа будет завершаться сразу после открытия, если она не сможет установить исключительную блокировку.

Приведем пример объявления явного курсора:

--курсор без параметров:

CURSOR company_cur

IS

SELECT company_id FROM company;

Открытие явных курсоров. Курсоры открываются в секциях выполнения или исключительных ситуаций блока. Синтаксис оператора:

OPEN имя_курсора [(значения_параметров)];

Команда OPEN открывает явный курсор, т.е. подготавливает курсор к использованию. На стадии выполнения запрос разбирается, вычисляются значения переменных связи, строки выбираются в контекстную область и множество результатов становится доступным. Таким образом, обработка открытого курсора включает в себя такие этапы выполнения команды SQL, как разбор, связывание, непосредственно открытие и исполнение. В каждый момент времени в курсоре может быть только одна активная запись.

Выборка записей из курсора. Команда FETCH выбирает записи их контекстной области в переменные, чтобы ими можно было воспользоваться в программе PL/SQL. Эта команда действует только на текущую запись и движется по результирующему множеству, отбирая по одной записи за раз. Исключением является только использование фразы BULK COLLECT, которая выбирает все записи результирующего множества за один раз.

FETCH имя_курсора INTO список_переменных или запись_PL/SQL;

Здесь имя_курсора – это имя открытого курсора, а список_переменных – это одна или несколько, разделенных запятыми переменных, которые по количеству и типам соответствует количеству и типам столбцов, включенных в результирующее множество.

Если в качестве приемника использовать запись_PL/SQL, то для ссылки на значения из записи используется следующий синтаксис: имя_переменной.имя_столбца.

Закрытие курсора. Для того, чтобы закрыть явный курсор, используется следующий синтаксис:

CLOSE имя_курсора;

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

Если курсор объявляется в локальном анонимном блоке функции или процедуры, то такой курсор будет автоматически закрыт по завершении блока. Курсоры, объявленные в пакете, должны быть закрыты явно, иначе они останутся открытыми до завершения сеанса. Попытка закрытия курсора, который не был открыт, приводит к порождению исключения ORA-01001: INVALID CURSOR.

Атрибуты явного курсора. Для использования с курсорами Oracle предлагает шесть атрибутов, приведенных в таблице

Таблица

Название атрибута	описание
%ISOPEN	Возвращает TRUE, если курсор открыт; FALSE – если курсор не открыт.
%FOUND	Возвращает NULL перед первым извлечением, TRUE – при успешном извлечении записи, FALSE – если не было возвращено ни одной строки. Генерируется исключение INVALID_CURSOR в случае, если курсор не был открыт или уже закрыт.
%NOTFOUND	Возвращает NULL перед первым извлечением, FALSE при успешном извлечении записи, TRUE – если не было возвращено ни одной строки. Генерируется исключение INVALID_CURSOR в случае, если курсор не был открыт или уже закрыт
%ROWCOUNT	Возвращает количество записей, извлечённых из курсора в произвольный момент времени. Генерируется исключение INVALID_CURSOR в случае, если курсор не был открыт или уже закрыт.
%BULK_EXEPTIONS	Предоставляет информацию об исключительных ситуациях при использовании операций с массивами или фразы BULK COLLECT (псевдоассоциативный массив, содержащий порядковые номера обработанных записей, вызвавших исключение и соответствующие коды ошибок). Для продолжения обработки после возникновения исключения применяется параметр SAVE EXCEPTIONS.
%BULK_ROWCOUNT	Также используется при операциях BULK COLLECT и предоставляет информацию о числе строк, измененных за время операции (псевдоассоциативный массив, содержащий количество записей, изменённых FORALL для каждого элемента коллекции).

Курсорные атрибуты используются в соответствии со следующим синтаксисом:

имя_курсора%атрибут

Для обеспечения навигации по набору активных данных курсоры чаще всего используются с обычными циклами (LOOP…END LOOP, WHILE), например:

SET SERVEROUTPUT ON

DECLARE

v_author authors %ROWTYPE;

CURSOR auth_cur IS

SELECT *

FROM authors;

BEGIN

OPEN auth_cur;

LOOP

FETCH auth_cur INTO v_author

EXIT WHEN auth_cur %NOTFOUND;

DBMS_OUTPUT.PUT_LINE (v_author.last_name);

END LOOP;

CLOSE auth_cur;

END;

/

Существует также курсорный цикл FOR, для которого не требуется явного задания операторов OPEN, FETCH, CLOSE. Т.е., хотя курсор объявлен как явный, его обработкой управляет PL/SQL. Кроме того в цикле FOR используется переменная, которая никогда не объявляется в секции объявления переменных и курсоров этого блока. При попытке открытия уже открытого курсора в цикле FOR PL/SQL порождает исключение "ORA-06511:PL/SQL:cursor already open".

SET SERVEROUTPUT ON

DECLARE

CURSOR auth_cur IS

SELECT *

FROM authors;

BEGIN

FOR v_author IN auth_cur

LOOP

DBMS_OUTPUT.PUT_LINE (v_author.last_name);

END LOOP;

END;

/

Неявные курсоры. PL/SQL объявляет неявный курсор и работает с ним при каждом выполнении команды DML (INSERT, UPDATE, DELETE или MERGE) или команды SELECT INTO, которая возвращает одну строку из базы данных непосредственно в структуру данных PL/SQL. Каждый раз, когда вы напрямую запускаете команду SQL, вы работаете с неявными курсорами, так как каждому выполняемому оператору DML предоставляется контекстная область в PGA. В отличие от явных курсоров, неявные не надо объявлять, открывать, извлекать из них строки и закрывать, но можно использовать те же самые шесть атрибутов явных курсоров в виде SQL%атрибут (при этом использование атрибута SQL %ISOPEN будет всегда возвращать FALSE, т.е. по сути, бесполезно).

Общий синтаксис использования неявных курсоров можно представить следующими образом:

Команда_SQL

[RETURNING значение [, значение…]

INTO переменная [, переменная...] ]

SQL%атрибут;

Инструкция RETURNING применяется в командах INSERT, UPDATE и DELETE для получения данных, изменённых соответствующей командой DML. Применение этой инструкции позволяет избежать дополнительной команды SELECT для запроса результатов выполнения команды DML, например:

BEGIN

UPDATE аctivity SET last_accesssed:=SYSDATE

WHERE UID=user_id

RETURNING last_accessed, cost_center

INTO timestamp, chargeback_acct;

Курсорные переменные и подзапрсы. Приведем примеры объявления курсорных переменных:

--курсор, применяющий аргументы из списка_параметров:

CURSOR company_cur (id_in IN NUMBER)

IS

SELECT name FROM company

WHERE company_id=id_in;

--курсор, содержащий инструкцию RETURN:

CURSOR company_cur (id_in IN NUMBER)

RETURN company_id=id_in;

--курсора, содержащий инструкцию RETURN вместо команды SELECT:

CURSOR company_cur (id_in IN NUMBER)

RETURN company%ROWTYPE

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

Существует ряд ограничений для курсорных переменных:

• Нельзя объявлять курсорные переменные на уровне пакета. Однако такие переменные можно объявлять в функциях и процедурах пакетов.

• Нельзя присваивать курсорной переменной значение NULL, а также применять операторы сравнения для проверки на равенство, неравенство или неопределённость.

• Ни столбцы базы данных, ни коллекции не могут хранить курсорные переменные.

• Нельзя применять вызовы удалённых процедур для передачи курсорных переменных с одного сервера на другой.

Переменная типа CURSOR указывает на конкретное результирующее множество в то время как переменная типа REF CURSOR может применяться для указания на различные результирующие множества. Кроме того, SQL-код, используемый в переменной REF CURSOR, может назначаться в выполняемой секции блока PL/SQL что позволяет реализовывать динамические команды SQL.

Курсор и результирующее множество представляют собой объекты базы данных. Результирующее множество храниться до тех пор, пока на него указывает курсор. Поэтому через переменную REF CURSOR можно организовать передачу результирующих множеств данных от одного программного модуля PL/SQL к другому. Также переменные типа REF CURSOR позволяют скрыть незначительные изменения в запросе.

Тип REF_CURSOR имеет такой синтаксис:

TYPE имя_ref-курсора IS REF CURSOR

[RETURN тип_запись];

где тип_запись – это существующая в базе данных строка.

Если не применяется инструкция RETURN, то объявляется слабый тип REF CURSOR. Переменные, объявляемые со слабым типом REF CURSOR, могут быть составлены по любому запросу во время исполнения. Если же инструкция RETURN присутствует, то определяется сильный тип REF CURSOR. Переменная сильного типа REF CURSOR может быть сопоставлена таким запросам, результирующее множество которых при выполнении совпадает по количеству элементов и их типу со структурой записи, указанной в инструкции RETURN.

Для того чтобы использовать переменные типа REF CURSOR, необходимо сначала создать тип REF_CURSOR, затем объявить экземпляр на основе этого типа. Рассмотрим пример объявления слабого и сильного типов REF CURSOR:

DECLARE

--Создать тип курсора для таблицы компаний.

TYPE company_curtype IS REF CURSOR

RETURN companies%ROWTYPE;

--Создать переменную типа REF CURSOR.

company_cur company_curtype;

--Слабый тип, общий подход.

TYPE any_curtype IS REF CURSOR;

generic_curvar any_curtype;

Курсорная переменная может быть объявлена в секции объявлений программ PL/SQL как экземпляр типа данных REF CURSOR. Для того чтобы использовать переменную CURSOR, откройте её с помощью команды OPEN...FOR. После того как курсорная переменная открыта, можно применять FETCH и CLOSE с тем же синтаксисом, что и для других явных курсоров, например:

OPEN...FOR

OPEN имя_куросра FOR select_команда;

Курсорные подзапросы. Курсорные подзапросы также называют вложенными курсорными выражениями, так как они используют курсорное выражение внутри оператора SELECT языка SQL. Другими словами, это курсоры, используемые как выражение для столбца в списке выбора для явного курсора. Они могут использоваться со всеми определенными ранее типами курсоров, за исключением неявных. Возвращаемое значение всегда имеет тип REF CURSOR.

Курсорные выражения могут уменьшить объём ненужных данных, возвращаемый в вызывающую программу. В связи с этим их можно применять вместо приемов требующих соединения таблиц. Курсорное выражение открывается автоматически при извлечении родительской строки, а закрываются, когда происходит одно из событий:

• вложенный курсор явно закрывается программой.

• закрывается родительский курсор.

• заново выполняется родительский курсор.

• во время извлечений родительской строки порождается исключение.

CURSOR возвращает вложенный курсор из запроса и имеет следующий синтаксис:

CURSOR(подзапрос)

Рассмотрим пример, который использует курсорный подзапрос в явном курсоре:

SET SERVEROUTPUT ON

DECLARE

cv_author SYS_REFCURSOR;

v_title BOOKS.TITLE%TYPE;

v_author AUTHORS.%ROWTYPE;

v_counter PLS_INTEGER:= 0;

CURSOR book_cur

IS

SELECT b.title

CURSOR (SELECT *

FROM authors a

WHERE a.id = b.author1

OR a.id=b.author2

OR a.id=b.author3)

FROM books b

WHERE isbn=‘78824389’;

BEGIN

OPEN book_cur;

LOOP

FETCH book_cur INTO v_title, cv_author;

EXIT WHEN book_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(‘Title from the main cursor: ‘||v_title);

LOOP

FETCH cv_author INTO v_author;

EXIT WHEN cv_author%NOTFOUND;

v_counter :=v_counter + 1;

DMBS_OUTPUT.PUT_LINE(‘Author’ ||v_counter|| ‘:’

||v_author.first_name|| ‘ ‘

||v_author.last_name);

END LOOP;

END LOOP;

CLOSE book_cur;

END;

/