PL SQL CURSOR

• A cursor is a temporary work area created in the system memory when a SQL statement is executed. 
• This temporary work area is used to store the data retrieved from the database, and manipulate this data. 
• A cursor can hold more than one row, but can process only one row at a time.
• The set of rows the cursor holds is called the active set.

There are two types of cursors are there in Pl/sql known as Implicit Cursor and Explicit Cursor.

Implicit Cursor:

• Declared for every PLSQL DML or SELECT statement and maintained by Oracle server internally.
• We can use implicit cursor attributes to verify of the last executed statement 

Implicit Cursor Attributes:

• SQL%ROWCOUNT : Returns number of rows effected by most recent sql statement
• SQL%FOUND : Returns TRUE if the most recent sql statement effects one or more rows
• SQL%NOTFOUND : Returns TRUE if the most recent sql statement doesn't effects any rows
• SQL%ISOPEN : Always returns FALSE becose oracle close the cursor immediately after they executed.

      Explicit Cursor:

•      Declared for queries that return more than one row and maintained by the programmer
•      We Can evaluate the cursor status using explicit cursor attributes.

    Explicit Cursor Attributes:

•     cur_var%ROWCOUNT :  Returns number of rows fetched so far
•     cur_var%FOUND : Returns TRUE if the recent fetch contains a row
•     cur_var%NOTFOUND : Returns TRUE if the recent fetch doesn't have a row
•     cur_var%ISOPEN : Returns TRUE if the cursor is open

Below are the steps to use a cursor.

-- Declare a cursor 

-- Open the Cursor

-- Fetch the records from the cursor

-- Close the Cursor

At the time of declaration of a cursor we need to give the cursor name and the query which is fetch the records from the table.

When we open the cursor the cursor pointer points to the first record of the Active result set.

When we fetch one record from the cursor pointer points to the next record on the cursor.

When we close the cursor it releases the memory area.

For Example:

SET SERVEROUTPUT ON

DECLARE

v_name  employee.empname%TYPE;

v_sal  employee.salary%TYPE;

CURSOR emp_cur IS

SELECT empname,salary from employee;

BEGIN

IF  NOT emp_cur %ISOPEN THEN

    OPEN emp_cur ;

END IF;

LOOP

    FETCH emp_cur INTO v_name , v_sal;

    Dbms_output.put_line(‘Line number :’|| emp_cur%ROWCOUNT||

    ‘fetched. Name : ’|| v_name ||’  salary : ’|| v_sal);

EXIT WHEN emp_cur%NOTFOUND;

END LOOP;

CLOSE emp_cur ;

END;

Cursor For Loop:

After declaring a cursor, using Cursor For Loop we can directly do  Open, Fetch and close the cursor using a cursor for loop.

For example

FOR  rec IN emp_cur LOOP

Dbms_output.put_line(‘Line number :’|| emp_cur%ROWCOUNT||

‘fetched. Name : ’||rec.empname ||’  salary : ’|| rec.salary);

END LOOP;

Cursor For Loop Using SubQuery:

Using Cursor for loop with subquery we do all the operations i.e. declare,open, fetch and close a cursor directly.
For Example,

FOR emp_rec IN (SELECT empname, salary from employee)

LOOP

……………. –pl/sql statements

END LOOP;

Also check : PL SQL REF Cursor

Happy Coding :)