Что такое курсор в sql oracle

Что такое курсор в sql oracle

Cursor in SQL
To execute SQL statements, a work area is used by the Oracle engine for its internal processing and storing the information. This work area is private to SQL’s operations. The ‘Cursor’ is the PL/SQL construct that allows the user to name the work area and access the stored information in it.

Use of Cursor
The major function of a cursor is to retrieve data, one row at a time, from a result set, unlike the SQL commands which operate on all the rows in the result set at one time. Cursors are used when the user needs to update records in a singleton fashion or in a row by row manner, in a database table.
The Data that is stored in the Cursor is called the Active Data Set. Oracle DBMS has another predefined area in the main memory Set, within which the cursors are opened. Hence the size of the cursor is limited by the size of this pre-defined area.

Cursor Actions

• Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
• Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
• Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a block to perform data manipulation.
• Close: After data manipulation, close the cursor explicitly.
• Deallocate: Finally, delete the cursor definition and release all the system resources associated with the cursor.

Types of Cursors
Cursors are classified depending on the circumstances in which they are opened.

• Implicit Cursor: If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor. It is created “automatically” for the user by Oracle when a query is executed and is simpler to code.
• Explicit Cursor: A Cursor can also be opened for processing data through a PL/SQL block, on demand. Such a user-defined cursor is known as an Explicit Cursor.

Explicit cursor
An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. A suitable name for the cursor.

PL/SQL Cursor

Summary: in this tutorial, you will learn about PL/SQL cursor and its usage.

A cursor is a pointer that points to a result of a query. PL/SQL has two types of cursors: implicit cursors and explicit cursors.

Implicit cursors

Whenever Oracle executes an SQL statement such as SELECT INTO , INSERT , UPDATE , and DELETE , it automatically creates an implicit cursor.

Oracle internally manages the whole execution cycle of implicit cursors and reveals only the cursor’s information and statuses such as SQL%ROWCOUNT , SQL%ISOPEN , SQL%FOUND , and SQL%NOTFOUND .

The implicit cursor is not elegant when the query returns zero or multiple rows which cause NO_DATA_FOUND or TOO_MANY_ROWS exception respectively.

Explicit cursors

An explicit cursor is an SELECT statement declared explicitly in the declaration section of the current block or a package specification.

For an explicit cursor, you have control over its execution cycle from OPEN , FETCH , and CLOSE .

Oracle defines an execution cycle that executes an SQL statement and associates a cursor with it.

The following illustration shows the execution cycle of an explicit cursor:

Let’s examine each step in detail.

Declare a cursor

Before using an explicit cursor, you must declare it in the declaration section of a block or package as follows:

• First, specify the name of the cursor after the CURSOR keyword.
• Second, define a query to fetch data after the IS keyword.

Open a cursor

Before start fetching rows from the cursor, you must open it. To open a cursor, you use the following syntax:

In this syntax, the cursor_name is the name of the cursor declared in the declaration section.

When you open a cursor, Oracle parses the query, binds variables, and executes the associated SQL statement.

Oracle also determines an execution plan, associates host variables and cursor parameters with the placeholders in the SQL statement, determines the result set, and sets the cursor to the first row in the result set.

More about parameterized cursor in the next tutorial.

Fetch from a cursor

The FETCH statement places the contents of the current row into variables. The syntax of FETCH statement is as follows:

To retrieve all rows in a result set, you need to fetch each row till the last one.

Closing a cursor

After fetching all rows, you need to close the cursor with the CLOSE statement:

Closing a cursor instructs Oracle to release allocated memory at an appropriate time.

If you declare a cursor in an anonymous block, procedure, or function, the cursor will automatically be closed when the execution of these objects end.

However, you must explicitly close package-based cursors. Note that if you close a cursor that has not opened yet, Oracle will raise an INVALID_CURSOR exception.

Explicit Cursor Attributes

