Introduction
Oracle creates a work area for internal processing and information of an executing SQL statement. That work area is known as the Context Area.
A cursor is a pointer to the Context Area that contains all information related to the processing of SQL statements. Cursors are used when users need to manipulate data in a row-wise manner. The data held by the cursor is called Active Data Set.
Recommended topics, Coalesce in SQL and Tcl Commands in SQL
Types of Cursors
1. Implicit Cursors: When we execute a SQL statement and an explicit cursor is not used, the Oracle engine automatically opens a cursor for internal processing. Those cursors are called implicit cursors. These cursors are created when DML statements like DELETE, INSERT, UPDATE, and SELECT are executed.
Oracle has provided some attributes to check the status of DML statements. Whenever we execute a SQL statement, the cursor attributes tell about it affects any rows or not and how many rows are affected. The cursor attributes are listed below:
- %FOUND: It returns TRUE if an INSERT, UPDATE or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.
Example: SQL%FOUND
- %NOT FOUND: Its return value is TRUE if DML statements like INSERT, DELETE, and UPDATE affect no row, or a SELECT INTO statement return no rows. Otherwise, it returns FALSE. It is just the opposite of %FOUND.
Example: SQL%NOTFOUND
- %ROW COUNT: It returns the number of rows affected by DML statements like INSERT, DELETE, and UPDATE or returned by a SELECT INTO statement.
Example: SQL%ROWCOUNT
- %IS OPEN: It always returns FALSE for implicit cursors because the SQL cursor is automatically closed after executing its associated SQL statements.
Example: SQL%IS OPEN
2.Explicit Cursors: The cursor that has to be created, maintained, and closed by a program through PL/SQL code to execute any SELECT query that returns more than one row is called Explicit Cursor.
It is a user-defined cursor declared in the Declare section of the PL/SQL block and is used in its Executable area.