Cursors in Oracle

 Cursors: It is an Temporary buffer used to hold the    transactional data for manipulation purpose.
 It is not stored in database.
 It is not Re-usable.
 It is valid in Pl/sql Block only.
 It is created in the Logical memory only.
 2 Types :
 1. Implicit cursors: Automatically created by oracle       whenever  " DML" operations are performed by        user.

 2. Explicit Cursors: Created by user in Pl/sql Block.
   Used to retrieve multiple rows from multiple            tables into pl/sql block for manipulation purpose.
   It is based on SELECT stmt.

 Explicit Cursors :
 I . Declaring Cursor   Syntax:  cursor <cursor name> is <select stmt>;

 II. Cursor Operations: i> open <cursor name>;
     used to open the cursor
    memory will be alloted to cursor after opening it
 ii> Fetch <cursor name> into < Variables >;
     Used to retrieve data from cursor to pl/sql               variables.
     At a time it can retrieve only one row into                variables
     Generally Fetch will be placed in loop
 iii> close <cursor name>;
       used to close the cursor
      memory alloted will be Deallocated

 III. Cursor Attributes      Gives the status of cursor
      <cursor name>%<attribute>
 a> %isopen - Returns True / False
      Returns True if cursor is opened successfully
 b> %found - Returns True / False
     Returns True if Fetch statement sucessfully           retrieves the row into Pl/sql variables.
 c> %notfound - Returns True / False
     Returns True if Fetch statement fails to retrieve      the row into Pl/sql variables
 d> %rowcount - Returns Number
     Returns the No.of rows sucessfully retrieved          from cursor so far. Initially it holds 0.
     After every sucessful "Fetch" it is incremented          by 1.
---------------------------------------------------------------
 

 Using Explicit Cursors: * Pl/sql Block calculates new commission for all     employees using Explicit Cursors:

 declare
 cursor c1 is select empno,ename,comm from emp
 order by deptno;
 veno emp.empno%type;
 vname emp.ename%type;
 vcomm emp.comm%type;
 begin
 open c1;
 IF c1%isopen THEN
 dbms_output.put_line
 (' Employ New Commission Report ');
 loop
 fetch c1 into veno,vname,vcomm;
 exit when c1%notfound;
 if vcomm is null then
 vcomm := 3000;
 elsif vcomm = 0 then
 vcomm := 2500;
 else
 vcomm := vcomm + vcomm * .25;
 end if;
 update emp set comm = vcomm
 where empno =  veno;
 dbms_output.put_line(c1%rowcount||'  '||veno
 ||'  '||vname||'  '||vcomm);
 end loop;
 dbms_output.put_line(c1%rowcount ||' Employees  are updated with new commission ');
 close c1;
 -- commit;
 ELSE
 dbms_output.put_line(' Unable to open cursor... ');
 END IF;
 end;
---------------------------------------------------------------
 student - roll,name,class,m1,m2,m3,m4,m5,m6--------------------------------------------------------------
 * Pl/Sql block calculates Total marks and Average    marks of all students & Give Average marks of     class:
 declare
 cursor c2 is select roll,name,m1,m2,m3,m4,m5,m6  from student where class = 10;
 i c2%rowtype;   -- cursor record type declaration
 totmarks number(6);
 avgmarks number(5,2);
 classtot number(12) := 0 ;
 classavg number(7,2);
 begin
 open c2;
 dbms_output.put_line(' Student Report ');
 loop
 fetch c2 into i;
 exit when c2%notfound;
 totmarks := i.m1 + i.m2 + i.m3 + i.m4 + i.m5 +                         i.m6;
 avgmarks := totmarks / 6;
 classtot := classtot + totmarks;
 dbms_output.put_line(i.roll||'  '||i.name
 ||'  '||totmarks||'  '||avgmarks);
 end loop;
 classavg := classtot / c2%rowcount;
 dbms_output.put_line('Average marks of Class is :  '||classavg);
 close c2;
 end;
---------------------------------------------------------------
 Exercise:
 create " Item_Master " Table and insert the rows.
 Itno -- 101
 Name -- RICE
 UOM - (Pcs,Ltr,Mtr,Kgs)(Unit of Measurement) --                Kgs
 Class - (A,B,C) -- C
 Description -- Sona Masoori high quality
 Rate -- 38
 ROL ( Re order Level ) -- 100
 ROQ ( Re order Quantity ) -- 500
 QOH ( Quantity on hand ) -- 60

 * Write a pl/sql program to print the item details   with total value ( qoh * rate ) and also print total   value of entire stock.
 * Write a pl/sql program to print the items to be         Re-ordered.   ( if qoh <= rol ).
