Oracle数据库游标的使用

时间:2013-11-03
简介:数据库|下面主要说明Oracle数据库游标的使用,游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。  Or

下面主要说明Oracle数据库游标的使用,游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。

  Oracle数据库的Cursor类型包含三种: 静态游标:分为显式(explicit)游标和隐式(implicit)游标;REF游标:是一种引用类型,类似于指针。

  --测试数据

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

  declare i number:=1;

  beginwhile 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执行过程中为真,结束后为假。

  declarebegin  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 1050 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 1student_table.count loop

  dbms_output.put_line(student_table(i)。sno || ' '

  || student_table(i)。sname);

  end loop;

  forall i in student_table.firststudent_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;

TAGS:Oracle数据库游标的使用

上一编:Oracle 怎么查询表中重复记录?

下一编:怎么修改oracle用户表空间大小信息

New Hot