Bookmark and Share
Implicit cursors & Explicit Cursors
(Publish Date: 2008-8-5 2:00pm, Total Visits: 3251, Today: 2, This Week: 2, This Month: 3)

For every SQL statement execution certain area in memory is allocated. PL/SQL allow you to name this area. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable.

Implicit cursors

For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.

Syntax:

SELECT column_name INTO :block.field_name FROM table_name;

Explicit Cursors

Explicit cursors are used in queries that return multiple rows. The set of rows fetched by a query is called active set. The size of the active set meets the search criteria in the select statement. Explicit cursor is declared in the DECLARE section of PL/SQL program.

Syntax:

CURSOR <cursor-name> IS <select statement>

Sample Code:

DECLARE
CURSOR emp_cur IS SELECT ename FROM EMP;
BEGIN
----
---

END;

Notes: The syntax for implicit cursors is easier to use yet often, less efficient. An explicit cursor is DECLARE'd OPEN'ed FETCH'ed and CLOSE'ed. Note that the user has to explicitly define each step.