28 Курсоры в PL/SQL
Курсор – указатель на контекстную область памяти, с помощью которого программа на языке PL/SQL может управлять контекстной областью и ее состоянием во время обработки оператора.
Контекстная область содержит информацию, необходимую для начала и завершения обработки SQL оператора
Объявление курсора (в блоке DECLARE)
CURSOR <имя курсора>[(<передаваемые параметры>)] IS
SELECT<имена столбцов> FROM <имя таблицы>
WHERE <условие выбора>
Открытие и закрытие курсора
OPEN <имя курсора>[(<значения параметров>)]
CLOSE <имя курсора>
Если в программе более 1 курсора (иначе система сама сможет закрыть курсор), то необходимо закрыть курсор, в противном случае это может существенно снизить производительность блока PL/SQL. После закрытия курсора, нельзя выбирать из него строки.
Выборка данных из курсора
FETCH <имя курсора> INTO <список переменных>
FETCH <имя курсора> INTO <запись PL/SQL(%ROWTYPE)>
Виды курсоров
- 1. Явные (их необходимо объявлять, открывать и закрывать) и неявные (операторы DML)
- 2. Параметризованные (в скобках указываются параметры OPEN <имя курсора>(<значение параметра>)) и непараметризованные
- 3. Курсоры на обновление (добавляется строка FOR UPDATE [OF <имя столбца>])
Курсоры обрабатываются в цикле. Во всех циклах, кроме FOR необходимо открывать и закрывать курсор вручную, в цикле FOR – это делается автоматически. Курсоры могут возвращать любое количество строк. Курсоры могут обладать атрибутами.
==============================================
Курсоры PL/SQL
Курсор - это поименованный запрос, содержащий некоторое число строк в выборке. По сути, курсор является некоторой структурой, через которую пользователь получает доступ к строкам результирующей таблицы запроса.
Рассмотрим пример доступа к информации, которая хранится в базе данных, с использованием курсоров.
Пусть в базе данных есть таблица базы данных с именем T01, такая, как показана ниже.
A1 number 1 2 3
A2 varchar2(5) abc cba bca
A3 char(1) A B C
Опишем курсор для доступа к данным таблицы Т01.
CURSOR cur01 IS SELECT * FROM T01;
Работа с курсором выполняется по следующему алгоритму:
Открываем курсор:
OPEN cur01;
Выбираем данные из курсора в набор совместимых по типу переменных командой FETCH:
FETCH cur01 INTO x1,x2,x3;
Обрабатываем полученные данные.
Выполняем команду FETCH для получения данных из следующей строки результирующей таблицы запроса.
И т.д.
В PL/SQL для курсоров предусмотрено несколько методов. Метод %NOTFOUND возвращает булевское истинное значение, если выборка в курсор пуста. Метод %FOUND возвращает булевское истинное значение, если выборка в курсор непуста. После открытия курсора до первой команды FETCH значения, возвращаемые этими методами, равны NULL. Метод %ROWCOUNT возвращает число строк в выборке после открытия курсора.
Предопределенный в PL/SQL метод %TYPE позволяет определить тип переменной как совпадающий с типом переменной таблицы.
PL/SQL поддерживает тип данных RECORD, который позволяет создать объект, соответствующий строке таблицы, как показано в примере ниже.
DECLARE TYPE t01_rec_type IS RECORD
( x1 t01.A1%TYPE,
x2 t01.A2%TYPE,
x3 t01.A3%TYPE);
t01_rec t01_rec_type;
…
FETCH cur1 INTO t01_rec;
DBMS_OUTPUT.PUT_LINE (cur1%ROWCOUNT||' '||t01_rec.x2);
ѕ.
Обработка исключительных ситуаций в PL/SQL
Исключительная ситуация - это возникновение предопределенного и описанного события в системе. Например, ошибки преобразования типов переменных или переполнения при делении на нуль. Пример некоторых предопределенных ситуаций, распознаваемых в PL/SQL, приведен в таблице 12.1 ниже. Для получения полного списка таких ситуаций следует обратиться к документации по PL/SQL.
Таблица 12.1. Описание некоторых исключительных ситуацийLOGIN_DENID Неуспешное подключение к серверу
NOT_LOGGED_ON Попытка выполнить действие без подключения к серверу
INVALID_CURSOR Ссылка на недопустимый курсор или недопустимая операция с курсором
NO_DATA_FOUND Не найдены данные, соответствующие команде SELECT INTO
DUP_VAL_ON_INDEX Попытка вставить дубликат значения в колонку с ограничением на уникальное значение
VALUE_ERROR Арифметическая ошибка, ошибка усечения или преобразования
=====================================================
В лекции обсуждаются курсоры, их объявление и использование.
Управление курсором
Создание курсора
Под курсором в Oracle понимается получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи.
В PL/SQL поддерживаются два типа курсоров: явные и неявные.
Явный курсор объявляется разработчиком, а неявный курсор не требует объявления.
Курсор может возвращать одну строку, несколько строк или ни одной строки.
Для запросов, возвращающих более одной строки, можно использовать только явный курсор.
Курсор может быть объявлен в секциях объявлений любого блока PL/SQL, подпрограммы или пакета.
Для управления явным курсором используются операторы CURSOR, OPEN, FETCH и CLOSE.
Оператор CURSOR выполняет объявление явного курсора.
Оператор OPEN открывает курсор, создавая новый результирующий набор на базе указанного запроса.
Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца.
Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы.
Для объявления явного курсора используется оператор CURSOR, который может иметь следующее формальное описание:
CURSOR cursor_name
[(parameter[,parameter]...)]
[RETURN return_type]
IS select_statement;
Каждый параметр parameter определяется как:
cursor_parameter_name [IN]
datatype [{:= | DEFAULT} expr]
Параметр return_type определяет запись или строку базы данных, используемую для возвращаемых значений. Тип возвращаемого значения должен соответствовать столбцам, перечисленным в операторе SELECT. Список параметров определяет параметры курсора, передаваемые на сервер каждый раз при выполнении оператора OPEN.
Одновременно с созданием результирующего набора можно выполнить блокировку выбираемых строк. Для этого в операторе SELECT следует указать фразу FOR UPDATE.
Для задания параметров курсора подходит как позиционная, так и именованная нотация.
Для работы с курсором можно использовать следующие атрибуты, указываемые после имени курсора:
%ISOPEN - возвращает значение TRUE, если курсор открыт.
%FOUND - определяет, найдена ли строка, удовлетворяющая условию.
%NOTFOUND - возвращает TRUE, если строка не найдена.
%ROWCOUNT - возвращает номер текущей строки.
Оператор FETCH может быть выполнен в цикле LOOP-END LOOP. Это позволяет последовательно просматривать весь результирующий набор, который был открыт оператором OPEN.
Например:
DECLARE
CURSOR c1 IS SELECT f1, f2, f3, f4
FROM tbl1
WHERE f4 > 100;
CURSOR c2 RETURN tbl2%ROWTYPE IS
SELECT * FROM tbl2
WHERE f1_t2 = 10;
- Список параметров
- курсора
CURSOR c3 (p1 INTEGER DEFAULT 10,
p2 INTEGER DEFAULT 1300)
IS SELECT f1, f2, f3, f4
FROM tbl1
WHERE f4 > p1 AND f2 = p2;
- ...
BEGIN
OPEN c1; - Открытие курсора c1
LOOP
- Выборка одной строки
FETCH c1 INTO rec1;
- Строка успешно выбрана
EXIT WHEN c1%NOTFOUND;
END LOOP;
- Закрытие курсора
CLOSE c1;
- Открытие курсора c3
OPEN c3(10,700);
- ...
END;
Для повторного создания результирующего набора для других значений параметров курсор следует закрыть, а затем повторно открыть.
При выполнении SQL-оператора, для которого не был объявлен явный курсор, Oracle автоматически открывает неявный курсор.
При применении неявного курсора нельзя использовать операторы управления курсором OPEN, FETCH и CLOSE.
Если при неявном курсоре в результирующий набор записывается более одной строки, то Oracle инициирует исключение TOO_MANY_ROWS.
Если курсор создается в пакете, то его объявление и спецификация могут быть разделены: объявление курсора указывается в секции объявлений пакета, а спецификация курсора - в теле пакета.
Объявление курсора при создании пакета может иметь следующее формальное описание:
CURSOR cursor_name [(parameter
[, parameter]...)]
RETURN return_type;
Например:
- Создание пакета
CREATE PACKAGE p1 AS
- Объявление курсора
CURSOR c1 RETURN tbl1%ROWTYPE;
END p1;
- Создание тела пакета
CREATE PACKAGE BODY p1 AS
- Спецификация курсора
CURSOR c1 RETURN tbl1%ROWTYPE
IS SELECT * FROM tbl1
WHERE f3 > 700;
END p1;
Использование курсора в цикле FOR
Вместо управления курсором операторами OPEN, FETCH и CLOSE язык PL/SQL предоставляет возможность последовательной обработки курсора в цикле FOR.
Цикл FOR с курсором выполняет следующие действия:
Неявно объявляет переменную цикла как запись %ROWTYPE.
Открывает курсор.
При каждой итерации извлекает следующую строку из результирующего набора в поля неявно объявленной записи.
По достижении конца результирующего набора закрывает курсор.
Например:
DECLARE
CURSOR c1 IS
SELECT f1, f2 FROM tbl2
WHERE f1 = 10;
BEGIN
- Неявное объявление rec1
FOR rec1 IN c1 LOOP
/* Выбрана следующая
строка таблицы */
INSERT INTO temp_tbl
VALUES (rec1.f2);
END LOOP;
COMMIT;
END;
Тип REF CURSOR
При объявлении переменной курсора создается указатель типа REF CURSOR. Переменная типа REF CURSOR может передаваться через механизм RPC (вызовы удаленных процедур) между клиентским приложением и сервером Oracle. При использовании переменных курсора для передачи результирующих наборов между хранимыми подпрограммами PL/SQL и различными клиентскими приложениями каждое из приложений разделяет указатель на рабочую область, в которой расположен результирующий набор. Это позволяет одновременно ссылаться на одну и ту же рабочую область как клиентским приложениям, разработанным в Oracle Forms, в Visual Studio или в Delphi, так и OCI-клиентам или серверу Oracle.
Создание переменной курсора выполняется в два этапа: сначала определяется тип REF CURSOR, а затем объявляется переменная этого типа.
Определить тип REF CURSOR можно в любых блоках PL/SQL, подпрограммах или пакетах.
Определение типа REF CURSOR может иметь следующее формальное описание:
TYPE ref_type_name IS REF
CURSOR [ RETURN return_type ];
Параметр ref_type_name задает имя создаваемого типа, а параметр return_type должен определять запись или строку таблицы базы данных.
Если опция RETURN не указана, то переменную курсора можно использовать более гибко, ссылаясь на различные запросы, которые имеют разные типы записей. Однако применение опции RETURN обеспечивает более высокий уровень надежности, позволяя компилятору PL/SQL выполнять проверку совместимости типа переменной курсора с типом результатов запроса. Переменная курсора не может быть сохранена в базе данных.
Переменные курсора в PL/SQL аналогичны указателям языка C++.
При выполнении SQL-запроса Oracle создает неименованную рабочую область, в которой содержится сформированный результирующий набор. Для доступа к этому результирующему набору может использоваться:
явный курсор, который именует рабочую область;
переменная курсора, которая указывает на эту рабочую область.
Однако явный курсор всегда указывает только на одну и ту же рабочую область, а переменная курсора может ссылаться на различные рабочие области.
Рабочая область будет оставаться доступной до тех пор, пока на нее ссылается хотя бы одна переменная курсора.
Переменная курсора может быть использована в качестве формального параметра процедуры или функции.
Для управления переменной курсора используются операторы OPEN-FOR, FETCH и CLOSE.
Оператор OPEN-FOR связывает переменную курсора с запросом, выполняет запрос и получает результирующий набор.
Оператор OPEN-FOR может иметь следующее формальное описание:
OPEN {cursor_variable_name |
:host_cursor_variable_name}
FOR select_statement;
Параметр указывает переменную курсора host_cursor_variable_name, которая была объявлена как переменная основного языка, если блок PL/SQL выполняется в режиме встроенного SQL. Для ссылки на хост-переменную перед ней необходимо указывать символ двоеточия.
Переменная курсора, в отличие от самого курсора, не может иметь параметров.
Запрос, указываемый для переменной курсора, может использовать:
хост-переменные;
переменные PL/SQL;
параметры;
функции.
Запрос, на который ссылается переменная курсора, не может содержать фразу FOR UPDATE.
Для переменной курсора можно использовать атрибуты %FOUND, %NOTFOUND, %ISOPEN и %ROWCOUNT.
Например:
DECLARE
TYPE VarCur IS REF CURSOR
RETURN tbl1%ROWTYPE;
- Объявление переменной курсора
t1 VarCur;
BEGIN
IF NOT t1%ISOPEN THEN
- Открываем
- переменную курсора
OPEN t1 FOR SELECT *
FROM tbl1;
OPEN t1 FOR SELECT *
FROM tbl1
- Повторно открываем
- переменную курсора
WHERE f2>100;
END IF;
При попытке повторно открыть ранее открытую переменную курсора для другого результирующего набора предыдущий запрос будет потерян, а переменная будет ассоциирована с новым запросом.
Если попытаться повторно открыть уже открытый курсор, то Oracle инициирует исключение CURSOR_ALREADY_OPEN.
Если переменная курсора объявляется как формальный параметр подпрограммы, открывающей переменную курсора, то такой параметр должен быть указан с опцией IN OUT.
Следующий пример демонстрирует применение в качестве переменной хост-переменной основного языка программирования.
/* Объявление хост-переменных */
EXEC SQL BEGIN DECLARE
/* Объявление переменной курсоров */
SQL_CURSOR cur1;
EXEC SQL END DECLARE SECTION;
/* Инициализация хост-переменной */
EXEC SQL ALLOCATE : cur1;
EXEC SQL EXECUTE
/* Передача хост-переменной
курсора в блок PL/SQL */
BEGIN
OPEN :cur1 FOR SELECT *
FROM emp;
- :
OPEN :cur1 FOR SELECT *
FROM temp_emp;
END;
END-EXEC;
Применение переменных курсора имеет ряд следующих ограничений:
переменные курсора не могут быть объявлены в пакете (и сохранены в базе данных);
нельзя использовать механизм RPC для передачи переменных курсора между различными серверами;
удаленная подпрограмма не может получать значения от переменных курсора с другого сервера;
запрос, указываемый для переменной курсора оператором OPEN-FOR, не должен содержать фразы FOR UPDATE;
для переменных курсора не применимы операторы равенства, сравнения или эквивалентности значению NULL;
переменной курсора не может быть присвоено значение NULL;
типы REF CURSOR не могут использоваться для определения типа столбцов в SQL-операторах CREATE TABLE и CREATE VIEW, а также для определения типов элементов коллекций;
переменные курсора не могут быть использованы в динамическом SQL;
переменную курсора нельзя использовать вместо курсора в цикле FOR-IN-LOOP.
Выборка строк из результирующего набора выполняется оператором FETCH, который может иметь следующее формальное описание:
FETCH {cursor_variable_name |
:host_cursor_variable_name}
INTO {variable_name
[, variable_name]... |
record_name};
Например:
DECLARE
TYPE VarCur IS REF CURSOR
RETURN tbl1%ROWTYPE;
- Объявление переменной курсора
t1 VarCur;
tbl1_rec tbl1%ROWTYPE;
BEGIN
IF NOT t1%ISOPEN THEN
- Открываем
- переменную курсора
OPEN t1 FOR SELECT * FROM tbl1;
LOOP
- Извлечение строки
FETCH t1 INTO tbl1_rec;
- Проверка атрибута
EXIT WHEN е1%NOTFOUND;
END LOOP;
END IF;
CLOSE е1;В лекции обсуждаются курсоры, их объявление и использование.
Управление курсором
Создание курсора
Под курсором в Oracle понимается получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи.
В PL/SQL поддерживаются два типа курсоров: явные и неявные.
Явный курсор объявляется разработчиком, а неявный курсор не требует объявления.
Курсор может возвращать одну строку, несколько строк или ни одной строки.
Для запросов, возвращающих более одной строки, можно использовать только явный курсор.
Курсор может быть объявлен в секциях объявлений любого блока PL/SQL, подпрограммы или пакета.
Для управления явным курсором используются операторы CURSOR, OPEN, FETCH и CLOSE.
Оператор CURSOR выполняет объявление явного курсора.
Оператор OPEN открывает курсор, создавая новый результирующий набор на базе указанного запроса.
Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца.
Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы.
Для объявления явного курсора используется оператор CURSOR, который может иметь следующее формальное описание:
CURSOR cursor_name
[(parameter[,parameter]...)]
[RETURN return_type]
IS select_statement;
Каждый параметр parameter определяется как:
cursor_parameter_name [IN]
datatype [{:= | DEFAULT} expr]
Параметр return_type определяет запись или строку базы данных, используемую для возвращаемых значений. Тип возвращаемого значения должен соответствовать столбцам, перечисленным в операторе SELECT. Список параметров определяет параметры курсора, передаваемые на сервер каждый раз при выполнении оператора OPEN.
Одновременно с созданием результирующего набора можно выполнить блокировку выбираемых строк. Для этого в операторе SELECT следует указать фразу FOR UPDATE.
Для задания параметров курсора подходит как позиционная, так и именованная нотация.
Для работы с курсором можно использовать следующие атрибуты, указываемые после имени курсора:
%ISOPEN - возвращает значение TRUE, если курсор открыт.
%FOUND - определяет, найдена ли строка, удовлетворяющая условию.
%NOTFOUND - возвращает TRUE, если строка не найдена.
%ROWCOUNT - возвращает номер текущей строки.
Оператор FETCH может быть выполнен в цикле LOOP-END LOOP. Это позволяет последовательно просматривать весь результирующий набор, который был открыт оператором OPEN.
Например:
DECLARE
CURSOR c1 IS SELECT f1, f2, f3, f4
FROM tbl1
WHERE f4 > 100;
CURSOR c2 RETURN tbl2%ROWTYPE IS
SELECT * FROM tbl2
WHERE f1_t2 = 10;
- Список параметров
- курсора
CURSOR c3 (p1 INTEGER DEFAULT 10,
p2 INTEGER DEFAULT 1300)
IS SELECT f1, f2, f3, f4
FROM tbl1
WHERE f4 > p1 AND f2 = p2;
- ...
BEGIN
OPEN c1; - Открытие курсора c1
LOOP
- Выборка одной строки
FETCH c1 INTO rec1;
- Строка успешно выбрана
EXIT WHEN c1%NOTFOUND;
END LOOP;
- Закрытие курсора
CLOSE c1;
- Открытие курсора c3
OPEN c3(10,700);
- ...
END;
Для повторного создания результирующего набора для других значений параметров курсор следует закрыть, а затем повторно открыть.
При выполнении SQL-оператора, для которого не был объявлен явный курсор, Oracle автоматически открывает неявный курсор.
При применении неявного курсора нельзя использовать операторы управления курсором OPEN, FETCH и CLOSE.
Если при неявном курсоре в результирующий набор записывается более одной строки, то Oracle инициирует исключение TOO_MANY_ROWS.
Если курсор создается в пакете, то его объявление и спецификация могут быть разделены: объявление курсора указывается в секции объявлений пакета, а спецификация курсора - в теле пакета.
Объявление курсора при создании пакета может иметь следующее формальное описание:
CURSOR cursor_name [(parameter
[, parameter]...)]
RETURN return_type;
Например:
- Создание пакета
CREATE PACKAGE p1 AS
- Объявление курсора
CURSOR c1 RETURN tbl1%ROWTYPE;
END p1;
- Создание тела пакета
CREATE PACKAGE BODY p1 AS
- Спецификация курсора
CURSOR c1 RETURN tbl1%ROWTYPE
IS SELECT * FROM tbl1
WHERE f3 > 700;
END p1;
Использование курсора в цикле FOR
Вместо управления курсором операторами OPEN, FETCH и CLOSE язык PL/SQL предоставляет возможность последовательной обработки курсора в цикле FOR.
Цикл FOR с курсором выполняет следующие действия:
Неявно объявляет переменную цикла как запись %ROWTYPE.
Открывает курсор.
При каждой итерации извлекает следующую строку из результирующего набора в поля неявно объявленной записи.
По достижении конца результирующего набора закрывает курсор.
Например:
DECLARE
CURSOR c1 IS
SELECT f1, f2 FROM tbl2
WHERE f1 = 10;
BEGIN
- Неявное объявление rec1
FOR rec1 IN c1 LOOP
/* Выбрана следующая
строка таблицы */
INSERT INTO temp_tbl
VALUES (rec1.f2);
END LOOP;
COMMIT;
END;
Тип REF CURSOR
При объявлении переменной курсора создается указатель типа REF CURSOR. Переменная типа REF CURSOR может передаваться через механизм RPC (вызовы удаленных процедур) между клиентским приложением и сервером Oracle. При использовании переменных курсора для передачи результирующих наборов между хранимыми подпрограммами PL/SQL и различными клиентскими приложениями каждое из приложений разделяет указатель на рабочую область, в которой расположен результирующий набор. Это позволяет одновременно ссылаться на одну и ту же рабочую область как клиентским приложениям, разработанным в Oracle Forms, в Visual Studio или в Delphi, так и OCI-клиентам или серверу Oracle.
Создание переменной курсора выполняется в два этапа: сначала определяется тип REF CURSOR, а затем объявляется переменная этого типа.
Определить тип REF CURSOR можно в любых блоках PL/SQL, подпрограммах или пакетах.
Определение типа REF CURSOR может иметь следующее формальное описание:
TYPE ref_type_name IS REF
CURSOR [ RETURN return_type ];
Параметр ref_type_name задает имя создаваемого типа, а параметр return_type должен определять запись или строку таблицы базы данных.
Если опция RETURN не указана, то переменную курсора можно использовать более гибко, ссылаясь на различные запросы, которые имеют разные типы записей. Однако применение опции RETURN обеспечивает более высокий уровень надежности, позволяя компилятору PL/SQL выполнять проверку совместимости типа переменной курсора с типом результатов запроса. Переменная курсора не может быть сохранена в базе данных.
Переменные курсора в PL/SQL аналогичны указателям языка C++.
При выполнении SQL-запроса Oracle создает неименованную рабочую область, в которой содержится сформированный результирующий набор. Для доступа к этому результирующему набору может использоваться:
явный курсор, который именует рабочую область;
переменная курсора, которая указывает на эту рабочую область.
Однако явный курсор всегда указывает только на одну и ту же рабочую область, а переменная курсора может ссылаться на различные рабочие области.
Рабочая область будет оставаться доступной до тех пор, пока на нее ссылается хотя бы одна переменная курсора.
Переменная курсора может быть использована в качестве формального параметра процедуры или функции.
Для управления переменной курсора используются операторы OPEN-FOR, FETCH и CLOSE.
Оператор OPEN-FOR связывает переменную курсора с запросом, выполняет запрос и получает результирующий набор.
Оператор OPEN-FOR может иметь следующее формальное описание:
OPEN {cursor_variable_name |
:host_cursor_variable_name}
FOR select_statement;
Параметр указывает переменную курсора host_cursor_variable_name, которая была объявлена как переменная основного языка, если блок PL/SQL выполняется в режиме встроенного SQL. Для ссылки на хост-переменную перед ней необходимо указывать символ двоеточия.
Переменная курсора, в отличие от самого курсора, не может иметь параметров.
Запрос, указываемый для переменной курсора, может использовать:
хост-переменные;
переменные PL/SQL;
параметры;
функции.
Запрос, на который ссылается переменная курсора, не может содержать фразу FOR UPDATE.
Для переменной курсора можно использовать атрибуты %FOUND, %NOTFOUND, %ISOPEN и %ROWCOUNT.
Например:
DECLARE
TYPE VarCur IS REF CURSOR
RETURN tbl1%ROWTYPE;
- Объявление переменной курсора
t1 VarCur;
BEGIN
IF NOT t1%ISOPEN THEN
- Открываем
- переменную курсора
OPEN t1 FOR SELECT *
FROM tbl1;
OPEN t1 FOR SELECT *
FROM tbl1
- Повторно открываем
- переменную курсора
WHERE f2>100;
END IF;
При попытке повторно открыть ранее открытую переменную курсора для другого результирующего набора предыдущий запрос будет потерян, а переменная будет ассоциирована с новым запросом.
Если попытаться повторно открыть уже открытый курсор, то Oracle инициирует исключение CURSOR_ALREADY_OPEN.
Если переменная курсора объявляется как формальный параметр подпрограммы, открывающей переменную курсора, то такой параметр должен быть указан с опцией IN OUT.
Следующий пример демонстрирует применение в качестве переменной хост-переменной основного языка программирования.
/* Объявление хост-переменных */
EXEC SQL BEGIN DECLARE
/* Объявление переменной курсоров */
SQL_CURSOR cur1;
EXEC SQL END DECLARE SECTION;
/* Инициализация хост-переменной */
EXEC SQL ALLOCATE : cur1;
EXEC SQL EXECUTE
/* Передача хост-переменной
курсора в блок PL/SQL */
BEGIN
OPEN :cur1 FOR SELECT *
FROM emp;
- :
OPEN :cur1 FOR SELECT *
FROM temp_emp;
END;
END-EXEC;
Применение переменных курсора имеет ряд следующих ограничений:
переменные курсора не могут быть объявлены в пакете (и сохранены в базе данных);
нельзя использовать механизм RPC для передачи переменных курсора между различными серверами;
удаленная подпрограмма не может получать значения от переменных курсора с другого сервера;
запрос, указываемый для переменной курсора оператором OPEN-FOR, не должен содержать фразы FOR UPDATE;
для переменных курсора не применимы операторы равенства, сравнения или эквивалентности значению NULL;
переменной курсора не может быть присвоено значение NULL;
типы REF CURSOR не могут использоваться для определения типа столбцов в SQL-операторах CREATE TABLE и CREATE VIEW, а также для определения типов элементов коллекций;
переменные курсора не могут быть использованы в динамическом SQL;
переменную курсора нельзя использовать вместо курсора в цикле FOR-IN-LOOP.
Выборка строк из результирующего набора выполняется оператором FETCH, который может иметь следующее формальное описание:
FETCH {cursor_variable_name |
:host_cursor_variable_name}
INTO {variable_name
[, variable_name]... |
record_name};
Например:
DECLARE
TYPE VarCur IS REF CURSOR
RETURN tbl1%ROWTYPE;
- Объявление переменной курсора
t1 VarCur;
tbl1_rec tbl1%ROWTYPE;
BEGIN
IF NOT t1%ISOPEN THEN
- Открываем
- переменную курсора
OPEN t1 FOR SELECT * FROM tbl1;
LOOP
- Извлечение строки
FETCH t1 INTO tbl1_rec;
- Проверка атрибута
EXIT WHEN е1%NOTFOUND;
END LOOP;
END IF;
CLOSE е1;