天天看點
傳回

ORACLE學習筆記（八）——遊标——資料的緩沖區 一、什麼是遊标二、顯式遊标三、隐式遊标

一、什麼是遊标

    1、遊标的概念

        可以将遊标（Cursor）形象地看成一個變動的光标。它實際上是一個指針，它在一段oracle存放資料查詢結果集或者資料操作結果集的記憶體中，這個指針可以指向結果集中的任何一條記錄。這樣就可以得到它所指向的資料了，但初始時它指向首記錄。這種模型很像程式設計中的數組。

    2、遊标的類型

        oracle遊标分為靜态遊标和REF遊标兩類。其中，靜态遊标就像一個資料快照，打開遊标後的結果集是對資料庫資料的一個備份，資料不随着對表執行DML操作後而改變。從這個特性來說，結果集是靜态的。

        靜态遊标包含兩中類型：

        （1）、顯式遊标

          是指在使用之前必須有着明确的遊标聲明和定義，這樣的遊标定義會關聯資料查詢語句，通常會傳回一行或者多行。關閉遊标後就不能再對其進行操作了。顯示遊标由使用者自定義來完成，一切由使用者控制。

        （2）、隐式遊标

          和顯式遊标不同，它被PL/SQL自動管理，也被稱為SQL遊标，由oracle自動管理。該遊标使用者無法控制，但能得到它的屬性資訊。

二、顯式遊标

    1、遊智語法 

CURSOR    cursor_name    [    parameter_name    datatype,     ...    ]    IS    select_statement;

    2、遊标的使用步驟

        (1)聲明遊标（并關聯一個查詢）

DECLARE    CURSOR    cursor_name    IS    select_statement;

        (2)打開遊标（遊标一旦打開，結果集都是靜态的）

OPEN    cursor_name;

        (3)讀取遊标

        讀取資料要利用FETCH語句完成，它可以把遊标指向位置的記錄放入到PL/SQL聲明的變量中。正常情況下，FETCH語句要和循環語句一起使用，這樣指針會不斷前進，直到某個條件不符合要求而退出。

FETCH    cursor_name    INTO    Record_name;

        (4)關閉遊标

CLOSE    cursor_name;

    3、遊标中的LOOP語句 

declare

  cursor info_cursor is select id,position,strain from CONSTANTINFO where id <11;
  --使用%type可以取得表中的字段類型，使用%rowtype可以申明基于某個表的行類型
  cur_id CONSTANTINFO.id%TYPE;
  cur_pos CONSTANTINFO.position%TYPE;
  cur_str CONSTANTINFO.strain%TYPE;

begin
  --打開遊标
  open info_cursor;
    --循環擷取記錄集
    loop
      fetch info_cursor into cur_id,cur_pos,cur_str;
      --使用遊标屬性：記錄集到尾，沒有記錄退出循環
      exit when info_cursor%notfound;
      --輸出遊标擷取的記錄集
      dbms_output.put_line(cur_id ||'-----'|| cur_pos ||'-----'|| cur_str);
    end loop;
  --關閉遊标
  close info_cursor;
  --遊标中使用異常處理
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('沒有找到資料！');
  WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('資料過多！');
END;

    4、使用BULK COLLECT和FOR語句的遊标——實作批量讀取資料

        使用FETCH INTO語句提取資料，這種方式是單條資料提取，在資料量很大的情況下執行效率并不理想。而FETCH...BULK COLLECT INTO語句可以批量提取資料，在資料量大的情況下它的執行效率相當快。

declare
  cursor info_cursor is select id,position,strain from CONSTANTINFO where id <11;
  --定義和表STRACONSTANTINFO行對象一緻的集合類型constantinfo_rd，用于存放批量得到的資料
  type constantinfo_tab is table of CONSTANTINFO%rowtype;
  constantinfo_rd constantinfo_tab;
begin
  --打開遊标
  open info_cursor;
    --循環擷取記錄集
    loop
      fetch info_cursor bulk collect into constantinfo_rd limit 2;
      for i in 1..constantinfo_rd.count loop
          dbms_output.put_line('資料ID：'||constantinfo_rd(i).id ||'  位置：'||
             constantinfo_rd(i).position ||'  應變：'|| constantinfo_rd(i).strain);
          end loop;
      --記錄集到尾，沒有記錄退出循環
      exit when info_cursor%notfound;
    end loop;
  --關閉遊标
  close info_cursor;
  --遊标中使用異常處理
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('沒有找到資料！');
  WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('資料過多！');
END;

    5、使用CURSOR FOR LOOP——使用更加簡單

        遊标有很多機會都是疊代結果集，在PL/SQL這個過程中可以使用更簡單的方式實作，CURSOR FOR LOOP不需要特别的聲明變量，它可以提出行對象類型的資料。這種方式在隐式遊标中使用更顯友善。

declare
--聲明遊标關聯查詢
  CURSOR info_cursor IS select id,position,strain from CONSTANTINFO where id<11;
