Курсоры

Дон Кламадж (Dan Clamage)

Cursors
Источник: http://users.telerama.com/~dclamage/cursors.doc

Обзор

Курсор - это указатель на зарезервированный участок памяти в SGA, где обрабатывается SQL (SELECT) или DML (UPDATE/INSERT/DELETE) предложение.

SGA (Системная Глобальная Область) большой участок оперативной памяти, зарезервированный операционной системой, который используется для работы Oracle. В SGA (между прочим) находятся разделяемый пул (shared memory pool) и пул частной памяти (private memory pool), где выполняются PL/SQL-программы. Когда ваш код скомпилирован и загружен в память для выполнения, определение курсора загружается в разделяемый пул: текст курсора и разобранный код. Под термином "совместно используемый" подразумевается то, что только одна копия существует в памяти и означает, если Вы и Я оба вводят один и тот же идентичный запрос (запросы совпадают по-символьно), мы используем уже разобранный курсор. Oracle знает, что ваш курсор и мой - идентичны при сравнении текстовой версии курсора. И в этом случае Oracle может использовать тот курсор, который уже в памяти.

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

РИС.1. Типы Курсоров

Существует три основных типов курсоров:

Неявный,

Явный и

Курсорные циклы FOR.

Неявные Курсоры

Неявный курсор управляется автоматически, прозрачно для Вас. В процессе выполнения, курсор открывается, из него выбираются данные и закрывается, все за один шаг.Неявные курсоры используются только в том случае, если надо возвратить единственную строку из таблицы.

 
SELECT addr1, addr2, city, state, zip5
INTO v_addr1, v_addr2, v_city, v_state, v_zip5
FROM addresses
WHERE … -- извлечь по уникальному ключу
 -- (exact match on unique key)
 

Если неявный курсор должен возвратить более, чем одну строку, срабатывает исключение (TOO_MANY_ROWS). Дело в том, что Вы можете выбирать (SELECT INTO) только в скалярные переменные (имеющие только единственное значение).

Если неявный курсор вообще не возвращает строк, срабатывает исключение (NO_DATA_FOUND).

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

Типичные исключения, которые могут возникать при работе с курсором:

 
Exception Name  ORACLE Error SQLCODE Value
----------------------------------------------------------
CURSOR_ALREADY_OPEN ORA-06511 -6511
DUP_VAL_ON_INDEX ORA-00001 -1
INVALID_CURSOR ORA-01001 -1001
INVALID_NUMBER ORA-01722 -1722
LOGIN_DENIED  ORA-01017 -1017
NO_DATA_FOUND ORA-01403 +100
NOT_LOGGED_ON ORA-01012 -1012
PROGRAM_ERROR ORA-06501 -6501
STORAGE_ERROR ORA-06500 -6500
TIMEOUT_ON_RESOURCE ORA-00051 -51
TOO_MANY_ROWS ORA-01422 -1422
TRANSACTION_BACKED_OUT ORA-00061 -61
VALUE_ERROR ORA-06502 -6502
ZERO_DIVIDE OAR-01476 -1476
 

Если объявлена переменная типа record, поля которой точно совпадают с данными, запрашиваемыми в операторе SELECT, то можно использовать конструкцию SELECT INTO.

 
DECLARE
address_rec addresses%ROWTYPE;
BEGIN
SELECT *
INTO address_rec
FROM addresses
WHERE …
 

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

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

Явные курсоры используются, когда ожидается, что запрос может вернуть как ни одной, так и много строк. Он же позволяет выполнить сложные действия (логику) при выборке каждой строки. Управление явными курсорами осуществляет программист. Это означает, что Вы осуществляете открытие, извлечение данных и закрытие (OPEN, FETCH, CLOSE) курсора. При использовании явных курсоров можно выделить 4 основных этапа:

1. Объявление курсора.
2. Открытие (OPEN) курсора.
3. Извлечение (FETCH) данных из курсора один или более раз.
4. Закрытие (CLOSE) курсор.

 
DECLARE CURSOR addresses_c IS -- объявление курсора
SELECT * FROM addresses
WHERE … ;
addresses_rec addresses_c%ROWTYPE; -- переменная куда передаются строки из курсора
BEGIN
OPEN addresses_c; -- вычисление набора данных
 -- (computes the data set)
LOOP
FETCH addresses_c INTO addresses_rec; -- извлечь строку
EXIT WHEN addresses_c%NOTFOUND; -- выход из цикла
… -- your logic here
END LOOP; -- полное или диапазонное сканирование определенной таблицы
 -- (full or range scan on addresses table)
CLOSE addresses_c;
 

Самый простой путь определить переменную типа record, чтобы извлечь (FETCH) строки из курсора - создать переменную, основанную на типе курсора. В этом случае, если выражение SELECT изменяется, то поля переменной также изменяются, но уже автоматически, без дополнительных усилий с вашей стороны. Это наиболее предпочтительный метод, определения переменных типа record. Если выбирается целая табличная строка, Вы можете использовать record переменную с теми же типами полей что и столбцы таблицы.

Для ссылок на поля записи, используются точки. Примеры:

 
addresses_rec.state := 'PA';
IF (addresses_rec.city != some_city) THEN …
local_zip5 := addresses_rec.zip5;
 

