PLSQL基礎（三）遊標

遊標有兩種:

顯示遊標，隱式遊標

 

顯示遊標是用CURSOR...IS命令定義的遊標，它可以對查詢語句(SELECT)返回的多條記錄進行處理，而隱式遊標是在執行插入(INSERT),刪除(DELETE),修改（UPDATE）和返回單條記錄的查詢（SELECT）語句時由PLSQL自動定義的。

 

顯示遊標的操作

1)開啟遊標    2）推進遊標     3）關閉遊標

 

 

聲明遊標：

DECLARE

    v_auths auths%ROWTYPE;

    v_code auths.author_code%TYPE;

    CURSOR c_auths IS

     SELECT * FROM auths WHERE author_code = v_code;

 

開啟遊標:

v_code = 'A00001';

OPEN c_auths;

 

聲明遊標

DECLARE

    CURSOR c_Auths(P_code auths.author_code%TYPE) IS

    SELECT * FROM auths WHERE author_code = P_code;

開啟遊標

OPEN c_Auths（'A00001');

 

當開啟顯示遊標後，就可以使用FETCH語句來推進遊標，返回查詢結果中的一行。沒執行完一條FETCH語句後，顯示遊標會自動指向查詢結果集的下一行。

FETCH c_Auths INTO c_Auths;

 

當整個結果集都檢索完以後，應當關閉遊標，關閉遊標用來通知PLSQL遊標操作已經結束，並且釋放遊標所佔用的資源.

CLOSE cursor_name;

 

 

遊標的屬性

遊標有四個屬性 %FOUND,%NOTFOUND,%ISOPEN,%ROWCOUNT

 

顯式遊標的推薦迴圈

DECLARE

    v_Salary  Auths.salary%TYPE;

    v_Code    Auths.author_code%TYPE;

    CURSOR c_salary IS

    SELECT salary,author_code FROM auths WHERE author_code <= 'A00006';

BEGIN

     OPEN c_salary;

    LOOP

         FETCH c_salary INTO v_Salary,v_Code;

         EXIT WHEN c_salary%NOTFOUNT;

         IF v_salary <= 2000 THEN

              UPDATE auths SET salary = salary + 50 WHERE author_code = v_Code;

         END IF;

     END LOOP;

 

     CLOSE c_salary;

   COMMIT;

END;

 

無論是使用LOOP...END LOOP語句還是使用WHILE...LOOP語句來完成遊標的推進迴圈，都必須使用OPEN,FETCH,CLOSE語句來控制遊標的開啟，推進和關閉，PLSQL還提供了一種簡單類型的迴圈，可以自動控制遊標的開啟，推進和關閉,這叫做遊標的FOR迴圈.

DECLARE

    CURSOR c_salary IS

    SELECT salary FROM auths WHERE author_code <= 'A00006';

BEGIN

--開始遊標FOR迴圈，隱含地開啟c_salary遊標

    FOR v_salary IN c_calary LOOP

          IF v_salary.salary <= 200 THEN

               UPDATE auths SET salary = salary + 50 WHERE salary = v_salary.salary;

          END IF;

    END LOOP;

    COMMIT;

END;

 

上例中v_salary沒有在塊的定義部分聲明，該變數被PLSQL編譯器隱含的聲明了,該變數的類型為c_salary%ROWTYPE,其範圍只在迴圈內部。

 

 

 

隱式遊標

顯示遊標僅僅是用來控制返回多行的SELECT語句，而隱式遊標是指向處理所有的SQL語句的環境地區的指標，隱式遊標也叫SQL遊標，與顯示遊標不同的是，SQL遊標不能通過專門的命令開啟或關閉，PLSQL隱式的開啟SQL遊標，並在它內部處理SQL語句，然後關閉它。

SQL遊標用來處理INSERT,UPDATE,DELETE以及返回一行的SELECT ...INTO語句。一個SQL遊標不管是開啟還是關閉，OPEN,FETCH,CLOSE命令都不能操作它。

