Тип ref cursor oracle

Обновлено: 04.07.2024

Представьте себе вариант решения задачи, когда вам нужно распечатать список сотрудников, но вы не знаете какой должен быть фильтр (условие WHERE). Реализация этой логики выглядит следующим бразом:
1. Курсор по таблице EMP.
2. Печать всей информации с помощью процедуры DBMS_OUTPUT.PUT_LINE.

В данном подходе есть значительный камень преткновения. Как вы можете построить курсор, если не знаете, содержимое фильтра данных? (То есть, что делать, если вы не знаете, какие столбцы и условия необходимы в блоках WHERE и SELECT запроса?) Динамический SQL позволяет вам ответить на этот вопрос и в сочетании с Oracle построить так называемый REF CURSOR.

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

Создание типа данных REF CURSOR

Для использования REF CURSOR переменных, необходимо использовать тип данных REF CURSOR.
Тип данных REF CURSOR не может быть использован вне PL/SQL среды. Есть два типа REF CURSOR-ов: слабые и сильные (weak and strong). Слабый REF CURSOR может указывать на любой набор данных, как показано здесь:

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

Сильный REF CURSOR явно заявляет тип данных, на которые можно ссылаться. В следующем примере, только запросы, которые возвращают строки так же, как в таблице EMP допускаются:

Также возможно открыть рефкурсор для SELECT-запроса заключенного в строку (в данном случае используется слабый REF CURSOR):

Использование слабого реф-курсора:

Примечания:
Строка 4: Создание переменной курсора. Как отметили ранее, лучше использовать
предопределенных типов данных SYS_REFCURSOR для слабых REF-курсоров вместо определения одного из ваших собственных.
Строка 7-8: Открытие переменной курсора для всей строки SELECT-запроса, построенного на лету.

Объявление типа возвращаемых данных

Как вы заметили в предыдущем примере отсутствует часть кода (. ) имена переменных в которых происходит выборка строки данных. Так SELECT-запрос строится динамически, у Oracle нет возможности проверить количество и тип полей которые планируется вернуть. Т.к. мы планируем вернуть все поля, рассмотрим три варианта как это возможно сделать:

Явно объявить переменные для каждого столбца с соответствующим типом данных. Если количество столбцов больше, чем несколько, вероятно увеличение трудоемкости дальнейшего обслуживания кода.
Объявить переменную типа RECORD со всеми переменными в ней. Это решение жизнеспособной, но оно по-прежнему вызывает вопрос обслуживания.
Объявить переменную EMP%ROWTYPE. Это лучший выбор, потому что Вы получаете типы данных непосредственно из таблицы EMP и не заботитесь о любых возможных изменениях типов ее полей.

Пример использования типа данных EMP%ROWTYPE

Примечания:
Строка 5: Создание новой переменной v_emp_rec.
Строка 11: Выборка данных из курсора в новую переменную.
Строка 13: Вывод на экран всей информации о сотруднике.
Используем следующий код для проверки функциональности:

В результате мы видим всю информацию о сотрудниках из отдела №10.

Запись опубликована 18.11.2010 в 2:55 дп и размещена в рубрике Динамический SQL и PL/SQL. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

По некоторым особенностям функционирования он отличается от поставщика Microsoft OLE DB для Oracle (MSDAORA).

По соображениям производительности поставщик данных для Oracle не привязывает типы данных ссылочных курсоров автоматически, как если бы они явно не заданы.

Указанный поставщик данных не поддерживает никаких escape-последовательностей ODBC, включая escape-последовательность , используемую для задания параметров REF CURSOR.

Для выполнения хранимой процедуры, возвращающей ссылки REF CURSOR, необходимо определить параметры в OracleParameterCollection с помощью OracleType курсора и Direction выходных данных. Этот поставщик данных поддерживает привязку данных типа REF CURSOR только в качестве выходных параметров. Этот поставщик не поддерживает данные типа REF CURSOR как входные параметры.

Получение модуля OracleDataReader из значения параметра не поддерживается. Значения имеют тип DBNull после выполнения команды.