Можно также определить RECORD TYPE и заставить курсор вернуть его.

 
-- заголовок пакета
TYPE ADDRESSES_TYPE IS RECORD (
addr1 addresses.addr1%TYPE,
addr2 addresses.addr1%TYPE,
city addresses.city%TYPE,
state addresses.state%TYPE,
zip5 addresses.zip5%TYPE);
CURSOR addresses_c(Caddr_key addresses.addr_key%TYPE)
RETURN ADDRESSES_TYPE;
-- тело пакета
CURSOR addresses_c(Caddr_key addresses.addr_key%TYPE)
RETURN ADDRESSES_TYPE IS
SELECT addr1, addr2, city, state, zip5
FROM addresses
WHERE addr_key = Caddr_key;
 

Как Вы видите, сперва это используется для упакованных курсоров, которые Вы хотите сделать общими. Пакетная спецификация содержит только описание курсора (имя и тип возврата), тогда как (скрыто) тело курсора содержится в выражении SELECT. Оба компонента курсора должны быть согласованы (match). Тип record использован для того, чтобы обеспечивать удобный тип данных для курсора. Тип record и курсорная фраза SELECT сопоставимы.

Устанавливая тело курсора в теле пакета, Вы можете поддержать утверждение курсора более легко без необходимости перекомпилировать заголовок пакета, который может повлиять на зависимые программные устройства.

Атрибуты Курсора

Курсоры имеют (4) атрибуты:

FOUND Строка успешно извлечена? (TRUE/FALSE)

NOTFOUND Строка не извлечена? (TRUE/FALSE)

ISOPEN Курсор открыт? (TRUE/FALSE)

ROWCOUNT Количество извлеченных строк

Первые три величины Boolean (булевы), а четвертая – число. FOUND, NOTFOUND и ROWCOUNT – используются для открытых курсоров. ISOPEN может использоваться для того, чтобы протестировать, какие курсоры открыты, а какие закрыты. Вы используете их с нотацией атрибута; например:

 
IF (addresses_c%ISOPEN) THEN
 -- закрыты ли потенциально открытые курсоры 
 --(close potentially open cursors)
CLOSE addresses_c; 
 -- Используется в обработчике исключений 
 --(use this in exception handler)
END IF;
EXIT WHEN addresses_c%NOTFOUND;
 -- стандартный способ выхода из цикла
 -- (standard way of exiting loop)
 

Параметризованные курсоры

Явные курсоры могут использовать параметры:

 
CURSOR workload_c (Cempid employees.empid%TYPE,
Cprojid projects.projid%TYPE,
Cwork_date DATE) IS
SELECT *
FROM workload
WHERE
empid = Cempid AND
projid = Cprojid AND
work_date = Cwork_date;
 

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

Имя параметра должно отличаться от имени столбца в локальном курсоре; в противном случае, будет возвращена каждая строка! (x = x всегда TRUE). Будьте последовательны при выборе имен параметров; например, "всегда использовать символ С после имени столбца" это правило может использовать, как стандарт при работе с явными курсорами.

Имя параметра является просто “заполнителем” для переменной, которую Вы поставляете. Область - строго локальная в теле курсора.

 
OPEN workload_c (v_empid, v_projid, SYSDATE);
 

Поскольку курсоры являются объектами только для чтения, Вам не нужно определять поток для параметров. Поток - всегда в (IN).

Курсорные циклы FOR

Курсорный цикл FOR - итеративная конструкция, которая позволяет использовать OPEN, FETCH, CLOSE курсор в пределах цикла. Он позволяет выполнять операции над каждой строкой, выбранной в цикле. Обычно он используется, когда Вы собираетесь обрабатывать каждую строку, которую он возвращает. Если Вам нужно прервать цикл преждевременно, используйте явные курсоры (с их большим управлением).

 
DECLARE
CURSOR addresses_c IS
SELECT * FROM addresses
WHERE state = 'PA';
BEGIN
FOR rec IN addresses_c LOOP
DBMS_OUTPUT.put_line('ROWCOUNT=' ||
TO_CHAR(addresses_c%ROWCOUNT, '000'));
END LOOP;
END;
 

Вывод из курсора (см. выше: rec) может использоваться только в цикле. Вы не можете использовать запись (record) за пределами цикла, поскольку она не существует за пределами области цикла. Если Вам нужно видеть значение переменной типа record за пределами цикла, назначьте ему некоторую переменную, определенную за пределами цикла. Record переменная неявно объявлена с теми же типами полей, что и строки курсора, и может быть названа, как угодно.

Ни один из атрибутов курсора, кроме ISOPEN, недоступен за пределами цикла, поскольку курсор не открыт. Если Вы пытаетесь вызвать FOUND, NOTFOUND и ROWCOUNT за пределами курсора для (FOR) цикла, то получите ошибку: ORA-01001: invalid cursor.

Подобно явным курсорам, курсор, использованный на курсоре для (FOR) цикла, может также использовать параметры.

Самый простейший вид курсора для (FOR) цикла представляет собой запрос, вставленный в описание цикла:

 
FOR x IN (SELECT * FROM addresses) LOOP
cnt := cnt + 1; 
 -- сделаем здесь что-нибудь полезное
 -- (do something useful here)
END LOOP;