1. Главная страница » Компьютеры » Oracle курсор с параметрами

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

Автор: | 16.12.2019

Раньше мы с вами рассматривали оператор SELECT, который получал все данные из таблиц. Но как известно, чаще вы будете использовать условие WHERE в курсорном операторе SELECT. И в дальнейшем вам будет необходимо определять условия выборки. Как это сделать наиболее правильно, самый простой способ вот так:

Замечательно, а если нужно другое значение отличное от 37? Что тогда? Напрашивается вывод о том, что в этом случае необходим курсор с параметрами или "параметризованный курсор"! Он определяется вот так:

Но еще более правильно, будет сделать вот так:

Применив оператор TYPE мы сделали код более мобильным, так как если тип поля изменится, то не нужно будет лопатить весь код в поисках ошибки! Это так же считается наиболее правильным стилем программирования! 🙂 И теперь при открытии такого курсора, его оператор OPEN будет принимать передаваемый параметр вот так:

Подытожим наши заключения следующим блоком:

После запуска получаем:

Вот так отработал наш с вами первый параметризованный курсор, надеюсь, все понятно, если нет — спрашивайте! Для закрепления, давайте запишем тот же курсор, с циклом FOR:

Параметров у курсора может быть несколько, например вот так:

Получаем после запуска:

Что собственно и требовалось доказать. Вот так работают курсоры с параметрами и без таковых. Надеюсь, теперь вы научились писать курсоры, и выбирать из них данные! 🙂

понедельник, 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 );

Читайте также:  Ford ecosport объем багажника

Во время компиляции 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;

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

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.
Это означает что вы можете повторно использовать курсор и назначить курсору позже в коде другой запрос.

Читайте также:  Https go mail ru sct 1

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 Андрей на 12:55

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

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

  • Неявные курсоры. Команда SELECT .. . INTO считывает одну строку данных и при­сваивает ее в качестве значения локальной переменной программы. Это простейший (и зачастую наиболее эффективный) способ доступа к данным, но он часто ведет к написанию сходных и даже одинаковых SQL -команд SELECT во многих местах программы.
  • Явные курсоры. Запрос можно явно объявить как курсор в разделе объявлений локального блока или пакета. После этого такой курсор можно будет открывать и выбирать из него данные в одной или нескольких программах, причем возмож­ности управления явным курсором шире, чем у неявного.
  • Курсорные переменные. Курсорные переменные (в объявлении которых задается тип REF CURSOR ) позволяют передавать из программы в программу указатель на результирующий набор строк запроса. Любая программа, для которой доступна такая переменная, может открыть курсор, извлечь из него необходимые данные и закрыть его.
  • Курсорные выражения. Ключевое слово CURSOR превращает команду SELECT в набор REF CURSOR, который может использоваться совместно с табличными функциями для повышения производительности приложения.
  • Динамические SQL -запросы. Oracle позволяет динамически конструировать и вы­полнять запросы с использованием либо встроенного динамического SQL либо программ пакета DMBS_SQL . Этот встроенный пакет описывается в документации Oracle, а также в книге Oracle Built-in Packages (O’Reilly).

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

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

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

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

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

В этом случае каждая выбранная из курсора запись представляет строку таблицы employee. Однако с курсором можно связать любую допустимую команду SELECT . В сле­дующем примере в объявлении курсора объединяются три таблицы:

