Oracle PL/SQL Implicit Cursors

Implicit Cursors

Implicit cursors are automatically created and used by Oracle every time you issue a select statement in PL/SQL. If you use an implicit cursor, Oracle will perform the open, fetches, and close for you automatically. Implicit cursors are used in statements that return only one row. If the SQL statement returns more than one row, an error will occur.

For a long time there have been debates over the relative merits of implicit cursorsand explicit cursors. The short answer is that implicit cursors are faster and result in much neater code so there are very few cases where you need to resort to explicit cursor.

The process of an implicit cursor is as follows:

Whenever an SQL statement is executed, any given PL/SQL block issues an implicit cursor, as long as an explicit cursor does not exist for that SQL statement.
A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).
All UPDATE and DELETE statements have cursors that recognize the set of rows that will be affected by the operation.
An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
The most recently opened cursor is called the “SQL%” Cursor.

The implicit cursor is used to process INSERT, UPDATE, DELETE, and SELECT INTO statements. Oracle automatically performs the OPEN, FETCH, and CLOSE operations, during the processing of an implicit cursor.

In the following PL/SQL code block, the select statement makes use of an implicit cursor:

Begin
Update emp Where 1=2;
Dbms_output.put_line(sql%rowcount ||’ ‘|| ‘ rows are affected by the update statement’);
End;

SELECT SUM (sal) INTO TOTAL
FROM emp
WHERE depno = 10;