You can also declare a static cursor that accepts parameters. In addition, you can pass the values of those parameters when you open the cursor. In the following example, a parameterized cursor is created. The cursor displays the names and the salaries of all the specified employees from the emp table. For all the specified employees, the salary must be less than a specified value that is passed as a parameter.
DECLARE my_record emp%ROWTYPE; CURSOR c1 (max_wage NUMBER) IS SELECT * FROM emp WHERE sal < max_wage; BEGIN OPEN c1(2000); LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.ename || ', salary = ' || my_record.sal); END LOOP; CLOSE c1; END;
For example, if the value 2000 is passed as max_wage, only the names and the salaries of all the employees whose salary is less than 2,000 appear. The preceding query returns the following result:
Name = SMITH, salary = 800.00 Name = ALLEN, salary = 1600.00 Name = WARD, salary = 1250.00 Name = MARTIN, salary = 1250.00 Name = TURNER, salary = 1500.00 Name = ADAMS, salary = 1100.00 Name = JAMES, salary = 950.00 Name = MILLER, salary = 1300.00