游标分为隐式游标和显式游标。
隐式游标:
有个默认的 SQL%ROWCOUNT 属性,在DML语言操作的时候,会返回影响的行数。
例:
/*隐式游标*/
declare
v_num shen%ROWTYPE; --保存一行数据
begin
select * into v_num from shen where id='2';
dbms_output.put_line(v_num.name);
dbms_output.put_line(SQL%ROWCOUNT);
end;
显示游标:
declare
v_num shen%ROWTYPE ; --保存shen一行数据
cursor cur_shen is select * from shen;
begin
if cur_shen%isopen then
null;
else
open cur_shen;
end if;
/*while 写法
fetch cur_shen into v_num;
while cur_shen%found
loop
dbms_output.put_line(cur_shen%rowcount);
dbms_output.put_line(v_num.name);
fetch cur_shen into v_num; --把游标下移一行
end loop;
*/
/*loop 写法*/
loop
fetch cur_shen into v_num;
exit when cur_shen%notfound;
dbms_output.put_line(cur_shen%rowcount);
dbms_output.put_line(v_num.name);
end loop;
close cur_shen;
end;
/******for 写法 *******/
declare
cursor cur_shen is select * from shen;
v_num shen%ROWTYPE; --存储一行记录
begin
for v_num in cur_shen loop
exit when cur_shen%NOTFOUND;
dbms_output.put_line(cur_shen%rowcount);
dbms_output.put_line(v_num.name);
end loop;
end;
declare
cursor cur_shen is select id from shen;
v_num number; --存储一行记录
begin
if cur_shen%ISOPEN then
null;
else
open cur_shen;
end if;
loop
fetch cur_shen into v_num;
exit when cur_shen%NOTFOUND;
--dbms_output.put_line(cur_shen%rowcount);
dbms_output.put_line(v_num);
end loop;
end;
/******for 写法结束**********/