Oracle数据库游标的类型

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。

Oracle数据库的Cursor类型包含三种: 静态游标：分为显式（explicit）游标和隐式（implicit）游标；REF游标：是一种引用类型，类似于指针。

--测试数据

create table student(sno number primary key,sname varchar2(10));

declare i number:=1;
begin
while i<=50  
loop 
      insert into student(sno,sname) values (i,'name'||to_char(i));
      i:=i+1;
end loop;
end;

隐式游标属性:

SQL%ROWCOUNT 整型代表DML语句成功执行的数据行数。

SQL%FOUND 布尔型值为TRUE代表插入、删除、更新或单行查询操作成功。

SQL%NOTFOUND 布尔型与SQL%FOUND属性返回值相反。

SQL%ISOPEN 布尔型DML执行过程中为真，结束后为假。

declare
begin
  update student set sname ='name'||to_char(sno*10) where sname='name80';
  if sql%found then
    dbms_output.put_line('name is updated');
  else
    dbms_output.put_line('没有记录');
  end if;
end;


declare    
begin      
  for names in (select * from student) loop    
    dbms_output.put_line(names.sname);    
  end loop;    
  exception when others then    
    dbms_output.put_line(sqlerrm);    
end;

显式游标属性：

%ROWCOUNT 获得FETCH语句返回的数据行数。

%FOUND 最近的FETCH语句返回一行数据则为真，否则为假。

%NOTFOUND 布尔型与%FOUND属性返回值相反。

%ISOPEN 布尔型游标已经打开时值为真，否则为假。

对于显式游标的运用分为四个步骤：

a 定义游标 --- Cursor [Cursor Name] IS;

b 打开游标 --- Open [Cursor Name];

c 操作数据 --- Fetch [Cursor name];

d 关闭游标 --- Close [Cursor Name];

典型显式游标：

declare cursor cur_rs is select * from student;
sinfo student%rowtype;
begin
  open cur_rs;
  loop
    fetch cur_rs into sinfo;
    exit when cur_rs%%notfound;
    dbms_output.put_line(sinfo.sname); 
  end loop;
  exception when others then    
    dbms_output.put_line(sqlerrm);
end;

带参数open的显式cursor：


declare cursor cur_rs(in_name varchar2) is select * from student where sname=in_name;
begin
  for sinfo in cur_rs('sname') loop
    dbms_output.put_line(sinfo.sname);
  end loop;
  exception when others then    
    dbms_output.put_line(sqlerrm);
end;

使用current of语句执行update或delete操作：


declare    
cursor cur_rs is select * from student for update;    
begin
  for sinfo in cur_rs loop 
      update student set sname=sname||'xx' where current of cur_rs;   
  end loop;   
  commit;    
  exception when others then    
  dbms_output.put_line(sqlerrm);    
end;

REF游标，用于处理运行时才能确定的动态sql查询结果，利用REF CURSOR，可以在程序间传递结果集（一个程序里打开游标变量，在另外的程序里处理数据）。

也可以利用REF CURSOR实现BULK SQL，提高SQL性能。

REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR。

Strong REF CURSOR: 指定retrun type，CURSOR变量的类型必须和return type一致。

Weak REF CURSOR: 不指定return type，能和任何类型的CURSOR变量匹配。

运行时根据动态sql查询结果遍历：

create or replace package pkg_test01 as    
type student_refcursor_type is ref cursor return student%rowtype;    
procedure student_rs_loop(cur_rs IN student_refcursor_type);    
end pkg_test01; 

create or replace package body pkg_test01 as    
procedure student_rs_loop(cur_rs IN student_refcursor_type) is    
std student%rowtype;    
begin
  loop    
    fetch cur_rs into std;    
    exit when cur_rs%NOTFOUND;    
    dbms_output.put_line(std.sname);    
  end loop;    
end student_rs_loop;    
end pkg_test01;


declare stdRefCur pkg_test01.student_refcursor_type;    
begin
  for i in 10..50 loop    
    dbms_output.put_line('Student NO=' || i);    
    open stdRefCur for select * from student where sno=i;    
    pkg_test01.student_rs_loop(stdRefCur); 
end loop;
exception when others then dbms_output.put_line(sqlerrm);    
close stdRefCur;     
end;

使用FORALL和BULK COLLECT子句。利用BULK SQL可以减少PLSQL Engine和SQL Engine之间的通信开销，提高性能。

1.加速INSERT, UPDATE, DELETE语句的执行，也就是用FORALL语句来替代循环语句。

2.加速SELECT，用BULK COLLECT INTO 来替代INTO。

create table student_tmp as select sno,sname from student where 0=1;

--删除主键约束
alter table student drop constraint SYS_C0040802;  

--执行两遍插入
insert into student select * from student where sno=50;


declare cursor cur_std(stdid student.sno%type) is select sno,sname from student where sno=stdid;    
type student_table_type is table of cur_std%rowtype index by pls_integer;    
student_table student_table_type;    

begin     
open cur_std(50);    
fetch cur_std bulk collect into student_table;    
close cur_std;    

for i in 1..student_table.count loop    
 dbms_output.put_line(student_table(i).sno || ' ' || student_table(i).sname);    
end loop;    

forall i in student_table.first..student_table.last    
 insert into student_tmp values(student_table(i).sno, student_table(i).sname);    
commit;    

end;    

--清理实验环境
drop table student purge;
drop package pkg_test01;