---------------------------------------------------------------
 * Pl/SQL Block using Cursor with operations:
 declare
 cursor c2 is select * from dept;
 i  c2%rowtype;
 begin
  open c2;
 loop
 fetch c2 into i;
 exit when c2%notfound;
 Dbms_output.put_line(c2%rowcount||'  '||i.deptno
 ||'   '||i.dname||'   '||i.loc);
 end loop;
 close c2;
 end;

 Cursor For Loop:
 used to perform cursor operations automatically.
 Improves performance.
 Syntax: 
 For < variable > in <cursor name> loop
      < Exec stmts >;
 End loop;

 Advantage: No need to
 1. open cursor
 2. fetch rows
 3. check for end of rows
 4. close cursor
 5. declare variables
--------------------------------------------------------------- 

* Pl/SQL Block using Cursor For Loop: declare
   cursor c2 is select * from dept;
 begin
 for i in c2 loop
  dbms_output.put_line(c2%rowcount||'  '||i.deptno
  ||'  '||i.dname||'  '||i.loc);
 end loop;    -- close cursor
 end;

   for i in c2 loop  ---> declares i variable
                                 open cursor                                                           fetch 1 row into i
                             check for end of rows
---------------------------------------------------------------
 Ex: Different Cursor Declarations
 Cursor with Equi join: cursor ecur is select     empno,ename,sal,job,emp.deptno,dname,loc from   emp,dept where emp.deptno = dept.deptno;

 cursor with arthematic expressions :
 declare
 cursor epays is select empno ecode, sal basic,
 round(sal *.25) da, round(sal * .35) hra,
 round(sal  * .15)   pf,
 round(sal + sal * .25 + sal * .35 - sal *  .15) gross
 from emp where sal >= 7000;
 begin
 dbms_output.put_line(' Employees Pay Report ');
 dbms_output.put_line(' Ecode  Basic  Da  Hra  Pf    Gross ');
 for k in epays loop
 dbms_output.put_line(k.ecode||'  '||k.basic
 ||'   '||k.da||'  '||k.hra||'  '||k.pf||'  '||k.gross);
 end loop;
 end;
                                                             
 Cursor Using Scalar Query: declare
 cursor c3 is select empno, ename, job, sal,
 (select min(sal) from emp where job = E.job)    Lopay,
 (select max(sal) from emp where job = E.job)    Hipay
  from emp E order by job;
 begin
 dbms_output.put_line(' Job wise Salary Limits ');
 dbms_output.put_line(' Empno    Emp name    Job       Salary   Lopay    Hipay ');
 for k in c3 loop
 dbms_output.put_line(k.empno||'  '||k.ename
 ||'  '||k.job||'  '||k.sal||'  '||k.lopay||'  '||k.hipay);
 end loop;
 end;           
---------------------------------------------------------------  

* Pl/Sql Block calculating Bonus for all employees: declare
 cursor c1 is select empno, ename,
 sal + nvl(comm,0) net, job from emp order by sal;
 i c1%rowtype;
 bonus number(20,2);
 --  totbonus number(20) := 0;
 begin
 dbms_output.put_line(' Employee Bonus Report ');
 dbms_output.put_line(' Sno Empno Emp  Name       Job   Bonus ');
 for i in c1 loop
 if i.job = 'CLERK' then
 bonus := round(i.net * 1.25);
 elsif i.job = 'SALESMAN' then
 bonus := round(i.net * 1.35);
 elsif i.job = 'MANAGER' then
 bonus := round(i.net * 1.65);
 else
 bonus := round(i.net * 1.75);
 end if;
 -- totbonus := totbonus + bonus;
 dbms_output.put_line(c1%rowcount||'  '||
  i.empno||'   '||i.ename||'   '||i.job||'   '||bonus);
 -- Calculate bonus for first 10 lowest paid
  -- employees only
 -- exit when (c1%rowcount >= 10);
 -- total bonus upto 200000 only
 -- IF totbonus > 200000 THEN
 --   exit;
 -- END IF;
 end loop;
 end;

 exit when (c1%rowcount >= 10 or
                 totbonus >= 200000);
---------------------------------------------------------------  

