Introduction

Usually there are situations where we may require using a cursor a number of times inside our procedure. But if the cursor definition is vast in size it's tedious to write the same cursor multiple times for varying conditions used in a where clause. There the parameterized cursor which can be used frequently being declared only once in the code is useful.

Instead of using several cursors definitions of similar nature which take different values in the where clause we can use one parameterized cursor where the value to be used in the where clause is passed through the input parameter to the cursor.

Requirements

Oracle database 9i

Code

Listing 1

CREATE OR REPLACE
PROCEDURE parameterized_cursor_p as       
     /*Parameterized Cursor declaration*/
       Cursor multiple_dept(dept_name in varchar) is
       select a.EMPLOYEE_ID,a.FIRST_NAME ,b.DNAME      
       from emp a,dept b 
       where a.DEPARTMENT_ID=b.DEPTNO
       and b.DNAME =dept_name;
  BEGIN 
      /*First use of cursor in selecting the SALES Guys*/
       dbms_output.put_line('First use of cursor in selecting the SALES Guys');
       For Sales_Emp in multiple_dept('SALES')
       loop
       dbms_output.put_line(Sales_Emp.FIRST_NAME||
          ' with Emp number '||Sales_Emp.EMPLOYEE_ID ||
          '  is in  '||Sales_Emp.DEPARTMENT_NAME);
       end loop;
     /*Second use of cursor in selecting the RESEARCH Guys*/
       dbms_output.put_line('Second use of cursor in selecting the RESEARCH Guys');
       For Sales_Emp in multiple_dept('RESEARCH')
       loop
       dbms_output.put_line(Sales_Emp.FIRST_NAME||' with EMP number '||
           Sales_Emp.EMPLOYEE_ID ||'  is in  '||Sales_Emp.DEPARTMENT_NAME);
       end loop;
       END  parameterized_cursor_p;

Analysis

As per the code we are trying to use the same cursor twice depending upon the parameters passed to it. By using the conventional method of using cursor we would have to write the same cursor twice, first for fetching the SALE guys and next for the RESEARCH guys.

Execution of the procedure

Figure 1

Conclusion

This is a very simple example of using a parameterized cursor. The same technique can be followed for cursors of much larger size, used repeatedly in the procedure.