游标

--显示游标
declare
cursor cur_stu is select sname,sadd from students where sid=5 ;--定义一个游标
row_stu cur_stu%rowtype;--基于游标定义一个记录
begin
open cur_stu;--打开游标
loop
fetch cur_stu into row_stu;--从游标中提取一行到记录行中
exit when cur_stu%notfound;--退出条件
if cur_stu%found then
dbms_output.put_line(row_stu.sname||row_stu.sadd);
end if;
end loop;
close cur_stu ;--关闭游标
end;


等同于

declare
s_name students.sname%type;
s_add students.sadd%type;
cursor cur_stu is select sname,sadd from students where sid=5 ;--定义一个游标
row_stu cur_stu%rowtype;--基于游标定义一个记录
begin
open cur_stu;--打开游标
loop
fetch cur_stu into s_name ,s_add ;--从游标中提取一行到记录行中
exit when cur_stu%notfound;--退出条件
if cur_stu%found then
dbms_output.put_line(s_name ||s_add);
end if;
end loop;
close cur_stu ;--关闭游标
end;



--ref 游标  (引用游标)


declare
type stu_cursor_type is ref cursor;--创建一个引用游标
stu_cursor stu_cursor_type;--定义游标变量
stu_record students%rowtype;--基于游标定义一个记录
begin
open stu_cursor for select * from students;--打开游标变量
loop
fetch stu_cursor into stu_record;----从游标中提取一行到记录行中
exit when stu_cursor%notfound;--退出条件
if stu_cursor%found then
dbms_output.put_line(stu_record.sname||stu_record.sadd);
end if;
end loop;
close stu_cursor;--关闭游标
end;


-----动态绑定(1 使用execute immediate '' using 参数列表;
              2 使用open 游标名 for '' using 参数列表;
)


1 使用execute immediate '' using 参数列表;
declare
 begin
 execute immediate
 'update students set smoney=999 where sid=:no' using 5;
 end;


 2 使用open 游标名 for '' using 参数列表;
 declare
type stu_cursor_type is ref cursor;
 stu_cursor  stu_cursor_type;
 stu_table students%rowtype;
 begin
open stu_cursor for 'select * from students where sid=:no' using 5;
loop
fetch stu_cursor into stu_table;
exit when stu_cursor%notfound;
dbms_output.put_line(stu_table.sname);
end loop;
 end;