那个捕鱼机的价格

書名： Oracle 11g從入門到精通（第2版）（軟件開發視頻大講堂）
作者名：明日科技
本章字數： 6555字
更新時間： 2020-11-28 15:54:59

5.4 PL/SQL游標

視頻講解：光盤\TM\lx\5\PL/SQL游標．mp4

游標提供了一種從表中檢索數據并進行操作的靈活手段，游標主要用在服務器上，處理由客戶端發送給服務器端的SQL語句，或是批處理、存儲過程、觸發器中的數據處理請求。游標的作用就相當于指針，通過游標PL/SQL程序可以一次處理查詢結果集中的一行，并可以對該行數據執行特定操作，從而為用戶在處理數據的過程中提供了很大方便。

在Oracle中，通過游標操作數據主要使用顯式游標和隱式游標。另外，還包括具有引用類型特性的REF游標。因篇幅限制，本書主要介紹前兩種經常使用的游標（顯式游標和隱式游標）。

5.4.1 基本原理

在PL/SQL塊中執行SELECT、INSERT、UPDATE和DELETE語句時，Oracle會在內存中為其分配上下文區（Context Area），即一個緩沖區。游標是指向該區的一個指針，或是命名一個工作區（Work Area），或是一種結構化數據類型。它為應用程序提供了一種對多行數據查詢結果集中的每一行數據進行單獨處理的方法，是設計嵌入式SQL語句的應用程序的常用編程方法。

游標分為顯式游標和隱式游標兩種。顯式游標是由用戶聲明和操作的一種游標；隱式游標是Oracle為所有數據操縱語句（包括只返回單行數據的查詢語句）自動聲明和操作的一種游標。在每個用戶會話中，可以同時打開多個游標，其數量由數據庫初始化參數文件中的OPEN CURSORS參數定義。

說明

游標在PL/SQL中作為對數據庫操作的必備元素應該熟練掌握，靈活地使用游標才能深刻地領會程序控制數據庫操作的內涵。

5.4.2 顯式游標

顯示游標是由用戶聲明和操作的一種游標，通常用于操作查詢結果集（即由SELECT語句返回的查詢結果），使用它處理數據的步驟包括：聲明游標、打開游標、讀取游標和關閉游標4個步驟。其中讀取游標可能是個反復操作的步驟，因為游標每次只能讀取一行數據，所以對于多條記錄，需要反復讀取，直到游標讀取不到數據為止，其操作過程如圖5.12所示。

圖5.12 顯示游標操作數據的過程

游標聲明需要在塊的聲明部分進行，其他的3個步驟都在執行部分或異常處理中進行。

1．聲明游標

聲明游標主要包括游標名稱和為游標提供結果集的SELECT語句。因此，在聲明游標時，必須指定游標名稱和游標所使用的SELECT語句，聲明游標的語法格式如下：

        cursor cur_name[(input_parameter1[, input_parameter2]…)]
        [return  ret_type]
        is select_ sentence;

cur_name：表示所聲明的游標名稱。

ret_type：表示執行游標操作后的返回值類型，這是一個可選項。

select_ sentence：游標所使用的SELECT語句，它為游標的反復讀取提供了結果集。

input_parameter1：作為游標的“輸入參數”，可以有多個，這是一個可選項。它指定用戶在打開游標后向游標中傳遞的值，該參數的定義和初始化格式如下：

        para_name [in] datatype [{:= | default} para_value]

其中，para_name表示參數名稱，其后面的關鍵字IN表示輸入方向，可以省略；datatype表示參數的數據類型，但數據類型不可以指定長度；para_value表示該參數的初始值或默認值，它也可以是一個表達式；para_name參數的初始值既可以以常規的方式賦值（:=），也可以使用關鍵字default初始化默認值。

與聲明變量一樣，定義游標也應該放在PL/SQL塊的declare部分，下面來看一個具體的例子。

【例5.19】聲明一個游標，用來讀取emp表中職務為銷售員（SALESMAN）的雇員信息，代碼如下。

        SQL> declare
          2   cursor cur_emp(var_job in varchar2:='SALESMAN')
          3   is select empno, ename, sal
          4     from emp
          5     where job=var_job;

