CodeSnip: How to make use of parameterized cursor in Oracle
page 1 of 1
Published: 25 Apr 2008
Abstract
Often it is convenient to pass a given set of values as input parameter to a cursor. It is very useful where nested cursors are used. Parameterizing the cursor makes it more usable and avoids the limitation of hard coding values in where the clause. In this code snippet, Deepankar examines this technique with the help of the relevant SQL code. He also provides a brief analysis of the code and also a screenshot of the final output.
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 27156/ 82

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

Picture 3

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.



User Comments

Title: Good   
Name: Krishna Gattu
Date: 1/24/2013 1:16:08 PM
Comment:
This article is easy to understanding parameterized cursor
Title: Good Explanation   
Name: Deeps
Date: 1/6/2013 8:01:05 AM
Comment:
This article has very well explained the parameterized cursor.
Title: printing issue   
Name: rahul
Date: 7/27/2012 6:01:38 AM
Comment:
how can you select "Sales_Emp.DEPARTMENT_NAME" while printing the same as cursor holds only three columns.
i guess this is by mistake
Title: Query   
Name: Debo Barshan Modak
Date: 9/28/2010 12:57:30 AM
Comment:
Yes, parametrized cursor is indeed useful, there is no doubt in it. But I have a question, suppose if I have cursor like,

CURSOR cur_emp IS
SELECT * FROM emp
WHERE dept_no = v_dept_no;

Now, before giving a call to the above cursor I am initializing the value of the variable v_dept_no with the required value. In this way also, we can make use of the same cursor with different values in the cursor where clause. Now, my question is that, does the performance of the code increases if I use a parametrized cursor in similar situation in order to achieve the same result?
Title: Parameterized Cursor   
Name: Swethasree
Date: 6/18/2010 4:58:47 AM
Comment:
Clear Explanation.Easy to Understand.
Title: parameterized cursors   
Name: ramachandra
Date: 4/26/2010 5:26:28 AM
Comment:
this example very good some body don't no about this.
this is very is example and anlysing easily.
Title: Paramererized cursor   
Name: Kiran
Date: 4/8/2010 6:11:38 AM
Comment:
good example for parameterized cursor
Title: excellent   
Name: Jitendra Kumar
Date: 3/28/2010 12:55:39 PM
Comment:
Very good Example
Title: Parameterised Cursor is easy to learn and use   
Name: Sandhya Magar
Date: 7/22/2009 8:24:21 AM
Comment:
Nice explanation.
Title: we can use parameters in cursors   
Name: Kondeeti.Srinivasa Rao
Date: 5/30/2009 10:21:25 AM
Comment:
Good Example for parameters in cursors

Thanks for your time.
Title: parameterized cursor in Oracle   
Name: AVISHEK PAUL
Date: 5/18/2009 3:13:57 AM
Comment:
Properly explained...
Thanks!
Title: PARAMETERIZED CURSOR   
Name: NIKHILA NANDA DHAL
Date: 4/15/2009 6:11:23 AM
Comment:
Really very simple to understand.Thanks
Title: Parameterized Cursor   
Name: pratap557
Date: 2/18/2009 4:41:15 AM
Comment:
Thanks alot ..
Title: That was very nicely explained   
Name: Raghav
Date: 9/23/2008 5:55:44 AM
Comment:
Nice work.
Title: Explained in simple way to understand every one   
Name: Durga Prasad
Date: 8/27/2008 2:48:47 AM
Comment:
Hi, Thanks for providing information,

Above article is Easy to understand.

Godd work.
Title: nice   
Name: anupriya
Date: 7/7/2008 5:53:17 AM
Comment:
explained neatly thanks
Title: Simply great.   
Name: Manoj Kumar Sharma
Date: 5/20/2008 4:42:20 AM
Comment:
Nicely explained the Parametrized Cursor.really very simple to understand.Thanks.
Title: Easy to understand   
Name: Pravat Kumar Jena
Date: 5/14/2008 6:58:27 AM
Comment:
Thanx......
Title: Good one..   
Name: Anshul
Date: 5/11/2008 8:08:04 PM
Comment:
Nice way to explain Parameterized cursor.

Thanks..
Title: Feed back   
Name: Anj
Date: 5/7/2008 6:56:53 AM
Comment:
declaration part is not given .please provide that also.It will increase readiblity






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 8/2/2014 4:35:33 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search