Cursor structures

In PL/SQL, a cursor structure allows the processing of a SELECT statement and accesses the result returned by that query. Each and every SQL statement in a PL/SQL block is a cursor. A cursor is a handle to the chunk of the memory area where the SQL statements are processed and the result is stored. For a dedicated database, the chunk of memory is in the User Global Area (UGA) while, for shared server connections, the cursor context area is allocated in the System Global Area (SGA).

Cursors can be of two types:

• Implicit cursors: Every SQL query in the executable or exception section of a PL/SQL block is an implicit cursor. SELECT..INTO, SELECT..BULK COLLECT INTO, SELECT in CURSOR FOR loop, INSERT, UPDATE, DELETE, and MERGE are implicit cursors.

• Explicit cursors: A cursor defined by the user or developer in the declaration section of a PL/SQL program is an explicit cursor.

Cursor execution cycle

A cursor is a handler to execute an SQL query and lives for the life of a session...