Explain parameterized cursors in PL/SQL

How it works

A cursor can declare its own parameters, behaving like a subprogram's IN parameters (optional defaults included) — that makes one cursor definition reusable with different filter values each time it's opened, instead of hardcoding a WHERE condition or depending on a variable already in scope.

DECLARE
  CURSOR c_by_dept(p_dept_id NUMBER, p_min_salary NUMBER DEFAULT 0) IS
    SELECT last_name, salary FROM employees
    WHERE department_id = p_dept_id AND salary >= p_min_salary;
BEGIN
  FOR r IN c_by_dept(90, 8000) LOOP
    DBMS_OUTPUT.PUT_LINE(r.last_name || ': ' || r.salary);
  END LOOP;
END;
/

Related Questions