Что такое курсор в oracle

Мой блог

понедельник, 14 января 2013 г.

Курсоры в Oracle

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

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 );

Например я могу создать функцию, которая принимает некий входной параметр, создает курсор и возвращает тип 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

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;
/

SET SERVEROUTPUT ON

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

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

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

Источник

Курсоры (CURSOR) в PL/SQL: основные принципы программирования

При выполнении команды SQL из PL/SQL РСУБД Oracle назначает ей приватную рабочую область, а некоторые данные записывает в системную глобальную область (SGA, System Global Area). В приватной рабочей области содержится информация о команде SQL и набор данных, возвращаемых или обрабатываемых этой командой. PL/SQL предоставляет программистам несколько механизмов доступа к этой рабочей области и содержащейся в ней информации; все они так или иначе связаны с опреде­лением курсоров и выполнением операций с ними.

Основные принципы работы с курсорами

Курсор проще всего представить себе как указатель на таблицу в базе данных. Напри­мер, следующее объявление связывает всю таблицу employee с курсором employee_cur :

Объявленный курсор можно открыть:

Далее из него можно выбирать строки:

Завершив работу с курсором, его следует закрыть:

Терминология

Типичные операции с запросами и курсорами

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

Рис. 1. Упрощенная схема выборки данных с использованием курсора

Знакомство с атрибутами курсоров

В этом разделе перечисляются и вкратце описываются атрибуты курсоров.

Источник

Курсоры (Cursors)

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

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

Сравнение явных и неявных курсоров

В PL/SQL неявные курсоры – это курсоры, которые определяются в момент выполнения.

Явный курсор – это курсор, который определяется до начала выполнения.

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

Типичные операции над запросами

Для исполнения оператора SQL внутри программы PL/SQL выполняет одни и те же операции для всех типов курсоров. В одних случаях PL/SQL выполняет их автоматически, а в других (для явных курсоров) программисту необходимо написать соответствующий код.

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

Скомпилированная версия курсора может использоваться повторно во избежание расходов на разбор и повторную компиляцию.

Полный и частичный разбор

Процесс компиляции нового курсора называется полным разбором. Упрощенно данный процесс может быть представлен четырьмя этапами:

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

Tags: Курсоры, Cursors

PL/SQL

Исходные коды проекта хранятся на github. Можете заводить Issue и Discussions, при необходимости.
Чтобы задать вопрос, добавить свои знания, исправить ошибки и неточности, пишите в телеграм чате.

Источник

PL / SQL — курсоры

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

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

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

Неявные курсоры автоматически создаются Oracle при каждом выполнении оператора SQL, когда для оператора нет явного курсора. Программисты не могут контролировать неявные курсоры и информацию в них.

Всякий раз, когда выполняется оператор DML (INSERT, UPDATE и DELETE), с этим оператором связывается неявный курсор. Для операций INSERT курсор содержит данные, которые необходимо вставить. Для операций UPDATE и DELETE курсор определяет строки, которые будут затронуты.

Возвращает TRUE, если инструкция INSERT, UPDATE или DELETE затронула одну или несколько строк или инструкция SELECT INTO вернула одну или несколько строк. В противном случае он возвращает FALSE.

Логическая противоположность% FOUND. Он возвращает TRUE, если инструкция INSERT, UPDATE или DELETE не затронула строки, или инструкция SELECT INTO не вернула строки. В противном случае он возвращает FALSE.

Всегда возвращает FALSE для неявных курсоров, потому что Oracle автоматически закрывает курсор SQL после выполнения соответствующего оператора SQL.

Возвращает количество строк, затронутых оператором INSERT, UPDATE или DELETE или возвращенных оператором SELECT INTO.

Источник

Работа с явными курсорами в PL/SQL

В этом блоке PL/SQL выполняются следующие операции с курсором.

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

Объявление явного курсора

Чтобы получить возможность использовать явный курсор, его необходимо объявить в разделе объявлений блока PL/SQL или пакета:

Несколько примеров объявлений явных курсоров.

Имя курсора

Объявление курсора в пакете

В секции RETURN могут быть указаны любые из следующих структур данных:

Прежде чем переходить к подробному рассмотрению секции RETURN и ее преимуществ, давайте сначала разберемся, для чего вообще может понадобиться объявление курсоров в пакете? Почему не объявить явный курсор в той программе, в которой он использу­ется — в процедуре, функции или анонимном блоке?

Ответ прост и убедителен. Определяя курсор в пакете, можно многократно использовать заданный в нем запрос, не повторяя один и тот же код в разных местах приложения. Реа­лизация запроса в одном месте упрощает его доработку и сопровождение кода. Некоторая экономия времени достигается за счет сокращения количества обрабатываемых запросов.

Объявляя курсоры в пакетах для повторного использования, следует учитывать одно важное обстоятельство. Все структуры данных, в том числе и курсоры, объ­являемые на «уровне пакета» (не внутри конкретной функции или процедуры), сохраняют свои значения на протяжении всего сеанса. Это означает, что пакетный курсор будет оставаться открытым до тех пор, пока вы явно не закроете его, или до завершения сеанса. Курсоры, объявленные в локальных блоках, автоматически закрываются при завершении этих блоков.

