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;

Источник

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.

Источник

Русские Блоги

Запись Oracle (14.13) курсор с параметрами

Один, курсор с параметрами

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

Рассмотрим пример передачи к курсору любого region_id и возврата имени страны / региона в этом регионе.

Два, использование курсора параметра

(1) Используйте синтаксис параметра для определения курсора

Каждый параметр, указанный в объявлении курсора, должен иметь соответствующее значение в операторе OPEN. Тип данных параметра такой же, как тип данных скалярной переменной, но им не задан размер. Имя параметра используется в предложении WHERE оператора SELECT курсора.

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

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

(2) Курсор с параметрами

Еще один пример курсора с параметрами

(3) Курсор для параметров цикла

При необходимости мы можем использовать цикл FOR курсора:

(4) Курсор с несколькими параметрами

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

другой пример
Курсор могут получить все IT-программисты, чей доход превышает 10 000 долларов.

Интеллектуальная рекомендация

Разница между ArrayList и LinkedList

ArrayList: Нижний слой представляет собой массив, хорошо подходящий для поиска данных (доступа) LinkedList: Базовый связанный список, удобный для изменения данных (включая добавление и удаление данных.

nginx скомпилируйте и установите

nginx скомпилируйте и установите 1. Установите среду компиляции 2. Установите программный пакет pcre (сделайте так, чтобы nginx поддерживал модуль перезаписи http) 3. Установите openssl-devel (сделайт.

Используйте Matlab, чтобы нарисовать трехмерный график разложения Фурье

цель: Изучите анализ преобразования Фурье и другие методы анализа. Понять взаимосвязь между частотной областью преобразования Фурье и временной областью. Используйте MATLAB, чтобы нарисовать трехмерну.

Поговорим о статусе IPv6 и переходе

1. Текущее состояние3 февраля 2011 года адреса IPv4 были выделены, и основные операторы ждут, чтобы исчерпать свои сбережения. Люди все больше полагаются на проводные и беспроводные маршрутизаторы, та.

The note introduces basic Python syntax and strings. Python notes of open courses @Codecademy. Brief Introduction Python is a high level scripting language with object oriented features. Python progra.

Источник

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

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

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

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

В неявных курсорах курсорные выражения никогда не используются.

Синтаксис курсорного выражения очень прост:

Oracle неявно открывает вложенный курсор при выборке строки, содержащей его вы­ражение из родительского или внешнего курсора. Он закрывается, когда:

Использование курсорных выражений

Существует два разных, но очень полезных способа использования курсорных вы­ражений:

Выборка вложенного запроса как столбца

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

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

Реализация потоковых функций в выражениях CURSOR

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

Ограничения, связанные с курсорными выражениями

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

Источник

Oracle курсор с параметрами

Настало время рассмотреть немаловажный аспект, а именно курсорные атрибуты. В PL/SQL имеется четыре основных курсорных атрибута %FOUND, %NOTFOUND, %ISOPEN и %ROWCOUNT. Атрибуты курсора объявляются подобно операторам %TYPE и %ROWTYPE, справа от имени курсора, вот так:

Для того, чтобы понять что это за операторы и для чего они предназначены, давайте предположим, что наша учебная таблица OFFICES, содержит два столбца OFFICE и CITY, а так же имеет только две записи. Вот таким образом: (на самом деле в вашей учебной базе таблица OFFICES содержит 5ть записей и больше полей, но пока просто включите свою фантазию!) 🙂

Теперь к этой таблице запишем вот такой блок PL/SQL и объявим в нем курсор get_offices следующим образом:

Теперь давайте рассмотрим по очереди все атрибуты курсоров. Начнем с атрибута %FOUND. Данный атрибут является логическим объектом и возвращает следующие значения согласно точкам, указанным в нашем курсоре. Надеюсь, что дополнительно пояснять не нужно, так как все достаточно хорошо видно в таблице!

Атрибут %NOTFOUND, как вы уже догадались, является полной противоположностью %FOUND и так же хорошо понятен из приведенной ниже таблицы:

Атрибут %ISOPEN так же логический объект и указывает только на то, открыт ли курсор или нет. Возвращаемые значение приведены ниже:

Атрибут %ROWCOUNT является числовым атрибутом и возвращает число строк считанных курсором на определенный момент времени. Его значение приведены ниже:

Надеюсь теперь вам стало окончательно ясно, как работает последний курсор из прошлого шага, где мы применили атрибут %NOTFOUND в теле цикла, дабы оповестить оного, что пора заканчивать чтение строк! 🙂 Что ж, применим наши знания на практике, так как сухая теория это еще не все. Перепишем, наш учебный курсор вот так:

Не пугайтесь этих 64-х строк кода, в дальнейшем наши программы будут все больше и больше, привыкайте! 🙂 Здесь, я постарался применить все атрибуты для того, чтобы на практике показать, как они работают и устроены, по этому еще раз самостоятельно посмотрите весь код и обратите внимание на то, как срабатывают разные атрибуты при разных условиях, а на дом вам задание написать тот же курсор, применив цикл и выбрав все записи из него! Дерзайте. 🙂

Источник