Единственным значением перечисления CommandBehavior , которое работает с КЛЮЧЕВЫМи курсорами (например, при вызове ExecuteReader ), является клосеконнектион; все остальные игнорируются.

Порядок ССЫЛОЧных КУРСОРов в OracleDataReader зависит от порядка параметров в OracleParameterCollection. Свойство ParameterName не учитывается.

в этом разделе

Примеры REF CURSOR
Содержит три примера, которые демонстрируют использование данных типа REF CURSOR.

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

Извлечение данных нескольких REF CURSOR с использованием OracleDataReader
демонстрирует выполнение хранимой процедуры PL/SQL, возвращающей два параметра REF CURSOR, и считывание значений с помощью OracleDataReader.

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

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

create table t1(id, type, text)
as
select object_id, object_type, object_name
from all_objects;

create table t1
as
select object_id id, object_type type, object_name text
from all_objects;

select id, type, text from t1
where />
17367 SCHEDULE FILE_WATCHER_SCHEDULE

select id, type, text from t1
where type = 'SCHEDULE';

17364 SCHEDULE DAILY_PURGE_SCHEDULE
17367 SCHEDULE FILE_WATCHER_SCHEDULE
17372 SCHEDULE PMO_DEFERRED_GIDX_MAINT_SCHED
18172 SCHEDULE BSLN_MAINTAIN_STATS_SCHED

Неявные курсоры определяются в момент выполнения:

DECLARE
v_text t1.text%TYPE;

BEGIN
SELECT text INTO v_text
FROM t1
WHERE /> DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END;
/

В ходе выполнения кода создается курсор для выборки значения text.

Явный курсор определяется до начала выполнения:

DECLARE
CURSOR c_get_text
IS
SELECT text
FROM t1
WHERE />
v_text t1.text%TYPE;

BEGIN
OPEN c_get_text;
FETCH c_get_text INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
CLOSE c_get_text;
END;
/

Преимущество явного курсора заключается в наличии у него атрибутов,
облегчающих применение условных операторов.

CREATE OR REPLACE PROCEDURE proc1
AS
CURSOR c_get_text
IS
SELECT text
FROM t1
WHERE />
v_text t1.text%TYPE;

BEGIN
OPEN c_get_text;
FETCH c_get_text INTO v_text;
IF c_get_text%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END IF;
CLOSE c_get_text;
END;
/

А как подобное сделать с неявным курсором:

CREATE OR REPLACE PROCEDURE proc2
AS
v_text t1.text%TYPE;
v_bool BOOLEAN := TRUE;

BEGIN
BEGIN
SELECT text INTO v_text
FROM t1
WHERE />
EXCEPTION
WHEN no_data_found THEN
v_bool := FALSE;
WHEN others THEN
RAISE;
END;

IF NOT v_bool THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END IF;
END;
/

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

Параметризация курсоров помогает повысить степень их повторного использования.

курсор с параметром:

DECLARE
CURSOR c_get_text(par1 NUMBER)
IS
SELECT text
FROM t1
WHERE />
v_text t1.text%TYPE;

BEGIN
OPEN c_get_text(17367);
FETCH c_get_text INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
CLOSE c_get_text;
END;
/

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

CREATE OR REPLACE PROCEDURE proc_ref
AS
v_curs SYS_REFCURSOR;
v_text t1.text%TYPE;

BEGIN
OPEN v_curs
FOR
'SELECT text '
|| 'FROM t1 '
|| 'WHERE />
FETCH v_curs INTO v_text;

DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );

Во время компиляции Oracle не знает, каким будет тексе запроса, - он видит строковую переменную.
Но наличие типа REF CURSOR говорит ему о том, что надо будет обеспечить некую работу с курсором.

Например я могу создать функцию, которая принимает некий входной параметр, создает курсор и возвращает тип REF CURSOR :

CREATE OR REPLACE FUNCTION func1(par1 NUMBER)
RETURN SYS_REFCURSOR
IS
v_curs SYS_REFCURSOR;

BEGIN
OPEN v_curs
FOR
'SELECT text '
|| 'FROM t1 '
|| 'WHERE /> || par1;

Другой пользователь может воспользоваться этой функцией так:

v_curs SYS_REFCURSOR;
v_text t1.text%TYPE;

BEGIN
v_curs := func1(17367);

FETCH v_curs INTO v_text;

IF v_curs%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END IF;

Для пользователя, вызывающего функцию func1(), она для него представляет черный ящик, возвращающий курсор.

Сильнотипизированный и слаботипизированный REF CURSOR.

TYPE имя_типа_курсора IS REF CURSOR [ RETURN возвращаемый_тип ];

TYPE refcursor IS REF CURSOR RETURN table1%ROWTYPE;

TYPE refcursor IS REF CURSOR;

Первая форма REF CURSOR называется сильно типизированной, поскольку тип структуры,
возвращаемый курсорной переменной, задается в момент объявления
(непосредственно или путем привязки к типу строки таблицы).

Вторая форма (без предложения RETURN) называется слаботипизированной.
Тип возвращаемой структуры данных для нее не задается.
Такая курсорная переменная обладает большей гибкостью, поскольку для нее можно задавать любые запросы
с любой структурой возвращаемых данных.

В Oracle 9i появился предопределенный слабый тип REF CURSOR с именем SYS_REFCURSOR,
теперь можно не определять собственный слабый тип, достаточно использовать стандартный тип Oracle:

DECLARE
my_cursor SYS_REFCURSOR;

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

TYPE my_type_rec IS RECORD (text t1.text%TYPE);
TYPE my_type_cur IS REF CURSOR RETURN my_type_rec;
v_curs my_type_cur;
v_text t1.text%TYPE;

BEGIN
OPEN v_curs
FOR
SELECT text
FROM t1
WHERE />
FETCH v_curs INTO v_text;

DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );

TYPE my_type_cur IS REF CURSOR RETURN t1%ROWTYPE;
v_curs my_type_cur;
v_var t1%ROWTYPE;

BEGIN
OPEN v_curs
FOR
SELECT *
FROM t1
WHERE />
FETCH v_curs INTO v_var;

DBMS_OUTPUT.PUT_LINE( 'id = ' || v_var.id || ', type = ' || v_var.type || ', text = ' || v_var.text );

id = 17367, type = SCHEDULE, text = FILE_WATCHER_SCHEDULE

Пример слаботипизированного курсора:

TYPE my_type_cur IS REF CURSOR;
v_curs my_type_cur;
v_text t1.text%TYPE;

BEGIN
OPEN v_curs
FOR
SELECT text
FROM t1
WHERE />
FETCH v_curs INTO v_text;

DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );

v_curs SYS_REFCURSOR;
v_text t1.text%TYPE;

BEGIN
OPEN v_curs
FOR
SELECT text
FROM t1
WHERE />
FETCH v_curs INTO v_text;

DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );

Курсор можно передавать в качестве параметра:

1. Функция принимающая курсор

CREATE OR REPLACE FUNCTION get_cursor(p_curs SYS_REFCURSOR)
RETURN VARCHAR2
IS
v_text t1.text%TYPE;

FETCH p_curs INTO v_text;

IF p_curs%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'Данные найдены. ' );
END IF;

2. Процедура принимающая текст SQL

CREATE OR REPLACE PROCEDURE get_sql (p_sql VARCHAR2)
IS
v_curs SYS_REFCURSOR;
v_res VARCHAR2(50);
BEGIN
IF v_curs%ISOPEN THEN
CLOSE v_curs;
END IF;
BEGIN
OPEN v_curs FOR p_sql;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to open cursor');
END;
v_res := get_cursor(v_curs);
CLOSE v_curs;
DBMS_OUTPUT.PUT_LINE(v_res);
END;
/

BEGIN
get_sql( 'SELECT text FROM t1 WHERE );
END;
/

Данные найдены.
FILE_WATCHER_SCHEDULE

Ещё примеры:

SET SERVEROUTPUT ON

var1 tab.col1%TYPE;
var2 tab.col2%TYPE;
var3 tab.col3%TYPE;

CURSOR cur IS
SELECT col1, col1, col3
FROM tab
ORDER BY col1;

BEGIN
-- Открываем курсор

LOOP
-- Выбираем из курсора строки
FETCH cur
INTO var1, var2, var3;

EXIT WHEN cur%NOTFOUND;

-- Выводим значения переменных
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var1 || ', col2 = ' || var2 || ', col3 = ' || var3 );
END LOOP;