A cursor has four attributes to which you can reference in the following format:

where cursor_name is the name of the explicit cursor.

1) %ISOPEN

This attribute is TRUE if the cursor is open or FALSE if it is not.

2) %FOUND

This attribute has four values:

• NULL before the first fetch
• TRUE if a record was fetched successfully
• FALSE if no row returned
• INVALID_CURSOR if the cursor is not opened

3) %NOTFOUND

This attribute has four values:

• NULL before the first fetch
• FALSE if a record was fetched successfully
• TRUE if no row returned
• INVALID_CURSOR if the cursor is not opened

3) %ROWCOUNT

The %ROWCOUNT attribute returns the number of rows fetched from the cursor. If the cursor is not opened, this attribute returns INVALID_CURSOR .

PL/SQL cursor example

We will use the orders and order_items tables from the sample database for the demonstration.

The following statement creates a view that returns the sales revenues by customers:

The values of the credit column are 5% of the total sales revenues.

Suppose you need to develop a anonymous block that:

1. Reset credit limits of all customers to zero.
2. Fetch customers sorted by sales in descending order and gives them new credit limits from a budget of 1 million.

The following anonymous block illustrates the logic:

In the declaration section, we declare three variables.

The first one is l_budget whose initial value is 1,000,000 .

The second variable is an explicit cursor variable named c_sales whose SELECT statement retrieves data from the sales view:

The third variable is a cursor-based record named c_sales .

In the execution section, we perform the following:

• First, reset credit limits of all customers to zero using an UPDATE statement.
• Second, open the c_sales cursor.
• Third, fetch each row from the cursor. In each loop iteration, we update the credit limit and reduced the budget. The loop terminates when there is no row to fetch or the budget is exhausted.
• Finally, close the cursor.

The following query retrieves data from the customers table to verify the update:

As you can see clearly from the result, only the first few customers have the credit limits. If you sum up all credit limits, the total should be 1 million as shown follows:

Now, you should understand PL/SQL cursors including implicit and explicit cursors, and how to use them effectively to process data, row by row, from a table.

Курсоры

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

В PL/SQL поддерживаются два типа курсоров : явные и неявные.

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

Курсор может возвращать одну строку, несколько строк или ни одной строки.

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

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

Для управления явным курсором используются операторы CURSOR , OPEN , FETCH и CLOSE .

Оператор CURSOR выполняет объявление явного курсора .

Оператор OPEN открывает курсор , создавая новый результирующий набор на базе указанного запроса.

Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца.

Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы.

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

Каждый параметр parameter определяется как:

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

Одновременно с созданием результирующего набора можно выполнить блокировку выбираемых строк. Для этого в операторе SELECT следует указать фразу FOR UPDATE .

Для задания параметров курсора подходит как позиционная, так и именованная нотация .

Для работы с курсором можно использовать следующие атрибуты, указываемые после имени курсора :

• %ISOPEN — возвращает значение TRUE , если курсор открыт.
• %FOUND — определяет, найдена ли строка, удовлетворяющая условию.
• %NOTFOUND — возвращает TRUE , если строка не найдена.
• %ROWCOUNT — возвращает номер текущей строки.

Оператор FETCH может быть выполнен в цикле LOOP-END LOOP . Это позволяет последовательно просматривать весь результирующий набор, который был открыт оператором OPEN .

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

При выполнении SQL-оператора, для которого не был объявлен явный курсор , Oracle автоматически открывает неявный курсор .

При применении неявного курсора нельзя использовать операторы управления курсором OPEN , FETCH и CLOSE .

Если при неявном курсоре в результирующий набор записывается более одной строки, то Oracle инициирует исключение TOO_MANY_ROWS .

Если курсор создается в пакете, то его объявление и спецификация могут быть разделены: объявление курсора указывается в секции объявлений пакета, а спецификация курсора — в теле пакета.

Объявление курсора при создании пакета может иметь следующее формальное описание:

