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:
- Rows returned by the query
- Number of rows processed by the query
- 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.
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.
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:
- %ISOPEN – Boolean operator, returns TRUE if the cursor is already Open otherwise returns FALSE.
- %FOUND – Boolean operator, returns TRUE row if it was returned by the FETCH otherwise returns FALSE.
- %NOTFOUND – It is the opposite of %FOUND.
- %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.
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