Write a Pl/sql program to print 2 Tables  content      independently with proper Titles as below :      Deparment Information
  Dept id     Dept Name    Location
 Employee Information
  Empno  Emp Name  Salary .......
---------------------------------------------------------------

 Cursor With Parameters: (8.0) Used to accept the input dynamically while    opening the cursor.
 Supports to define generalized cursors.
 Maximum 32 parameters can be passed to Cursor.

 Dedicated cursor
 cursor c1 is select * from emp where 
 deptno = 10;

 declare
 -- Dynamic cursor
 cursor c1(dno number) is select * from emp
 where deptno = dno;
 begin
 open c1(10);
 ----------------
 ----------------
 close c1;
 open c1(30);
 ++++++++++
 ++++++++++
 close c1;
 end;
---------------------------------------------------------------  

* Pl/Sql block calculates increment for a particular      dept employees. ( using Cursor Parameters ):
 declare
 cursor c1(dno number) is select * from emp
 where deptno = dno;
 i c1%rowtype;
 incr number(16);
 net number(16,2);
 expr number(2);
 begin
 open c1(&dept);
 dbms_output.put_line(' Employ Increment
 Report ');
 loop
 fetch c1 into i;
 exit when c1%notfound;
 net := i.sal + nvl(i.comm,0);
 expr :=
  round(months_between(sysdate,i.hiredate)/12);
 if expr <= 2 then
 incr := net *.25;
 elsif expr < 5 then
 incr := net * .45;
 else
 incr := net * .65;
 end if;
 update emp set sal = sal + incr
 where empno = i.empno;
 dbms_output.put_line(i.empno||'  '||i.ename
 ||'  '||i.job||'  '||expr||'  '||incr);
 end loop;
 close c1;
 -- commit;
 -- Re-opening cursor in For loop
 dbms_output.put_line ( 'Employ Details are :' );
 for k in c1(&deptid) loop
 dbms_output.put_line(k.empno||'  '||k.ename
 ||'  '||k.job||'  '||k.hiredate);
 end loop;
 end;

 Ex 2:
 cursor c2(dno number, vjob varchar2) is
 select * from emp where deptno = dno
 and job = vjob;

 * open c2(30,'CLERK');
 * for i in c2(20,'SALESMAN') loop

 Ex 3:
 cursor c3(vcourse varchar2,vtime varchar2) is
 select * from student where course = vcourse
 and timing = vtime;
 * open c3('Oracle9i','10 am');
 * for k in c3('Oracle9i','4.30 pm') loop

  cursor c4 is select * from student
  where course = '&course';
 * It is valid in oracle sql environment. Not             supported while using Pl/sql programs in other        s/w tools .

 * Cursor Parameters are used to pass values from     other Application s/w tools like Java , .Net,
  Developer 6i, ERP's , ..... etc

 * Explicit cursors are used to retrieve Huge loads       of data from multiple tables into Logical memory     for processing purpose .
---------------------------------------------------------------
  Implicit Cursors: - Automatically defined by oracle whenever " DML"     operations are performed by user.
 - It has a fixed name " SQL ".
* - It gives the status of "DML" stmts in pl/sql Block
 - It will not support cursor operations.
 - It supports %found,%notfound,%rowcount             attributes.
   %found returns TRUE if DML is success.
  %notfound returns TRUE if DML is failure.
  %rowcount returns no.of rows manipulated by          DML statement.        %isopen is not valid.

 >update emp set sal = sal + 2000
    where empno = 790;

  begin
  update emp set sal = sal + 2000
  where empno = 790;
  dbms_output.put_line(' Employ Updated ');
  end;
  
 In above program we r unable to find DML stmt       Status.
 Using Implicit cursor it is possible to know status.

 Using Implicit cursors:
 declare
 vdept number(2) := &dept; 
 begin
 update emp set sal = sal + 1000
 where deptno =  vdept;
 if sql%Notfound then
 dbms_output.put_line('Unable to Update rows - No  such dept exists');
 else
 dbms_output.put_line(sql%rowcount||' Employees  are updated sucessfully ');
 commit;
 end if;
 end;
---------------------------------------------------------------
 declare
 vdept number(2) := &dept;
 begin
 delete from emp where deptno = vdept;
 if sql%rowcount > 3 then
 dbms_output.put_line(' Invalid operation - cannot    remove more than 3 employees ');
 rollback;
 else
 dbms_output.put_line(sql%rowcount||' Employees  are removed');
 commit;
 end if;
 end;
---------------------------------------------------------------