begin
     --把遊标傳回資料放到curcfl中，該類型是個%ROWTYPE類型
     FOR curcfl IN info_cursor
     --疊代輸出資料
     LOOP
         DBMS_OUTPUT.PUT_LINE('資料ID：'||curcfl.id ||'  位置：'|| curcfl.position ||'  應變：'|| curcfl.strain);
     END LOOP;
     --遊标中使用異常處理
     EXCEPTION
     WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('沒有找到資料！');
     WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE('資料過多！');
end;

    6、顯式遊标的屬性

    （1）%ISOPEN：用于判斷遊标是否打開，打開則傳回TRUE，否則FALSE；

    （2）%FOUND：用來檢測行資料是否有效。有效則傳回TRUE,否則反之；

    （3）%NOTFOUND：與%FOUND正好相反，沒有提取出資料傳回TRUE，否則反之；

    （4）%ROWCOUNT：累計到目前為止使用FETCH提取資料的行數。較為常用，使用它可以知道目前已經傳回多少行資料了

    7、帶參數的遊标

        在使用顯式遊标時是可以指定參數的，指定的參數包括參數的順序和參數的類型。參數可以傳遞給遊标在查詢中使用。這樣就友善了使用者根據不同的查詢條件進行查詢。也友善了遊标在存儲過程中的使用。

DECLARE
  --使用%type可以取得表中的字段類型，并且聲明了變量賦了值；使用%rowtype可以申明基于某個表的行類型
  cur_id CONSTANTINFO.id%TYPE := '100';
  cur_pos CONSTANTINFO.position%TYPE := '10000';
  cur_rcd CONSTANTINFO%ROWTYPE;
  
  --聲明遊标，包括兩個參數和類型
  CURSOR info_cursor(iid VARCHAR2,pos NUMBER) IS SELECT * FROM constantinfo WHERE id < iid AND position < pos;

BEGIN
  --打開遊标,并且給參數賦予變量
  OPEN info_cursor(cur_id,cur_pos);
    --循環擷取記錄集
    LOOP
      FETCH info_cursor INTO cur_rcd;
      --使用遊标屬性：記錄集到尾，沒有記錄退出循環
      EXIT WHEN info_cursor%NOTFOUND;
      --輸出遊标擷取的記錄集
      DBMS_OUTPUT.PUT_LINE(cur_rcd.id ||'-----'|| cur_rcd.position ||'-----'|| cur_rcd.strain);
    END LOOP;
  --關閉遊标
  CLOSE info_cursor;
  --遊标中使用異常處理
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('沒有找到資料！');
  WHEN TOO_MANY_ROWS THEN
      DBMS_OUTPUT.PUT_LINE('資料過多！');
END;

三、隐式遊标

    1、隐式遊标的特點

        隐式遊标沒有像顯式遊标那樣聲明一個遊标名稱，而是直接使用了SQL名稱，這是隐式遊标的預設名稱，可直接使用。

        （1）隐式遊标由PL/SQL自動管理；

        （2）隐式遊标的預設名稱是SQL；

        （3）SELECT語句或DML操作都會長産生隐式遊标的；

        （4）隐式遊标的屬性始終是最新執行的SQL語句的。

declare
  --使用%type可以取得表中的字段類型.
  cur_id CONSTANTINFO.id%TYPE;
  cur_pos CONSTANTINFO.position%TYPE;
  cur_str CONSTANTINFO.strain%TYPE;
begin
    select id,position,strain from CONSTANTINFO WHERE id < 11; 
    loop
      --使用隐式遊标屬性，記錄集到尾，沒有記錄退出循環
      IF SQL%FOUND;
      --輸出遊标擷取的記錄集
      dbms_output.put_line(cur_id ||'-----'|| cur_pos ||'-----'|| cur_str);
      END IF;
      EXIT WHEN SQL%NOTFOUND;
    end loop;
  --遊标中使用異常處理
  EXCEPTION
      WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('沒有找到資料！');
      WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('資料過多！');
END;

    2、遊标中使用異常處理

       上述的所有SQL中都添加了異常處理的代碼。當資料庫中的記錄發生變化時使用SELECT INTO語句傳回的結果有可能不是單條記錄，這時候會出現腳本中斷并報錯的現象，是以需要使用異常處理：

--遊标中使用異常處理
     EXCEPTION
     WHEN NO_DATA_FOUND THEN
         DBMS_OUTPUT.PUT_LINE('沒有找到資料！');
     WHEN TOO_MANY_ROWS THEN
         DBMS_OUTPUT.PUT_LINE('資料過多！');

    3、隐式遊标的屬性

    （1）%ISOPEN：用于判斷遊标是否打開。HOWEVER，在隐式遊标中該屬性永遠傳回FALSE，它由ORACLE自己控制；

    （2）%FOUND：當DML操作對資料有影響的時候傳回TRUE，SELECT INTO語句傳回資料時傳回TRUE，否則反之；

    （3）%NOTFOUND：與%FOUND正好相反；

    （4）%ROWCOUNT：該屬性反映出DML操作對資料影響的行數。

ORACLE 11g 資料庫 Oracle 資料庫遊标 遊标
上一篇: oracle資料庫查詢空間大小,Oracle檢視資料庫空間使用情況
下一篇: oracle資料庫删除表日志查詢,oracle資料庫rman删除Archivelog的方法

繼續閱讀