Cursors in Pl/SQL

What are PL/SQL - Cursors?

The pointer to this context area is the cursor. A cursor is used by PL/SQL to control the context area. The rows (one or more) that a SQL statement returns are stored in a cursor. The set of rows the cursor holds is called the active set.

Compared with SQL commands, which work on all of the rows in the result set at once, a cursor's primary job is to obtain data from a result set one row at a time. Cursors are utilized when a user updates records in a database table in a singleton or row-by-row method. The Active Data Set refers to the Data kept in the Cursor. The cursors are opened in another predetermined section of the main memory Set that is part of the Oracle DBMS. As a result, the size of this pre-defined area determines how big the cursor can be.

Types of Cursors

You can give a cursor a name so that a program can use it to get and handle the rows returned by the SQL statement one at a time. Two different types of cursors exist.

• Implicit cursors
• Explicit cursors

Implicit Cursors

Description

When there is no explicit cursor for an SQL statement, Oracle automatically creates implicit cursors every time the statement is run. Implicit cursors and the data they contain are beyond the control of programmers.

An implicit cursor is attached to every DML statement (INSERT, UPDATE, and DELETE) that is issued. The cursor contains the data that has to be inserted for INSERT operations. The cursor identifies the rows impacted by operations like UPDATE and DELETE.

Attributes

The most recent implicit cursor is known as the SQL cursor in PL/SQL, and it always has attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. Additional attributes for usage with the FORALL command include %BULK_ROWCOUNT and %BULK_EXCEPTIONS in the SQL cursor. The most popular attributes are described in the following table:

Example

Any SQL cursor attribute can be retrieved by using the syntax sql%attribute_name as in the example below.

Here's an example of using implicit cursors in PL/SQL:

Explanation: In this example, we first update an employee's salary and then fetch their name using implicit cursors. We use SQL%FOUND and SQL%ROWCOUNT to check the results of the SQL statements. The SQL%NOTFOUND attribute can also be used to check if no rows were affected.

Explicit Cursors

Description

Programmers can construct explicit cursors to control the context area better. The declaration part of the PL/SQL Block needs to contain an explicit definition of the cursor. It is built on a SELECT Statement that produces several rows.

Syntax

The syntax for creating an explicit cursor is −

Steps to Declare a Explicit Cursor

Using an explicit cursor involves the following actions:

1. Declaring the cursor to begin the memory initialization
2. Using the cursor to assign memory
3. Fetching up the cursor to get the info
4. Cursor closure releases the memory that has been allocated.

Example

Declaring the Cursor

Declaring the cursor gives it a name and a SELECT statement that goes with it. For instance,

Opening the Cursor

Opening the cursor prepares it to receive the rows returned by the SQL operation by allocating memory for it. For example, we shall open the cursor described above:

Fetching the Cursor

One row at a time must be accessed to fetch the cursor. For instance, we'll pull rows from the cursor we just opened in the manner described below.

Closing the Cursor

Return of the allocated memory occurs when the cursor is closed. For instance, we'll close the cursor that was just opened, as shown below:

Example

Explicit cursors in Oracle PL/SQL are user-defined cursors that provide more control over the result set retrieval and processing compared to implicit cursors. You must declare, open, fetch, and close explicit cursors explicitly. Here's an example of using an explicit cursor in PL/SQL:

In this example, we declare an explicit cursor named emp_cursor. Here are the steps:

1. Declare the cursor: We define the cursor's query inside the CURSOR declaration. This query retrieves the full name of an employee based on the provided emp_id.
2. Open the cursor: Before fetching data, we open the cursor using the OPEN statement.
3. Fetch data from the cursor: We use the FETCH statement to retrieve data from the cursor into the emp_name variable.
4. Check if a row was found: We use the cursor attribute emp_cursor%FOUND to check if a row was successfully fetched.
5. Close the cursor: After we're done with the cursor, we close it using the CLOSE statement to release associated resources.

Using explicit cursors allows you to have more control over result sets, and you can iterate through multiple rows by repeatedly fetching data until no more rows are found.