-- Закрываем курсор
CLOSE cur;
END;
/

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

Для получения доступа к строкам из курсора можно использовать цикл FOR.
При использовании цикла FOR не нужно явно открывать курсор - цикл FOR сделает это автоматически.

SET SERVEROUTPUT ON

CURSOR cur IS
SELECT col1, col1, col3
FROM tab
ORDER BY col1;

BEGIN
FOR var IN cur LOOP
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var.col1 || ', col2 = ' || var.col2 || ', col3 = ' || var.col3 );
END LOOP;
END;
/

Выражение OPEN - FOR

С курсором можно использовать выражение OPEN - FOR, которое добавляет еще больше гибкости при обработке курсоров,
поскольку вы можете назначить курсор для другого запроса.
Запрос может быть любым корректным выражением SELECT.
Это означает что вы можете повторно использовать курсор и назначить курсору позже в коде другой запрос.

SET SERVEROUTPUT ON

-- Определим тип REF CURSOR
TYPE t_cur IS
REF CURSOR RETURN tab%ROWTYPE;

-- Определим объект типа t_cur
cur t_cur;

-- Определим объект для хранения столбцов из таблицы tab
var tab%ROWTYPE;

BEGIN
-- назначим запрос для объекта cur и откроем его
OPEN cur FOR
SELECT * FROM tab WHERE col1 < 5;

-- Выбираем строки из cur в var
LOOP
FETCH cur INTO var;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var.col1 || ', col2 = ' || var.col2 || ', col3 = ' || var.col3 );
END LOOP;

-- Закрываем объект cur
CLOSE cur;
END;
/

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

SET SERVEROUTPUT ON

DECLARE
-- Определим тип REF CURSOR
TYPE t_cur IS REF CURSOR;

-- Определим объект типа t_cur
cur t_cur;

-- Определим объект для хранения столбцов из таблицы tab1
var1 tab1%ROWTYPE;

-- Определим объект для хранения столбцов из таблицы tab2
var2 tab2%ROWTYPE;

BEGIN
-- назначим запрос для объекта cur и откроем его
OPEN cur FOR
SELECT * FROM tab1 WHERE col1 < 5;

-- Выбираем строки из cur в var1
LOOP
FETCH cur INTO var1;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var1.col1 || ', col2 = ' || var1.col2 || ', col3 = ' || var1.col3 );
END LOOP;

-- назначим новый запрос для объекта cur и откроем его
OPEN cur FOR
SELECT * FROM tab2 WHERE col1 < 3;

Церковь была отворена, за оградой стояло несколько саней;
по паперти ходили люди.
«Сюда! сюда!» — закричало несколько голосов .
А. С. Пушкин, «Метель»

Аннотация

Рассматриваются ссылки на курсор, представляющие собой инструмент продвинутого программирования в Oracle. Приводятся примеры работы через ссылки на курсор в программах на PL/SQL и Java и в SQL*Plus.

Ссылки на курсор

В пору моего обучения в техническом ВУЗе люди, как и сечас, были склонны называть себя как угодно, и в народе бытовал критерий настоящего математика. Право им назваться отдавалось тому, кто знал, что такое поле Галуа. Таки не ставши математиком, не берусь судить о корректности этого критерия (сдается, он ребяческий), однако занимаясь Oracle, могу предложить другой критерий профессионализма: для программиста этой СУБД.

В диалекте SQL, придуманном фирмой Oracle, есть много разных конструкций, не всякому известных; например, для аналитические функции, или же средства для работы с рукотворными объектами. Но это вещи специфические, не каждому программисту потребные, а вот ссылки на курсор – явление безусловно общего характера.

Они позволяют перенести программную логику на сервер. Клиентские приложения оказываются не так жестко привязаны к конкретным запросам; одни и те же запросы могут вызываться с одинаковым эффектом из программ на PL/SQL, C или Java.
Они позволяют перенести вычислительную нагрузку на сервер.

