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

Обновлено: 21.11.2024

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

Знание цикла создания курсора и простого создания курсора является обязательным для понимания концепции параметра курсора, поэтому я настоятельно рекомендую вам прочитать предыдущее руководство «Введение в курсор и простое создание курсора».

Что такое параметризованный курсор?

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

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

Синтаксис параметризованного курсора в базе данных Oracle

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

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

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

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

Некоторые замечательные преимущества параметризованных курсоров

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

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

Избегает проблем с областью действия

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

Когда нам нужен параметризованный курсор?

Вам должно быть интересно, когда нам нужен курсор с параметрами в нашем PL/SQL.

Самый простой ответ — когда вам нужно использовать курсор более чем в одном месте с разными значениями для одного и того же предложения WHERE вашего оператора SELECT.

Если вы можете что-то добавить к этому и у вас есть другая идея для использования параметризованного курсора. Тогда я всегда готов выслушать ваши мысли, не забудьте поделиться ими со мной на моей странице в Facebook или Twitter.

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

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

В этом учебном блоге все. Если вы хотите, чтобы я что-то добавил, дайте мне знать в моих социальных сетях. Также не забудьте поделиться этим блогом в социальных сетях. Спасибо и хорошего дня!

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

курсор c7 (v_key varchar2)
выбирает initcap(book_title) bk_title,
сумма(количество) продаж, author_key
из
объединяет продажи книги с помощью (book_key)
присоединиться к book_author, используя (book_key)
где author_key = v_key
группировать по initcap(book_title), author_key;

Курсор выше определяется как принимающий параметр с типом данных varchar2. В этом примере переменная используется в предложении WHERE для фильтрации результатов перед группировкой. Заменив курсор c7 на c6 в приведенном выше примере, блок PL/SQL становится намного более эффективным, поскольку внутренний курсор возвращает только строку, принадлежащую указанному автору.

SQL> declare
2 курсор c5 is
3 select initcap(author_last_name) a_name,
4 author_key
5 от автора;
6 курсор c7 (v_key varchar2 ) is
7 выберите initcap(book_title) bk_title,
8 сумма(количество) продаж, author_key
9 из
10 продаж присоединения книг с использованием (book_key)
11 присоединения book_author с использованием (book_key)
12 где author_key = v_key
13 группировать по initcap(book_title), author_key;
14 r_c5 c5%rowtype;
15 r_c7 c7%rowtype;
16 begin
17 открыть c5;
18 цикл
19 извлечь c5 в r_c5;
20 выйти, когда c5%notfound ;
21 dbms_output.put_line(chr(10)||
r_c5.a_name);
22 begin
23 open c7(r_c5.author_key);
24 loop
25 выборка c7 в r_c7;
26 выход, когда c7%notfound;
27 dbms_output.put_line (r_c7.bk_title||
28 ', '||r_c7.sales);
29 конец петли;
30 закрыть с7;
31 конец;
32 конец петли;
33 закрыть с5;
34 закрыть;
35 /

Результаты, возвращаемые в этом примере, такие же, как и в предыдущем примере, и для экономии места они были опущены. Этот пример с использованием c7 намного эффективнее, чем использование c6, потому что он устраняет возврат ненужных данных в курсор c7, а также позволяет удалить IF/ Предложение THEN с проверкой его условия. Обратите внимание, что строка 23 открыла курсор и передала текущий author_key из внешнего курсора. Если бы строка 23 не передала параметр при открытии курсора, PL/SQL выдал бы исключение.

open c7;
*
ОШИБКА в строке 23:
ORA-06550: строка 23, столбец 7:
PLS-00306: неправильное количество или типы аргументов в вызов 'C7'
ORA-06550: строка 23, столбец 7:
PL/SQL: оператор SQL игнорируется

Давайте закончим одним последним замечанием о курсорах. Этот пример использовался для демонстрации вложенных курсоров и передачи параметров курсорам. Несмотря на то, что окончательная версия была намного эффективнее первой, она по-прежнему нарушает важное правило программирования PL/SQL: не выполняйте SQL внутри цикла, если только нет другого способа получить данные.

Пример можно было бы написать для выполнения одного курсора, возвращающего все необходимые данные: имя_автора, название_книги и продажи. И тогда он мог бы использовать цикл для обработки результатов. Этот метод был бы даже более эффективным, поскольку он выполнял бы только один запрос к базе данных. До сих пор мы вручную управляли курсором, и много раз вам требовался такой уровень контроля. Однако Oracle предоставила упрощенный метод использования курсоров с циклом FOR.

