oracle数据库游标的再次深入学习

游标的概念：

在PL/SQL块中执行查询语句（select）和数据操纵（DML）时，Oracle会在内存中分配一个缓冲区，缓冲区包含了处理过程的必要信息，包括已处理的行数，指向被分析的指针和查询情况下的活动集，即查询语句返回的数据行集，该缓冲区域称为上下文区，游标就是指向该缓冲区域的句柄指针。

为了处理select语句返回多行数据的情况，可以使用游标处理多行信息，也可以使用select BULK COLLECT INTO语句处理多行数据。

游标分为两类：

1. 显式游标：用户定义、操作，用户处理返回多行数据的select查询
2. 隐式游标：系统自动进行操作，用于处理DML语句和返回单行的select 查询

DML: Data Manipulation Language 数据操纵语言

显式游标操作过程

利用显式游标处理select查询返回多行数据，需要预先定义显式游标，然后打开游标，检索游标，关闭游标等操作

定义游标

根据要查询的数据情况创建游标，在PL/SQL声明部分定义游标，语法为：

CURSOR cursor_name is select_statment

注意事项：

1. 游标必须在声明部分进行定义；
2. 游标在定义时可以应用PL/SQL变量，但是变量必须在声明游标之前声明
3. 定义游标时并没有产生数据，只是将定义信息保存到数据字典中。
4. 游标定义后可以用哪个Cursor%ROWTYPE定义记录变量;

打开游标

open cursor_name;

检索游标

fetch cursor_name into variable_list|record_variable;

关闭游标

close cursor_name;

举个栗子

根据输入产品编码查询该产品的库存信息

DECLARE 
v_sku  VARCHAR2(100);
cursor skuInfo is select  s.sku,sum(d.qty) qty from sku_table s join inv_table d on d.sku = s.sku where s.sku = v_sku  group by s.sku;
v_skuInfo skuInfo%rowtype;
begin
v_sku:=&sku;
DBMS_OUTPUT.put_line(v_sku);
open skuInfo;
loop
   fetch skuInfo into v_skuInfo ;
   exit when skuInfo%notfound;
   DBMS_OUTPUT.PUT_LINE('sku:'|| v_skuInfo.sku || ' 库存量：'|| v_skuInfo.qty);
end loop;
close skuInfo;
end;

显示游标属性

利用游标属性可以判断当前游标的状态，显式游标的属性如下

属性名称	返回数据类型	注释
%ISOPEN	布尔型	用于判断游标是否打开，如果已经打开返回true，反之返回false
%FOUND	布尔型	判断最近一次使用fetch语句时是否存缓冲区检索到数据，如果监测到返回true，反之false
%NOTFOUND	布尔型	与%found相反
%ROWTCOUNT	数值型	返回目前为止从游标缓冲区检索到的记录个数
%BULK_ROWCOUNT	数值型	用于取的forall语句执行批绑定操作时第i个元素所影响的行数

显式游标检索

1. 利用简单循环

   利用简单循环游标的基本方式为：

   declare
   cursor cursor_name is select .....
   begin
    open cursor_name;
    loop 
           fetch ... into ....;
           exit when cursor_name%notfound;
    end loop;
    close cursor_name;
   end;
   

   举个例子：

   查询成品表中的码垛方式

   declare 
   cursor v_header is select * from putaway_table;
   v_row v_header%rowtype;
   begin
       open v_header;
       loop
           fetch v_header into v_row;
           exit when v_header%notfound;
           DBMS_OUTPUT.PUT_LINE(nvl(v_row.PUTAWAYID,'未知') || ' adder is '|| v_row.editwho);
       end loop;
       close v_header;
   end;
   

exit when 子句应该是fetch...into...语句的下一条语句

2. 利用while循环检索

   利用while循环检索游标的基本方式为：

   declare
    cursor cursor_name is select ....
   begin
    open cursor_name;
    fetch...into ...;
    while cursor_name%found loop
        fetch ... into ...;
    end loop;
    close cursor_name;
   end;
   

   举个例子：

   declare 
   cursor v_header is select * from putaway_table;
   v_row v_header%rowtype;
   begin
       open v_header;
       fetch  v_header into v_row;
       while v_header%found  loop
           fetch v_header into v_row;
           DBMS_OUTPUT.PUT_LINE(nvl(v_row.PUTAWAYID,'未知') || ' adder is '|| v_row.editwho);
           fetch  v_header into v_row;
       end loop;
       close v_header;
   end;
   

   在循环体外进行一次fetch操作，做我第一次循环的条件，然后在循环体内，完成任务计算之后，再进行一次fetch 作为下次循环的条件