PL / SQL – курсоры

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

Курсор – это указатель на эту контекстную область. PL / SQL управляет контекстной областью с помощью курсора. Курсор содержит строки (одну или несколько), возвращаемые оператором SQL. Набор строк, которые содержит курсор, называется активным набором .

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

• Неявные курсоры
• Явные курсоры

Неявные курсоры

Неявные курсоры автоматически создаются Oracle при каждом выполнении оператора SQL, когда для оператора нет явного курсора. Программисты не могут контролировать неявные курсоры и информацию в них.

Всякий раз, когда выполняется оператор DML (INSERT, UPDATE и DELETE), с этим оператором связывается неявный курсор. Для операций INSERT курсор содержит данные, которые необходимо вставить. Для операций UPDATE и DELETE курсор определяет строки, которые будут затронуты.

В PL / SQL вы можете ссылаться на самый последний неявный курсор как на курсор SQL , который всегда имеет атрибуты, такие как % FOUND,% ISOPEN,% NOTFOUND и % ROWCOUNT . Курсор SQL имеет дополнительные атрибуты % BULK_ROWCOUNT и % BULK_EXCEPTIONS , предназначенные для использования с оператором FORALL . В следующей таблице приведено описание наиболее часто используемых атрибутов.

Возвращает TRUE, если инструкция INSERT, UPDATE или DELETE затронула одну или несколько строк или инструкция SELECT INTO вернула одну или несколько строк. В противном случае он возвращает FALSE.

Логическая противоположность% FOUND. Он возвращает TRUE, если инструкция INSERT, UPDATE или DELETE не затронула строки, или инструкция SELECT INTO не вернула строки. В противном случае он возвращает FALSE.

Всегда возвращает FALSE для неявных курсоров, потому что Oracle автоматически закрывает курсор SQL после выполнения соответствующего оператора SQL.

Возвращает количество строк, затронутых оператором INSERT, UPDATE или DELETE или возвращенных оператором SELECT INTO.

Возвращает TRUE, если инструкция INSERT, UPDATE или DELETE затронула одну или несколько строк или инструкция SELECT INTO вернула одну или несколько строк. В противном случае он возвращает FALSE.

Логическая противоположность% FOUND. Он возвращает TRUE, если инструкция INSERT, UPDATE или DELETE не затронула строки, или инструкция SELECT INTO не вернула строки. В противном случае он возвращает FALSE.

Всегда возвращает FALSE для неявных курсоров, потому что Oracle автоматически закрывает курсор SQL после выполнения соответствующего оператора SQL.

Возвращает количество строк, затронутых оператором INSERT, UPDATE или DELETE или возвращенных оператором SELECT INTO.

Любой атрибут курсора SQL будет доступен как sql% attribute_name, как показано ниже в примере.

пример

Мы будем использовать таблицу CUSTOMERS, которую мы создали и использовали в предыдущих главах.

Следующая программа обновит таблицу и увеличит зарплату каждого клиента на 500 и использует атрибут SQL% ROWCOUNT, чтобы определить количество затронутых строк:

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –

Если вы проверите записи в таблице клиентов, вы увидите, что строки были обновлены –

Явные курсоры

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

Синтаксис для создания явного курсора –

Работа с явным курсором включает в себя следующие шаги:

• Объявление курсора для инициализации памяти
• Открытие курсора для выделения памяти
• Выбор курсора для получения данных
• Закрытие курсора для освобождения выделенной памяти

Объявление курсора

Объявление курсора определяет курсор с именем и соответствующим оператором SELECT. Например –

Открытие курсора

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

Извлечение курсора

Выборка курсора вовлекает доступ к одной строке за один раз. Например, мы будем выбирать строки из открытого выше курсора следующим образом:

Закрытие курсора

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

пример

Ниже приведен полный пример, иллюстрирующий понятия явных курсоров & minua;

Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –