参数化游标 (PL/SQL)

以下示例包含一个参数化游标。此游标显示 EMP 表中每个薪水低于传入参数值所指定值的职员的姓名和薪水。

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;

如果传递 2000 作为 max_wage 的值，那么将只返回那些薪水低于 2000 的职员的姓名和薪水数据：

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

参数化游标只能引用其自己的参数。参数化游标无法引用局部变量。在此示例中，cursor_id 必须用于 SELECT 语句中，原因是 in_id 没有在游标的作用域内。

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;