程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> OracleCursor詳解與實例

OracleCursor詳解與實例

編輯：Oracle教程

Oracle Cursor詳解與實例

摘要：詳細介紹oracle數據庫中關於游標的定義和使用。通過實例操作來深入了解cursor的用法和用處。

一：相關概念

1、concep

When Oracle Database executes aSQL statement , it stores the result set and processing information in anunnamed private SQL area . A pointer to this unnamed area , called a cursor ,let you retrieve the rows of the result set one at a time . Cursor attributesreturn information about the state of the cursor .

2、概念：

游標是SQL的一個內存工作區，由系統或用戶以變量的形式定義。游標的作用就是用於臨時存儲從數據庫中提取的數據塊。在某些情況下，需要把數據從存放在磁盤的表中調到計算機內存中進行處理，最後將處理結果顯示出來或最終寫回數據庫。這樣數據處理的速度才會提高，否則頻繁的磁盤數據交換會降低效率。Cursor類型：靜態游標——分為顯式（explicit）游標和隱式（implicit）游標、REF游標——動態游標、是一種引用類型、類似於指針。

二：具體類型及使用

1、implicit cursor

1） explanation：Everytime you run either a SQL DML statement or a PL/SQLSELECTINTO statement, PL/SQLopens an implicit cursor. You can get information about this cursor from itsattributes, but you cannot control it. After the statement runs, the databasecloses the cursor; however, its attribute values remain available until anotherDML orSELECTINTO statement runs.

2） implicit cursor（隱式游標）由系統自動打開和關閉、當我們執行一個SQL DML時、系統會自動打開一個cursor、當執行完畢之後系統會關閉cursor、我們不能直接控制cursor、但是卻可以通過implicit Cursor的屬性來了解操作的狀態和結果、從而達到流程的控制——Cursor的屬性包括：

i、SQL%ROWCOUNT整形——代表DML語句成功執行的行數

ii、SQL%FOUND布爾型——值為true時代表插入、刪除、更新或查詢操作成功

iii、SQL%NOTFOUND布爾型——與上面相反

v、SQL%ISOPEN布爾型——DML執行過程中為真、否則為假

3）示例：

begin
  update student set sname='chy' WHERE sno='1';
  if sql%isopen then
     dbms_output.put_line('cursor is opening !');
  else
     dbms_output.put_line('cursor is closed !');
  end if;
  if sql%found then
     dbms_output.put_line('DML is successed !');
  else
     dbms_output.put_line('DML is failed !');
  end if;
  if sql%notfound then
     dbms_output.put_line('DML is failed !');
  else
     dbms_output.put_line('DML is successed !');
  end if;
      dbms_output.put_line(sql%rowcount||' is the number of result !');
  exception 
      when no_data_found then
           dbms_output.put_line('Sorry No data');
      when too_many_rows then
           dbms_output.put_line('Too Many rows');
end;

2、explicit cursor

1）explanation：PL/SQLalso lets you declare explicit cursors. An explicit cursor has a name and isassociated with a query (SQLSELECT statement)—usually one that returns multiplerows. After declaring an explicit cursor, you must open it (with the OPENstatement), fetch rows one at a time from the result set (with the FETCHstatement), and close the cursor (with the CLOSE statement). After closing thecursor, you can neither fetch records from the result set nor see the cursorattribute values.

很直白的說明了顯示游標的用處、以及用法。

2）explicit cursor的屬性包含：

游標的屬性返回值類型意義

%ROWCOUNT 整型獲得FETCH語句返回的數據行數

%FOUND 布爾型最近的FETCH語句返回一行數據則為真，否則為假

%NOTFOUND 布爾型與%FOUND屬性返回值相反

%ISOPEN 布爾型游標已經打開時值為真，否則為假

3）對於explicit Cursor使用分四個步驟：

a 定義游標——Cursor [ Cursor Name[param_name, param_type]] IS select xxx from xxxwhere xxx;

b 打開游標——Open [ Cursor Name[varialbe_value] ] ;

c 操作游標——Fetch [ Cursor Name ];

d 關閉游標——Close [ Cursor Name ] ;

4）具體使用顯示游標,遍歷循環游標步驟：

a）使用顯示游標

i、聲明游標：劃分存儲區域，注意此時並沒有執行Select語句。CURSOR游標名(參數列表) [返回值類型] IS Select 語句；

ii、打開游標：執行Select語句，獲得結果集存儲到游標中，此時游標指向結果集頭，而不是第一條記錄。open游標名(參數列表);

iii、獲取記錄：移動游標取一條記錄 fetch 游標名 into 臨時記錄或屬性類型變量；

v、關閉游標：將游標放入緩沖池中，沒有完全釋放資源。可重新打開。

close 游標名；

b）遍歷循環游標

i、for循環游標

