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).

CREATE TABLE xx_test_cursor
(
    emp_name         VARCHAR2 (200),
    emp_id           NUMBER,
    emp_join_date    DATE,
    manager_name     VARCHAR2 (200)
);

INSERT INTO xx_test_cursor
     VALUES ('John',1001,SYSDATE - 30, 'Frank');

INSERT INTO xx_test_cursor
     VALUES ('Graeme', 1002, SYSDATE - 40, 'Frank');

INSERT INTO xx_test_cursor
     VALUES ('Shawn', 1003, SYSDATE - 50, 'Frank');

INSERT INTO xx_test_cursor
     VALUES ('Jonty', 1004, SYSDATE - 60, 'Frank');

COMMIT;

SELECT * FROM xx_test_cursor;

The select statement gives below result:

A sample example of an implicit cursor: SELECT INTO

DECLARE
    l_emp_name    xx_test_imp_cursor.emp_name%TYPE;
    l_join_date   xx_test_imp_cursor.emp_join_date%TYPE;
BEGIN
    SELECT emp_name, emp_join_date
      INTO l_emp_name, l_join_date
      FROM xx_test_imp_cursor
     WHERE manager_name = 'Frank' 
       AND emp_id = 1001;

    DBMS_OUTPUT.put_line (
           'Emp Name is: '
        || l_emp_name
        || ' and Emp Join Date is: '
        || l_join_date);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Exception during fetching data: ' || SQLERRM);
END;

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)

DECLARE
    l_emp_name    xx_test_cursor.emp_name%TYPE;
    l_join_date   xx_test_cursor.emp_join_date%TYPE;
BEGIN
    SELECT emp_name, emp_join_date
      INTO l_emp_name, l_join_date
      FROM xx_test_cursor
     WHERE manager_name = 'Frank';

    DBMS_OUTPUT.put_line (
           'Emp Name is: '
        || l_emp_name
        || ' and Emp Join Date is: '
        || l_join_date);
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Exception during fetching data: ' || SQLERRM);
END;

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:

DECLARE
BEGIN
    UPDATE xx_test_cursor
       SET emp_name = emp_name || '_new'
     WHERE emp_id = 1001;

    IF SQL%FOUND
    THEN
        DBMS_OUTPUT.put_line ('SQL Found is True, thus executing the following statement..');
        INSERT INTO XX_TEST_CURSOR
             VALUES ('DARELL',
                     1005,
                     SYSDATE - 60,
                     'Frank');
        COMMIT;              
    END IF;
    
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Exception when processing data: ' || SQLERRM);
END;

• %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.

DECLARE
BEGIN
    UPDATE xx_test_cursor
       SET emp_name = emp_name || '_new'
     WHERE emp_id = 2006;

    IF SQL%NOTFOUND
    THEN
        DBMS_OUTPUT.put_line ('SQL%NOTFOUND is True, thus executing the following statement..');
        INSERT INTO XX_TEST_CURSOR
             VALUES ('SCOTT',
                     1006,
                     SYSDATE - 60,
                     'Frank');
        COMMIT;              
    END IF;
    
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Exception when processing data: ' || SQLERRM);
END;

• %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.

DECLARE
BEGIN
    DELETE FROM xx_test_cursor
          WHERE emp_id = 1006;

    DBMS_OUTPUT.put_line ('Total Number of rows deleted: ' || SQL%ROWCOUNT);
    
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line ('Exception when processing data: ' || SQLERRM);
END;

And the corresponding dbms_output displays as below: