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.