循環游標隱式打開游標，自動滾動獲取一條記錄，並自動創建臨時記錄類型變量存儲記錄。處理完後自動關閉游標。

……

for 變量名 In 游標名

loop

數據處理語句；

end loop；

ii、loop循環游標

……

loop

fetch 游標名 into 臨時記錄或屬性類型變量；

exit when 游標名%notfound；

end loop；

iii、while循環

……

open 游標名

fetch 游標名into臨時記錄或屬性類型變量；

while 游標名%foundloop

-- do something

fetch 游標名into臨時記錄或屬性類型變量；

end loop;

……

close 游標名

5）常見顯式Cursor用法：

i、使用for循環來使用cursor：

declare 
  cursor cur is select * from t_user where age = 22;
  userinfo t_user%rowtype;
begin
  for userinfo in cur loop
    exit when cur%notfound;
    dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
  end loop;
  exception 
    when others then
      dbms_output.put_line(sqlerrm);
end;

ii、使用fetch來使用cursor： exp2

declare 
  cursor cur is select * from t_user where age = 22;
  userinfo t_user%rowtype;
begin
  open cur;
  loop
     exit when cur%notfound;
     fetch cur into userinfo;
     dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
  end loop;
  exception
     when others then
          dbms_output.put_line(sqlerrm);
 close cur;
end;

iii、使用fetch結合while使用cursor：exp3

declare
  cursor cur is select * from t_user where age = 23;
  userinfo t_user%rowtype;
begin
  open cur;
  fetch cur into userinfo;
  if cur%isopen then
    while cur%found loop
          dbms_output.put_line('user id : ' || userinfo.id || '-' || 'user name : ' || userinfo.username);
          fetch cur into userinfo;
    end loop;
    dbms_output.put_line('totle result : ' || cur%rowcount);
  else
    dbms_output.put_line('cursor is closed!');
  end if;  
  close cur;
  exception
     when others then
          dbms_output.put_line(sqlerrm);
 close cur;
end;

v、使用cursor實現數據的修改（帶參數的cursor）、下面三種作用是一樣的、只是內部實現有點區別

-- 給工作為CLERK的員工加薪

--one
declare
   cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
   ef emp1%rowtype;
   c_sal emp1.sal%type;
begin
  for ef in cur('CLERK') LOOP
    EXIT WHEN CUR%NOTFOUND;
    IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;
    UPDATE EMP1 SET EMP1.SAL=C_SAL WHERE CURRENT OF CUR;
  END LOOP;
  EXCEPTION
    WHEN OTHERS THEN
     dbms_output.put_line(sqlerrm);
END;
--two
declare
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
  EF emp1%rowtype;
  C_SAL emp1.sal%type;
begin
  open cur('CLERK');
  fetch cur into EF;
  while cur%found loop 
    EXIT WHEN CUR%NOTFOUND;
    IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;
    update emp1 set emp1.sal=C_SAL where current of cur;
    fetch cur into EF;
  end loop;
  close cur;
end;  
--three
declare
  --define the cursor Note: the select sql is not excuted!
  cursor cur(c_job varchar2) is select * from emp1 where emp1.job=c_job for update of sal;
  ef emp1%rowtype;
  c_sal emp1.sal%type;
begin
  open cur('CLERK');
  fetch cur into ef;
  
  while cur%found loop
    exit when cur%notfound;
    case
      when ef.sal < 1000 
      then c_sal := ef.sal*1.2;
      when ef.sal < 2000 
      then c_sal := ef.sal*1.4;
      when ef.sal < 3000 
      then c_sal := ef.sal*1.6;
    end case;
    update emp1 set emp1.sal = c_sal where current of cur;
    fetch cur into ef;
  end loop;
  close cur;
end;

vi、使用cursor實現數據的刪除：

--use cursor to delect date
create table emp3 as select * from emp;

--delete the date of emp3 where the job is 'CLERK';
declare
  cursor cur(c_job varchar2) is select * from emp3 where emp3.job=c_job for update;
  ef emp3%rowtype;
begin
  for ef in cur('CLERK') loop
    exit when cur%notfound;
    delete from emp3 where current of cur;
  end loop;
end;

補充：

ref cursor會在下一個筆記中出現、這裡補充一個循環時使用的判斷條件if的東西。注意看下面兩段代碼：

IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSIF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSIF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;

IF EF.SAL < 1000 THEN
      C_SAL := EF.SAL*1.2;
    ELSE IF EF.SAL < 2000 THEN
      C_SAL := EF.SAL*1.5;
    ELSE IF EF.SAL < 3000 THEN
      C_SAL := EF.SAL*2;
    ELSE
      C_SAL := EF.SAL*2.2;
    END IF;

當我們使用多個if條件的時候寫成後則就會出錯、必須要寫成前面的elsif來結合if多條件的情況！