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;
/

 

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

游标使用一:

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;
/