oracle10g- oracle 关于集合数据的处理 cursor 游标
pl sql 高级应用
游标的设计与开发
什么是游标
为什么用游标
怎么样使用游标
存储过程的创建参数的使用存储过程的调用
包的设计与应用
什么是包
包的创建与使用
游标是一种plsql控制结构 可以对sql语句的处理进行显示的控制便于对表的行数据进行逐条的处理
游标分为显式和隐式两种情况
游标的属性%found %isopen %notfound %rowcount
例子
--*******************
declare
cursor mycur is
select * from t1;
myrec t1%rowtype;
begin
open mycur;//打开
fetch mycur into myrec;
whie mycur%found loop
dbms_output.put_line(myrec.id||myrec.name);
fetch mycur into myrec;//记录去获取下一个记录
end loop;
close mycur;//要关闭
end;
/
--*******************
带参数的游标
declare
cursor mycur(did number) is
select name from t1 where id=did;
dname t1.name%type;
begin
open mycur(1);//打开关传入参数
loop
fetch mycur into dname;
dbms_output.put_line(dname);
exit while mycur%notfound;
end loop;
close mycur;//要关闭
end;
/
--***********
加强的for循环//不用打开和关闭
declare
Cursor mycur (did number) is
select name from t1 where id=did;
begin
dbms_output.put_line('*****结果集为*****');
for c in mycur(8) loop
dbms_output.put_line(c.name);
end loop;
end;
/
--************************
declare
cursor mycur(did number) is
select name from t1 where id=did;
dname t1.name%type;
begin
open mycur(1);//打开关传入参数
if mycur%isopen then
dbms_output.put_line('游标已经打开了');
else
open mycur(1);
end if;
loop
fetch mycur into dname;
dbms_output.put_line(dname);
exit while mycur%notfound;
end loop;
close mycur;//要关闭
end;
/
--*******
declare
tname varchar2(10);
cursor mycur is
select * from t1;
begin
open mycur;
loop
fetch mycur into dname;
exit when mycur%notfound or mycur%notfound is null;
dbms_output.put_line('共有多少行：'||mycur%rowcount);
end loop;
end;
/
--*******;
用游标来修改数据
declare
cursor mycur is
select name from t1 for update;//一定要用for update 否则不允许更新的
dname varchar2(10);
begin
open mycur;
fetch mycur into dname ;
while mycur%found loop
update name set name=name||'_name' where current of mycur;//一定要用current of mycur
fetch mycur into dname;
end loop;
end;
/
--***********
隐式游标
begin
for cur in (select name from t1) loop
dbms_output.put_line(cur.name);
end loop;
end;
/
不用打开和关闭
