Oracle游标—for、loop、if结合应用

浏览数：15 / 时间：2015年06月12日

一、需求

什么时候会用到Oracle游标，以及其中的for、loop、if呢？

先看这样一个需求：

有一张学生授课表T_TEACHING，每个学生都有数门课程：

主键ID（自增）	课程号COURSE_ID	学号USER_ID
1	01	201501
2	02	201501
3	03	201501
4	01	201502
5	01	201503
6	01	201504
7	02	201504
...	...	...

但是因为某些原因，导致有的学生课程不全（本应该每个学生都有3门课），应该如何把不全的学生检索出来，再给这些学生添加课程呢，并且要求能够快速解决这个问题。

二、分析

我们对需求进行一步步梳理：

1、我们不知道哪个学生课程不全，所以需要循环判断每个学生

通常用for循环：for USER_ID in USER_IDS

2、循环判断每个学生就要拿到所有的学号

select：select USER_ID from T_TEACHING GROUP BY USER_ID;

3、有了学号，判断这个学生是否缺少课程，然后添加这门课

（1）select：select count(*) from T_TEACHING where USER_ID=@USER_ID and COURSE_ID=@COURSE_ID;

（2）if else 判断

（3）insert：insert into T_TEACHING(COURSE_ID,USER_ID) values (@COURSE_ID,@USER_ID)

通过分析大致的流程出来了，然后再看细节：

我们通过select ..GROUP BY获取了授课表中所有的学号，然后就对这些学号进行循环获取，关键在于select ..GROUP BY出来如何循环获取。

三、方法

在oracle中提供了“游标”

　　游标是什么，游标用来处理从数据库中检索的多行记录（使用SELECT语句）。利用游标，程序可以逐个地处理和遍历一次检索返回的整个记录集。

这正是我们想要的！！

结合for循环，我们可以使用for循环游标，格式如下：

 --(1)定义游标
    --关键字cursor
    cursor x is select语句

 --(2)定义游标变量
　　y x%rowtype

 --(3)使用for循环来使用这个游标
    for y in x loop

      --逻辑处理

    end loop;

下面我们通过sql存储过程来完整的实现上面的需求

declare
       --定义类型
       cursor t_tea
       is
       select USER_ID from T_TEACHING GROUP BY USER_ID;
       --定义一个游标变量
       t_row t_tea%rowtype;
       --定义一个number类型的临时变量
　　　 v_count number;
 begin
      for t_row in t_tea loop 
　　　 select count(*) into v_count from T_TEACHING where USER_ID=t_row.USER_ID and COURSE_ID=02;
　　　 　if v_count = 0 then
　　　 　　  insert into T_TEACHING(COURSE_ID,USER_ID) values (02,t_row.USER_ID);
　　　 　end if;
       end loop;
end;