Oracle入门（十四.13）之带参数的游标

一、带参数的游标

    参数是一个变量，其名称用于游标声明中。 当游标打开时，参数值被传递给Oracle服务器，Oracle服务器使用它来决定要将哪些行检索到光标的活动集中。

    这意味着您可以在块中多次打开和关闭显式光标，或者在同一个块的不同执行中打开和关闭显式光标，每次都返回一个不同的活动集。

    考虑将任何region_id传递给游标的示例，并返回该区域中国家/地区的名称。

例子

DECLARE
 CURSOR c_country (p_region_id NUMBER) IS
 SELECT country_id, country_name
 FROM wf_countries
 WHERE region_id = p_region_id;
 v_country_record c_country%ROWTYPE;
BEGIN
 OPEN c_country (5);  --更改为需要的区域。
 LOOP
 FETCH c_country INTO v_country_record;
 EXIT WHEN c_country%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(v_country_record.country_id
 || ' ' || v_country_record.country_name);
 END LOOP;
 CLOSE c_country;
END;

二、参数游标用法

（1）使用参数语法定义游标

在游标声明中命名的每个参数在OPEN语句中必须具有对应的值。 参数数据类型与标量变量的数据类型相同，但不给它们大小。 参数名称在游标SELECT语句的WHERE子句中使用。

CURSOR cursor_name
 [(parameter_name datatype, ...)]
IS
 select_statement;

在语法中：
cursor_name是声明的游标的PL / SQL标识符
•parameter_name是参数的名称
•数据类型是参数的标量数据类型

•select_statement是一个没有INTO子句的SELECT语句

用参数打开游标

以下是用参数打开游标的语法：

OPEN cursor_name(parameter_value,.....) ;

（2）带参数的游标

游标打开时将参数值传递给游标。 因此，您可以多次打开一个显式光标并每次获取一个不同的活动集。 在以下示例中，游标会多次打开。

具有参数的光标的另一个例子

DECLARE
v_deptid employees.department_id%TYPE;
CURSOR empcur (p_deptid NUMBER) IS
 SELECT employee_id, salary
 FROM employees
 WHERE department_id = p_deptid;
v_emp_rec empcur%ROWTYPE;
BEGIN
SELECT MAX(department_id) INTO v_deptid
 FROM employees;
OPEN empcur(v_deptid);
LOOP
 FETCH empcur INTO v_emp_rec;
 EXIT WHEN empcur%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(v_emp_rec.employee_id
 || ' ' || v_emp_rec.salary);
END LOOP;
CLOSE empcur;
END;

（3）光标FOR循环参数

如果需要，我们可以使用游标FOR循环：

DECLARE
 CURSOR emp_cursor (p_deptno NUMBER) IS
 SELECT employee_id, last_name
 FROM employees
 WHERE department_id = p_deptno;
BEGIN
 FOR v_emp_record IN emp_cursor(10) LOOP
 ….
 END LOOP;
END;

（4）具有多个参数的游标

在下面的例子中，声明了一个游标并用两个参数调用：

DECLARE
 CURSOR countrycursor2 (p_region_id NUMBER,
 p_population NUMBER) IS
 SELECT country_id, country_name, population
 FROM wf_countries
 WHERE region_id = p_region_id
 OR population > p_population;
BEGIN
 FOR v_country_record IN countrycursor2(145,10000000) LOOP
DBMS_OUTPUT.PUT_LINE(v_country_record.country_id ||' '
 || v_country_record. country_name||' '||
v_country_record.population);
END LOOP;
END;

另一个例子
该光标可以获取所有收入超过10000美元的IT程序员。

DECLARE
 CURSOR emp_cursor3 (p_job VARCHAR2,
 p_salary NUMBER) IS
 SELECT employee_id, last_name
 FROM employees
 WHERE job_id = p_job
 AND salary > p_salary;
BEGIN
 FOR v_emp_record IN emp_cursor3('IT_PROG', 10000) LOOP
   DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id ||' ' ||v_emp_record.last_name);
 END LOOP;
END;