A user can also declare a static cursor that accepts parameters and can pass values for those parameters when that cursor is opened. In the following example, a parameterized cursor is created. The cursor will display the name and salary of all employees from the emp table that have a salary less than a specified value which 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 we pass the value 2000 as max_wage, we will only be shown the name and salary of all employees that have a salary less than 2000. The following data shows the result of the above query:
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