Тип ref cursor oracle
There are no constructors for OracleRefCursor objects. They can be acquired only as parameter values from PL/SQL stored procedures, stored functions, or anonymous blocks.
An OracleRefCursor object is a connected object. The connection used to execute the command returning an OracleRefCursor object is required for its lifetime. Once the connection associated with an OracleRefCursor object is closed, the OracleRefCursor object cannot be used.
Терминология
В 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 , позволяющей за один раз выбрать из базы данных более одной строки.
3.11.6 Updating a DataSet Obtained from a REF CURSOR
REF CURSOR types cannot be updated. However, data that is retrieved into a DataSet can be updated. Therefore, the OracleDataAdapter class requires a custom SQL statement to flush any REF CURSOR data updates to the database.
The OracleCommandBuilder object cannot be used to generate SQL statements for REF CURSOR updates.
Реализация потоковых функций в выражениях CURSOR
Потоковые функции, также называемые преобразующими функциями, позволяют преобразовать данные из одного состояния в другое без использования локальных структур данных для промежуточного хранения результатов. Предположим, нужно взять данные из таблицы StockTable и переместить их в таблицу TickerTable , преобразуя одну строку StockTable в две строки TickerTable . С курсорными выражениями и табличными функциями решение может быть реализовано следующим образом:
Функция StockPivot содержит всю сложную логику, необходимую для выполнения преобразования.
There are no constructors for OracleRefCursor objects. They can be acquired only as parameter values from PL/SQL stored procedures, stored functions, or anonymous blocks.
An OracleRefCursor object is a connected object. The connection used to execute the command returning an OracleRefCursor object is required for its lifetime. Once the connection associated with an OracleRefCursor object is closed, the OracleRefCursor object cannot be used.
Behavior of ExecuteScalar Method for REF CURSOR
The ExecuteScalar method returns the value of the first column of the first row of the REF CURSOR if it is one of the following:
A return value of a stored function execution
The first bind parameter of a stored procedure execution
3.11.2 Obtaining a REF CURSOR Data Type
A REF CURSOR data type can be obtained as an OracleDataReader , DataSet , or OracleRefCursor object. If the REF CURSOR data type is obtained as an OracleRefCursor object, it can be used to create an OracleDataReader object or populate a DataSet from it. When accessing a REF CURSOR data type, always bind it as an OracleDbType.RefCursor parameter.
Populating an OracleRefCursor from a REF CURSOR
When the ExecuteNonQuery method is invoked on a command that returns one or more REF CURSOR data types, each of the OracleCommand parameters that are bound as an OracleDbType.RefCursor gets a reference to an OracleRefCursor object.
To create an OracleDataReader object from an OracleRefCursor object, invoke the GetDataReader method from the OracleRefCursor object. Subsequent calls to the GetDataReader method return a reference to the same OracleDataReader object.
To populate a DataSet with an OracleRefCursor object, the application can invoke a Fill method of the OracleDataAdapter class that takes an OracleRefCursor object. Similar to the OracleDataReader object, an OracleRefCursor object is forward-only. Therefore, once a row is read from an OracleRefCursor object, that same row cannot be obtained again from it unless it is populated again from a query.
When multiple REF CURSOR data types are returned from a command execution as OracleRefCursor objects, the application can choose to create an OracleDataReader object or populate a DataSet with a particular OracleRefCursor object. All the OracleDataReader objects or DataSet objects created from the OracleRefCursor objects are active at the same time, and can be accessed in any order.
Updating a DataSet Obtained from a REF CURSOR
REF CURSOR types cannot be updated. However, data that is retrieved into a DataSet can be updated. Therefore, the OracleDataAdapter class requires a custom SQL statement to flush any REF CURSOR data updates to the database.
The OracleCommandBuilder object cannot be used to generate SQL statements for REF CURSOR updates.
Obtaining a REF CURSOR Data Type
A REF CURSOR data type can be obtained as an OracleDataReader , DataSet , or OracleRefCursor object. If the REF CURSOR data type is obtained as an OracleRefCursor object, it can be used to create an OracleDataReader object or populate a DataSet from it. When accessing a REF CURSOR data type, always bind it as an OracleDbType.RefCursor parameter.
Основные принципы работы с курсорами
Курсор проще всего представить себе как указатель на таблицу в базе данных. Например, следующее объявление связывает всю таблицу employee с курсором employee_cur :
Объявленный курсор можно открыть:
Далее из него можно выбирать строки:
Завершив работу с курсором, его следует закрыть:
В этом случае каждая выбранная из курсора запись представляет строку таблицы employee. Однако с курсором можно связать любую допустимую команду SELECT . В следующем примере в объявлении курсора объединяются три таблицы:
В данном случае курсор действует не как указатель на конкретную таблицу базы данных — он указывает на виртуальную таблицу или неявное представление, определяемое командой SELECT . (Такая таблица называется виртуальной, потому что команда SELECT генерирует данные с табличной структурой, но эта таблица существует только временно, пока программа работает с возвращенными командой данными.) Если тройное объединение возвращает таблицу из 20 строк и 3 столбцов, то курсор действует как указатель на эти 20 строк.
3.11.5 Populating an OracleRefCursor from a REF CURSOR
When the ExecuteNonQuery method is invoked on a command that returns one or more REF CURSOR data types, each of the OracleCommand parameters that are bound as an OracleDbType.RefCursor gets a reference to an OracleRefCursor object.
To create an OracleDataReader object from an OracleRefCursor object, invoke the GetDataReader method from the OracleRefCursor object. Subsequent calls to the GetDataReader method return a reference to the same OracleDataReader object.
To populate a DataSet with an OracleRefCursor object, the application can invoke a Fill method of the OracleDataAdapter class that takes an OracleRefCursor object. Similar to the OracleDataReader object, an OracleRefCursor object is forward-only. Therefore, once a row is read from an OracleRefCursor object, that same row cannot be obtained again from it unless it is populated again from a query.
When multiple REF CURSOR data types are returned from a command execution as OracleRefCursor objects, the application can choose to create an OracleDataReader object or populate a DataSet with a particular OracleRefCursor object. All the OracleDataReader objects or DataSet objects created from the OracleRefCursor objects are active at the same time, and can be accessed in any order.
3.11.8 Passing a REF CURSOR to a Stored Procedure
Одной из важнейших характеристик 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).
3.11.8 Passing a REF CURSOR to a Stored Procedure
3.11.2 Obtaining a REF CURSOR Data Type
A REF CURSOR data type can be obtained as an OracleDataReader , DataSet , or OracleRefCursor object. If the REF CURSOR data type is obtained as an OracleRefCursor object, it can be used to create an OracleDataReader object or populate a DataSet from it. When accessing a REF CURSOR data type, always bind it as an OracleDbType.RefCursor parameter.
Populating an OracleDataReader from a REF CURSOR
A REF CURSOR data type can be obtained as an OracleDataReader object by calling the ExecuteReader method of the OracleCommand object. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor . None of the output parameters of type OracleDbType.RefCursor is populated after the ExecuteReader method is invoked.
If there are multiple output REF CURSOR parameters, use the NextResult method of the OracleDataReader object to access the next REF CURSOR data type. The OracleDataReader NextResult method provides sequential access to the REF CURSOR data types; only one REF CURSOR data type can be accessed at a given time.
The order in which OracleDataReader objects are created for the corresponding REF CURSOR data types depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR data type, then it becomes the first OracleDataReader object and all the output REF CURSOR data types follow the order in which the parameters are bound.
3.11.6 Updating a DataSet Obtained from a REF CURSOR
REF CURSOR types cannot be updated. However, data that is retrieved into a DataSet can be updated. Therefore, the OracleDataAdapter class requires a custom SQL statement to flush any REF CURSOR data updates to the database.
The OracleCommandBuilder object cannot be used to generate SQL statements for REF CURSOR updates.
3.11.7 Behavior of ExecuteScalar Method for REF CURSOR
The ExecuteScalar method returns the value of the first column of the first row of the REF CURSOR if it is one of the following:
A return value of a stored function execution
The first bind parameter of a stored procedure execution
Типичные операции с запросами и курсорами
Независимо от типа курсора процесс выполнения команд 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. Упрощенная схема выборки данных с использованием курсора
There are no constructors for OracleRefCursor objects. They can be acquired only as parameter values from PL/SQL stored procedures, stored functions, or anonymous blocks.
An OracleRefCursor object is a connected object. The connection used to execute the command returning an OracleRefCursor object is required for its lifetime. Once the connection associated with an OracleRefCursor object is closed, the OracleRefCursor object cannot be used.
3.11.4 Populating the DataSet from a REF CURSOR
For the Fill method to populate the DataSet properly, the SelectCommand property of the OracleDataAdapter class must be bound with an output parameter of type OracleDbType.RefCursor . If the Fill method is successful, the DataSet is populated with a DataTable that represents a REF CURSOR data type.
If the command execution returns multiple REF CURSOR data types, the DataSet is populated with multiple DataTable objects.
This property is particularly useful when a DataSet is being populated with more than one REF CURSOR , one or more of which is NULL . For example, if a DataSet is populated by executing a stored procedure that returns three REF CURSOR s and the second REF CURSOR is NULL , the REFCursorName property value for the first DataTable is REFCursor and for the second DataTable , REFCursor2 . No DataTable is populated for the NULL REF CURSOR .
3.11.3 Populating an OracleDataReader from a REF CURSOR
A REF CURSOR data type can be obtained as an OracleDataReader object by calling the ExecuteReader method of the OracleCommand object. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor . None of the output parameters of type OracleDbType.RefCursor is populated after the ExecuteReader method is invoked.
If there are multiple output REF CURSOR parameters, use the NextResult method of the OracleDataReader object to access the next REF CURSOR data type. The OracleDataReader NextResult method provides sequential access to the REF CURSOR data types; only one REF CURSOR data type can be accessed at a given time.
The order in which OracleDataReader objects are created for the corresponding REF CURSOR data types depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR data type, then it becomes the first OracleDataReader object and all the output REF CURSOR data types follow the order in which the parameters are bound.
Populating the DataSet from a REF CURSOR
For the Fill method to populate the DataSet properly, the SelectCommand property of the OracleDataAdapter class must be bound with an output parameter of type OracleDbType.RefCursor . If the Fill method is successful, the DataSet is populated with a DataTable that represents a REF CURSOR data type.
If the command execution returns multiple REF CURSOR data types, the DataSet is populated with multiple DataTable objects.
This property is particularly useful when a DataSet is being populated with more than one REF CURSOR , one or more of which is NULL . For example, if a DataSet is populated by executing a stored procedure that returns three REF CURSOR s and the second REF CURSOR is NULL , the REFCursorName property value for the first DataTable is REFCursor and for the second DataTable , REFCursor2 . No DataTable is populated for the NULL REF CURSOR .
Выборка вложенного запроса как столбца
Следующая процедура демонстрирует первый способ использования вложенного курсорного выражения. Запрос верхнего уровня выбирает два элемента данных: название города и отделение компании в этом городе, информация о котором содержится во вложенном курсоре. Этот вложенный курсор, в свою очередь, извлекает с помощью курсорного выражения еще один вложенный курсор — на этот раз с фамилиями всех сотрудников каждого отделения.
Те же данные можно было бы извлечь из базы данных с помощью нескольких явных курсоров, открываемых и обрабатываемых во вложенных циклах. Однако курсорное выражение позволяет применить другой подход, более лаконичный и эффективный (поскольку вся обработка происходит в исполнителе команд SQL, что сокращает количество переключений контекста):
3.11.4 Populating the DataSet from a REF CURSOR
For the Fill method to populate the DataSet properly, the SelectCommand property of the OracleDataAdapter class must be bound with an output parameter of type OracleDbType.RefCursor . If the Fill method is successful, the DataSet is populated with a DataTable that represents a REF CURSOR data type.
If the command execution returns multiple REF CURSOR data types, the DataSet is populated with multiple DataTable objects.
This property is particularly useful when a DataSet is being populated with more than one REF CURSOR , one or more of which is NULL . For example, if a DataSet is populated by executing a stored procedure that returns three REF CURSOR s and the second REF CURSOR is NULL , the REFCursorName property value for the first DataTable is REFCursor and for the second DataTable , REFCursor2 . No DataTable is populated for the NULL REF CURSOR .
Passing a REF CURSOR to a Stored Procedure
Oracle расширяет язык SQL еще одним мощным элементом: курсорными выражениями. Курсорное выражение — это выражение со специальным оператором CURSOR , используемое в SQL -запросе и определяющее вложенный курсор. Каждая строка результирующего набора вложенного курсора может содержать диапазон значений, допустимых для SQL-запросов; кроме того, она может включать другие курсоры, определяемые вложенными запросами.
Синтаксис CURSOR, введенный в Oracle8i , не мог использоваться в программах PL/SQL . В Oracle9i недостаток был устранен, и теперь курсорные выражения могут применяться в командах SQL программ PL/SQL .
С помощью курсорного выражения можно вернуть большой и сложный набор связанных значений из одной или нескольких таблиц. Такой набор обычно обрабатывается во вложенных циклах: главный цикл выбирает строки основного курсора, а вложенные циклы выбирают строки вложенных курсоров.
Курсорное выражение может быть довольно сложным, что зависит от сложности извлекаемых с его помощью данных. Тем не менее разработчику полезно знать все возможные способы получения данных из баз данных Oracle.
Курсорные выражения могут использоваться в следующих структурных компонентах языка:
- объявления явных курсоров;
- динамические SQL-запросы;
- объявления и переменные типа REF CURSOR.
В неявных курсорах курсорные выражения никогда не используются.
Синтаксис курсорного выражения очень прост:
Oracle неявно открывает вложенный курсор при выборке строки, содержащей его выражение из родительского или внешнего курсора. Он закрывается, когда:
- вы явно закрываете курсор;
- родительский курсор повторно выполняется, закрывается или отменяется;
- при выборке из родительского курсора инициируется исключение. В этом случае вложенный курсор закрывается вместе с родительским.
Использование курсорных выражений
Существует два разных, но очень полезных способа использования курсорных выражений:
- для выборки вложенного запроса как столбца внешнего запроса;
- для преобразования запроса в результирующий набор, который может передаться в аргументе функции.
3.11.3 Populating an OracleDataReader from a REF CURSOR
A REF CURSOR data type can be obtained as an OracleDataReader object by calling the ExecuteReader method of the OracleCommand object. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor . None of the output parameters of type OracleDbType.RefCursor is populated after the ExecuteReader method is invoked.
If there are multiple output REF CURSOR parameters, use the NextResult method of the OracleDataReader object to access the next REF CURSOR data type. The OracleDataReader NextResult method provides sequential access to the REF CURSOR data types; only one REF CURSOR data type can be accessed at a given time.
The order in which OracleDataReader objects are created for the corresponding REF CURSOR data types depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR data type, then it becomes the first OracleDataReader object and all the output REF CURSOR data types follow the order in which the parameters are bound.
3.11.5 Populating an OracleRefCursor from a REF CURSOR
When the ExecuteNonQuery method is invoked on a command that returns one or more REF CURSOR data types, each of the OracleCommand parameters that are bound as an OracleDbType.RefCursor gets a reference to an OracleRefCursor object.
To create an OracleDataReader object from an OracleRefCursor object, invoke the GetDataReader method from the OracleRefCursor object. Subsequent calls to the GetDataReader method return a reference to the same OracleDataReader object.
To populate a DataSet with an OracleRefCursor object, the application can invoke a Fill method of the OracleDataAdapter class that takes an OracleRefCursor object. Similar to the OracleDataReader object, an OracleRefCursor object is forward-only. Therefore, once a row is read from an OracleRefCursor object, that same row cannot be obtained again from it unless it is populated again from a query.
When multiple REF CURSOR data types are returned from a command execution as OracleRefCursor objects, the application can choose to create an OracleDataReader object or populate a DataSet with a particular OracleRefCursor object. All the OracleDataReader objects or DataSet objects created from the OracleRefCursor objects are active at the same time, and can be accessed in any order.
3.11.7 Behavior of ExecuteScalar Method for REF CURSOR
The ExecuteScalar method returns the value of the first column of the first row of the REF CURSOR if it is one of the following:
A return value of a stored function execution
The first bind parameter of a stored procedure execution
3.11.2 Obtaining a REF CURSOR Data Type
A REF CURSOR data type can be obtained as an OracleDataReader , DataSet , or OracleRefCursor object. If the REF CURSOR data type is obtained as an OracleRefCursor object, it can be used to create an OracleDataReader object or populate a DataSet from it. When accessing a REF CURSOR data type, always bind it as an OracleDbType.RefCursor parameter.
Читайте также: