Oracle系列之二：游标

当在Pl/sql块中执行select语句或DML语句时，oracle会为其分配上下文区(Context area),而游标就是指向上下文区的游标。

Pl/sql的游标包括显式游标和隐式游标两种，其中隐式游标用于处理SELECT INTO 和DML语句，而显式游标专门用来处理select语句返回的多行数据。

一．显式游标

显示游标包括定义游标、打开游标、提取数据、关闭游标四个阶段。

1. 定义游标

   在使用显示游标之前，必须先定义游标。语法如下：

   Cursor cursor_name is select_statement;

2. 打开游标

   当打开游标时，Oracle会执行游标所对应的SQL语句，并且将SELECT结果暂时存放到结果集中。语法如下：

   Open cursor_name;

3. 提取数据

   在打开游标之后，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];

4. 关闭游标

   在提取并处理了结果集中的所有数据以后，就可以关闭结果集了。语法如下：

   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;

 

1. 使用游标更新或删除数据

如果通过游标更新或删除数据，在定义游标时必须要带有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;

 

1. 使用游标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;