В данном случае курсор действует не как указатель на конкретную таблицу базы дан­ных — он указывает на виртуальную таблицу или неявное представление, определяемое командой SELECT . (Такая таблица называется виртуальной, потому что команда SELECT генерирует данные с табличной структурой, но эта таблица существует только вре­менно, пока программа работает с возвращенными командой данными.) Если тройное объединение возвращает таблицу из 20 строк и 3 столбцов, то курсор действует как указатель на эти 20 строк.

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

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

  • Статический SQL . Команда SQL называется статической, если она полностью определяется во время компиляции программы.
  • Динамический SQL . Команда SQL называется динамической, если она строится и выполняется на стадии выполнения программы, так что в программном коде нет ее фиксированного объявления. Для динамического выполнения команд SQL мо­гут использоваться программы встроенного пакета DBMS_SQL (имеющегося во всех версиях Oracle) или встроенный динамический SQL .
  • Результирующий набор строк. Набор строк с результирующими данными, удов­летворяющими критериям, определяемым командой SQL. Результирующий набор кэшируется в системной глобальной области с целью ускорения чтения и модифи­кации его данных.
  • Неявный курсор. При каждом выполнении команды DML ( INSERT, UPDATE, MERGE или delete) или команды select into, возвращающей строку из базы данных прямо в структуру данных программы, PL/SQL создает неявный курсор. Курсор этого типа называется неявным, поскольку Oracle автоматически выполняет многие связанные с ним операции, такие как открытие, выборка данных и даже закрытие.
  • Явный курсор. Команда SELECT , явно определенная в программе как курсор. Все опе­рации с явным курсором (открытие, выборка данных, закрытие и т. д.) в программе должны выполняться явно. Как правило, явные курсоры используются для выборки из базы данных набора строк с использованием статического SQL.
  • Курсорная переменная. Объявленная программистом переменная, указывающая на объект курсора в базе данных. Ее значение (то есть указатель на курсор или резуль­тирующий набор строк) во время выполнения программы может меняться, как у всех остальных переменных. В разные моменты времени курсорная переменная мо­жет указывать на разные объекты курсора. Курсорную переменную можно передать в качестве параметра процедуре или функции. Такие переменные очень полезны для передачи результирующих наборов из программ PL/SQL в другие среды (например, Java или Visual Basic).
  • Атрибут курсора. Атрибут курсора имеет форму %имя_атрибута и добавляется к имени курсора или курсорной переменной. Это что-то вроде внутренней пере­менной Oracle, возвращающей информацию о состоянии курсора — например о том, открыт ли курсор, или сколько строк из курсора вернул запрос. У явных и неявных курсоров и в динамическом SQL в атрибутах курсоров существуют некоторые раз­личия, которые рассматриваются в этой статье.
  • SELECT FOR UPDATE. Разновидность обычной команды SELECT , устанавливающая блокировку на каждую возвращаемую запросом строку данных. Пользоваться ею следует только в тех случаях, когда нужно «зарезервировать» запрошенные данные, чтобы никто другой не мог изменить их, пока с ними работаете вы.
  • Пакетная обработка. В Oracle8i и выше PL/SQL поддерживает запросы с секцией BULK COLLECT , позволяющей за один раз выбрать из базы данных более одной строки.
Читайте также:  Lexus nx i рестайлинг 200

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

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

  • Разбор. Первым шагом при обработке команды SQL должен быть ее разбор (син­таксический анализ), то есть проверка ее корректности и формирование плана выполнения (с применением оптимизации по синтаксису или по стоимости в за­висимости от того, какое значение параметра 0PTIMIZER_M0DE задал администратор базы данных).
  • Привязка. Привязкой называется установление соответствия между значениями программы и параметрами команды SQL. Для статического SQL привязка произ­водится ядром PL/SQL . Привязка параметров в динамическом SQL выполняется явно с использованием переменных привязки.
  • Открытие. При открытии курсора определяется результирующий набор строк команд SQL, для чего используются переменные привязки. Указатель активной или текущей строки указывает на первую строку результирующего набора. Иногда явное открытие курсора не требуется; ядро PL/SQL выполняет эту операцию авто­матически (так происходит в случае применения неявных курсоров и встроенного динамического SQL ).
  • Выполнение. На этой стадии команда выполняется ядром SQL .
  • Выборка. Выборка очередной строки из результирующего набора строк курсора осуществляется командой FETCH . После каждой выборки PL/SQL перемещает ука­затель на одну строку вперед. Работая с явными курсорами, помните, что и после завершения перебора всех строк можно снова и снова выполнять команду FETCH , но PL/SQL ничего не будет делать (и не станет инициировать исключение) — для вы­явления этого условия следует использовать атрибуты курсора.
  • Закрытие. Операция закрывает курсор и освобождает используемую им память. Закрытый курсор уже не содержит результирующий набор строк. Иногда явное за­крытие курсора не требуется, последовательность PL/SQL делает это автоматически (для неявных курсоров и встроенного динамического SQL ).

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

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

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

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

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

*

code