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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

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.

Источник

Курсорные переменные и REF CURSOR в PL/SQL на примерах

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

Важнейшим преимуществом курсорных переменных является то, что они предостав­ляют механизм передачи результатов запроса (выбранных из строк курсора) между разными программами PL/SQL, в том числе между клиентскими и серверными про­граммами. До выхода PL/SQL Release 2.3 приходилось выбирать из курсора все данные, сохранять их в переменных PL/SQL (например, в коллекции) и передавать эти данные в аргументах. А курсорная переменная позволяет передать другой программе ссылку на объект курсора, чтобы та могла работать с его данными. Это упрощает программный код и повышает его эффективность.

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

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

Рис. 1. Передача ссылки на курсор между программами

Когда используются курсорные переменные?

Ниже перечислены основные области применения курсорных переменных.

Сходство со статическими курсорами

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

Объявление типов REF CURSOR

По аналогии с таблицами PL/SQL или записями типа, определяемого программистом, курсорная переменная объявляется в два этапа.

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

Обратите внимание, что секция RETURN в объявлениях типа REF CURSOR не обязательна, поэтому допустимы оба следующих объявления:

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

Объявление курсорной переменной

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

Пример создания курсорной переменной:

Рис. 2. Курсоры и курсорные переменные

Важно понимать различие между операцией объявления курсорной переменной и опе­рацией создания реального объекта курсора, то есть результирующего набора строк, определяемого командой SQL курсора. Как известно, константа является значением, а переменная — указателем на значение; аналогичным образом статический набор яв­ляется набором данных, а курсорная переменная — указателем на этот набор. Различие продемонстрировано на рис. 2. Обратите внимание на то, что две разные переменные в разных программах ссылаются на один и тот же объект курсора.

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

Значение (объект курсора) присваивается курсорной переменной при открытии курсора. Таким образом, синтаксис традиционной команды OPEN позволяет использовать после секции FOR команду SELECT :

Рис. 3. Совместимость типа данных REF CURSOR и типа данных значений, возвращаемых командой SELECT

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

Выборка данных из курсорной переменной

Как уже упоминалось, синтаксис команды FETCH для курсорной переменной не отлича­ется от синтаксиса статического курсора:

Если курсорная переменная объявляется в сильнотипизированной форме, компилятор PL/SQL проверяет совместимость типов структур в предложении INTO со структурой запроса, связанного с курсорной переменной.

Если курсорная переменная объявляется в слаботипизированной форме, компилятор не сможет выполнить подобную проверку. Данные из такой курсорной переменной могут извлекаться в любые структуры данных, поскольку компилятор не знает, какой объект курсора (и какая команда SQL) будет ей присвоен на момент выборки.

Обработка исключения ROWTYPE_MISMATCH

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

2. Созданная процедура используется для перебора строений.

В следующем примере я передаю адрес, а затем пытаюсь выполнить выборку из кур­сора в предположении, что адрес относится к жилому дому. Если адрес в действитель­ности относится к коммерческой недвижимости, PL/SQL инициирует исключение ROWTYPE_MISMATCH из-за несовместимости структур записей. Затем раздел исключений снова осуществляет выборку, на этот раз в запись коммерческого строения, и перебор завершается:

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

В этом разделе более подробно рассматриваются правила и вопросы, связанные с исполь­зованием курсорных переменных в программах. Речь пойдет о правилах соответствия типов данных строк, псевдонимах курсорных переменных и области их действия. Прежде всего помните, что курсорная переменная — это ссылка на объект курсора, представляющий данные, выбранные из базы данных с помощью содержащегося в нем запроса. Это не сам объект курсора. Курсорная переменная может быть связана с опре­деленным запросом при выполнении одного из следующих условий:

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

Правила соответствия типов строк, проверяемые во время компиляции

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

Правила соответствия типов строк, проверяемые во время выполнения

При выполнении программы PL/SQL проверяет, соблюдаются ли следующие правила:

Псевдоним объекта курсора

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

Следующий анонимный блок демонстрирует принцип действия псевдонимов курсоров:

Основные действия, выполняемые этим кодом, описаны в следующей таблице.

Любое изменения состояния объекта курсора отражается во всех ссылающихся на него переменных.

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

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

Передача курсорных переменных в аргументах

идентификация типа REF CURSOR

В заголовке программы необходимо идентифицировать тип параметра курсорной пере­менной. Для этого он должен быть заранее объявлен.

Если вы создаете локальный модуль внутри другой программы, тип курсора можно определить в той же программе. Пример:

Если вы создаете отдельную процедуру или функцию, сослаться на существующий тип REF CURSOR можно только одним способом: разместив команду TYPE в пакете. Все переменные, объявленные в спецификации пакета, становятся глобальными в рамках сеанса, и для ссылок на этот тип курсора может использоваться точечный синтаксис. Последовательность действий в этом случае должна быть такой:

Назначение режима параметра

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

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

Значение курсорной переменной (то есть объект курсора, на который указывает пере­менная) может измениться только в результате выполнения двух операций:

Если курсорная переменная уже указывает на объект курсора, OPEN FOR не изменяет ссылку; изменяется только запрос, связанный с объектом.

Операции FETCH и CLOSE изменяют состояние объекта курсора, но не ссылку на объект, которая является значением курсорной переменной.

Пример программы, использующей курсорные переменные в параметрах:

Процедура копирует старую курсорную переменную в новую переменную. Первый параметр объявлен с режимом IN, потому что он используется только в правой части присваивания. Второй параметр должен быть объявлен в режиме OUT (или IN OUT), потому что его значение изменяется внутри процедуры. Обратите внимание: тип curvar_type определяется в пакете company.

Ограничения на использование курсорных переменных

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

Источник

Работа с явными курсорами в 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.

Источник