Поскольку принципиально для программирования ссылки на курсоры не нужны, большинство разработчиков обходится обычным способом общения с СУБД. Выгоды же извлекают программисты, которых и можно в данном контексте признать профессионалами.

Ссылки на курсор реализованы в Oracle программно и в SQL. В программе они реализуются в виде специальной переменной, и именно этот вариант показан в этой статье. Клиентскими средами будут выступать PL/SQL, SQL*Plus и Java.

Описание ссылки на курсор и использование в PL/SQL

На каком бы языке вы не общались с БД посредством ссылки на курсор, без программирования на PL/SQL не обойтись. Формальная сторона работы со ссылкой на курсор в PL/SQL обставлена просто.

Во-первых, чтобы завести в PL/SQL переменную-ссылку на курсор, нужно сначала описать ее тип. Это делается в разделе описания с помощью предложения TYPE:

Если конструкция RETURN присутствует, ссылка на курсор называется строгой; если нет – нестрогой. Нестрогая может ссылаться на любой курсор (запрос), а строгая – только на тот, что возвращает результат указанного типа.

Пример описания обоих типов ссылки на курсор:

Открытие курсора с помощью переменной-ссылки на курсор:

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

Во-вторых, для удобства программирования поддерживается «системный» тип SYS_REFCURSOR нестрогой ссылки на курсор. Так, в блоке выше, в разделе описания можно было бы не приводить предложение TYPE, а сразу сказать:

Тип SYS_REFCURSOR сокращает текст программы, а иногда позволяет и обойтись без создания служебного пакета (пример чего в этой статье не рассматривается).

Создание пакета в PL/SQL

Приводимые ниже примеры в каждой из трех сред программирования будут использовать для доступа к БД через ссылку на курсор один и тот же пакет. В реальной жизни именно на подобный пакет и ляжет описание требуемой программной логики. Тут же он воимя наглядности устроен максимально просто, (почти) безо всякой программно-прикладной логики, но это обстоятельство и обеспечивает ему универсальность.

Выдадим в SQL*Plus:

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

(а) в теле будет обязательно присутствовать предложение OPEN

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

Остальное регламентируется исключительно логикой приложения и организации программы.

Пример программирования в PL/SQL

В этом примере с помощью нашего пакета открываем курсор и передаем ссылку на курсор в программу. Извлечение результатов предложений SELECT выполняется отдельными процедурами, причем для результатов с разной структурой мы вынуждены предусмотреть разные процедуры извлечения. Это – плата за минимализм и общность пакета.

Выдадим в SQL*Plus:

(Чтобы не усложнять пример, результат на экране почти не оформляется).

Обратим внимание: программам, обрабатывающим по общей схеме однотипные курсоры, мы передаем не тексты запроса, а ссылки на уже открытый и динамически (а не статически, как в случае его определения в отдельном пакете) сформированный курсор.

Пример программирования в SQL*Plus

SQL*Plus позволяет заводить собственные переменные, в том числе и типа нестрогой ссылки на курсор. Открывается курсор, как и в примере выше, нашим пакетом, а вот извлечение возможно обычной командой PRINT. Эта команда умеет распознавать структуру фактического курсора, что очень удобно для работы.

Выдадим в SQL*Plus:

В отличие от предыдущего примера команда PRINT закрывает курсор, так что вторичная выдача

приведет к ошибке.

Пример программирования в Java

В клиентской программе на Java обращаться к БД через ссылку на курсор можно с помощью собственных расширений, сделанных фирмой Oracle в реализации ею драйвера JDBC. В программе ниже предполагается имя СУБД MYDB. Обратите внимание, что текст с запросом SQL передается нашему пакету объектом класса CallableStatement, а извлечение в программу ссылки на курсор делается после приведения этого объекта к сугубо Oracle’овскому классу OracleCallableStatement.

Получение в программу ссылки на курсор соответствует формированию объекта класса ResultSet, обработка которого делается стандартно.

Выдача второго запроса в программе ниже демонстрирует возможность использования одного и того же пакета для получения результата разной структуры. Очевидно, по своей гибкости эта техника находится посередине между тем, что имеется в PL/SQL и в SQL*Plus.

Читайте также: