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

ujtrnmtquj

静态游标和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