在上面的代碼中，聲明了一個名稱為cur_emp的游標，并定義了一個輸入參數var_job（類型為varchar2，但不可以指定長度，如varchar2（10），否則程序報錯），該參數用來存儲雇員的職務（初始值為SALESMAN），然后使用SELECT語句檢索得到職務是銷售員的結果集，以等待游標逐行讀取它。

2．打開游標

在游標聲明完畢之后，必須打開才能使用，打開游標的語法格式如下：

        open cur_name[(para_value1[, para_value2]…)];

cur_name：要打開的游標名稱。

para_value1：指定“輸入參數”的值，根據聲明游標時的實際情況，可以是多個或一個，這是一個可選項。如果在聲明游標時定義了“輸入參數”，并初始化其值，而在此處省略“輸入參數”的值，則表示游標將使用“輸入參數”的初始值；若在此處指定“輸入參數”的值，則表示游標將使用這個指定的“參數值”。

打開游標就是執行定義的SELECT語句。執行完畢，查詢結果裝入內存，游標停在查詢結果的首部，注意并不是第一行。當打開一個游標時，會完成以下幾件事。

檢查聯編變量的取值。

根據聯編變量的取值，確定活動集。

活動集的指針指向第一行。

緊接上一個例子中的代碼，打開游標的代碼如下：

        open cur_emp('MANAGER');

上面這條語句表示打開游標cur_emp，然后給游標的“輸入參數”賦值為“MANAGER”。當然這里可以省略“（'MANAGER'）”，這樣表示“輸入參數”的值仍然使用其初始值（即SALESMAN）。

3．讀取游標

當打開一個游標之后，就可以讀取游標中的數據了，讀取游標就是逐行將結果集中的數據保存到變量中。讀取游標使用fetch…into語句，其語法格式如下。

        fetch cur_name into {variable};

cur_name：要讀取的游標名稱。

variable：一個變量列表或“記錄”變量（RECORD類型）, Oracle使用“記錄”變量來存儲游標中的數據，要比使用變量列表方便得多。

在游標中包含一個數據行指針，它用來指向當前數據行。剛剛打開游標時，指針指向結果集中的第一行，當使用FETCH…INTO語句讀取數據完畢之后，游標中的指針將自動指向下一行數據。這樣，就可以在循環結構中使用FETCH…INTO語句來讀取數據，這樣每一次循環都會從結果集中讀取一行數據，直到指針指向結果集中最后一條記錄之后為止（實際上，最后一條記錄之后是不存在的，是空的，這里只是表示遍歷完所有的數據行），這時游標的%found屬性值為false（關于游標的屬性會在5.4.4節中介紹）。

下面通過一個具體的實例來演示一下如何使用游標讀取數據。

