[oracle]引用游标

一.注意事项

1.创建游标变量需要两个步骤：

(1).声明ref游标类型
(2).声明ref游标类型的变量

2.cursor与ref cursor区别：

(1).cursor定义好后无法修改，ref cursor截图根据逻辑来动态打开。
(2).cursor不能返回给客户端，ref cursor可以。
(3).cursor可以使全局的global，ref cursor则必须定义在过程或函数中。
(4).cursor不能再子程序间传递，ref cursor可以。
(5).cursor中定义的静态sql比ref cursor效率高，所以ref cursor通常用于向客户端返回结果集。

以下通过例子来简单上手引用游标

准备数据

创建两张表，一张是学生表，一张是年级表。

create sequence studentTest_id_seq
increment by 1
start with 1
minvalue 1
maxvalue 999999999;
 
create table studentTest(
       id number(9) not null primary key,--编号
       name varchar(20) not null,--姓名
       classNo number(3) not null,--班级编号
       sex varchar(2) not null,--性别
       score number(5,2) not null,--分数
       birthday date,--出生日期
       address varchar(100)--地址
);
 
insert into studentTest values(studentTest_id_seq.Nextval,'黄小花',101,'女',95,to_date('2011-01-01','yyyy-MM-dd'),'北京高新区');
insert into studentTest values(studentTest_id_seq.Nextval,'李大傻',101,'男',33,to_date('2010-10-01','yyyy-MM-dd'),null);
insert into studentTest values(studentTest_id_seq.Nextval,'张翠花',102,'女',79,null,'桂林欧家村');
insert into studentTest values(studentTest_id_seq.Nextval,'王二狗',102,'男',82,to_date('2009-02-25','yyyy-MM-dd'),'桂林秀峰区');
insert into studentTest values(studentTest_id_seq.Nextval,'刘蓉蓉',101,'女',46,to_date('2010-08-20','yyyy-MM-dd'),'广东深圳');
insert into studentTest values(studentTest_id_seq.Nextval,'陈大胖',102,'男',27,to_date('2011-04-30','yyyy-MM-dd'),'天津塘沽');
commit;
 
create table classTest
(
  id NUMBER(5) not null primary key,--班级编号
  name VARCHAR2(50) not null,--班级名称
  place VARCHAR2(50)--班级位置
);
 
insert into classTest (ID, NAME, PLACE) values (101, '普通1班', 'C栋教学楼305');
insert into classTest (ID, NAME, PLACE) values (102, '普通2班', 'B栋教学楼112');
insert into classTest (ID, NAME, PLACE) values (103, '尖子1班', '学霸楼606');
insert into classTest (ID, NAME, PLACE) values (104, '尖子2班', '学霸楼707');
insert into classTest (ID, NAME, PLACE) values (105, '尖子3班', '学霸楼808');
commit;

例子1.简单引用游标(弱类型游标)

相比显式游标，定义时不指定查询语句，打开时指定查询语句。一般也是有固定的四个步骤：声明游标 、打开游标 、提取数据 、关闭游标。

create or replace procedure procTest1(choice int)
as
  type refType is ref cursor;--1.(1).定义游标类型 ：相当于声明一个引用游标的类，类名为refType
  cur_student refType;--1.(2).声明游标：给refType这个类声明一个对象，叫cur_student
  row_student studentTest%rowtype;
  row_class classTest%rowtype;
begin
  if choice=1 then
    open cur_student for select * from studentTest;--2.打开游标
    loop
      fetch cur_student into row_student;--3.提取数据
      exit when cur_student%notfound;
      dbms_output.put_line('id=' || row_student.id || '，姓名=' || row_student.name ||'，班级=' || row_student.classno || '，性别=' ||row_student.sex || '，分数=' || row_student.score ||'，出生日期=' ||to_char(row_student.birthday, 'yyyy-MM-dd') || '，地址=' ||row_student.address);
    end loop;
    close cur_student;--4.关闭游标
    dbms_output.put_line('游标使用完毕');
  elsif choice=2 then
    open cur_student for select * from classTest;--2.打开游标
    loop
      fetch cur_student into row_class;--3.提取数据
      exit when cur_student%notfound;
      dbms_output.put_line('班级id='||row_class.id||'，班级名称='||row_class.name||'，班级位置='||row_class.place);
    end loop;
    close cur_student;--4.关闭游标
    dbms_output.put_line('游标使用完毕');
  else
    dbms_output.put_line('选择错误！');
  end if;
