Cursor and Its Types

Cursor

--------

Cursor is a select Statement, Once the Cursor is called, It will create result set of the Query in Virtual Table i.e. Called Active Set.

Cursor is of Two types

   1. Implicit cursor

   2. Explicit cursor

Cursor is used to Process multiple rows in Pl/sql Block.
Explicit cursor is of two types.i.e.

    1)     Using Keywords Cursor

        2)     For Loop Cursor

Using Keywords Cursor

-----------------------------

Following Keywords are used to call Cursor

Openà  To Open the Cursor

Fetch à To fetch the details of cursor into variable

Exità  Exit when Cursor is notfound

Close à closing the Cursor

Syntax of Keywords Cursor

-----------------------------

DECLARE

--Variable/cursor Declaration

BEGIN

OPEN ;

LOOP

FETCH INTO ;

EXIT WHEN %NOTFOUND;

--Process statements

END LOOP;

CLOSE ;

END;

Example to Display the employee details using Cursor keywords.

     A.  Display details of employees who is working in deptno 20.

     B.   Two examples to display employee details using cursor keywords

   Using Direct Variables

   Using Rowtype.

Using Direct Variables

DECLARE

ln_empno varchar2(100);

ln_ename varchar2(100);

CURSOR c1

IS

SELECT empno,ename FROM emp where deptno = 20 ;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO ln_empno,ln_ename;

DBMS_OUTPUT.PUT_LINE(‘Employee Number :’||ln_empno);

DBMS_OUTPUT.PUT_LINE(‘Employee name :’||ln_ename);

         EXIT WHEN C1%NOTFOUND;

END LOOP;

CLOSE C1;

END;

In Above pl/sql block

         ·        Declare a cursor to fetch the deptno 20 employee details and also declare two variable to fetch               employee details.

         ·        Fetch the employee details of cursor into variables.

         ·        While fetching empno,ename , Datatype of fetching variables are also should match.

         ·        To Display the output of fetched variables we use pre-defined                             DBMS_OUTPUT.PUT_LINE Package.

Using Row type

DECLARE

    lv_rec emp%ROWTYPE;

CURSOR c1

IS

SELECT * FROM emp where deptno = 20 ;

BEGIN

OPEN C1;

LOOP

FETCH C1 INTO lv_rec;

DBMS_OUTPUT.PUT_LINE(‘Employee Number :’||lv_rec.empno);

DBMS_OUTPUT.PUT_LINE(‘Employee name :’||lv_rec.ename);

EXIT WHEN C1%NOTFOUND;

END LOOP;

CLOSE C1;

END;

In above pl/sql block

        ·        Declare a cursor variable and rowtype variable.

        ·        Fetch all the employee information into rowtype variable.

        ·        At max only one record can be fetched into rowtype variable.

        ·        To display all records , Need to loop the cursor.

        ·        Display output using pre defined package DBMS_OUTPUT.PUT_LINE.

Example to Display the employee details using For Loop.

DECLARE

CURSOR c1

IS

SELECT empno ,ename FROM emp WHERE deptno =20;

BEGIN

  FOR lv_emp_details IN C1

  LOOP

  DBMS_OUTPUT.PUT_LINE(‘Employee number:’||lv_emp_details.empno);

  DBMS_OUTPUT.PUT_LINE(‘Employee name:’||lv_emp_details.empno);

  END LOOP;

END;

In above pl/sql block

        ·        Declared a cursor and fetched using For Loop

        ·        In For loop lv_emp_details doesnot required to declare , The for loop variable does an                            implicit declare.

        ·        All the Cursor Details are copied to lv_emp_details record by record.

        ·        Display each record using DBMS_OUTPUT.PUT_LINE.

Displaying employee details Without declaring the Cursor.

BEGIN

  FOR lv_emp_details IN (select * from emp where deptno =20)

  LOOP

  DBMS_OUTPUT.PUT_LINE(‘Employee number:’||lv_emp_details.empno);

  DBMS_OUTPUT.PUT_LINE(‘Employee name:’||lv_emp_details.empno);

  END LOOP;

END;

Exercise on cursor

Update Commission of employees

When deptno is 10 , update commission as 10%of sal

When deptno is 20, Upate commission as 20% of sal.

When deptno is 30, update commission as 30% of sal.

If not any of the dept above , Update commission as 40% of sal.

Please do the following exercise in two ways.

       1)   Using Cursor key words

       2)   Using For Loop.

Solution

Using Keywords Curosor

Declare

lv_rec emp%ROWTYPE;

CURSOR c1

IS

SELECT * FROM emp;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO lv_rec;

IF lv_rec.deptno =10 THEN

  Update emp

       Set comm = 0.10*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is  updated with 10% of sal’);

ELSIF lv_rec.deptno =20 then

  Update emp

       Set comm = 0.20*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is updated with 20% of sal’);

ELSIF lv_rec.deptno = 30 then

  Update emp

       Set comm = 0.30*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is updated with 30% of sal’);

ELSE

  Update emp

       Set comm = 0.40*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is updated with 40% of sal’);

END IF;

EXIT WHEN c1%NOTFOUND;

END LOOP;

CLOSE c1;

END;

Using ForLoop Curosor

Declare

CURSOR c1

IS

SELECT * FROM emp;

BEGIN

FOR lv_rec IN c1

LOOP

IF lv_rec.deptno =10 THEN

  Update emp

       Set comm = 0.10*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is updated with 10% of sal’);

ELSIF lv_rec.deptno =20 then

  Update emp

       Set comm = 0.20*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is updated with 20% of sal’);

ELSIF lv_rec.deptno = 30 then

  Update emp

       Set comm = 0.30*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is updated with 30% of sal’);

ELSE

  Update emp

       Set comm = 0.40*lv_rec.sal

  Where empno = lv_rec.empno;

  DBMS_OUTPUT.PUT_LINE(The Following Empno ‘||lv_rec.empno||’ is updated with 40% of sal’);

END IF;

END LOOP;

END;