PL/SQL Cursor

In PL/SQL a Cursor is a reserved area in memory where Oracle executes SQL statements. It enables traversal over the records in a database. Cursors facilitate subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database records. A cursor contains information on a select statement and the rows of data accessed by it.

There are two types of cursors:

Implicit cursors: The implicit cursors are created automatically by Oracle while an SQL statement is executed. In this process, the user is unaware of implicit cursor. Oracle automatically performs the OPEN, FETCH, and CLOSE operations.
Explicit cursors: The explicit cursors provides more control over context area. Explicit cursors are explicitly declared in the DECLARE section of the PL/SQL block. In explicit cursor DECLARE,OPEN,FETCH,and CLOSE operations are done by the programmer.

The working process of an explicit cursor:

Declare: The cursor is initialised into temporary memory area.
Open: The cursor is opened and the temporary memory area is allotted.
Fetch: Cursor opened and ready to retrieve rows from data.
Close: The CLOSE statement disables the cursor, and releases the temporary memory area.

Orcale provides some attributes known as Implicit cursor?s attributes to check the status of DML operations. Some of them are: %FOUND, %NOTFOUND, %ROWCOUNT and %ISOPEN.

Cursor Attributes

CURSOR ATTRIBUTE	SYNTAX	DESCRIPTION
%NOTFOUND	cursor_name%NOTFOUND	%NOTFOUND returns TRUE if last fetch did not return a row, Else FALSE if last fetch returns row.
%FOUND	cursor_name%FOUND	%FOUND returns TRUE if the cursor is open, fetches the row till the last fetch. FALSE if last fetch did not return any row.
%ROWCOUNT	cursor_name%ROWCOUNT	%ROWCOUNT keeps track of fetched rows from cursor until it is closed.
%ISOPEN	cursor_name%ISOPEN	%ISOPEN returns TRUE if its cursor or cursor variable is open, otherwise, %ISOPEN returns FALSE.