SQL遊標與顯示遊標類似，也有%FOUND,%NOTFOUND,%ISOPEN,%ROWTYPE屬性，SQL遊標的屬性通常是返回執行INSERT,DELETE,UPDATE或SELECT...INTO語句時的資訊，當開啟SQL遊標之前，SQL遊標的屬性都是NULL.

%FOUND

當使用INSERT,DELETE或者UPDATE語句處理一行或多行，或執行SELECT INTO語句返回一行時，%FOUND屬性返回TRUE,否則返回FALSE.

注意:如果執行SELECT INTO語句時返回多行，則會產生TOO_MANEY_ROWS異常，並將控制權轉移到異常處理部分，%FOUND屬性並不返回TRUE.如果執行SELECT INTO語句時返回0行，會產生NO_DATE_FOUND異常，%FOUND屬性並不返回FALSE.

 

%NOTFOUND

%NOTFOUND恰與%FOUND屬性相反，當使用INSERT,DELETE或者UPDATE語句處理的函數為0時，%NOTFOUND屬性返回TRUE,否則返回FALSE.

 

%ISOPEN

因為在執行了DML語句後，Oracle會自動關閉SQL遊標，所以%ISOPEN總會FALSE.

 

%ROWCOUNT

該屬性返回執行INSERT,DELETE或UPDATE語句返回的行數,或返回執行SELECT INTO語句時查詢的行數，如果INSERT,DELETE,UPDATE或SELECT INTO語句返回的行數為0，則%ROWTYPE屬性返回0.

BEGIN  

      UPDATE auths SET entry_date_time = SYSDATE WHERE author_code = 'A00007';

     --如果UPDATE語句中修改的行不存在(SQL%NOTFOUND返回TURE)

 IF SQL%NOTFOUND THEN

      INSERT INTO auths values(......)......

END IF;

END;

我們使用SQL%ROWCOUNT完成與上例相同的功能。

BEGIN

    UPDATE auths SET entry_date_time = SYSDATE WHERE author_code = 'A00007';

     IF SQL%ROWCOUNT = 0 THEN

         INSERT INTO ......

    END IF;

END;

 

 

 

遊標變數

到目前為止前面所有顯示遊標的例子都是靜態資料指標----即遊標與一個SQL語句關聯，並且該SQL語句在編譯時間已經確定，而遊標變數是一個參考型別（REF）的變數，當程式運行時使用遊標變數可以指定不同的查詢，所以遊標變數的使用比靜態資料指標更靈活。

我們可以使用一個沒有指定結果集類型的遊標變數來指定多個不同類型的查詢.

DECLARE

--定義遊標變數類型t_CurRef,該變數類型沒有指定結果集類型，所以該遊標變數類型的變數可以返回不同的PLSQL記錄類型

        TYPE t_CurRef IS REF COUSOR

--聲明一個遊標變數類型的變數

          c_CursorRef t_CurRef;

--定義PLSQL記錄類型

         TPYE t_AuthorRec IS RECORD

        (

                AuthorCode auths.author_code%TYPE;

                Name auths.name%TYPE

        );

       

        TYPE t_ArticleRec IS RECODE

         (

                 AuthorCode article.author_code%TYPE

                 Title     article.Title%TYPE

           );

  --聲明兩個記錄類型變數

   v_Author t_AuthorRec;

   v_Ariticle  t_ArticleRec;

BEGIN

--開啟遊標變數c_CursorRef,返回t_AuthorRec類型的記錄

    OPEN  c_CursorRef FOR

     SELECT author_code,name FROM auths;

    

     FECTCH c_CursorRef INTO v_Author;

     WHILE c_CursorRef%FOUND LOOP

           ...

           FETCH c_CursorRef INTO v_Author;

     END LOOP;

   

     CLOSE c_CursorRef;

 

     OPEN c_CursorRef FOR

     SELECT  author_code,title FROM ariticle

      FETCH c_CursorRef INTO v_Article;

     WHILE c_CursorRef%FOUND LOOP

                   ...

            FETCH c_CursorRef INTO v_Article；

    END LOOP;

   

    CLOSE c_CursorRef;

END;

 

 

注意,在上例中，第一次關閉遊標變數是可以省略的，因為在第二次開啟遊標變數是，就將第一次的查詢丟失掉了.