欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle游标的使用

程序员文章站 2024-01-28 14:34:04
游标的定义: 游标的作用:获取查询返回的记录。在通过查询将记录检索到游标中,可以一次从游标中取一行 使用游标的5个步骤: (1)、声明变量,用于保存记录的列值 (2)、声明...

游标的定义:

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

使用游标的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