Oracle遊標遍歷%rowtype中的記錄

那麼我們使用Oracle遊標

遊標分為：靜態遊標和引用遊標（動態遊標）

靜態遊標：由使用者定義（隱式遊標、顯示遊標）結果集不變

引用遊標遊標：結果集變化

隱式遊標：用DML操作時，自動使用隱式遊標。我們可以使用隱式遊標判斷SQL語句執行結果

自動宣告和處理的。在Session會話區，開啟遊標。處理後自動關閉。可以返回單行查詢。

隱式遊標使用：

declare 

%NOTFOUND  －－ 執行行沒有找到。 

%FOUND  －－執行行找到 

%ROWCOUNT －－遊標影響行數 

%ISOPEN －－ 當前遊標是否開啟 

我們現在通過遊標來看看上篇文章的例子
通過迴圈來遍歷資料：

1、loop when迴圈

declare 
cursor myCur is select * from hr.jobs; 
oneRow hr.jobs%rowtype; 
begin 
       open myCur; 
  loop 
       fetch myCur into oneRow; 
  dbms_output.put_line(oneRow.job_id ||'    ' ||onerow.job_title); 
  exit when myCur%notFound; 
       end loop; 
  close myCur; 
end; 

2、while 迴圈

declare 
cursor myCur is select * from hr.jobs; 
oneRow hr.jobs%rowtype; 
begin 
open myCur; 
fetch myCur into oneRow; 
while (myCur%found) 
  loop 
  dbms_output.put_line(oneRow.job_id ||'    ' ||onerow.job_title); 
  fetch myCur into oneRow; 
  end loop; 
  close myCur; 
end; 

3、for 迴圈

declare 
cursor myCur is select * from hr.jobs; 
oneRow hr.jobs%rowtype; 
begin 
   for oneRow in myCur loop 
dbms_output.put_line(oneRow.job_id ||'    ' ||onerow.job_title); 
   end loop; 
end; 

結果如下：

AD_PRES    President 
AD_VP    Administration Vice President 
AD_ASST    Administration Assistant 
FI_MGR    Finance Manager 
FI_ACCOUNT    Accountant 
AC_MGR    Accounting Manager 
AC_ACCOUNT    Public Accountant 
SA_MAN    Sales Manager 
SA_REP    Sales Representative 
PU_MAN    Purchasing Manager 
PU_CLERK    Purchasing Clerk 
ST_MAN    Stock Manager 
ST_CLERK    Stock Clerk 
SH_CLERK    Shipping Clerk 
IT_PROG    Programmer 
MK_MAN    Marketing Manager 
MK_REP    Marketing Representative 
HR_REP    Human Resources Representative 
PR_REP    Public Relations Representative