oracle遊標__oracle

 

1，什麼是遊標。
 ①從表中檢索出結果集，從中每次指向一條記錄進行互動的機制。
    
 ②關聯式資料庫中的操作是在完整的行集合上執行的。
  由 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;