oracle 进阶（四）游标的使用
游标
游标是从数据表中提取出来的数据，以临时表的形式存放在内存中，
在游标中有一个数据指针，在初始状态下指向的是首记录，
利用 FETCH 语句可以移动该指针，从而对游标中的数据进行各种操作，
然后将操作结果写回到数据表中。
游标分为显式游标和隐式游标两种。
显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；
而对于非查询语句，如修改、删除操作，则由 ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为 SQL，这是由 ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。INSERT, UPDATE, DELETE, SELECT INTO 语句中不必明确定义游标
显式游标是由用户声明和操作的一种游标。 以下主要针对显式游标做处理。
1、游标的声明
声明游标的语句格式如下：
CURSOR cursor_name
[（arg1 arg1_datatype
[,arg2 arg2_datatype]……)]
[RETURN return_datatype]
IS select_statement;
声明游标完成了下面两个目的：
（1）给游标命名；
（2）将一个查询与游标关联起来。
声明游标之后，还可以基于游标用 %ROWTYPE 定义记录变量，
然后该记录变量便具有了 select_statement 中选择列表的列名或别名。
语法格式为：
record_variable cursor_name%ROWTYPE
2、打开游标
打开游标的语句格式如下：
OPEN cursor_name;
打开游标将激活查询并识别活动集，可是在执行游标取回命令之前，并没有真正取回记录。
OPEN 命令还初始化了游标指针，使其指向活动集的第一条记录。游标被打开后，直到关闭之前，取回到活动集的所有数据都是静态的。换句话说，游标忽略所有在游标打开之后，对数据执行的 SQL DML 命令 (INSERT、UPDATE、DELETE 和 SELECT)，因此只有在需要时才打开它，要刷新活动集，只需关闭并重新打开游标即可。
3、从游标中取回数据
FETCH 命令以每次一条记录的方式取回活动集中的记录。通常将 FETCH 命令和某种迭代处理结合起来使用，在迭代处理中，FETCH 命令每执行一次，游标前进到活动集的下一条记录。
FETCH 命令的语句格式如下：
FETCH cursor_name INTO record_list;
执行 FETCH 命令后，活动集中的结果被取回到 PL/SQL 变量中，以便在 PL/SQL 块中使用。每取回一条记录，游标的指针就移向活动集的下一条记录。 当检索到最后一行数据时，如果再次执行 FETCH 语句，操作将失败，并使游标属性 %NOTFOUND 置为 true. 所以每次执行玩 FETCH 语句后，检查游标属性 %NOTFOUND 就可以判断 FETCH 语句是否执行成功，以便确定是否给变量赋了值。
4、关闭游标
CLOSE 语句关闭以前打开的游标，以释放它所占用的系统资源。
其语法格式为：
CLOSE cursor_name;
5、嵌套游标示例
--声明游标
declare
cursor v_dept_cursor is select * from dept;
cursor v_emp_cursor(v_deptno dept.deptno%type) is select ename,sal from emp where deptno=v_deptno;
v_dept dept%rowtype;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_tot_sal emp.sal%type;
begin
--打开游标
open v_dept_cursor;
--读取数据
loop
fetch v_dept_cursor into v_dept;
if v_dept_cursor%found then
dbms_output.put_line('部门编号'||v_dept.deptno||'部门名称：'||v_dept.dname);
--打开游标
open v_emp_cursor(v_dept.deptno);
tot_sal:=0;
loop
fetch v_emp_cursor into v_ename,v_sal;
if v_emp_cursor%found then
dbms_output.put_line('员工姓名：'||v_ename||'员工薪资：'||v_sal);
v_tot_sal:=v_tot_sal+v_sal;
else
exit;
end if;
end loop;
dbms_output.put_line('部门薪资总和:'||v_tot_sal);
close v_emp_cursor;
else
exit;
end if;
end loop;
--关闭游标
close v_dept_cursor;
end;
6、游标 for 循环
为了简化游标操作，PL/SQL 提供了游标 FOR 循环语句。
一个游标 FOR 循环可以隐含的实现 OPEN，FETCH，CLOSE 游标以及循环处理结果集的功能。
其语法格式为：
FOR index_variable IN
cursor_name[(value1[,value2]……)] LOOP
语句段；
END LOOP；
其中：
cursor_name 为已经声明的游标
value1，value2，……是应用程序传递给游标的参数
index_variable 是游标 FOR 循环隐含声明的索引变量，该索引变量为记录变量，其结构与游标查询语句返回的结果集的结构相同。
--显式游标for循环读取游标
--声明游标
declare
cursor v_dept_cursor is select * from dept;
cursor v_emp_cursor(v_deptno dept.deptno%type) is select ename,sal from emp where deptno=v_deptno;
v_dept dept%rowtype;
v_emp emp%rowtype;
v_tot_sal emp.sal%type;
begin
--读取数据
for v_dept in v_dept_cursor
loop
dbms_output.put_line('部门编号'||v_dept.deptno||'部门名称：'||v_dept.dname);
v_tot_sal:=0;
for v_emp in v_emp_cursor(v_dept.deptno)
loop
dbms_output.put_line('员工姓名:'||v_emp.ename||'--'||'员工薪资:'||r_emp.sal);
v_tot_sal:=v_tot_sal+v_emp.sal;
end loop;
dbms_output.put_line('部门薪资总和:'||v_tot_sal);
end loop;
end;
7、游标属性
无论是显式游标还是隐式游标，均有 %ISOPEN，%FOUND，%NOTFOUND，%ROWCOUNT 四种属性。
它们描述与游标操作相关的 DML 语句的执行情况。游标属性只能用在 PL/SQL 过程语句内。
属性 | 描述 |
Cursor_name%ISOPEN | 如果游标打开则为 TRUE，否则为 FALSE |
Cursor_name%FOUND | 如果最近一次提取操作 FETCH 成功则为 TRUE，否则为 FALSE |
Cursor_name%NOTFOUND | 如果最近一次提取操作 FETCH 失败则为 TRUE，否则为 FALSE |
Cursor_name%ROWCOUNT | 返回最近一次提取到的数据行的序号 |