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.

The Oracle server implicitly opens a cursor to process each SQL statement not associated with an explicitly declared cursor. PL/SQL allows you to refer to the most recent implicit cursor as the SQL cursor.

For a long time there have been debates over the relative merits of implicit cursors and 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:

1. 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.
2. A cursor is automatically associated with every DML statement (UPDATE, DELETE, and INSERT).
3. All UPDATE and DELETE statements have cursors those recognize the set of rows that will be affected by the operation.
4. An INSERT statement requires a place to accept the data that is to be inserted in the database; the implicit cursor fulfills this need.
5. 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.

Example 1 of an Implicit cursors

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;

Another Example of an Implicit cursor

The following single-row query calculates and returns the total salary for a department. PL/SQL creates an implicit cursor for this statement:

SELECT SUM (salary) INTO department_total
FROM employee
WHERE department_number = 10;


PL/SQL provides some attributes, which allow you to evaluate what happened when the implicit cursor was last used. You can use these attributes in PL/SQL statements like functions but you cannot use then within SQL statements.

%ROWCOUNT	The number of rows processed by a SQL statement.
%FOUND	TRUE if at least one row was processed.
%NOTFOUND	TRUE if no rows were processed.
%ISOPEN	TRUE if cursor is open or FALSE if cursor has not been opened or has been closed. Only used with explicit cursors.

An Example of PL/SQL Attribute

DECLARE
rows_deleted NUMBER;
BEGIN
DELETE * FROM emp;
rows_deleted := SQL%ROWCOUNT;
END;


The implicit cursor has the following drawbacks:

• It is less efficient than an explicit cursor.
• It is more vulnerable to data errors.
• It gives you less programmatic control.

Please see this link to know if Implicit cursors are fast or Explicit cursors.