[oracle]静态游标之显式游标

最新推荐文章于 2024-01-13 11:26:29 发布

呆呆的笨笨君

最新推荐文章于 2024-01-13 11:26:29 发布

阅读量411

点赞数

分类专栏： Oracle 文章标签： oracle 静态游标显式游标

本文链接：https://blog.csdn.net/qq_20481125/article/details/101214237

版权

Oracle 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一.Oracle中游标分类介绍

1.静态游标：结果集已经确实（静态定义）的游标。在执行前，明确知道sql语句游标，静态游标又分以下两种类型。

(1).显式游标：用户显示声明的游标，即指定结果集。当查询返回结果超过一行时，就需要一个显式游标。使用显示游标需要用户自己写sql语句。
(2).隐式游标：所有DML语句（增、删、改、查询单条记录）为隐式游标，该变量名不需要用户自己声明，它由系统帮我们定义，叫sql。使用时不需要声明隐式游标，它由系统定义。

2.动态游标 (REF游标)：关联结果集的临时对象，在执行前不知道sql语句游标，执行时才知道sql语句的游标。动态游标又分为以下两种类型。

(1).强类型游标：规定返回类型。
(2).弱类型游标：不规定返回类型，可以获取任何结果集。

而这一篇讲的就是静态游标当中的显式游标。

二.举例说明显式游标

先准备数据，这是一张学生表。

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;
 
select * from studentTest;

在这里插入图片描述

1.简单游标

一般都有固定的四个步骤：声明游标、打开游标、提取数据、关闭游标。

--###############简单游标###############
create or replace procedure proc1 as
  cursor cur_student is select * from studentTest; --1.定义游标
  row_student studentTest%rowtype; --定义变量与表studentTest的数据行映射，用于存储游标读取到的数据。只能存一行的数据，可以理解成row_student就相当于一个实体对象。
begin
  open cur_student; --2.打开游标
  loop
    fetch cur_student into row_student; --3.在循环中读取游标的每一行数据，填充到对象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; --4.关闭游标
  dbms_output.put_line('游标使用完毕');
end;
 
call proc1();

这个简单游标还可以改造成如下两种形式

--while循环形式
create or replace procedure proc1_2 as
  cursor cur_student is select * from studentTest;
  row_student studentTest%rowtype;
begin
  open cur_student;
  fetch cur_student into row_student;
  while cur_student%found loop
    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);
    fetch cur_student into row_student;
  end loop;
  close cur_student;
  dbms_output.put_line('游标使用完毕');
end;
 
call proc1_2();
 
--for循环
create or replace procedure proc1_3
as
       cursor cur_student is select * from studentTest;
       --row_student studentTest%rowtype;--使用for循环时，这一行可以不用声明
begin
       --for循环会默认打开游标，循环结束会默认关闭，所以不需要手动写打开和关闭游标的代码。
       for row_student in cur_student--游标变量student会在for循环这里自动声明
       loop
         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;
       dbms_output.put_line('游标使用完毕');
end;
 
call proc1_3();

上面3个存储过程效果等同，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，地址=天津塘沽
游标使用完毕

2.带条件的游标

--查询指定班级的学生
create or replace procedure proc2(stu_classno number)
as
       cursor cur_student is select * from studentTest where classno=stu_classno;--设置了查询条件
       row_student studentTest%rowtype;
begin
       dbms_output.put_line('以下是班级'||stu_classno||'的学生信息');
       open cur_student;
       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 proc2(101);

output输出内容为

以下是班级101的学生信息
id=1，姓名=黄小花，班级=101，性别=女，分数=95，出生日期=2011-01-01，地址=北京高新区
id=2，姓名=李大傻，班级=101，性别=男，分数=33，出生日期=2010-10-01，地址=
id=5，姓名=刘蓉蓉，班级=101，性别=女，分数=46，出生日期=2010-08-20，地址=广东深圳
游标使用完毕

3.带参游标

--按照性别来查询学生信息
create or replace procedure proc3(stu_sex varchar)
as
       cursor cur_student(stu_sex_cur varchar) is select * from studentTest where sex=stu_sex_cur;
       row_student studentTest%rowtype;
begin
       open cur_student(stu_sex);--打开游标时传参
       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 proc3('女');

output输出内容为

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

4.使用显式游标更新行

更新指修改/删除

--场景：因为102班改卷有误，所以给102班的学生分数都+5分。
create or replace procedure proc4
as
       --select ... for update依旧是执行查询，但是会加上行级锁，期间其他用户无法操作该行，在执行commit或rollback后才会解锁。
       cursor cur_student(stu_classno_cur number) is select * from studentTest where classno=stu_classno_cur for update;
       row_student studentTest%rowtype;
