Oracle系列之二：游标
当在Pl/sql块中执行select语句或DML语句时，oracle会为其分配上下文区(Context area),而游标就是指向上下文区的游标。
Pl/sql的游标包括显式游标和隐式游标两种，其中隐式游标用于处理SELECT INTO 和DML语句，而显式游标专门用来处理select语句返回的多行数据。
一．显式游标
显示游标包括定义游标、打开游标、提取数据、关闭游标四个阶段。
-
定义游标
在使用显示游标之前，必须先定义游标。语法如下：
Cursor cursor_name is select_statement;
-
打开游标
当打开游标时，Oracle会执行游标所对应的SQL语句，并且将SELECT结果暂时存放到结果集中。语法如下：
Open cursor_name;
-
提取数据
在打开游标之后，SELECT语句的结果被存放到游标结果集中。为了处理结果集中的数据，需要使用FETCH语句提取游标数据。在ORACLE 9I之前，使用FETCH语句每次只能提取一条数据，但从ORACLE gi之后，通过使用BULK COLLECT INTO 语句可以提取多行数据。语法如下：
Fetch cursor_name into var1,var2…..
Fetch cursor_name bulk collect into collect1,collect2……[Limit rows];
-
关闭游标
在提取并处理了结果集中的所有数据以后，就可以关闭结果集了。语法如下：
Close cursor_name;
游标属性
游标属性用于返回显式游标的执行信息，这些属性包插%ISOPEN,%FOUND,%NOTFOUND,%ROWCOUNT.
%ISOPEN用于确定游标是否已经打开，如果已经打开，则返回True;
%FOUND用于检查是否从结果集中提取到了数据。如果提取到数据，则返回True;
%NOTFOUND与%FOUND相反
%ROWCOUNT属性用于返回到当前行为止已经提取到的实际行数。
下面是使用游标的几个示例：
set serveroutput on;
declare
--定义游标
cursor emp_cursor is
select ename, sal from emp where deptno =30;
v_name varchar2(10);
v_sal number(10);
begin
--打开游标
open emp_cursor;
loop
--用fetch...into 语句提取数据
fetch emp_cursor
into v_name, v_sal;
--使用游标属性，当没有数据时，退出。
exit when emp_cursor%notfound;
dbms_output.put_line('name:'|| v_name ||', salary:'|| v_sal);
end loop;
--关闭游标。
Close emp_cursor;
end;
--bulk collect into 语句的使用
set serveroutput on;
declare
cursor emp_cursor is
select ename, sal from emp where deptno =30;
type emp_record_type is record
(
name emp.ename%type,
sal emp.sal%type
);
type emp_table_type is table of emp_record_type;
emp_table emp_table_type;
begin
open emp_cursor;
--因为是一次性全部提取，所以不用循环。
fetch emp_cursor bulk collect
into emp_table;
for i in1..emp_table.count loop
dbms_output.put_line('name:'|| emp_table(i).name ||', salary:'|| emp_table(i).sal);
end loop;
close emp_cursor;
end;
--使用bulk collect into limit,用于限制每次提取的行数。
set serveroutput on;
declare
cursor emp_cursor is
select ename, sal from emp where deptno =30;
type emp_record_type is record
(
name emp.ename%type,
sal emp.sal%type
);
type emp_table_type is table of emp_record_type;
rows int:=3;
v_count int:=0;
begin
open emp_cursor;
loop
--limit关键字用于限制行数
fetch emp_cursor bulk collect
into emp_table limit rows;
exit when emp_cursor%notfound;
for i in1..(emp_cursor%rowcount-v_count) loop
dbms_output.put_line('name:'|| emp_table(i).name ||', salary:'|| emp_table(i).sal);
end loop;
--注意，%rowcount表示的是已经提取的行数，而不是结果集中数据的总行数。
v_count:=emp_cursor%rowcount;
end loop;
close emp_cursor;
end;
--基于游标定义记录变量
--这样可以减少代码量，而且清晰。当需要逐条处理时，是非常有用的，
--若批量处理，也可以直接使用其记录类型，而不定义变量
declare
cursor emp_cursor is select ename,sal from emp where deptno=30;
--定义记录变量
--emp_record emp_cursor%rowtype;
--直接使用其记录类型
type emp_table_type is table of emp_cursor%rowtype;
emp_table emp_table_type;
begin
open emp_cursor;
fetch emp_cursor bulk collect into emp_table;
for i in1..emp_table.count loop
dbms_output.put_line('name:'||emp_table(i).ename||', sal:'||emp_table(i).sal);
end loop;
close emp_cursor;
end;
二.参数游标
参数游标是指带有参数的游标，在定义了参数游标之后，当使用不同的参数值多次打开游标的时，可以生成不同的结果集。定义参数游标的格式如下：
Cursor cursor_name(param datatype) is select_Statement;
--使用参数游标示例
declare
cursor ename_cursor(no emp.deptno%type) is
select ename from emp where deptno = no;
ename_record ename_cursor%rowtype;
begin
open ename_cursor(30);
--在这没有循环，只是取了第一条记录的值。
fetch ename_cursor
into ename_record;
if ename_cursor%found then
dbms_output.put_line(ename_record.ename);
end if;
close ename_cursor;
end;
- 使用游标更新或删除数据
如果通过游标更新或删除数据，在定义游标时必须要带有For update子句，语法如下：
Cursor cursor_name(param param_datatype) is select_statement for update [of coloum_reference] [nowait];
其中，for update语句用于在结果集数据上加行级共享锁，以防止其它的用户在相应行上执行dml操作；当select引用多引表时，of子句确定哪些表要加锁，如果没有of子句，则会在select语句所引用到的全部表上加锁；nowait子句用于指定不等待锁——默认情况下，如果其它会话已经在被作用行上加锁，那么当前会话会一直等待对方释放锁，通过NOWAIT,可以避免等待，如果其它会话在作用行上已经加锁，那么当前会话就会显示错误提示信息，并退出PL/SQL块。
在提取了游标数据之后，为了更新或删除当前游标行数据，必须在update或delete语句中引用where current of 子句
示例如下：
declare
cursor emp_info_cursor is
select ename, sal, dname, emp.deptno
from emp, dept
where emp.deptno = dept.deptno
--of后面的列也和上面的select无关系，它是只告诉oracle对哪个表上锁。
for update of emp.comm;
emp_info_record emp_info_cursor%rowtype;
begin
open emp_info_cursor;
loop
fetch emp_info_cursor
into emp_info_record;
exit when emp_info_cursor%notfound;
if emp_info_record.deptno =30 then
--更新的列和游标中select的列是没有关系的。
update emp
set comm = nvl(comm,0)+100
where current of emp_info_cursor;
end if;
end loop;
close emp_info_cursor;
end;
-
使用游标FOR循环
游标for循环是在在PL/SQL块中使用游标最简单的方式，它简化了对游标的处理。当使用游标for循环时，Oracle会自动打开游标，提取数据并关闭游标 。
语法如下：
For record_name in cursor_name loop
Statement1;
Statement2;
End loop;
如上，cursor_name是游标名，record_name是oracle隐含定义的记录变量名。
示例如下：
declare
cursor emp_cursor is
select ename, sal from emp;
begin
for emp_record in emp_cursor loop
dbms_output.put_line('姓名：'|| emp_record.ename ||' ，薪水：'||
emp_record.sal);
end loop;
end;