end;
 
call procTest1(1);

output输出结果为

id=1，姓名=黄小花，班级=101，性别=女，分数=95，出生日期=2011-01-01，地址=北京高新区
id=2，姓名=李大傻，班级=101，性别=男，分数=33，出生日期=2010-10-01，地址=
id=3，姓名=张翠花，班级=102，性别=女，分数=79，出生日期=，地址=桂林欧家村
id=4，姓名=王二狗，班级=102，性别=男，分数=82，出生日期=2009-02-25，地址=桂林秀峰区
id=5，姓名=刘蓉蓉，班级=101，性别=女，分数=46，出生日期=2010-08-20，地址=广东深圳
id=6，姓名=陈大胖，班级=102，性别=男，分数=27，出生日期=2011-04-30，地址=天津塘沽
游标使用完毕

call procTest1(2);

output输出结果为

班级id=101，班级名称=普通1班，班级位置=C栋教学楼305
班级id=102，班级名称=普通2班，班级位置=B栋教学楼112
班级id=103，班级名称=尖子1班，班级位置=学霸楼606
班级id=104，班级名称=尖子2班，班级位置=学霸楼707
班级id=105，班级名称=尖子3班，班级位置=学霸楼808
游标使用完毕

上面的例子1是弱类型游标，那什么是弱类型游标呢？看它和下面强类型游标的对比就知道了。

例子2.强类型游标

定义游标类型的时候指定了返回值

create or replace procedure procTest1_2(choice int)
as
  type refType is ref cursor return studentTest%rowtype;--相比弱类型游标，多了个返回值，相当于指定了游标只能返回表studentTest的rowtype类型。
  cur_student refType;
  row_student studentTest%rowtype;
  row_class classTest%rowtype;
begin
  if choice=1 then
    open cur_student for select * from studentTest;--符合游标返回值类型
    loop
      fetch cur_student into row_student;
      exit when cur_student%notfound;
      dbms_output.put_line('id=' || row_student.id || '，姓名=' || row_student.name ||'，班级=' || row_student.classno || '，性别=' ||row_student.sex || '，分数=' || row_student.score ||'，出生日期=' ||to_char(row_student.birthday, 'yyyy-MM-dd') || '，地址=' ||row_student.address);
    end loop;
    close cur_student;
    dbms_output.put_line('游标使用完毕');
  elsif choice=2 then
    open cur_student for select * from classTest;--不符合游标返回值类型，所以会导致编译不通过。
    loop
      fetch cur_student into row_class;
      exit when cur_student%notfound;
      dbms_output.put_line('班级id='||row_class.id||'，班级名称='||row_class.name||'，班级位置='||row_class.place);
    end loop;
    close cur_student;
    dbms_output.put_line('游标使用完毕');
  else
    dbms_output.put_line('选择错误！');
  end if;
end;
 
call procTest1_2(1);--这个存储过程因为编译不通过，所以这一句代码执行不了。

可以看一下存储过程的编译出错信息

例子3.使用游标变量执行动态sql(即ref游标如何传参)

(1).单个参数：查询指定性别的学生

create or replace procedure procTest2(ssex varchar2)
as
       type refType is ref cursor;
       cur_student refType;
       row_student studentTest%rowtype;
       mysql varchar2(100);
begin
  mysql:='select * from studentTest where sex=:1';--占位符写成":序号"形式
  open cur_student for mysql using ssex;--using后面接参数列表
  loop
    fetch cur_student into row_student;
    exit when cur_student%notfound;
    dbms_output.put_line('id=' || row_student.id || '，姓名=' || row_student.name ||'，班级=' || row_student.classno || '，性别=' ||row_student.sex || '，分数=' || row_student.score ||'，出生日期=' ||to_char(row_student.birthday, 'yyyy-MM-dd') || '，地址=' ||row_student.address);
  end loop;
  close cur_student;
  dbms_output.put_line('游标使用完毕');
