oracle 显示游标

夹谷冰岚

　　在oracle9i之前，为了查询处理select语句返回的多行数据，开发人员必须要使用显示游标，在oracle9i开始，开发人员即可以使用显示游标处理多行数据，也可以使用select ...bulk collect into 语句处理多行数据。
一、显示游标
PL/SQL包含隐藏游标和显示游标等两种游标类型，其中隐藏游标用于处理select into和DML语句，而显示游标用于专门处理select语句返回多行数据。
1、使用显示游标
为了处理select语法返回的多行数据，开发人员可以使用显示数据，使用显示数据包括定义游标、打开游标、提取游标、和关闭游标四个阶段：
(1)、定义游标：
   在使用显示游标之前，必须首先在定义部分定义游标，定义游标用于指定游标所对应的select语句，语法如下：
cursor cursor_name is select_statement；
   如上所示,cursor_name用于指定游标的名称，select_statement用于指定游标所对应的select语句，
(2)打开游标：
  当打开游标时，oracle会执行游标所对应的select语句，并且将select语句的结果暂时保持到结果集中，语法如下；
open cursor_name；
该游标名称必须在定义部分定义成游标。
(3)、提出数据
在游标打开之后，select语句返回的结果集临时保持到游标结果集中，为了处理结果集的数据，需要使用fetch语句提取游标数据，在oracle9i之前，使用fetch语句每次只能提取一行数据，从oracle9i开始，通过使用fetch..bulk collect into语句，每次可以提取多行数据，语法如下：
语法一：fetch cursor_name into variable1,variable2,....;
语法二：fetch cursor_name bulk collect into collect1,collect2,..[limit rows];
如上所示：variable用于指定接收游标数据的变量；collect用于指定接收游标结果的集合变量， 注意，当使用语法一时，必须要使用循环语句处理结果集的所有数据。
(4)、关闭游标
在提取并处理结果集的所有数据之后，就可以关闭游标并释放器结果集了，语法如下：
close cursor_name；
2、显示游标属性
   显示游标属性用于返回显示游标的执行信息，这些属性包括%isopen，%found，%notfond和%rowcount，当使用游标时，必须要在显示游标属性之前带有显示游标名作为前缀(游标名属性名)。
(1)、%isopen
   该属性用于确定游标是否打开，如果游标已经打开，则返回值为true，如果游标没有打开，则返回值为false，示例如下： 
   

if cl%isopen then     ----如果游标打开，则执行相应操作。
......
else                  -----如果游标未打开，则打开游标
open cl;
end if;

　　
　　(2)、%found
  该属性用于检查是否从结果集中提取到了数据，如果提取到了数据，则返回值为true，如果未提取到数据，则返回值为false，示例如下： 

loop   
fetch cl into var1，var2，；  -------提取数据到变量中
if cl%found then        -----------如果提取到数据，则进行相应操作
......；
else                         ---------如果没有提取到数据，则退出循环
exit；
end if；
end loop；
　　
(3)、%notfound
   该属性与%found属性恰好相反，如果提到数据，则返回false，如果没有提取到数据，则返回true。示例如下： 

loop
fetch cl into var1，var2；
if  cl%notfound then
。。。。。。。。；
else
exit；
end if；
end loop；
　　
(4)、%rowcount
   该属性用于返回到当前行为止，已经提取到得实际行数，示例如下： 

loop
fetch  cl into var1，var2；
if cl%rowcount>0 then
......
end if;
end loop;

　　
  3、显示游标示例
(1)、在显示游标时使用fetch...into语句
     在oracle9i之前使用fetch..into语句，每次只能处理一行数据，为了处理结果集中的多行数据，必须要使用循环语句进行处理，示例如下：

declare
cursor cursor_temp IS select name,age,address,id from cip_temps  where id=10;
v_name cip_temps.name%TYPE;
v_age cip_temps.age%TYPE;
v_address cip_temps.address%TYPE;
v_id cip_temps.id%TYPE;
begin
open cursor_temp;
loop
fetch cursor_temp into v_name,v_age,v_address,v_id;
exit when cursor_temp%notfound;
dbms_output.put_line(v_name ||':'|| v_age ||':'|| v_address||':'|| v_id);
end loop;
close cursor_temp;
end;
　　
　　
(2)、在显示游标中，使用fetch..bulk collect into 语句提取所有数据
  从oracle9i开始，通过使用fetch..bulk collect into 语句，一次就可以提取结果集的所有数据，示例如下：


declare
cursor temp_cursor is select  * from cip_temps where id=1;
type temp_table_type is table of cip_temps%rowtype;
temp_table temp_table_type;
begin
open temp_cursor;
fetch temp_cursor bulk collect into temp_table;
for i in 1..temp_table.count loop
dbms_output.put_line(temp_table(i).name||':'||temp_table(i).age||':'||temp_table(i).address||':'||temp_table(i).id);
end loop;
close temp_cursor;
end;

 


　　(3)、在显示游标中，使用fetch..bulk collect into..limit 语句提取部分数据
  当使用fetch..bulk collect into子句提取数据时，默认情况下会提取结果集中的全部数据，如果结果集的数据含有大量数据，并且使用array集合变量接收数据，那么可能需要限制每次提取的行数，示例如下：

declare
type temp_array_type is array(5) of cip_temps%rowtype;
temp_array temp_array_type;
cursor temp_cursor is select * from cip_temps;
rows int:=5;
v_count int:=0;
begin
open temp_cursor;
loop
fetch temp_cursor bulk collect into temp_array limit rows;
dbms_output.put_line('message:');
for i in 1..(temp_cursor%rowcount-v_count) loop
dbms_output.put_line(temp_array(i).name||':'||temp_array(i).age||':'||temp_array(i).address||':'||temp_array(i).id);
end loop;
v_count:=temp_cursor%rowcount;
exit when temp_cursor%notfound;
end loop;
close temp_cursor;
end;

　　
　　
(4)、使用游标属性
   为了显示游标的执行信息，需要使用显示游标的属性，下面以显示游标属性%isopen和%rowcount为例：

declare   
type name_table_type is table of  cip_temps.name%type index by binary_integer;   
name_table name_table_type;   
cursor  temp_cursor is select name from cip_temps;   
begin   
if not temp_cursor%isopen then   
open temp_cursor;   
end if;   
fetch temp_cursor bulk collect into name_table;   
dbms_output.put_line(temp_cursor%rowcount);   
close temp_cursor;   
end;