Cursor

It is a temporary SQL result holder when we execute the SQL block/statement. So we can say it is a temporary memory area which basically gets used to navigate and manipulate the result which got during execution of a SQL statement and it basically contains the result of select statement and the rows of data accessed by it.

It has a capability to hold the record set, it means n number of rows but we can process one by one moving cursor ahead.

Note: - set of rows a cursor holds is known as active set.

There are two types of cursor in Pl/Sql which are as below:-

i)                    Implicit Cursor                                           ii) Explicit Cursor

1)      Implicit Cursor:-

Default cursor which are getting created by Oracle application itself internally to navigate the records. Whenever we execute any DML statement like Insert, Update and Delete and even in case of select statement as well. This is getting created for a select statement which returns only single row even.

Even we can use implicit cursor’s attribute to have a finer control on sql statement and better messages.

Cursor attributes are %FOUND, %NOTFOUND, %ROWCOUNT, %ISOPEN, %BULK_ROWCOUNT and %BULK_EXCEPTIONS. These attributes return useful information about the execution of a data manipulation statement.

Meaning of these attributes is as below:-

%FOUND:-

        Returns true if DML statement affect minimum one row and if select into … statement returns minimum one row otherwise false.

Before the first fetch from an open cursor, cursor_name%FOUND returns NULL. Afterward it returns true if the last fetch returned a row otherwise false.

%NOTFOUND:-

        Returns true if DML statement does not affect any single row even and if select into … statement does not return even a single row.

Before the first fetch from an open cursor, cursor_name%NOTFOUND returns NULL. Afterward it returns false if the last fetch returned a row, otherwise true.

%ROWCOUNT:-

        It Returns ‘Number of rows’ which are affected by a DML and Select statement. Before the first fetch from an open cursor, cursor_name%ROWCOUNT returns 0. Afterward it returns the number of rows fetched.

%ISOPEN:-

        If a cursor is open, cursor_name%ISOPEN returns true, false otherwise.

%BULK_ROWCOUNT:-

        This acts like an index by table. Its ith element stores the number of rows processed by the ith execution of DML/Select statement. If the ith execution affects no rows, %BULK_ROWCOUNT (i) returns zero.

%BULK_EXCEPTIONS:-

 For each index value i between 1 and SQL%BULK_EXCEPTIONS.COUNT,  SQL%BULK_EXCEPTIONS(i).ERROR_INDEX specifies which iteration of the FORALL loop caused an exception. SQL%BULK_EXCEPTIONS(i).ERROR_CODE specifies the Oracle error code that corresponds to the exception.

Below are the examples for use of implicit cursor:

i)                    %ROWCOUNT:-

DECLARE

   SELECT emp_name, emp_id, address FROM emp

      ORDER BY emp_id DESC; 

BEGIN

      If (SQL%ROWCOUNT > 50) Then

        dbms_output.put_line('Emp ' || e_name || e_id || e_add);

END;

/

ii)                   %NOTFOUND:-

DECLARE

 SELECT emp_name, emp_id, address FROM emp

      ORDER BY emp_id DESC; 

BEGIN

   if (cur%NOTFOUND) Then

      dbms_output.put_line('Emp ' || e_name || e_id || e_add);

END;

/

2)      Explicit Cursor:-

This is same as implicit curson

Steps are as below to work with an explicit cursor.

i)                    Open the cursor.

ii)                   Fetch the records in the cursor one at a time.

iii)                 Close the cursor.

Syntax:

To open a cursor: OPEN cursor_name;

To fetch records from a cursor is: FETCH cursor_name INTO record_name;

To close a cursor is: CLOSE cursor_name;

After a cursor is opened, it first row becomes the current row, and when the data is fetched it is copied into the record or variables and the logical pointer moves to the next row and it becomes the current row. For every fetch, the pointer moves to the next row. After last row fetch even if we will try to fetch it will throw an error.
When there is more than one row in a cursor we can use loop as below:

LOOP 

    process the row

END LOOP; 

The explicit cursor attributes are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN, which are the same as explained in implicit cursor section.

Below are the examples for use of explicit cursor:

i)                    %ROWCOUNT:-

DECLARE

   CURSOR cur is

   SELECT emp_name, emp_id, address FROM emp

      ORDER BY emp_id DESC; 

   e_name employees.emp_name%TYPE;

   e_id employees.emp_id%TYPE;

   e_add employees.address%TYPE;

BEGIN

   OPEN cur;

   LOOP

      FETCH cur INTO e_name, e_id, e_add;

      EXIT WHEN (cur%ROWCOUNT > 50);

      dbms_output.put_line('Emp ' || e_name || e_id || e_add);

   END LOOP;

   CLOSE cur;

END;

/

ii)                   %NOTFOUND:-

DECLARE

   CURSOR cur is

   SELECT emp_name, emp_id, address FROM emp

      ORDER BY emp_id DESC; 

   e_name employees.emp_name%TYPE;

   e_id employees.emp_id%TYPE;

   e_add employees.address%TYPE;

BEGIN

   OPEN cur;

   LOOP

      FETCH cur INTO e_name, e_id, e_add;

      EXIT WHEN (cur%NOTFOUND);

      dbms_output.put_line('Emp ' || e_name || e_id || e_add);

   END LOOP;

   CLOSE cur;

END;

/

iii)                 %ISOPEN:-

IF NOT (cur%ISOPEN) THEN

   OPEN cur;

END IF;