Cursors – Implicit Cursors

What are Cursors? 

A cursor is a pointer to a private SQL area, temporarily allocated in the session’s User Global Area (UGA), that stores information about the processing of a SELECT statement or a DML statement (Insert, Update or Delete). In layman terms, a Cursor is used to fetch (SELECT) a particular dataset from a much larger dataset which can then be used for data manipulation using other DML Operations like Insert, Update or Delete.

There are two types of Cursors: Implicit Cursors and Explicit Cursors.

In this post, we will discuss mainly about Implicit Cursors.

Implicit Cursors: Whenever you execute a simple SELECT statement or a DML Statement like INSERT, UPDATE or DELETE statement, Oracle database creates an implicit cursor to process these statements. In such scenarios, Oracle opens a Cursor, fetches the row and then closes the Cursor after completing the sql operation. In Implicit cursors, the cursor life cycle is maintained by the Oracle Database.

Consider the following example. Create a table called xx_test_cursor and insert 4 records into that table. (below code).

The select statement gives below result:

A sample example of an implicit cursor: SELECT INTO

This gives the following result:

But, if the SELECT statement fetches more than 1 row in the dataset, then the program ends in exception. (in the code below I have commented the emp_id = 1001)

And since this Select statement fetches more than 1 row, the code throws the following error:

Note: Actually Implicit Cursors are faster to process than an explicit cursor because there isn’t a separate step that declares, opens, fetches and closes the cursor.

Attributes of an Implicit Cursor:  There are 4 important attributes of an Implicit Cursor:

  • %FOUND – Until a DML statement like Insert, Update or Delete affects one or more rows, the SQL%FOUND will result as NULL The example below tries to update a record in the xx_test_cursor table. Only if the Update statement affects one or more rows, then the next statement in the IF condition is executed:

  • %ISOPEN – For Implicit Cursors, the database automatically closes the Cursor after executing the operation. Thus, the %ISOPEN always yields a FALSE for Implicit Cursors (because the Cursor is already closed). It is more useful for Explicit Cursors.
  • %NOTFOUND – %NOTFOUND is opposite to the %FOUND attribute. This attribute checks if the corresponding DML Operation like Insert, Update or Delete modified any data in the table. If the SQL code did not update / insert / delete any data, then this attribute returns TRUE. Else if the SQL modifies any data, then it returns FALSE.
    • In the example below – the UPDATE statement tries to update the table for a record that does not exist. Thus, no data was modified and the %NOTFOUND is true. And accordingly, the later INSERT statement works.

  • %ROWCOUNT – The %ROWCOUNT attribute returns the total number of records that got affected by the DML Operation.
    • For example – in the code below – the test table has 1 record with emp_id = 1006. Thus, the delete statement deletes 1 record and the displays the same in the dbms_output.

And the corresponding dbms_output displays as below:

Leave a Reply

  • (will not be published)