Обучение Oracle от Дона Берлесона

Лучшие на сайте «Учебные курсы Oracle» находятся на расстоянии одного телефонного звонка! Вы можете пройти индивидуальное обучение Oracle от Дональда Берлесона прямо в своем магазине!

Бурлесон — американская команда

Примечание. Эта документация по Oracle была создана в качестве справочника по поддержке и обучению Oracle для использования нашими специалистами-консультантами по настройке производительности администраторов баз данных. Не стесняйтесь задавать вопросы на нашем форуме Oracle.

Проверьте опыт! Любой, кто рассматривает возможность использования услуг эксперта службы поддержки Oracle, должен самостоятельно проверить свои полномочия и опыт, а не полагаться на рекламу и самопровозглашенный опыт. Все законные эксперты Oracle публикуют свои квалификации Oracle.

Ошибки? Технология Oracle меняется, и мы стараемся обновлять нашу информацию о поддержке BC Oracle. Если вы обнаружите ошибку или у вас есть предложение по улучшению нашего контента, мы будем признательны за ваш отзыв. Просто электронная почта:

и укажите URL-адрес страницы.

Burleson Consulting

Оракул поддержки баз данных

Для выполнения многострочного запроса Oracle открывает безымянную рабочую область, в которой хранится информация об обработке. Курсор позволяет вам назвать рабочую область, получить доступ к информации и обрабатывать строки по отдельности. Дополнительные сведения см. в разделе «Запрос данных с помощью PL/SQL».

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

Описание ключевого слова и параметра

Явный курсор, ранее объявленный в текущей области.

Спецификатор типа. Синтаксис типа данных см. в разделе «Объявление констант и переменных».

Таблица базы данных или представление, которое должно быть доступно при разработке объявления.

Комбинация переменных, констант, литералов, операторов и вызовов функций. Самое простое выражение состоит из одной переменной. Когда объявление разработано, параметру присваивается значение выражения. Значение и параметр должны иметь совместимые типы данных.

Переменная, объявленная как формальный параметр курсора.Параметр курсора может появиться в запросе везде, где может появиться константа. Формальные параметры курсора должны быть параметрами IN. Запрос также может ссылаться на другие переменные PL/SQL в пределах своей области действия.

Определяемая пользователем запись, ранее объявленная в текущей области.

Определяемый пользователем тип записи, который был определен с помощью спецификатора типа данных RECORD .

Указывает тип данных возвращаемого значения курсора. Вы можете использовать атрибут %ROWTYPE в предложении RETURN, чтобы указать тип записи, представляющий строку в таблице базы данных или строку, возвращенную ранее объявленным курсором. Кроме того, вы можете использовать атрибут %TYPE для указания типа данных ранее объявленной записи.

Тело курсора должно иметь оператор SELECT и то же предложение RETURN, что и соответствующая спецификация курсора. Кроме того, количество, порядок и типы данных выбранных элементов в предложении SELECT должны соответствовать предложению RETURN.

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

Запрос, который возвращает результирующий набор строк. Его синтаксис похож на синтаксис select_into_statement без предложения INTO. См. «Выражение SELECT INTO». Если объявление курсора объявляет параметры, каждый параметр должен использоваться в запросе.

Предоставляет тип данных ранее объявленной пользовательской записи.

Вы должны объявить курсор, прежде чем ссылаться на него в операторах OPEN, FETCH или CLOSE. Вы должны объявить переменную, прежде чем ссылаться на нее в объявлении курсора. Слово SQL зарезервировано PL/SQL как имя по умолчанию для неявных курсоров и не может использоваться в объявлении курсора.

Нельзя присваивать значения имени курсора или использовать его в выражении. Однако курсоры и переменные следуют одним и тем же правилам области видимости. Дополнительные сведения см. в разделе «Область действия и видимость идентификаторов PL/SQL».

Вы извлекаете данные из курсора, открывая его, а затем извлекая из него. Поскольку оператор FETCH указывает целевые переменные, использование предложения INTO в операторе SELECT объявления cursor_declaration является избыточным и недопустимым.

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

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

SQL, SQL Server, учебные пособия, Oracle, PL/SQL, вопросы и ответы для интервью, соединения, вопросы с несколькими вариантами ответов, викторины, хранимые процедуры, выбор, вставка, обновление, удаление и другие последние темы по SQL, SQL Server и Oracle .

