oracle游标的定义使用

2008-02-23 15:12:57| 分类： oracle|字号

游标中定义的参数只要定义类型，不用定义长度，精度等；

游标使用一:

declare
cursor mycur is --声明游标
select * from a;
myrec a%rowtype; --声明与表字段相同的记录
begin
open mycur;--打开游标
fetch mycur into myrec;
while mycur%found loop
dbms_output.put_line(myrec.col1||':'||myrec.col2);
fetch mycur into myrec;
end loop;
close mycur;
end;
/

游标使用二: 使用参数

declare
cursor mycur(id varchar) is
select col2 from a where col1=id;
myrec a.col2%type;
begin
open mycur('1');
loop
fetch mycur into myrec;
exit when mycur%notfound;
dbms_output.put_line(myrec);
end loop;
close mycur;
end;
/

游标使用二: 使用参数二for循环，在for循环中不用声明游标，也不用打开关闭

declare
cursor mycur(id varchar) is
select col2 from a where col1=id;
begin
for myrec in mycur('1') loop
dbms_output.put_line(myrec.col2);
end loop;
end;
/

判读游标是否打开，如果没有打开

declare
myrec a.col2%type;
cursor mycur(id varchar) is
select col2 from a where col1=id;
begin
if mycur%isopen then//判读打开
dbms_output.put_line('游标打开了');
else
open mycur('1');
end if;
fetch mycur into myrec;
close mycur;
dbms_output.put_line(myrec);
end;
/
利用游标获得数据行数
declare
t_name varchar(10);
cursor mycur is
select col2 from a;
begin
open mycur;
loop
fetch mycur into t_name;//数据填充到游标
exit when mycur%NOTFOUND or mycur%NOTFOUND IS NULL;
END LOOP;
DBMS_OUTPUT.PUT_LINE(MYCUR%ROWCOUNT);
CLOSE MYCUR;
END;
/

利用游标update数据

declare
cursor cur is
select col1 from a for UPDATE;//for update 是必须的
temp varchar(10);
begin
open cur;
fetch cur into temp;
while cur%FOUND loop
update a set col1=col1||'_why' where current of cur;//current of cur也是必须的
fetch cur into temp;
end loop;
close cur;
end;
/