【例5.20】聲明一個檢索emp表中雇員信息的游標，然后打開游標，并指定檢索職務是“MANAGER”的雇員信息，接著使用FETCH…INTO語句和WHILE循環語句讀取游標中的所有雇員信息，最后輸出讀取的雇員信息，代碼如下（實例位置：光盤\TM\sl\5\12）。

        SQL>set serveroutput on
        SQL>declare
          2   /*聲明游標，檢索雇員信息*/
          3   cursor cur_emp(var_job in varchar2:='SALESMAN')
          4   is select empno, ename, sal
          5     from emp
          6     where job=var_job;
          7   type record_emp is record          --聲明一個記錄類型（RECORD類型）
          8   (
          9     /*定義當前記錄的成員變量*/
         10     var_empno emp.empno%type,
         11     var_ename emp.ename%type,
         12     var_sal emp.sal%type
         13   );
         14   emp_row record_emp;                --聲明一個record_emp類型的變量
         15  begin
         16   open cur_emp('MANAGER');           --打開游標
         17   fetch cur_emp into emp_row;        --先讓指針指向結果集中的第一行，并將值保存到emp_row中
         18   while cur_emp%found loop
         19     dbms_output.put_line(emp_row.var_ename||’的編號是’||emp_row.var_empno||'，工資是’||emp_row.var_sal);
         20     fetch cur_emp into emp_row;      --讓指針指向結果集中的下一行，并將值保存到emp_row中
         21   end loop;
         22   close cur_emp;                     --關閉游標
         23  end;
         24  /

本例運行結果如圖5.13所示。

圖5.13 使用游標讀取員工信息

對于上例中的代碼，在while語句之前，首先使用fetch…into語句將游標中的指針移動到結果集中的第一行，這樣屬性%found的值就為true，從而保證while語句的循環判斷條件成立。

4．關閉游標

當所有的活動集都被檢索以后，游標就應該被關閉。PL/SQL程序將被告知對于游標的處理已經結束，與游標相關聯的資源可以被釋放了。這些資源包括用來存儲活動集的存儲空間，以及用來存儲活動集的臨時空間。

關閉游標的語法格式如下。

        close cur_name;

參數cur_name表示要關閉的游標名稱。一旦關閉了游標，也就關閉了SELECT操作，釋放了占用的內存區。如果再從游標提取數據就是非法的。這樣做會產生下面的Oracle錯誤：

        ORA-1001:Invalid CUSOR          --非法游標

或

        ORA-1002:FETCH out of sequence     --超出界限

類似地，關閉一個已經被關閉的游標也是非法的，這也會觸發ORA-1001錯誤。

例如，上個例子中，在讀取完結果集之后，使用如下的close語句關閉游標：

        SQL>close cur_emp;                    --關閉游標

5.4.3 隱式游標

在執行一個SQL語句時，Oracle會自動創建一個隱式游標。這個游標是內存中處理該語句的工作區域。隱式游標主要是處理數據操縱語句（如UPDATE、DELETE語句）的執行結果，當然特殊情況下，也可以處理SELECT語句的查詢結果。由于隱式游標也有屬性，當使用隱式游標的屬性時，需要在屬性前面加上隱式游標的默認名稱—SQL。

在實際的PL/SQL編程中，經常使用隱式游標來判斷更新數據行或刪除數據行的情況，下面就來看一個實例。

【例5.21】在SCOTT模式下，把emp表中銷售員（即SALESMAN）的工資上調20%，然后使用隱式游標sql的%rowcount屬性輸出上調工資的員工數量，代碼如下（實例位置：光盤\TM\sl\5\13）。

        SQL> set serveroutput on
        SQL> begin
          2   update emp
          3   set sal=sal*(1+0.2)
          4    where job='SALESMAN';                 --把銷售員的工資上調20%
          5   if sql%notfound then                   --若update語句沒有影響到任何一行數據
          6     dbms_output.put_line(’沒有雇員需要上調工資’);
          7   else                                   --若update語句至少影響到一行數據
          8     dbms_output.put_line(’有’||sql%rowcount||’個雇員工資上調20%');
          9   end if;
         10  end;
         11  /

本例運行結果如圖5.14所示。

圖5.14 使用隱式游標更新數據記錄

在上面的代碼中，標識符“sql”就是update語句在更新數據過程中所使用的隱式游標，它通常處于隱藏狀態，是由Oracle系統自動創建的。當需要使用隱式游標的屬性時，標識符“sql”就必須顯式地添加到屬性名稱之前。另外，無論是隱式游標還是顯式游標，它們的屬性總是反映最近的一條SQL語句的處理結果。因此在一個PL/SQL塊中出現多個SQL語句時，游標的屬性值只能反映出緊挨著它的上一條SQL語句的處理結果。

5.4.4 游標的屬性

無論是顯式游標還是隱式游標，都具有%found、%notfound、%rowcount和%isopen4個屬性，通過這4個屬性可以獲知SQL語句的執行結果以及該游標的狀態信息。它們描述與游標操作相關的DML語句的執行情況。游標屬性只能用在PL/SQL的流程控制語句內，而不能用在SQL語句內。下面對這4個屬性的功能進行講解。

%found：布爾型屬性，如果SQL語句至少影響到一行數據，則該屬性為true，否則為false。

%notfound：布爾型屬性，與%found屬性的功能相反。

%rowcount：數字型屬性，返回受SQL語句影響的行數。

%isopen：布爾型屬性，當游標已經打開時返回true，游標關閉時則為false。

下面對游標的屬性進行具體介紹。

1．是否找到游標（%FOUND）

該屬性表示當前游標是否指向有效一行，若是則值為TRUE，否則值為FALSE。檢查此屬性可以判斷是否結束游標使用。

【例5.22】使用%FOUND，代碼如下。

        SQL>open cur_emp;                                --打開游標
         2   fetch cur_emp into var_ename, var_job;      --將第一行數據放入變量中，游標后移
        3   loop
        4    exit when not cur_em%found;                 --使用了%FOUND屬性
        5   end loop;

在隱式游標中此屬性的引用方法是SQL %FOUND。

【例5.23】使用SQL %FOUND，代碼如下。

        SQL>delete from emp where empno=emp_id;    --emp_id為一個有值變量
         2   if SQL %FOUND then                   --如果刪除成功則寫入SUCCESS表中該行員工編號
         3    insert into success values(empno);
         4   else                                 --不成功則寫入FAIL表中該行員工編號
         5    insert into fail values(empno);
         6  end if;

2．是否沒找到游標（%NOTFOUND）

該屬性與%FOUND屬性相類似，但其值恰好相反。

【例5.24】使用%NOTFOUND，代碼如下。

        SQL>open cur_emp;                                --打開游標
         2   fetch cur_emp into var_ename, var_job;      --將第一行數據放入變量中，游標后移
         3   loop
         4    exit when cur_em%notfound;                 --使用了%NOTFOUND屬性
         5   end loop;

在隱式游標中此屬性的引用方法是SQL %NOTFOUND。

【例5.25】使用SQL %NOTFOUND，代碼如下。

        SQL>delete from emp where empno=emp_id;    --emp_id為一個有值變量
         2   if SQL %NOTFOUND then                --如果刪除不成功則寫入FAIL表中該行員工編號
         3    insert into fail values(empno);
         4   else                                 --刪除成功則寫入FAIL表中該行員工編號
         5    insert into success values(empno);
         6  end if;

3．游標行數（%ROWCOUNT）

該屬性記錄了游標抽取過的記錄行數，也可以理解為當前游標所在的行號。這個屬性在循環判斷中也很有用，使得不必抽取所有記錄行就可以中斷游標操作。

【例5.26】使用%ROWCOUNT，代碼如下。

        SQL> loop
         2    fetch cur_emp into var_empno, var_ename, var_job;
         3    exit when cur_emp%ROWCOUNT=10;   --只抽取10條記錄
         4    …
         5   end loop;

還可以用FOR語句控制游標的循環，系統隱含地定義了一個數據類型為ROWCOUNT的記錄，作為循環計數器，并將隱式地打開和關閉游標。

4．游標是否打開（%ISOPEN）

該屬性表示游標是否處于打開狀態。在實際應用中，使用一個游標前第一步往往是檢查它的%ISOPEN屬性，看其是否已打開，若沒有，要打開游標再向下操作。這是防止運行過程中出錯的必備一步。

【例5.27】使用%ISOPEN，代碼如下。

        SQL> IF cur_emp%ISOPEN TNEH
         2    FETCH cur_emp INTO var_empno, var_ename, var_job;
         3   ELSE
         4    OPEN cur_emp;
         5   END IF;

在隱式游標中此屬性的引用方法是SQL%ISOPEN。隱式游標中SQL%ISOPEN屬性總為TRUE，因此在隱式游標使用中不用打開和關閉游標，也不用檢查其打開狀態。

5．參數化游標

在定義游標時，可以帶上參數，使得在使用游標時，根據參數不同所選中的數據行也不同，達到動態使用的目的。下面通過一個具體的實例來查看如何使用游標的屬性。以%found為例，來判斷檢索結果集中是否有數據行。

【例5.28】聲明一個游標，用于檢索指定員工編號的雇員信息，然后使用游標的%found屬性來判斷是否檢索到指定員工編號的雇員信息，代碼如下（實例位置：光盤\TM\sl\5\14）。

        SQL> set serveroutput on
        SQL> declare
          2   var_ename varchar2(50);                    --聲明變量，用來存儲雇員名稱
          3   var_job varchar2(50);                      --聲明變量，用來存儲雇員的職務
          4   /*聲明游標，檢索指定員工編號的雇員信息*/
          5   cursor cur_emp                             --定義游標，檢索指定編號的記錄信息
          6   is select ename, job
          7     from emp
          8     where empno=7499;
          9  begin
         10   open cur_emp;                              --打開游標
         11   fetch cur_emp into var_ename, var_job;     --讀取游標，并存儲雇員名和職務
         12   if cur_emp%found then                      --若檢索到數據記錄，則輸出雇員信息
         13     dbms_output.put_line(’編號是7499的雇員名稱為：'||var_ename||'，職務是：'||var_job);
         14   else
         15     dbms_output.put_line(’無數據記錄’);     --提示無記錄信息
         16   end if;
         17  end;
         18  /

本例運行結果如圖5.15所示。

圖5.15 使用游標的%found屬性判斷是否存在檢索記錄

在上面的例子中，若檢索到編號為7499的雇員信息，則select語句會返回一行數據，這時游標cur_emp的%found屬性值為true；若檢索不到編號為7499的雇員信息，則select語句無數據行返回，這時游標cur_emp的%found屬性值為false。

使用顯式游標時，需注意以下事項：

使用前須用%ISOPEN檢查其打開狀態，只有此值為TRUE的游標才可使用，否則要先將游標打開。

在使用游標過程中，每次都要用%FOUND或%NOTFOUND屬性檢查是否返回成功，即是否還有要操作的行。

將游標中行取至變量組中時，對應變量個數和數據類型必須完全一致。

使用完游標必須將其關閉，以釋放相應內存資源。

5.4.5 游標變量

如同常量和變量的區別一樣，前面所講的游標都是與一個SQL語句相關聯，并且在編譯該塊的時候此語句已經是可知的，是靜態的，而游標變量可以在運行時與不同的語句關聯，是動態的。游標變量被用于處理多行的查詢結果集。在同一個PL/SQL塊中，游標變量不同于特定的查詢綁定，而是在打開游標時才能確定所對應的查詢。因此，游標變量可以一次對應多個查詢。

使用游標變量之前，必須先聲明，然后在運行時必須為其分配存儲空間，因為游標變量是REF類型的變量，類似于高級語句中的指針。

1．聲明游標變量

游標變量是一種引用類型。當程序運行時，它們可以指向不同的存儲單元。如果要使用引用類型，首先要聲明該變量，然后相應的存儲單元必須被分配。PL/SQL中的引用類型通過下述的語法進行聲明：

        REF type

其中，type是已經被定義的類型。REF關鍵字指明新的類型必須是一個指向經過定義的類型的指針。因此，游標可以使用的類型就是REF CURSOR。

定義一個游標變量類型的完整語句如下：

        TYPE <類型名> IS REF CURSOR
        RETURN <返回類型>

其中，<類型名>是新的引用類型的名字，而<返回類型>是一個記錄類型，它指明了最終由游標變量返回的選擇列表的類型。

游標變量的返回類型必須是一個記錄類型。它可以被顯式聲明為一個用戶定義的記錄，或者隱式使用%ROWTYPE進行聲明。在定義了引用類型以后，就可以聲明該變量了。

【例5.29】在聲明部分，給出用于游標變量的不同游標，代碼如下。

        SQL> set serveroutput on
        SQL> DECLARE
          2   TYPE t_StudentRef IS REF CURSOR                    --定義使用%ROWTYPE
          3   RETURN STUDENTS%ROWTYPE;
          4   TYPE t_AbstractstudentsRecord IS RECORD(           --定義新的記錄類型
          5     sname STUDENTS.sname%TYPE,
          6     sex STUDENTS.sex%type);
          7   v_AbstractStudentsRecord t_AbstractStudentsRecord;
          8   TYPE t_AbstractStudentsRef IS REF CURSOR          --使用記錄類型的游標變量
          9   RETURN t_AbstractStudentsRecord;
         10   TYPE t_NameRef2 IS REF CURSOR                     --另一類型定義
         11   RETURN v_AbstractStudentsRecord%TYPE;
         12   v_StudentCV t_StudentsRef;                        --聲明上述類型的游標變量
         13   v_AbstractStudentCV t_AbstractStudentsRef;

上例中極少的游標變量是受限的，它的返回類型只能是特定類型。而在PL/SQL語句中，還有一種非受限游標變量，它在聲明的時候沒有RETURN子句。一個非受限游標變量可以為任何查詢打開。

【例5.30】定義游標變量，代碼如下。

        SQL> DECLARE
          2              --定義非受限游標變量
          3   TYPE t_FlexibleRefIS REF CURSOR;
          4              --游標變量
          5   V_CURSORVar t_FlexibleRef;

2．打開游標變量

如果要將一個游標變量與一個特定的SELECT語句相關聯，需要使用OPEN FOR語句，其語法格式是：

        OPEN<游標變量>FOR<SELECT語句>;

如果游標變量是受限的，則SELECT語句的返回類型必須與游標所限的記錄類型匹配，如果不匹配，Oracle會返回錯誤ORA_6504。

【例5.31】打開游標變量v_StudentSCV，代碼如下。

        SQL> DECLARE
          2   TYPE t_StudentRef IS REF CURSOR             --定義使用%ROWTYPE
          3   RETURN STUDENTS%ROWTYPE;
          4   v_StudentSCV t_StudentRef;                   --定義新的記錄類型
          5  BEGIN
          6   OPEN v_StudentSCV FOR
          7     SELECT*FROM STUDENTS; ;
          8  END;

3．關閉游標變量

游標變量的關閉和靜態游標的關閉類似，都是使用CLOSE語句，這會釋放查詢所使用的空間。關閉已經關閉的游標變量是非法的。

5.4.6 通過for語句循環游標

在使用隱式游標或顯式游標處理具有多行數據的結果集時，用戶可以配合使用FOR語句來完成。在使用FOR語句遍歷游標中的數據時，可以把它的計時器看做是一個自動的RECORD類型的變量。

（1）在FOR語句中遍歷隱式游標中的數據時，通常在關鍵字in的后面提供由SELECT語句檢索的結果集，在檢索結果集的過程中，Oracle系統會自動提供一個隱式的游標SQL。

【例5.32】使用隱式游標和FOR語句檢索出職務是銷售員（SALESMAN）的雇員信息并輸出，代碼如下（實例位置：光盤\TM\sl\5\15）。

        SQL> set serveroutput on
        SQL> begin
          2   for emp_record in(select empno, ename, sal from emp where job='SALESMAN') --遍歷隱式游標中的記錄
          3   loop
          4     dbms_output.put(’雇員編號：'||emp_record.empno);                 --輸出雇員編號
          5     dbms_output.put('；雇員名稱：'||emp_record.ename);                --輸出雇員名稱
          6     dbms_output.put_line('；雇員工資：'||emp_record.sal);             --輸出雇員工資
          7   end loop;
          8  end;
          9  /

本例運行效果如圖5.16所示。

圖5.16 使用隱式游標處理多行記錄

（2）在FOR語句中遍歷顯式游標中的數據時，通常在關鍵字IN的后面提供游標的名稱，其語法格式如下：

        for var_auto_record in cur_name loop
          plsqlsentence;
        end loop;

var_auto_record：自動的RECORD類型的變量，可以是任意合法的變量名稱。

cur_name：指定的游標名稱。

plsqlsentence:PL/SQL語句。

【例5.33】使用顯式游標和FOR語句檢索出部門編號是30的雇員信息并輸出，代碼如下（實例位置：光盤\TM\sl\5\16）。

        SQL> set serveroutput on
        SQL> declare
          2   cursor cur_emp is
          3   select*from emp
          4   where deptno=30;                                        --檢索部門編號為30的雇員信息
          5  begin
          6   for emp_record in cur_emp                               --遍歷雇員信息
          7   loop
          8     dbms_output.put(’雇員編號：'||emp_record.empno);      --輸出雇員編號
          9     dbms_output.put('；雇員名稱：'||emp_record.ename);     --輸出雇員名稱
         10     dbms_output.put_line('；雇員職務：'||emp_record.job);  --輸出雇員職務
         11   end loop;
         12  end;
         13  /

本例運行結果如圖5.17所示。

圖5.17 使用顯式游標處理多行記錄

說明

在使用游標（包括顯式和隱式）的FOR循環中，可以聲明游標，但不用進行打開游標、讀取游標和關閉游標等操作，這些由Oracle系統自動完成。

官术网_书友最值得收藏!

Oracle 11g從入門到精通（第2版） （軟件開發視頻大講堂）