Курсоры Oracle PL/SQL с параметрами

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

Мы можем передавать параметры в курсор и использовать их в запросе.

Мы можем передавать значения только курсору; и не может передавать значения из курсора через параметры.

Определяется только тип данных параметра, а не его длина.

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

Пример курсоров с параметрами

Следующий курсор печатает номер и название отдела в одной строке, за которыми следуют сотрудники, работающие в этом отделе (имя и зарплата) и общая зарплата.

DECLARE
CURSOR cur_dept IS SELECT * FROM dept ORDER BY deptno;
КУРСОР cur_emp (par_dept VARCHAR2) ЕСТЬ
ВЫБЕРИТЕ ename, зарплату
ОТ emp
ГДЕ deptno = par_dept
ЗАКАЗАТЬ ПО ename;

r_dept DEPT%ROWTYPE;
var_ename EMP.ENAME%TYPE;
var_salary EMP.SALARY%TYPE;
var_tot_salary NUMBER (10,2);

НАЧАТЬ
ОТКРЫТЬ cur_dept;
ЦИКЛ

FETCH cur_dept INTO r_dept;
ВЫХОД КОГДА cur_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Отдел: ' || r_dept.deptno || ' - '|| r_dept.dname);
var_tot_salary := 0;
ОТКРЫТЬ cur_emp (r_dept.deptno);
LOOP
FETCH cur_emp INTO var_ename, var_salary;
ВЫХОД КОГДА cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Имя: ' ||имя_переменной || ' Зарплата:'||var_salary);
var_tot_salary := var_tot_salary + var_salary;
КОНЕЦ ЦИКЛА;
ЗАКРЫТЬ cur_emp;
DBMS_OUTPUT.PUT_LINE ('Общая зарплата отдела: ' || var_tot_salary);

КОНЕЦ ЦИКЛА;
ЗАКРЫТЬ cur_dept;
КОНЕЦ;
/

Режим параметров может быть только IN.
Курсор становится более удобным для повторного использования с параметрами курсора.
Значения по умолчанию могут быть присвоены параметрам курсора.
Область параметров курсора локальна для курсора.

Нет комментариев:

Оставить комментарий

SQL Server — вопросы с несколькими вариантами ответов

SQL — вопросы с несколькими вариантами ответов

SQL Server, Oracle, SQL и PL/SQL

Если вам нравится читать этот блог SQL Server, Oracle, SQL/PLSQL, помогите увеличить доступность блогов, нажав кнопку "g +1".

SQL Server / Oracle SQL

