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;