3. 利用for循环检索游标

   利用for检索游标时，系统会自动打开、检索、和关闭游标，用户只需要考虑如何处理游标缓冲区中检索的数据，

   declare
       cursor cursor_name is select...
       begin
           for loop_var in cursor_name loop
           .....
       end loop
   end
   

利用for循环遍历游标时，系统首先隐含定义一个数据类型为cursor_name%rowType的循环变量，loop_var,然后自动打开游标，从游标缓冲区内提取数据并放入loop_var变量中，同时进行%found属性监测，当所有数据检索遍历完毕之后，循环中断，系统自动关闭游标。

举个例子：利用for循环实现

declare 
cursor v_header is select * from putaway_table;

begin
   for v_row in v_header loop
           DBMS_OUTPUT.PUT_LINE(nvl(v_row.PUTAWAYID,'未知') || ' adder is '|| v_row.editwho);
   end loop;
end;

由于用for循环检索游标时，游标的打开、数据的检索，是否检索到数据的判断、游标的关闭都是由系统自动完成的，因此可以不在声明部分定义游标，而在for语句中直接使用子句，因此以上程序代码可以修改为：

begin
   for v_row in ( select * from putaway_table) loop
           DBMS_OUTPUT.PUT_LINE(nvl(v_row.PUTAWAYID,'未知') || ' adder is '|| v_row.editwho);
   end loop;
end;

总结：在实际程序编写过程中，个人建议用for循环遍历，为了程序的可读性提高，使用for循环的第二种模式是比较合适的

隐式游标

隐式游标用于处理返回多行数据的select查询，但所有的sql语句都有一个执行的缓冲区，隐式游标就是指向该缓冲区的指针，有系统隐含打开、处理、关闭。隐式游标又称为SQL游标。SQL游标依然由一下4个属性

属性名称	返回数据类型	注释
SQL%ISOPEN	布尔型	用于判断游标是否打开，如果已经打开返回true，反之返回false
SQL%FOUND	布尔型	判断最近一次使用fetch语句时是否存缓冲区检索到数据，如果监测到返回true，反之false
SQL%NOTFOUND	布尔型	与%found相反
SQL%ROWTCOUNT	数值型	返回目前为止从游标缓冲区检索到的记录个数

简单举个例子：将skugroup为henan的修改为shanghan，如果不存在则直接插入一条信息

begin 
    update sku_table set skugroup = 'shhanghai' where skugroup='henan';
    if SQL%NOTFOUND THEN
        INSERT INTO sku_table (SKU,SKU_NAME,SKUGROUP) 
        VALUES ('100002','大馅饼','shanghai');
    end if;
end;

游标变量

在介绍显式游标在定义时与特定的查询绑定，其结构是不变的，因此又称为静态游标，游标变量数第一个指向多行查询结果集的指针，不与特定的查询绑定，因此具有非常大的灵活性，可以在打开游标变量时进行查询，可以返回不同结果的结果集；

在PL/SQL中使用游标变量包括定义游标应用类型（ref cursor）、声明游标变量、打开游标变量、检索游标变量、关闭游标变量等几个步骤。

1. 定义游标引用类型及游标变量，其语法如下

   type re_cursor_type_name is ref cursor [return return_tyoe]
   

   在oracle 11g中系统预定义了一个游标引用类型，称为ys_refcursor,可以直接使用它定义游标变量

2. 声明游标变量，基本形式如下

   ref_cursor_type_name var_name;

   例如：

   type emp_cursor_type is ref cursor return table_name%rowtype;
   
   v_row emp_cursor_type ;
   
   my_cursor sys_refcursor;
   

3. 打开游标变量

   open cursor_name form select_statement;

   如：v_row for select * from table_name;

4. 检索游标

   检索游标的方法与静态游标类似，这里就不在赘述

5. 关闭游标

   close cursor_var_name

举个简单的例子：

declare
type type_cursor is ref cursor;
v_cursor type_cursor;
begin
    open v_cursor for select * from table_name
end;