Введение в SQL
Синтаксис SQL
Выбор SQL
Вставка SQL
Обновление SQL
Удаление SQL
Соединения SQL
Примеры соединений SQL
br /> Явный SQL против.IN
SQL — NULL
Функциональные зависимости
Нормализация
Свойства ACID
Подзапросы SQL
SQL — Запросы с примерами
Представления SQL
Вставка, обновление, удаление представлений
Представления соединения SQL
Встроенные представления SQL
SQL — N-я самая высокая зарплата
SQL Вторая самая высокая зарплата
SQL — Усечение/удаление разницы
> SQL — разница Усечение/удаление
SQL — разница HAVING / WHERE
SQL — разница CAST / CONVERT
SQL — разница NOT IN / NOT EXIST
SQL — разница IN / EXISTS SQL — разница UNION / UNION ALL
SQL — разница вложенных / коррелированных подзапросов
SQL — REPLACE
SQL — TOP
SQL — LIKE
SQL — SELECT INTO
SQL — CREATE TABLE
SQL — CREATE TABLE (дополнительные примеры)
SQL — ALTER TABLE
SQL — Представления различий/материализованные представления
Счетчик SQL
SQL Обновить
SQL Clustered / Non -Кластеризованные индексы
SQL - Удалить повторяющиеся записи
SQL - Отличие уникального/первичного ключа
SQL - GETDATE()
SQL - DATEDIFF()
SQL - DATEADD()
SQL - DATEPART()
SQL - Convert()
SQL - SUM()
SQL - AVG()
SQL - MIN()
SQL - MAX()
SQL - Вставить в выборку
SQL - Вставить несколько значений в таблицу
SQL - Целостность ссылок
SQL - Ограничение ненулевого значения
SQL - Ограничение уникальности
SQL - Ограничение уникальности
br /> SQL — ограничение первичного ключа
SQL — ограничение внешнего ключа
SQL — ограничение по умолчанию
SQL — ограничение проверки
SQL — ROLLUP
SQL — CUBE
SQL - STUFF()
SQL - Count_Big
SQL - Binary_Checksum
SQL - Checksum_AGG
SQL - Index Include
SQL - Покрытый запрос
SQL - Identity
SQL — sp_columns
SQL — Diff локальные/глобальные временные таблицы
SQL — хранимая процедура
SQL — sp_ who
SQL — сеанс
SQL — динамический SQL
SQL — план выполнения SQL Server
SQL — sp_executesql
SQL — разница Execute/Execute()
SQL - Выражение Case
SQL - Пример переменной XML
SQL - Pivot
SQL - Слияние
Пример слияния SQL
Пример предложения слияния вывода
SQL - Compute / Вычисления по
SQL — Diff SCOPE_IDENTITY / IDENT_CURRENT / @@IDENTITY
SQL Coalesce
SQL — Импорт данных в таблицу SQL Server
SQL — Удаление повторяющихся записей в таблице SQL Server
/> SQL — проверка наличия столбца в таблице SQL Server
SQL — Has_perms_by_name
SQL — разница между логинами/пользователями
SQL — разница между переменной таблицы/таблицей Temp
SQL — Возможности SQLServer 2012
SQL — SERVERPROPERTY
SQL — Поиск первичного/внешнего ключа в таблице
SQL — Последовательность
SQL — Индексы Columnstore
SQL — Разница Первичный / Candida te Key
SQL — Throw — SQL Server 2012
SQL — Разница GUID / INT
Расширения SQL Server 2012
SQL — Разница между перекрестным соединением / полным внешним соединением
SQL - NTILE()
SQL - Зависимости объектов
SQL - Напоминание по электронной почте об обновлении таблицы
SQL - Определение объекта
SQL Server - Построитель отчетов 3
SQL Server - Определяется пользователем Роли
SQL Server — Filetables
SQL Server — Содержащиеся базы данных
SQL Server — Обновление из Select
SQL Server — Методы XML
SQL Server — Добавление столбца в таблицу
SQL Server — Добавление столбца в таблицу
br /> SQL Server — вставка нескольких строк
SQL Server — добавление столбца идентификации в таблицу
SQL Server — индексы
SQL Server — функции сравнения / хранимые процедуры
SQL — операторы сравнения Любые / Все
SQL — разница между первичным/внешним ключом
SQL — разница замены / вставки
SQL — ROW_NUMBER()
SQL — пересечение
SQL Server — локальные переменные SQL Server — Обновление с объединением
SQL Server — Слияние различий / Объединение хэшей
SQL Server — Обновление различий / Эксклюзивная блокировка
SQL Server — Разностный экземпляр / база данных
SQL Server - Разница Datetime2 / Datetime
SQL Server - Разница Disable / Drop Indexes
SQL Server - Разница Decimal / Float
SQL Server - DATEDIFF с примерами
SQL Server - Разница Raiserror / Throw SQL Server — проверка наличия столбца в таблице
SQL Server — запрос Excel с использованием связанного сервера
SQL Server — ожидание
SQL Server — ранг разницы, Dense_Rank и Row_Number
SQL Сервер - Примеры PATINDEX
SQL Server - Примеры COLLATE
SQL Server - Разница CHAR / VARCHAR
SQL Server - Вопросы для интервью для тестировщиков
Вопросы для интервью SQL
Разница GRANT / DENY / REVOKE
Подзапросы в предложении WHERE - Примеры
Как проверить индексы в таблице?
Примеры DATENAME
Вопросы для собеседования по присоединению к SQL

Oracle PL/SQL

Введение в PL/SQL
Процедуры PL/SQL
Функции PL/SQL
Коллекции PL/SQL
Записи PL/SQL
PL/SQL Записи на основе таблиц
Записи, определяемые программистом PL/SQL
Записи на основе курсоров PL/SQL
Таблицы PL/SQL
PL/SQL Varrays
Вложенные таблицы PL/SQL
Циклы PL/SQL
Триггеры PL/SQL
Курсоры PL/SQL
Неявные курсоры PL/SQL
Явные курсоры PL/SQL
Курсоры PL/SQL REF PL/SQL Cursor For Loop
PL/SQL Cursors with Parameters
PL/SQL Where Current Of and For Update
Примеры PL/SQL Cursors
PL/SQL Exceptions
Вопросы для интервью по PL/SQL
Карта сайта SQL PL/SQL

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