CURSORS

is the way you loop through the rows returned by a Select statement. Say for example from SQL* Plus if you write SELECT customer_name FROM customer returns the set of rows from customer table. If your corporation decides to give some discounts for your customers, based on how loyal he is, how much business we do with that customer etc etc, now we need to check some of the stuff before giving discounts, so we need to check one by one row from customer table and make a decision based on the rules. So here we cannot use a single update statement to the related tables. Now Cursor comes into picture. A cursor is nothing but a result set through which you can fetch one by one row.

They are two different types of cursors

• Explicit Cursors
• Implicit Cursors.

Implicit cursors is nothing but if you issue a select statement the server executes the query
and stores the rows in a memory area in the server and returns the rows in network packets to the server, here you do not have the control in the result set of rows.

Explicit cursors is the sql statements where you have the control over the result set
where you can fetch one by one row from the result set.

Things should be done while dealing with cursors
Declare a cursor
Open a cursor
Fetch data into variables from the cursor
Close the cursor

Following picture will give you some idea about the cursor.

Remember while working with cursors 
we must provide same number of variables as the number of columns we selected in the select statement of the cursor.
You cannot fetch once you closed the cursor, if you do it will raise an exception called invalid_cursor. You can do fetch only after opening the cursor.

Cursor Attributes
%ISOPEN returns TRUE if already the cursor is open. returns FALSE if its not opened.
%NOTFOUND returns TRUE if the last fetch statement does not return a row.
%FOUND returns TRUE if the last fetch statement return a row.
%ROWCOUNT total number of rows returned so far.

How you declare a Cursor?
This can be done in the DECLARE section of a PL/SQL Block.

DECLARE
        var_custname customer.cust_name%type;
        CURSOR getcustnames IS SELECT cust_name FROM customer;
BEGIN
        OPEN getcustnames; --opening a cursor, actually execute the sql
                                         --and places all the rows in server memory area 
        LOOP
           FETCH getcustnames into var_custname; -- Fetching the current record                   
           Exit When getcustnames%NOTFOUND --If all the rows got over then
                                                                          -- %NOTFOUND cursor attribute will be true.
                 dbms_output.put_line(var_custname); -- Display the customer name
       END LOOP;
       Close getcustnames; -- Close the cursor so that server releases memory.
END;

Passing Arguments to a cursor

DECLARE
        TYPE id_emp_table IS TABLE OF number(2) 
         INDEX BY BINARY_INTEGER;
         v_deptno number(2);
         i BINARY_INTEGER := 1;
         CURSOR get_empno(v_in_dept number(2))
         IS SELECT empno FROM emp where deptno = v_in_dept;
         empno_plsql_table id_emp_table;
BEGIN
         v_deptno := 30;
         Open c1(v_deptno);
         Loop
               Fetch c1 into v_empno_hold;
               If c1%FOUND then
                   empno_plsql_table(i) := v_empno_hold;
                   i := i + 1;
              Else
                   Exit;
              End If;
         End Loop;
         Close c1;
         For j in 1..i Loop
                dbms_output.put_line( empno_plsql_table(j) );
         End Loop;
END;

FOR UPDATE cursor

DECLARE
       v_deptno number(3);
       CURSOR c1 IS select empno, deptno from emp FOR UPDATE;
BEGIN
        For c1_record IN c1 Loop
            If deptno = 40 then
               DELETE from emp WHERE CURRENT OF c1;
            End If;
        End Loop;
        COMMIT WORK;
END;