what is the use of parameterize cursor?


pls help this question what is the use of parameterized cursor?

The point of parameterized cursors is to offer more flexibility to the designer/developer in the same way that parameterized functions and procedures do. Using parameters enables you to change the variable that you pass to the cursor as well (obviously) as the value of the variable.

Let's look at an example using the employees table in the hr schema that comes with the sample Oracle database.

CURSOR emp_cur (dept departments.department_id%TYPE) IS
  SELECT first_name||' '||last_name "Name" FROM employees
  WHERE department_id=dept
  AND hire_date >= to_date('01-JAN-2013','dd-mon-yyyy');

first_dept departments.department_id%TYPE;
second_dept departments.department_id%TYPE;

emp_name VARCHAR2(60);


first_dept := 60;
second_dept := 90;

OPEN emp_cur(first_dept);
FETCH emp_cur INTO emp_name;
CLOSE emp_cur;

DBMS_OUTPUT.PUT_LINE('First dept. Employee name = '||emp_name);

OPEN emp_cur(second_dept);
FETCH emp_cur INTO emp_name;
CLOSE emp_cur;

DBMS_OUTPUT.PUT_LINE('Second dept. Employee name = '||emp_name);


The above example produces the following output:

First dept. Employee name =
Second dept. Employee name = Steven King

In the example we declared a cursor which takes one parameter the department_id and we used the cursor twice passing a different variable each time. In this particular case we could have used a bind variable but that does not detract from the flexibility provided by the use of parameters.

For more help with PL/SQL see our PL/SQL tutorials

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.