ORACLE 游标 cursor的基本用法

一、基本原理

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。 

二、游标的作用

游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供了很大方便。

1、指定结果集中特定行的位置。

2、基于当前的结果集位置检索一行或连续的几行。

3、在结果集的当前位置修改行中的数据。

4、可以以编程的方式访问数据库

三、使用游标的条件

游标的效率较差，如果游标操作的数据超过1万行，那么就应该改写；如果使用了游标，就要尽量避免在游标循环中再进行表连接的操作。

四、游标的类型

游标分为显示游标和隐式游标两种。显示游标是由用户声明和操作的一种游标，通常用于查询结果集（即由SELECT语句返回的查询结果）；隐式游标是oracle为所有数据操纵语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标。在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN CURSORS参数定义。

1、显式游标

处理数据的步骤包括：声明游标、打开游标、读取游标和关闭游标 4个步骤。其中读取游标可能是个反复操作的步骤，因为游标每次只能读取一行数据，所以对于多条记录，需要反复读取，知道游标读取不到数据为止。

• 声明游标 

声明游标主要包括游标名称和为游标提供结果集的select语句。因此，在声明游标时，必须指定游标名称和游标所使用的select语句，声明游标的语法格式如下：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])] 

[return re_type]--执行游标操作后的返回值类型

IS SELECT语句; 

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。 
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。 

• 打开游标 

在游标声明完毕之后，必须打开才能使用，打开游标的语法格式如下： 

OPEN 游标名[(实际参数1[，实际参数2...])];

打开游标就是执行定义的select语句，执行完毕，查询结果装入内存，游标停在查询结果的首部，注意并不是第一行。当打开一个游标时，会完成以下几件事：

检查联编变量的取值；根据联编变量的取值，确定活动集；活动集的指针指向第一行。

• 读取游标

当打开一个游标之后，就可以读取游标中的数据了，读取游标就是逐行将结果集中的数据保存到变量中。读取游标使用fetch...into语句，其语法格式如下： 

FETCH 游标名 INTO 变量名1[，变量名2...]; 
或 
FETCH 游标名 INTO 记录变量; 

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。 
下面对这两种格式进行说明： 
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。 
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。 

定义记录变量的方法如下： 

变量名 表名|游标名%ROWTYPE； 

其中的表必须存在，游标名也必须先定义。 

• 关闭游标 

当所有的活动集都被检索后，游标就应该被关闭。程序将被告知对于游标的处理已经结束，与游标相关联的资源可以被释放了。这些资源包括用来存储活动集的存储空间，以及用来存储活动集的临时空间。

关闭游标语法格式如下：

CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，释放了占用的内存区。如果再从游标提取数据就是非法的，这样做会产生下面的oracle错误，必须重新打开才能使用。 

ORA-1001： Invalid CURSOR --非法游标
或
ORA-1002:FETCH out of sequence --超出界限

示例：

declare

cursor cur1 is
select shdh,line_no,order_code,order_line_no,rec_qty,inv_loc,itemcode,id,order_type
from  w_receive_erp
where  w_receive_erp.order_code is not null
and order_code >'P15072009'
and status=1 ;

begin 
  for temp1 in cur1
    loop    
     update w_receive_erp set status=3 where id=temp1.id ;
      commit;     
      end loop;
end;

2、隐式游标

在执行一个SQL语句时，Oracle 会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要是处理数据操纵语句的执行结果，特殊情况下，也可以处理SELECT语句的查询结果。由于隐式游标也有属性，当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称——SQL。

示例：

begin
  update emp set sal=sal*1.02 
  where job='SALESMAN';
  if sql%notfound then
    dbms_output.put_line('没有员工需要上调工资！');
    else
      dbms_output.put_line('有'||sql%rowcount||'个员工工资上调');
      end if;
  end;

五、游标的属性

无论是显示游标还是隐式游标，都具有%found、%notfound、%rowcount 和 %isopen 4个属性。通过这4个属性可以获知SQL语句的执行结果以及该游标的状态信息。游标属性只能在流程控制语句内，而不能用在SQL语句中。

%found：布尔型属性，如果SQL语句至少影响到一行数据，则该属性为true，否则为false。

%notfound：布尔型属性，与%found属性功能相反。

%rowcount ：数字型属性，返回手SQL语句影响的行数。

%isopen：布尔型属性，当游标已经打开时返回true，游标关闭时则为false。