Parameterized cursors (PL/SQL)

The following example includes a parameterized cursor. The cursor displays the name and salary of each employee in the EMP table whose salary is less than that specified by a passed-in parameter value.

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;

If 2000 is passed in as the value of max_wage, only the name and salary data for those employees whose salary is less than 2000 is returned:

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

Parameterized cursors can only reference its own parameters. Parameterized cursors cannot reference local variables. In this example, cursor_id must be used in the select statement because in_id is not within the scope of the cursor.

CREATE OR REPLACE PROCEDURE myproc (in_id IN NUMBER) IS
  CURSOR c(cursor_id in NUMBER) IS
  SELECT id,emp_name FROM employee WHERE id = cursor_id; 
  empName VARCHAR2(100);
  
  BEGIN
    FOR r IN c(in_id) LOOP
      empName := r.emp_name;
      DBMS_OUTPUT.PUT_LINE(empName);
    END LOOP;
  END;