ORACLE PL/SQL编程之把游标说透
与游标一样，游标变量也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。
4.2.1 声明游标变量
游标变量为一个指针，它属于参照类型，所以在声明游标变量类型之前必须先定义游标变量类型。在PL/SQL中，可以在块、子程序和包的声明区域内定义游标变量类型。
语法格式为：
TYPE ref_type_name IS REF CURSOR
[ RETURN return_type];
return_type 为游标变量的返回值类型，它必须为记录变量。
在定义游标变量类型时，可以采用强类型定义和弱类型定义两种。强类型定义必须指定游标变量的返回值类型，而弱类型定义则不说明返回值类型。
声明一个游标变量的两个步骤：
步骤一：定义一个REF CURSOU数据类型，如：
TYPE ref_cursor_type IS REF CURSOR;
步骤二：声明一个该数据类型的游标变量，如：
cv_ref REF_CURSOR_TYPE;
例：创建两个强类型定义游标变量和一个弱类型游标变量：
DECLARE
TYPE deptrecord IS RECORD(
Deptno departments.department_id%TYPE,
Dname departments.department_name%TYPE,
Loc departments.location_id%TYPE
);
TYPE deptcurtype IS REF CURSOR RETURN departments%ROWTYPE;
TYPE deptcurtyp1 IS REF CURSOR RETURN deptrecord;
TYPE curtype IS REF CURSOR;
Dept_c1 deptcurtype;
Dept_c2 deptcurtyp1;
Cv curtype;
4.2.2 游标变量操作
与游标一样，游标变量操作也包括打开、提取和关闭三个步骤。
1． 打开游标变量
打开游标变量时使用的是OPEN…FOR 语句。格式为：
OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
其中：cursor_variable_name为游标变量，host_cursor_variable_name为PL/SQL主机环境（如OCI: ORACLE Call Interface，Pro*c 程序等）中声明的游标变量。
OPEN…FOR 语句可以在关闭当前的游标变量之前重新打开游标变量，而不会导致CURSOR_ALREAD_OPEN异常错误。新打开游标变量时，前一个查询的内存处理区将被释放。
2． 提取游标变量数据
使用FETCH语句提取游标变量结果集合中的数据。格式为：
FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称；variable和record_variable分别为普通变量和记录变量名称。
3． 关闭游标变量
CLOSE语句关闭游标变量，格式为：
CLOSE {cursor_variable_name | :host_cursor_variable_name}
其中：cursor_variable_name和host_cursor_variable_name分别为游标变量和宿主游标变量名称，如果应用程序试图关闭一个未打开的游标变量，则将导致INVALID_CURSOR异常错误。
例15：强类型参照游标变量类型
DECLARE
TYPE emp_job_rec IS RECORD(
Employee_id employees.employee_id%TYPE,
Employee_name employees.first_name%TYPE,
Job_title employees.job_id%TYPE
);
TYPE emp_job_refcur_type IS REF CURSOR RETURN emp_job_rec;
Emp_refcur emp_job_refcur_type ;
Emp_job emp_job_rec;
BEGIN
OPEN emp_refcur FOR
SELECT employees.employee_id, employees.first_name||employees.last_name, employees.job_id
FROM employees
ORDER BY employees.department_id;
FETCH emp_refcur INTO emp_job;
WHILE emp_refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(emp_job.employee_id||': '||emp_job.employee_name||' is a '||emp_job.job_title);
FETCH emp_refcur INTO emp_job;
END LOOP;
END;
例16：弱类型参照游标变量类型
PROMPT
PROMPT 'What table would you like to see?'
ACCEPT tab PROMPT '(D)epartment, or (E)mployees:'
DECLARE
Type refcur_t IS REF CURSOR;
Refcur refcur_t;
TYPE sample_rec_type IS RECORD (
Id number,
Description VARCHAR2 (30)
);
sample sample_rec_type;
selection varchar2(1) := UPPER (SUBSTR ('&tab', 1, 1));
BEGIN
IF selection='D' THEN
OPEN refcur FOR
SELECT departments.department_id, departments.department_name FROM departments;
DBMS_OUTPUT.PUT_LINE('Department data');
ELSIF selection='E' THEN
OPEN refcur FOR
SELECT employees.employee_id, employees.first_name||' is a '||employees.job_id FROM employees;
DBMS_OUTPUT.PUT_LINE('Employee data');
ELSE
DBMS_OUTPUT.PUT_LINE('Please enter ''D'' or ''E''');
RETURN;
END IF;
DBMS_OUTPUT.PUT_LINE('----------------------');
FETCH refcur INTO sample;
WHILE refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(sample.id||': '||sample.description);
FETCH refcur INTO sample;
END LOOP;
CLOSE refcur;
END;
例17：使用游标变量（没有RETURN子句）
DECLARE
--定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR;
--声明一个游标变量
c1 EMP_CURSOR_TYPE;
--声明两个记录变量
v_emp_record employees%ROWTYPE;
v_reg_record regions%ROWTYPE;
BEGIN
OPEN c1 FOR SELECT * FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp_record.first_name||'的雇佣日期是'
||v_emp_record.hire_date);
END LOOP;
--将同一个游标变量对应到另一个SELECT语句
OPEN c1 FOR SELECT * FROM regions WHERE region_id IN（1，2）;
LOOP
FETCH c1 INTO v_reg_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_reg_record.region_id||'表示'
||v_reg_record.region_name);
END LOOP;
CLOSE c1;
END;
例18：使用游标变量（有RETURN子句）
DECLARE
--定义一个与employees表中的这几个列相同的记录数据类型
TYPE emp_record_type IS RECORD(
f_name employees.first_name%TYPE,
h_date employees.hire_date%TYPE,
j_id employees.job_id%TYPE);
--声明一个该记录数据类型的记录变量
v_emp_record EMP_RECORD_TYPE;
--定义一个游标数据类型
TYPE emp_cursor_type IS REF CURSOR
RETURN EMP_RECORD_TYPE;
--声明一个游标变量
c1 EMP_CURSOR_TYPE;
BEGIN
OPEN c1 FOR SELECT first_name, hire_date, job_id
FROM employees WHERE department_id = 20;
LOOP
FETCH c1 INTO v_emp_record;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('雇员名称：'||v_emp_record.f_name
||' 雇佣日期：'||v_emp_record.h_date
||' 岗位：'||v_emp_record.j_id);
END LOOP;
CLOSE c1;
END;
