[轉載]oracle遊標概念講解

標籤：

原文URL:http://www.2cto.com/database/201203/122387.html

ORACLE遊標概念講解

什麼是遊標？  ①從表中檢索出結果集，從中每次指向一條記錄進行互動的機制。      ②關聯式資料庫中的操作是在完整的行集合上執行的。   由SELECT 語句返回的行集合包括滿足該語句的WHERE 子句所列條件的所有行。由該語句返回完整的行集合叫做結果集。      應用程式，尤其是互動和線上應用程式，把完整的結果集作為一個單元處理並不總是有效。      這些應用程式需要一種機制來一次處理一行或連續的幾行。而遊標是對提供這一機制的結果集的擴充。        遊標是通過遊標庫來實現的。遊標庫是常常作為資料庫系統或資料訪問API 的一部分而得以實現的軟體，      用來管理從資料來源返回的資料的屬性（結果集）。這些屬性包括並發管理、在結果集中的位置、返回的行數，      以及是否能夠在結果集中向前和/或向後移動（可滾動性）。        遊標跟蹤結果集中的位置，並允許對結果集逐行執行多個操作，在這個過程中可能返回至原始表，也可能不返回至原始表。      換句話說，遊標從概念上講基於資料庫的表返回結果集。      由於它指示結果集中的當前位置 ，就像電腦螢幕上的游標指示當前位置一樣，“遊標”由此得名。   2，遊標有什麼作用？  ①指定結果集中特定行的位置。  ②基於當前的結果集位置檢索一行或連續的幾行。  ③在結果集的當前位置修改行中的資料。  ④對其他使用者所做的資料更改定義不同的敏感性層級。         ⑤可以以

編程

的方式訪問

資料庫

。      3，為什麼避免使用遊標？  ①在建立遊標時，最需要考慮的事情是，“是否有辦法避免使用遊標？”   因為遊標的效率較差，如果遊標操作的資料超過1萬行，那麼就應該改寫；   如果使用了遊標，就要盡量避免在遊標迴圈中再進行表串連的操作。   4，

Oracle

