Oracle遊標—for、loop、if結合應用

Oracle遊標—for、loop、if結合應用

一、需求

什麼時候會用到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;

四、總結　　

Oracle中的遊標迴圈不只有for迴圈，Oracle中提供了兩種遊標，顯示遊標和隱式遊標，其他遊標的使用方式可以在這裡瞭解，用到了在深入研究：