1、oracle-游标cursor、存储过程

静态游标和REF游标，静态游标分为：显示游标/隐式游标
显示游标使用步骤：声明游标，打开，读取，关闭
declare cursor cursor_name
open cursor_name
fetch cursor_name into record_name
close cursor_name

declare 
cursor my_test is select * from SF_OPERATORLOG;
row_record SF_OPERATORLOG%rowtype; （声明类型，或者varchar（20）,oid%type）
begin
  open my_test;
  fetch my_test into row_record;
  dbms_output.put_line(row_record.oid||'%$%'||row_record.tablename);
  close my_test;
 end; 

对于fetch要执行多条语句操作时，添加loop/end loop;

declare 
cursor my_test is select name,type from user_source;
row_oid user_source.name%type;
row_tablename user_source.type%type;
begin
  open my_test;
   loop
  fetch my_test into row_oid,row_tablename;
   exit when my_test%notfound;
  dbms_output.put_line(row_oid||'%$%'||row_tablename);
  end loop;
  close my_test;
 end; 

使用fetch...into...是单条数据提取，
使用fetch...bulk collect into + for进行批量提取
declare
cursor my_test is select * from user_source;
type tab is table of user_source%rowtype;
row_record tab;
begin
open my_test;
loop
fetch my_test bulk collect into row_record ;
for i in 1..row_record.count
loop
dbms_output.put_line(row_record(i).type||'---'||row_record(i).name);
end loop;
exit when my_test%notfound;
end loop;
close my_test;
end;
或是用cursor for loop迭代
declare
cursor my_test is select name,type from user_source;
begin
for index1 in my_test
loop
dbms_output.put_line(index1.name||'---'||index1.type);
end loop;
end;
游标的属性
%isopen(true/false) if my_test%isopen then else
%found(true/false) if my_test%found then else
%notfound(true)
%rowcount(int) 当前游标的行数

带参数的游标 191/171
隐式游标SQL 192/172