Oracle游标的使用

游标的定义：

游标的作用：获取查询返回的记录。在通过查询将记录检索到游标中，可以一次从游标中取一行

使用游标的5个步骤：

（1）、声明变量，用于保存记录的列值

（2）、声明游标，并制定查询

（3）、打开游标

（4）、一次从游标中获取一个记录，并将列值存储在（1）步声明的变量中

（5）、关闭游标

游标就像一个存放结果集的集合，他能让你更加轻松的获取其中的结果

set serveroutput on//设置服务器可见输出，这个很重要，要不然将看不到输出的结果

declare

v_product_id products.product_id%type;

v_name products.name%type;

v_price products.price%type;

cursor v_product_cursor is

select product_id,name,price

from products

order by product_id;

begin

open v_product_cursor;

loop

fetch v_product_cursor

into v_product_id,v_name,v_price

exit when v_product_cursor%notfound;

dbms_output.put_line('v_product_id= '|| v_product_id ||',v_name= '||v_name||',v_price= '||v_price);

end loop;

close v_product_cursor;

end;

上面这个例子就是关于游标使用的完整示例

隐式游标

当执行取一条记录的操作时，就是执行一次隐式游标。

隐式游标使用sql%found,sql%notfound,sql%rowcount三个属性.

sql%found,sql%notfound是布尔值，sql%rowcount是整数值。

sql%found和sql%notfound

在执行任何dml语句前sql%found和sql%notfound的值都是null,在执行dml语句后，sql%found的属性值将是：

. true:insert

. true:delete和update，至少有一行被delete或update.

. true:select into至少返回一行

当sql%found为true时,sql%notfound为false。

sql%rowcount

在执行任何dml语句之前，sql%rowcount的值都是null,对于select into语句，如果执行成功，sql%rowcount的

为1,如果没有成功，sql%rowcount的值为0，同时产生一个异常no_data_found.

sql%isopen

sql%isopen是一个布尔值，如果游标打开，则为true, 如果游标关闭，则为false.对于隐式游标而言sql%isopen总是false,这是因为隐式游标在dml语句执行时打开，结束时就立即关闭。

显示游标

当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用select into语句。pl/sql管理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显式游标在pl/sql块的声明部分声明，在执行部分 或异常处理部分打开，取数据,关闭。下表显示了显式游标和隐式游标的差别：

表1 隐式游标和显式游标

隐式游标	显式游标
pl/sql维护，当执行查询时自动打开和关闭	在程序中显式定义、打开、关闭，游标有一个名字。
游标属性前缀是sql	游标属性的前缀是游标名
属性%isopen总是为false	%isopen根据游标的状态确定值
select语句带有into子串，只有一行数据被处理	可以处理多行数据，在程序中设置循环，取出每一行数据。

游标与for循环

利用for循环可以访问游标中的记录，当使用for循环时，可以不显示地打开和关闭游标——for循环会自动执行这些操作，这是简化语句的一个方法，请看下面示例

declare

cursor c_product_cursor is

select product_id,name,price from products order by product_id;

begin

for v_product in c_product_cursor

loop

dbms_output.put_line

('product_id:'||v_product.product_id||' name:'||v_product.name||' price:'||v_product.price);

end loop;

end;

注意上面红色的字是临时使用的变量，无需定义，这便节省了一部分代码

open-for 语句

可以对游标使用open-for语句，由于可以将游标分配给不同的查询，因此可以更加灵活地处理游标。也就是说定义了一个游标的量，能够更加灵活的更换查询等的语句，从而达到灵活改变结果

declare

type t_product_cursor is

ref cursorreturn products%rowtype;//定义一种游标的类型，并且具有返回类型

v_product_cursort_product_cursor;//应用此类型，定义一个游标

v_product roducts%rowtype;

begin

open

v_product_cursor

for

select *from products where product_id<5;

loop

fetch v_product_cursor into v_product;

exit when v_product_cursor%notfound;

dbms_output.put_line(

'product_id: '||v_product.product_id||

' name: '||v_product.name||

' price: '||v_product.price);

end loop;

closev_product_cursor;

end;

/

游标中的更新和删除

在pl/sql中依然可以使用update和delete语句更新或删除数据行。显式游标只有在需要获得多行数据的情况下使用。pl/sql提供了仅仅使用游标就可以执行删除或更新记录的方法。
update 或delete语句中的where currentof子串专门处理要执行update或delete操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用for update子串，当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级（row-level)独占式锁定，其他对象只能查询这些数据行，不能进行 update、delete或select...for update操作。
语法：

for update [of [schema.]table.column[,[schema.]table.column]..
[nowait]

在多表查询中，使用of子句来锁定特定的表,如果忽略了of子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下oracle将等待，直到数据行解锁。
在update和delete中使用where current of子串的语法如下：

where{current of cursor_name|search_condition}

例：

delcare
cursor c1 is select empno,salary
from emp
where comm is null
for update of comm;
v_comm number(10,2);
begin
for r1 in c1 loop
if r1.salary<500 then
v_comm:=r1.salary*0.25;
elseif r1.salary<1000 then
v_comm:=r1.salary*0.20;
elseif r1.salary<3000 then
v_comm:=r1.salary*0.15;
else
v_comm:=r1.salary*0.12;
end if;
update emp
set comm=v_comm
where current of c1;
end loop;
end