遊標的類型？  ①靜態資料指標：結果集已經確實（靜態定義）的遊標。分為隱式和顯示遊標。   ⑴隱式遊標：所有DML語句為隱式遊標，通過隱式遊標屬性可以擷取SQL語句資訊。   ⑵顯示遊標：使用者顯示聲明的遊標，即指定結果集。當查詢返回結果超過一行時，就需要一個顯式遊標。  ②REF遊標：動態關連接果集的臨時對象。   5，Oracle遊標的狀態有哪些，怎麼使用遊標屬性？  ①遊標的狀態是通過屬性來表示。   %Found ：Fetch語句（擷取記錄）執行情況True or False。   %NotFound : 最後一條記錄是否提取出True or False。   %ISOpen ： 遊標是否開啟True or False。   %RowCount ：遊標當前提取的行數 。  ②使用遊標的屬性。   例子：/* conn scott/tiger */   Begin    Update emp Set  SAL = SAL + 0.1  Where JOB = ‘CLERK‘;    If  SQL%Found  Then     DBMS_OUTPUT.PUT_LINE(‘已經更新！‘);    Else     DBMS_OUTPUT.PUT_LINE(‘更新失敗！‘);    End  If；   End；   6，如何使用顯示遊標,？如何遍曆迴圈遊標？   ①使用顯示遊標    ⑴聲明遊標：劃分儲存地區，注意此時並沒有執行Select 語句。     CURSOR 遊標名( 參數 列表)   [傳回值類型]   IS   Select 語句；    ⑵開啟遊標：執行Select 語句，獲得結果集儲存到遊標中，此時遊標指向結果集頭， 而不是第一條記錄。     Open 遊標名( 參數 列表);    ⑶擷取記錄：移動遊標取一條記錄     Fetch  遊標名InTo  臨時記錄或屬性類型變數；    ⑷關閉遊標：將遊標放入緩衝池中，沒有完全釋放資源。可重新開啟。     Close  遊標名；  ②遍曆迴圈遊標   ⑴For 迴圈遊標    迴圈遊標隱式開啟遊標，自動滾動擷取一條記錄，並自動建立臨時記錄類型變數儲存記錄。處理完後自動關閉遊標。      For  變數名  In  遊標名       Loop       資料處理語句；      End Loop；    ⑵Loop迴圈遊標      。。。     Loop      Fatch  遊標名InTo  臨時記錄或屬性類型變數；      Exit  When   遊標名%NotFound；     End   Loop；      。。。   例子1：   /* conn scott/tiger */    Declare      Cursor myCur is select empno,ename,sal from emp;      vna varchar2(10);      vno number(4);      vsal number(7,2);   Begin      open myCur;      fetch myCur into vno,vna,vsal;      dbms_output.put_line(vno||‘    ‘||vna||‘    ‘||vsal);      close myCur;   End;   /    例子2：使用loop遍曆遊標。  /* conn scott/tiger */   Declare      Cursor myCur is select ename,job,sal,empno from emp;      varE myCur%rowType;   Begin      if myCur%isopen = false then         open myCur;        dbms_output.put_line(‘Opening...‘);      end if;      loop         fetch myCur into varE;         exit when myCur%notfound;         dbms_output.put_line(myCur%rowCount||‘    ‘||vare.empno||‘    ‘||vare.ename||‘    ‘||vare.sal);      end loop;      if myCur%isopen then         Close myCur;         dbms_output.put_line(‘Closing...‘);      end if;   End;   /     例子3：使用For迴圈遍曆遊標,   /* conn scott/tiger */   Declare      Cursor myCur is select * from emp;   Begin      for varA in myCur       loop          dbms_output.put_line(myCur%rowCount||‘    ‘||varA.empno||‘    ‘||varA.ename||‘  ‘||varA.sal);       end loop;   End;   /   7，怎樣更新和刪除顯示遊標中的記錄？  ①UPDATE或DELETE語句中的WHERE CURRENT OF子串專門處理要執行UPDATE或DELETE操作的表中取出的最近的資料。   要使用這個方法，在聲明遊標時必須使用FOR UPDATE子串，當對話使用FOR UPDATE子串開啟一個遊標時，   所有返回集中的資料行都將處於行級（ROW-LEVEL)獨佔式鎖定，其他對象只能查詢這些資料行，   不能進行UPDATE、DELETE或SELECT...FOR UPDATE操作。    在多表查詢中，使用OF子句來鎖定特定的表,如果忽略了OF子句，那麼所有表中選擇的資料行都將被鎖定。    如果這些資料行已經被其他會話鎖定，那麼正常情況下ORACLE將等待，直到資料行解鎖。  ②使用更新或刪除：   ⑴聲明更新或刪除顯示遊標：    Cursor 遊標名IS  SELECT 語句   For Update [ Of  更新列列名]；    Cursor 遊標名IS  SELECT 語句   For Delete [ Of  更新列列名]；   ⑵使用顯示遊標目前記錄來更新或刪除：    Update  表名   SET   更新語句  Where   Current  Of   遊標名；    Delete  From  表名   Where   Current  Of   遊標名；      例子1:更新顯示遊標記錄    /*conn scott/tiger*/    Declare      Cursor myCur is select job from emp for update;        vjob empa.job%type;        rsal empa.sal%type;     Begin        open myCur;        loop           fetch myCur into vjob;           exit when myCur%notFound;           case  (vjob)              when ‘ANALYST‘ then  rsal := 0.1;             when  ‘CLERK‘ then  rsal := 0.2;              when  ‘MANAGER‘ then  rsal := 0.3;              else                rsal := 0.5;           end case;         update emp set sal = sal + rsal where current of myCur;        end loop;     End;     /     例子2：刪除顯示遊標記錄     /*conn scott/tiger     Crate table  empa  Select * from scott.emp;     */     Declare       Cursor MyCursor  Select   JOB  From  empa  For  Update;       vSal   emp.Sal%TYPE;     Begin       Loop        Fetch  MyCursor  InTo  vSal;        Exit  When  MyCursor%NotFound;        If   vSal < 800 Then         Delete  From empa  Where  Cursor  Of   MyCursor;        End  If;         End    Loop;     End;/ 8，什麼是帶參數的顯示遊標？  ①與過程和函數相似，可以將參數傳遞給遊標並在查詢中使用。   參數只定義資料類型，沒有大小(所有Oracle中的形參只定義資料類型，不指定大小)。   與過程不同的是，遊標只能接受傳遞的值，而不能傳回值。 　  可以給參數設定一個預設值，當沒有參數值傳遞給遊標時，就使用預設值。   遊標中定義的參數只是一個預留位置，在別處引用該參數不一定可靠。  ②使用帶參數的顯示遊標   ⑴聲明帶參數的顯示遊標：    CURSOR 遊標名  [(parameter[,parameter],...)]    IS   Select語句；;       參數形式：1，參數名   資料類型          2，參數名   資料類型  DEFAULT  預設值           例子：     /*conn scott/tiger     Crate table  empa  Select * from scott.emp;     */     Declare       Cursor MyCursor(pSal  Number  Default   800)  Select   JOB  From  empa Where  SAL >  pSal ;       varA  MyCursor%ROWTYPE;     Begin       Loop        Fetch  MyCursor  InTo  varA;        Exit  When  MyCursor%NotFound;        DBMS_OUTPUT.PUT_LINE(MyCursor%RowCount||‘    ‘||varA.empno||‘    ‘||varA.ename||‘  ‘||varA.sal);        End    Loop;     End;/       REF CURSOR   1，什麼是REF遊標 ？  動態關連接果集的臨時對象。即在啟動並執行時候動態決定執行查詢。   2，REF 遊標 有什麼作用？  實現在程式間傳遞結果集的功能，利用REF CURSOR也可以實現BULK SQL，從而提高SQL效能。   3，靜態資料指標和REF 遊標的區別是什嗎？  ①靜態資料指標是靜態定義，REF 遊標是動態關聯；  ②使用REF 遊標需REF 遊標變數。  ③REF 遊標能做為參數進行傳遞，而靜態資料指標是不可能的。   4，什麼是REF 遊標變數？  REF遊標變數是一種 引用REF遊標類型  的變數，指向動態關聯的結果集。   5，怎麼使用  REF遊標 ？  ①聲明REF 遊標類型，確定REF 遊標類型；   ⑴強型別REF遊標：指定retrun type，REF 遊標變數的類型必須和return type一致。    文法：Type   REF遊標名   IS   Ref Cursor Return  結果集返回記錄類型；   ⑵弱類型REF遊標：不指定return type，能和任何類型的CURSOR變數匹配，用於擷取任何結果集。    文法：Type   REF遊標名   IS   Ref Cursor；    ②聲明Ref 遊標類型變數；   文法：變數名  已聲明Ref 遊標類型；    ③開啟REF遊標，關連接果集 ；   文法：Open   Ref 遊標類型變數   For   查詢語句返回結果集；    ④擷取記錄，操作記錄；   文法：Fatch    REF遊標名InTo   臨時記錄類型變數或屬性類型變數列表；    ⑤關閉遊標，完全釋放資源；   文法：Close   REF遊標名；    例子：強型別REF遊標  /*conn scott/tiger*/  Declare   Type MyRefCurA IS  REF CURSOR RETURN emp%RowType;   Type MyRefCurB IS  REF CURSOR RETURN emp.ename%Type;   vRefCurA  MyRefCurA;   vRefCurB  MyRefCurB;   vTempA  vRefCurA%RowType;   vTempB  vRefCurB.ename%Type;    Begin   Open  vRefCurA  For Select  *  from   emp   Where  SAL > 2000;   Loop    Fatch  vRefCurA InTo  vTempA;    Exit  When  vRefCurA%NotFound;    DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||‘  ‘|| vTempA.eno||‘  ‘||vTempA.ename ||‘  ‘||vTempA.sal)   End Loop;   Close vRefCurA;     DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------------------------------------------------------‘);     Open  vRefCurB  For Select  ename  from   emp   Where  SAL > 2000;   Loop    Fatch  vRefCurB InTo  vTempB;    Exit  When  vRefCurB%NotFound;    DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||‘  ‘||vTempB)   End Loop;   Close vRefCurB;     DBMS_OUTPUT.PUT_LINE(‘-------------------------------------------------------------------------------------------------------‘);       Open  vRefCurA  For Select  *  from   emp   Where  JOB = ‘CLERK‘;   Loop    Fatch  vRefCurA InTo  vTempA;    Exit  When  vRefCurA%NotFound;    DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||‘  ‘|| vTempA.eno||‘  ‘||vTempA.ename ||‘  ‘||vTempA.sal)   End Loop;   Close vRefCurA;  End;    例子：弱類型REF遊標  /*conn scott/tiger*/  Declare   Type MyRefCur  IS  Ref  Cursor;   vRefCur MyRefCur;   vtemp  vRefCur%RowType;  Begin   Case(&n)    When  1 Then Open vRefCur  For Select   *   from emp;    When  2 Then Open vRefCur  For Select   *   from dept;    Else     Open vRefCur  For Select   eno,  ename  from emp Where JOB = ‘CLERK‘;   End Case;   Close  vRefCur;  End;

 

[轉載]oracle遊標概念講解