begin
  for row_student in cur_student(102)
  loop
    update studentTest set score=score+5 where current of cur_student;--前面加上了行级锁，所以在此期间不会被其他用户修改。
  end loop;
  commit;--提交并解锁
  dbms_output.put_line('游标使用完毕');
end;
 
call proc4();
 
select * from studentTest;

可以看到102班的学生成绩确实都加了5分
在这里插入图片描述

5.二重循环游标

再新建一张表，为班级表。

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;
 
select * from classTest;

在这里插入图片描述

--按班级分组，显示每个班级下的每个学生的名字和分数，以及该班级的平均分
create or replace procedure proc5
as
      cursor classTest_cursor is select * from classTest order by id;--查询所有班级
      cursor studentTest_cursor(class_no number) is select * from studentTest where classno=class_no;--查询指定班级下的学生
      count_student int;--每个班级的人数
      sum_score studentTest.score%type;--每个班级的总分
      avg_score studentTest.score%type;--每个班级的平均分
begin
 for row_class in classTest_cursor
 loop
   dbms_output.put_line('*****班级名称：'||row_class.name||'*****');
   count_student:=0;
   sum_score:=0;
   for row_student  in studentTest_cursor(row_class.id)
   loop
     dbms_output.put_line('学生姓名：'||row_student.name||'，分数：'||row_student.score);
     count_student:=count_student+1;
     sum_score:=sum_score+row_student.score;
   end loop;
   if count_student=0 then
     dbms_output.put_line('*****该班级没有学生*****');
   else
     avg_score:=sum_score/count_student;
     dbms_output.put_line('*****该班级平均分为：'||avg_score||'*****');
   end if;
   dbms_output.put_line('');
 end loop;
end;

call proc5();

output输出内容为

*****班级名称：普通1班*****
学生姓名：黄小花，分数：95
学生姓名：李大傻，分数：33
学生姓名：刘蓉蓉，分数：46
*****该班级平均分为：58*****
 
*****班级名称：普通2班*****
学生姓名：张翠花，分数：84
学生姓名：王二狗，分数：87
学生姓名：陈大胖，分数：32
*****该班级平均分为：67.67*****
 
*****班级名称：尖子1班*****
*****该班级没有学生*****
 
*****班级名称：尖子2班*****
*****该班级没有学生*****
 
*****班级名称：尖子3班*****
*****该班级没有学生*****

注意要点：

以二.1 里的存储过程proc1为例，fetch cur_student into row_student;和exit when cur_student%notfound;这两行代码一般来说建议放一起，而且fetch cur_student into row_student;必须要在exit when cur_student%notfound;前面。

如果不这样做会有什么问题？下面举例说明。

1.如果把exit when cur_student%notfound;放在fetch cur_student into row_student;前面。

create or replace procedure proc1_4 as
  cursor cur_student is select * from studentTest;
  row_student studentTest%rowtype;
begin
  open cur_student;
  loop
    exit when cur_student%notfound;--放在fetch语句前面
    fetch cur_student into row_student;
    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 proc1_4();

output输出内容为

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

这里会发现一个问题，最后一行数据(id=6的学生)被多输出了一次，这是怎么回事呢？

原来是这样的，oracle的boolean类型变量，不只是true和false两种，还可能存在null。对于一个游标来说，如果它从没有执行过fetch语句，那么它的cur_student%notfound返回值是null。以上的存储过程proc1_4中，第一遍循环的时候，cur_student%notfound就是返回null。

2.如果fetch cur_student into row_student;和exit when cur_student%notfound;没放在一起，中间夹有其他代码。

create or replace procedure proc1_5 as
  cursor cur_student is select * from studentTest;
  row_student studentTest%rowtype;
begin
  open cur_student;
  loop
    fetch cur_student into row_student;--fetch语句和exit语句中间夹有其他代码
    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);
    exit when cur_student%notfound;
  end loop;
  close cur_student;
  dbms_output.put_line('游标使用完毕');
end;
 
call proc1_5();

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，地址=天津塘沽
id=6，姓名=陈大胖，班级=102，性别=男，分数=27，出生日期=2011-04-30，地址=天津塘沽
游标使用完毕

会发现也是最后一行多输出了一次，这又是为什么呢？

上面两种问题实际上都是这样的，数据共有6行，所以前面6次循环都是正常的，而进入到第7次循环的时候，fetch cur_student into row_student;其实已经读取不到游标的数据了，但是它不会给row_student这个变量填充值，所以row_student的数据是上一次循环产生的数据，所以dbms_output.put_line会把这条数据又输出一遍，直到碰到exit when cur_student%notfound;这条语句才结束循环。

所以一般来说，没有特殊需求的话，把fetch语句和exit语句都放在循环内部的前2行，且fetch语句要在exit语句前面。