Oracle PL/SQL Cursor: Implicit and Explicit

In this article, I’ll explain cursors and different types of cursors(Implicit and Explicit) with examples.

When an SQL statement is processed, Oracle creates a memory area known as the context area or Process Global Area (PGA). A cursor is a pointer to this context area in the database. It contains all information needed for processing the statement. In PL/SQL, the context area is controlled by Cursor. A cursor contains information on a select statement and the rows of data accessed by it. The Process Global Area (PGA) is called the context area that holds the following:

1. Rows returned by the query
2. Number of rows processed by the query
3. A pointer to the parsed query in the Shared Pool

If data is added, deleted, or modified after the cursor is opened, the new or changed data is not reflected in the cursor result set. A cursor is used to refer to a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: implicit cursors and explicit cursors.

Implicit Cursors

Every DML statements by default use implicit cursors. They are opened and closed automatically we do not have to use OPEN, FETCH and CLOSE commands but the cursor attributes are still available for use.

Explicit Cursors

Here the developer controls most of the operations and the cursor is declared using the SELECT statement in the declaration section of the block. In order to make use of an explicit cursor, it has to be declared, opened, fetched, and then at the end closed.

Syntax to create cursor:

CURSOR cursor_name [parameter_list]
[RETURN return_type]
IS query
[FOR UPDATE [OF (column_list)][NOWAIT]];

The cursor_name can be any valid identifier, and the optional parameter_list if the list of parameters the cursor can have. The optional return_type is the clause that specifies the type of data returned by the cursor. The query can be any valid query with SELECT FROM and other clauses. FOR UPDATE clause locks the records once the cursor is opened. The syntax to open the cursor is:

OPEN CURSOR cursor_name [parameter_value];

Fetch records from the cursor: It retrieves records from the context area to the cursor variable. It operates on the current record only and proceeds through the result set one record at a time. Syntax is:

FETCH cursor_name INTO variable_name(s) | PL/SQL_record;

We always have to close the explicit cursors. Until the cursor is closed, the memory is not released. Syntax to close the cursor is:

CLOSE cursor_name;

Cursor Attributes

1. %ISOPEN – Boolean operator, returns TRUE if the cursor is already Open otherwise returns FALSE.
2. %FOUND – Boolean operator, returns TRUE row if it was returned by the FETCH otherwise returns FALSE.
3. %NOTFOUND – It is the opposite of %FOUND.
4. %ROWCOUNT – Returns the number of rows fetched from the cursor.

Consider we have two tables – person and jobs with the following data:

The following piece of code displays the number of jobs each person is having.

SET SERVEROUTPUT ON;
DECLARE
    v_first_name persons.first_name%TYPE;
    v_last_name  persons.last_name%TYPE;
    v_row_count  PLS_INTEGER := 0;
    v_job_count PLS_INTEGER := 0;
    CURSOR per_cur IS
    SELECT a.first_name, 
           a.last_name,
           COUNT(b.job_name)
      FROM persons a,
           jobs    b
     WHERE a.job_id = b.job_id
     GROUP BY a.first_name,
              a.last_name
     ORDER BY a.last_name DESC;

BEGIN
	OPEN per_cur;
	LOOP
		FETCH per_cur INTO v_first_name, v_last_name, v_job_count;
		EXIT WHEN per_cur%NOTFOUND;
		-- Alternatively use EXIT WHEN NOT per_cur%found;
			v_row_count := per_cur%rowcount;
			dbms_output.put_line(v_row_count || ' rows processed so far');
			dbms_output.put_line( v_first_name || ', ' || v_last_name
			|| ' has ' || v_job_count || ' number of job(s).');
	END LOOP;
	CLOSE per_cur;
	IF per_cur%ISOPEN = FALSE
	THEN
	DBMS_OUTPUT.PUT_LINE('Cursor closed');
	ELSE
	DBMS_OUTPUT.PUT_LINE('The cursor is still open');
	END IF;
	EXCEPTION
	WHEN OTHERS
	THEN
	DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

That’s all about this article.

If you like this article, please like, comment, and share.

Request you to subscribe to my YouTube channel for regular updates.

Further readings:

Creating Procedures, Functions, and Packages in PL/SQL

Error and Exception Handling In Oracle PL/SQL

Different types of Loops in Oracle PL/SQL

Creating schema and adding roles in an Oracle Autonomous Database

How to provision an Autonomous Database on the Oracle Cloud