Все символы до ключевого слова IS образуют спецификацию, а после IS следует тело курсора. Разделение объявления курсора может служить двум целям.

Открытие явного курсора

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

Здесь имякурсора — это имя объявленного ранее курсора, а аргумент — значение, передаваемое курсору, если он объявлен со списком параметров.

Независимо от того, когда будет выполнена первая выборка данных, реализованная в Oracle модель целостности данных гарантирует, что все операции выборки будут возвращать данные в состоянии на момент открытия курсора. Иными словами, от от­крытия и до закрытия курсора при выборке из него данных полностью игнорируются выполняемые за это время операции вставки, обновления и удаления.

При попытке открыть уже открытый курсор PL/SQL выдаст следующее сообщение об ошибке:

Поэтому перед открытием курсора следует проверить его состояние по значению атри­бута %isopen:

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

Если в программе выполняется цикл FOR с использованием курсора, этот курсор не нуждается в явном открытии (выборке данных, закрытии). Ядро PL/SQL делает это автоматически.

Выборка данных из явного курсора

Примеры явных курсоров

Следующие примеры демонстрируют разные способы выборки данных.

Выборка после обработки последней строки

Следовательно, если вам понадобится узнать, успешно ли прошла выборка очередной строки, проверка переменных из списка INTO ничего не даст. Вместо этого следует про­верить атрибут %FOUND или %NOTFOUND (см. далее раздел «Атрибуты явных курсоров»).

Псевдонимы столбцов явного курсора

Команда SELECT в объявлении курсора определяет список возвращаемых им столбцов. Наряду с именами столбцов таблиц этот список может содержать выражения, называ­емые вычисляемыми, или виртуальными столбцами.

Рассмотрим следующий запрос. Команда SELECT выбирает названия всех компаний, заказывавших товары в течение 2001 года, а также общую сумму заказов (предпола­гается, что для текущего экземпляра базы данных по умолчанию используется маска форматирования DD-MON-YYYY ):

При выполнении этой команды в SQL*Plus будет получен следующий результат:

Как видите, заголовок столбца SUM ( INV_AMT ) плохо подходит для отчета, но для простого просмотра данных он вполне годится. Теперь выполним тот же запрос в программе PL/ SQL с использованием явного курсора и добавим псевдоним столбца:

Закрытие явного курсора

Когда-то в детстве нас учили прибирать за собой, и эта привычка осталась у нас (хотя и не у всех) на всю жизнь. Оказывается, это правило играет исключительно важную роль и в программировании, и особенно когда дело доходит до управления курсорами. Никогда не забывайте закрыть курсор, если он вам больше не нужен!

Синтаксис команды CLOSE :

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

Атрибуты явных курсоров

Oracle поддерживает четыре атрибута ( %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNTM ) для полу­чения информации о состоянии явного курсора. Ссылка на атрибут имеет следующий синтаксис: курсор%атрибут

Здесь курсор — имя объявленного курсора.

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

Таблица 1. Атрибуты явных курсоров

Значения атрибутов курсоров до и после выполнения различных операций с ними указаны в табл. 2.

Работая с атрибутами явных курсоров, необходимо учитывать следующее:

Таблица 2. Значения атрибутов курсоров

Использование всех этих атрибутов продемонстрировано в следующем примере:

Параметры курсора

Ранее в блогах уже неоднократно приводились примеры использования параметров про­цедур и функций. Параметры — это средство передачи информации в программный модуль и из него. При правильном использовании они делают модули более полезными и гибкими.

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

Количество параметров курсора не ограничено. При вызове OPEN для курсора должны быть заданы значения всех параметров (кроме параметров, для которых определены значения по умолчанию).

Для выполнения этой задачи мы не стали использовать параметры, да они и не нужны. В данном случае курсор возвращает все строки, относящиеся к конкретной категории. Но как быть, если при каждом обращении к этому курсору категория изменяется?

Курсоры с параметрами

Конечно, мы не станем определять отдельный курсор для каждой категории — это совер­шенно не согласуется с принципом разработки приложений, управляемых данными. Нам нужен всего один курсор, но такой, для которого можно было бы менять категорию — и он все равно возвращал бы требуемую информацию. И лучшим (хотя и не единственным) решением этой задачи является определение параметризованного курсора:

Открытие курсора с параметрами

Новый курсор можно открывать с указанием любой категории:

Вместо того чтобы считывать категорию из таблицы, мы просто подставляем параметр category_in в список выборки. Результат остается прежним, потому что условие WHERE ограничивает категорию выборки значением параметра.

Область действия параметра курсора

Режимы параметра курсора

Значения параметров по умолчанию

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

Поскольку для параметра emp_id_in определено значение по умолчанию, в команде FETCH его значение можно не указывать. В этом случае курсор вернет информацию о сотруднике с кодом 0.

Источник