Open Parameterized Cursor in Different Ways

To Understand about Cursor and it's Types, you can check the below post first- 
http://tipsfororacle.blogspot.in/2016/09/cursors-and-cursor-types.html

Now Let’s try opening Parameterized Cursor in below different ways-

1) Parameterized Cursor with Fetch

2) Parameterized Cursor with Cursor Loop
3) Parameterized Cursor with Cursor for Loop
4) Parameterized Cursor with bulk collect

SQL> SELECT D.DEPTNO, DEPARTMENT, SUM(SALARY) TotalSalary
  2  FROM EMP_TEST E, DEPT_TEST D
  3  Where
  4  E.DEPTNO(+)=D.DEPTNO
  5  GROUP BY D.DEPTNO,D.DEPARTMENT;

    DEPTNO DEPARTMENT           TOTALSALARY
---------- -------------------- -----------
        10 SDC                  157000
        30 Security             792000
        20 Insurance            127000
        40 Retail               301000
        50 BFSI                 107000
        60 CISCO                301000
   
6 rows selected.

Declare

       Cursor C1 (P_Deptno Number) Is

       Select Department, NVL(Sum(Salary),0) Total_Salary

       From EMP_TEST E, DEPT_TEST D

       Where D.DeptNo = E.DeptNo(+)

       and D.Deptno = P_Deptno

       Group By Department;

 

      l_Dname DEPT_TEST.Department%type;

      l_Sal Number;

      Type T_tab Is Table of C1%RowType Index By Binary_Integer;

      l_Tab T_tab;

   Begin

      /*Using Parameterized Cursor with Fetch*/

      Open C1(10);

      FETCH C1 INTO l_Dname, l_Sal;

      Close C1;

      Dbms_Output.Put_Line('Department: ' || l_Dname || ' Total Salary: ' || l_Sal);

      /*Using Parameterized Cursor With Cursor Loop*/

     

      Open C1(20);

      Loop

              Fetch C1 Into l_Dname, l_Sal;

              Exit When C1%NotFound;

      Dbms_Output.Put_Line('Department: ' || l_Dname || ' Total Salary: ' || l_Sal);

      End Loop;

      Close C1;

      /*Using Parameterized Cursor With Cursor For Loop*/

      For i in C1(30)

      Loop

              Dbms_Output.Put_Line('Department: ' || i.Department || ' Total Salary: ' || i.Total_Salary);

      End Loop;

      /*Using Parameterized Cursor With Bulk Collect*/

      Open C1(40);

      Fetch C1 Bulk Collect Into l_Tab;

      Dbms_Output.Put_Line('Department: ' || l_Tab(1).Department || ' Total Salary: ' || l_tab(1).Total_Salary);

      Close C1;

   End;

Output-

Department: SFDC Total Salary: 157000

Department: Insurance Total Salary: 127000

Department: Security Total Salary: 792000

Department: Retail Total Salary: 301000

PL/SQL procedure successfully completed.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit.

So, leave your thoughts before you leave the page.