end;
 
call procTest2('男');

output输出结果为

id=2，姓名=李大傻，班级=101，性别=男，分数=33，出生日期=2010-10-01，地址=
id=4，姓名=王二狗，班级=102，性别=男，分数=82，出生日期=2009-02-25，地址=桂林秀峰区
id=6，姓名=陈大胖，班级=102，性别=男，分数=27，出生日期=2011-04-30，地址=天津塘沽
游标使用完毕

call procTest2('女');

output输出结果为

id=1，姓名=黄小花，班级=101，性别=女，分数=95，出生日期=2011-01-01，地址=北京高新区
id=3，姓名=张翠花，班级=102，性别=女，分数=79，出生日期=，地址=桂林欧家村
id=5，姓名=刘蓉蓉，班级=101，性别=女，分数=46，出生日期=2010-08-20，地址=广东深圳
游标使用完毕

(2).多个参数以及模糊查询

create or replace procedure procTest3(sname varchar2,ssex varchar2)
as
       type refType is ref cursor;
       cur_student refType;
       row_student studentTest%rowtype;
       mysql varchar2(200);
begin
  mysql:='select * from studentTest where name like :1 and sex=:2';
  open cur_student for mysql using '%'||sname||'%',ssex;--参数列表：多个参数时按顺序用逗号拼接
  loop
    fetch cur_student into row_student;
    exit when cur_student%notfound;
    dbms_output.put_line('id=' || row_student.id || '，姓名=' || row_student.name ||'，班级=' || row_student.classno || '，性别=' ||row_student.sex || '，分数=' || row_student.score ||'，出生日期=' ||to_char(row_student.birthday, 'yyyy-MM-dd') || '，地址=' ||row_student.address);
  end loop;
  close cur_student;
  dbms_output.put_line('游标使用完毕');
end;

顺带一提：要使用模糊查询时下面这种写法会报错：绑定变量不存在。所以要用上面的那种写法。

mysql:='select * from studentTest where name like ''%:1%'' and sex=:2';--错误主要出在:1占位符处，:2占位符是没问题的。
open cur_student for mysql using sname,ssex;

查询名字带有“花”，且性别为“女”的学生。

call procTest3('花','女');

output输出结果为

id=1，姓名=黄小花，班级=101，性别=女，分数=95，出生日期=2011-01-01，地址=北京高新区
id=3，姓名=张翠花，班级=102，性别=女，分数=79，出生日期=，地址=桂林欧家村
游标使用完毕

查询名字带有“大”，且性别为“男”的学生。

call procTest3('大','男');

output输出结果为

id=2，姓名=李大傻，班级=101，性别=男，分数=33，出生日期=2010-10-01，地址=
id=6，姓名=陈大胖，班级=102，性别=男，分数=27，出生日期=2011-04-30，地址=天津塘沽
游标使用完毕

例子4.使用bulk collect批量提取

上述例子的游标基本都是通过loop一行一行的提取，而bulk collect可以一次性提取成列的集合，减少loop的开销。

create or replace procedure procTest4
as
       type refType is ref cursor;
       cur_class refType;
       type idList is table of classTest.id%type;--定义一个集合类
       ids idList;--声明一个集合类的对象
       type nameList is table of classTest.name%type;
       names nameList;
       type placeList is table of classTest.place%type;
       places placeList;
begin
  open cur_class for 'select c.id,c.name,c.place from classTest c';
  fetch cur_class bulk collect into ids,names,places;--提取成集合，这个提取是一次性提取，所以不能放在循环里。
  close cur_class;--可以直接关闭游标了
  for i in ids.first.. ids.last--i为集合中项的索引
  loop
    dbms_output.put_line('id='||ids(i)||'，name='||names(i)||'，place='||places(i));
  end loop;
  dbms_output.put_line('集合显示完毕');
end;
 
call procTest4();

output输出结果为

id=101，name=普通1班，place=C栋教学楼305
id=102，name=普通2班，place=B栋教学楼112
id=103，name=尖子1班，place=学霸楼606
id=104，name=尖子2班，place=学霸楼707
id=105，name=尖子3班，place=学霸楼808
集合显示完毕