The SQL Cursor: Looping Structures for Your Procedures

The Oracle Database handles data manipulation language (DML) statements such as INSERT, DELETE or UPDATE with background processes that create private work areas in the system’s memory. The database uses the same process when handling SELECT statements by transparently initializing and allocating blocks of memory as temporary workplaces in which to store the statement’s information and results. The pointers that indicate the work area’s memory locations are cursors. These work areas and cursors are off-limits to programmers, however PL/SQL allows for the explicit creation of cursors to handle SELECT statements in the same manner.

New to Oracle and PL/SQL? Take a class at Udemy to learn more

When a SQL statement is processed or executed from PL/SQL, Oracle establishes in the background a temporary private work area, or context area, in working memory. This work area contains all the pertinent information required to process the statement, such as the number of rows affected and the set of data returned. Where data manipulation language (DML) commands such as INSERT, UPDATE, DELETE or MERGE, are processed, the Oracle database establishes and controls the size and lifetime of the work area within memory.

SQL tracks the specific blocks of memory, assigned to a particular active work area using pointers, referred to as cursors. When processing DML statements, cursors effectively exist in the background, transparent to the programmer and beyond their control. However, when SELECT statements are processed, PL/SQL facilitates a method where the programmer can address these cursors and control the work area, in which case the cursors are termed explicit cursors.

Learn more about DML and Oracle Database at Udemy.

Explicit & Implicit Cursors

There are two distinct types of cursor: implicit and explicit.

Implicit Cursors

Whenever a statement is processed in SQL such as a SELECT, PL/SQL will create an implicit cursor to establish a temporary work area, so long as there is no existing explicit cursor in the code. Implicit cursors take their name from the fact that the programmer did not explicitly declare a cursor in his statement. Implicit cursors are under the control of Oracle and it carries out the open, fetch, and close processes automatically and autonomously.

Implicit cursors have no visible reference point for a programmer to leverage, so they are beyond the programmer’s control. However, that does not mean they are totally transparent. A programmer can still check the status of recently processed statements through the cursor attributes. PL/SQL refers to the most recent implicit cursor as the SQL cursor and it has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT which can be accesses via sql%attribute_name.

Oracle always uses implicit cursors for DML processing. When processing an INSERT, the cursor stores the data. When processing a DELETE or UPDATE the cursor stores information on the rows affected. Subsequently, a programmer cannot use these commands in their own explicit cursors.

An example of a query where Oracle opens an implicit cursor for the SELECT statement, then fetches the row, before closing the cursor is SELECT-INTO. It is the fastest and simplest way to fetch a single row from a SELECT statement. However, a programmer can explicitly declare a cursor and then perform the open, fetch, and close processes. When the programmer declares and defines a cursor the result is an explicit cursor.

Explicit Cursors

An explicit cursor is a SELECT statement defined by the programmer within the declaration section of code and assigned a name. With explicit cursors the programmer has complete control, and responsibility, over how information is accessed from the database and stored in the temporary work area. The programmer decides when to open the cursor, when to fetch records from the cursor – the table/s assigned in the SELECT statement defined within the cursor – how many records to fetch, and when to close the cursor. Because explicit cursors are directly under the programmer’s control and it is this granularity of control, which makes them off interest, they are the cursors discussed in this article.

Creating an Explicit Cursor

In order to create a cursor, the programmer emulates the procedures used within the implicit cursor, such as opening, fetching and closing. However, the first thing required is to reference the cursor by assigning it a name and defining its associated SELECT statement.

Declaring a Cursor

A programmer identifies a cursor within the declaration section of the block of PL/SQL code. By declaring the cursor the system can initialize memory for the temporary work area.

Syntax: Cursor cursor_name IS select_statement;

The syntax above is the simplest form of declaring a cursor as it supplies no parameters.

The syntax for declaring a cursor with parameters and a return clause are shown below

Declaring a Cursor with Parameters

Syntax: Cursor cursor_name (parameter_list) IS select_statement

Declaring a Cursor with a Return Clause

Syntax: Cursor cursor_name RETURN field%ROWTYPE IS select_statement

Opening a Cursor

Syntax: Open cursor_name;

Opening a cursor instructs the system to initialize and allocate the assigned memory blocks to the work area. The allocated memory is where the rows fetched by the SELECT statement will be stored or manipulated during the lifecycle of the statement. The memory allocation will remain reserved until the cursor is closed.

Fetch

Syntax: Fetch cursor_name INTO variable_list;

Parameters:

The variable list – this is the comma delimited list of variables that will store the cursors results

Fetching the data from the rows in the SELECT statement requires accessing each row one at a time.

Closing the Cursor

Syntax: Close cursor_name;

Closing the cursor releases all reserved allocated memory back to the system. If the cursor definition does not close the cursor appropriately after the statement finishes then the memory remains reserved and idle. If too many cursors remain unnecessarily open then the underlying system will reach the permitted concurrently open limit or run out of free memory resources and eventually crash.

For a better understanding of cursors and Oracle programming, take a course at Udemy.