Oracle-35-隱式遊標&顯式遊標

標籤：

一、遊標作用（或定義）

1.PL/SQL提供遊標機制處理多行記錄結果集；

2.遊標類似於指標，使應用程式一次可以處理其中的一行記錄，比如將遊標放入一個for迴圈中，每迴圈一次就處理一行記錄，那麼迴圈n次就可以處理n行記錄；

3.Oracle中，可以分為顯式遊標和隱式遊標兩種，比如select*fromstudent就是用隱式遊標進行遍曆student表，然後將查詢結果展示；

4.在平常在進行SELECT查詢、DML操作Oracle都會自動建立聲明“隱式遊標”來處理結果資料；

6.如果需要完成特定功能（SELECT、DML操作），則可以由PL/SQL程式來自訂一個“顯式遊標”。

 

二、隱式遊標

在執行一個SQL語句時，Oracle伺服器將自動建立一個隱式遊標。這個遊標儲存執行SQL語句的結果。

 

1.遊標的主要屬性（隱式和顯式皆可）：

（1）%FOUND：布爾型屬性，如果SQL語句至少影響一行操作，則返回TRUE，否則為FALSE；

比如如下程式：

declare

     a emp%rowtype;——a變數的資料類型是emp表中的行，便於後面將emp表一行資料存入a中

begin

     select* into a from emp where empno = 7935;

     if sql%found then——用隱式遊標判斷上一行的資料是否存在

          dbms_output.put_line(‘員工編號為7935的員工存在，其名字為‘ ||a.ename);

     end if;

end;

 

例1：練習遊標%found屬性

解：注意中的程式：

如果在where子句中輸入一個不存在的學號’h001’，其餘代碼同上，會報錯，提示資料找不到：

上述報錯可用exception解決，注意中的程式：

（2）%NOTFOUND：布爾型屬性，與%FOUND的功能相反；

（3）%ISOPEN：布爾型屬性，當遊標已開啟時返回TRUE，遊標關閉時為FALSE；

（4）%ROWCOUNT：數字型屬性，返回受SQL語句影響的行數。

 

例2：練習遊標%rowcount屬性。

解：當前student表中資料，注意’s015’行其餘資料為null：

現在用遊標%rowcount屬性更新資料：

然後我們查詢當前student表，發現’s015’行資料已經更新：

所以說通過遊標的%rowcount屬性可以查看update資料之後，原student表中受其影響的行數，其餘增刪改查同理可用%rowcount查詢。

 

2.隱式遊標需要注意的地方

（1）PL/SQL中使用select語句，必須和into關鍵字一起使用；

（2）PL/SQL中select只返回一行資料，如果超過一行資料，那就要使用顯式遊標；

（3）對於select into語句，如果執行成功，sql%rowcount的值為1，如果沒有成功，sql%rowcount的值為0且產生一個異常NO_DATA_FOUND；

 

3.【特別注意】

無論用遊標哪種屬性，記得格式：遊標名%屬性，比如隱式遊標sql%found，再比如顯式遊標cursor_name%found。

三、顯式遊標

當查詢結果返回多於一行時，必須使用顯式遊標。使用顯式遊標

的4個步驟：

Step1：聲明顯式遊標；

Step2：開啟顯式遊標；

Step3：檢索顯式資料；

Step4：關閉顯式遊標。

 

1.聲明顯式遊標的文法格式

cursor 顯式遊標名 [(parameter[,parameter…])] [return return_type] is查詢語句

 

其中parameter為顯式遊標的輸入參數，它可以讓使用者在開啟顯式遊標時，向顯式遊標傳遞值，parameter格式：

參數名 [in]參數資料類型 [{:= | default} expresson]

2.開啟顯式遊標格式

open 顯式遊標名 (‘張三’);

 

3.關閉遊標格式

close 顯式遊標名

注意在使用完遊標後切記關閉遊標。

 

4.利用顯式遊標檢索資料，在檢索資料時使用fetch語句找出結果集中的單行，並從中提取單個值傳遞給變數。做法是用一個loop迴圈，把fetch丟進去，比如：

loop

     fetch emp_cursorinto a;——利用fetch語句將遊標emp_cursor的值傳遞給變數a

end loop;

 

例3：練習顯式遊標

解：注意中的程式：

程式分段解析：

declare

      cursor v_cur(m in varchar2)——聲明顯式遊標，注意不要指定參數m的長度

      is select sno,sname from student wheresno=m;——將參數m值給sno，之後select語句據此篩選出合格資料

      type t_record is record(——定義記錄，便於之後用fetch語句將遊標內資料傳入變數中

           id varchar2(20),

           name varchar2(20)

      );

      t t_record;——定義相對於記錄t_record的變數t，

begin

      open v_cur(‘s001’);——開啟顯式遊標

      loop

           fetch v_cur into t;——用fetch語句將遊標內資料傳入變數中

           exit when v_cur%notfound;——定義跳出迴圈條件，即遊標內沒

資料時

           dbms_output.put_line(t.id || ‘  ‘ || t.name);

      end loop;

      close v_cur;——關閉顯式遊標

end;

 

【總結】

（1）無論是隱式還是顯式遊標，都有屬性：

%found, %notfound, %isopen,%rowcount；

（2）隱式遊標只能操作單行資料，多行資料必須用顯式遊標；

（3）隱式遊標名稱固定sql，顯式遊標名稱要自訂；

（4）顯式遊標要通過fetch語句將遊標中的值傳遞給變數，通常把該fetch語句放入loop迴圈，記得迴圈中要定義跳出迴圈條件；

（5）在使用顯式遊標時，cursor遊標名（參數名 in 參數資料類型），這種方式定義時，參數資料類型中不要指定長度，否則報錯，比如cursor v_cur (m in varchar2(20))是錯的，必須不指定長度，即cursor v_cur (m in varchar2)。

Oracle-35-隱式遊標&顯式遊標