【翻譯】Oracle游標詳細說明

   這篇文章是選取官方文檔的部分章節翻譯過來的，去除了原文中的例子，并在結尾補充了幾個例子。有興趣的朋友可以點擊文章末尾的連接去閱讀官方文檔。

一、游標的定義

  游標是指向專用SQL區域的指針，該區域存儲有關處理特定SELECT或DML語句的信息。本章解釋的游標是會話游標。會話游標存在于會話中直到會話結束。由PL/SQL創建和管理的游標稱為隱式游標，由用戶創建和管理的游標稱為顯式游標。你可以通過游標的屬性獲取任意會話游標的相關信息。通過查詢動態性能視圖V$OPEN_CURSOR，可以列出當前已經打開和解析的游標。

二、隱式游標

  每次執行select 或DML操作時，都會生成隱式游標。用戶不能控制隱式游標，但可以通過游標的屬性獲取游標的信息。

  隱式游標屬性的語法是SQLattribute（因此隱式游標也通常叫做SQL游標）。SQLattribute總是指向最近運行的select或DML語句。如果最近沒有運行這樣的語句，則屬性返回NULL。

  當語句運行結束時，隱式游標關閉，但是它的屬性會被保留到另一條select或DML語句執行。最近的語句運行的結果可能屬于不同的值，如果要保留屬性值供以后使用，可以將其保存在本地變量中。另外，其他的操作，如子程序的調用，可能在你測試之前改變屬性值。

隱式游標的屬性有以下幾種：

三、顯式游標

  顯式游標由用戶創建并管理。用戶在使用顯式游標之前必須聲明和定義游標名稱并將其與查詢相關聯（通常，查詢會返回多行）。然后用戶可以通過以下方式處理查詢結果集：

  使用open命令打開游標，使用fetch獲取行，使用close關閉游標；

  與隱式游標不同，可以通過名稱引用顯示游標或游標變量。因此，顯式游標或游標變量稱為命名游標。

3.1 聲明和定義顯式游標

  用戶可以首先聲明一個顯式游標，然后在同一個塊，子程序或包中定義它；或者同時聲明和定義它。

只聲明游標，具有如下的語法：

CURSOR cursor_name [ parameter_list ] RETURN return_type;

聲明游標并定義游標的語法：

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]  IS select_statement;

聲明顯式游標的例子：

DECLARE  CURSOR c1 RETURN departments%ROWTYPE;    -- 聲明C1  

CURSOR c2 IS SELECT employee_id, job_id, salary -- 聲明并且定義 c2 

FROM employees    

WHERE salary > 2000;  

CURSOR c1 RETURN departments%ROWTYPE IS -- 定義 c1,-- 重復return type 

SELECT * FROM departments 

WHERE department_id = 110;  

CURSOR c3 RETURN locations%ROWTYPE;      -- 聲明c3  

CURSOR c3 IS                     -- 定義c3, -- 忽略 return type      

SELECT * FROM locations                  

WHERE country_id = 'JP'; 

BEGIN  NULL; 

END; /

3.2 打開和關閉游標

  聲明和定義顯式游標后，可以使用OPEN語句打開它；您使用CLOSE語句關閉一個打開的顯式游標，從而允許重用其資源。關閉游標后，無法從其結果集中提取記錄或引用其屬性。

  用戶可以重新打開一個已經關閉的游標。游標在重新打開之間必須要被關閉。否則，PL/SQL會引發預定義異常CURSOR_ALREADY_OPEN.

3.3 使用顯式游標獲取數據

  打開一個顯式游標后，可以使用FETCH語句獲取結果集。返回一行的FETCH語句的基本語法是：

    FETCH cursor_name INTO into_clause

  into_clause是一個變量列表或單個記錄變量。 對于查詢返回的每個列，變量列表或記錄必須具有對應的類型兼容變量或字段。％TYPE和％ROWTYPE屬性可用于聲明變量和記錄以在FETCH語句中使用。FETCH語句檢索結果集的當前行，將該行的列值存儲到變量或記錄中，并將光標前移到下一行。通常，在LOOP語句中使用FETCH語句，當FETCH語句用完行時退出。 要檢測此退出條件，請使用游標屬性％NOTFOUND。因為當FETCH語句不返回任何行時，PL/SQL不會引發異常。

3.4 顯式游標查詢中的變量

  顯式游標查詢可以引用其作用域中的任何變量。 當打開顯式游標時，PL / SQL將評估查詢中的任何變量，并在標識結果集時使用這些值。 稍后更改變量的值不會更改結果集。【定義游標時，游標中結果集就已經確定，如要改變結果集，必須關閉游標并重新打開游標】

3.5 當顯式游標列需要別名時

  當顯式游標查詢包含虛擬列（表達式）時，如果滿足以下任一條件，則該列必須具有別名：

  1、使用游標讀取使用％ROWTYPE聲明的記錄。

  2、需要引用虛擬列

3.6 接受參數的顯示游標

  用戶可以創建具有形式參數的顯式游標，然后在每次打開游標時將不同的實際參數傳遞到游標。 在游標查詢中，可以在可以使用常量的任何位置使用正式的光標參數。在光標查詢之外，用戶不能引用正式的光標參數。

3.7 顯示游標的屬性

%ISOPEN  TRUE：游標打開狀態；FALSE：其他狀態。

%FOUND   NULL:顯示游標打開但是還沒有獲取第一行

TRUE:從顯式游標的最近一次提取返回了一行

FALSE：其他情況

%NOTFOUND NULL:顯示游標打開但是還沒有獲取第一行

FALSE：從顯式游標的最近一次提取返回了一行

TRUE:其他情況

%ROWCOUNT：在顯式游標打開后但在第一次提取之前為零；否則就獲取行數。

四、例子

4.1 獲取tabs中表名稱的兩個例子

方法一：將結果集裝在記錄中

declare

--表行的記錄變量

cursor mycur is select * from tabs;

cur_result tabs%rowtype;

begin

open mycur;--打開游標

loop

fetch mycur into cur_result;--獲取數據

exit when mycur%notfound;--退出循環

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--關閉游標

end;

方法二：將結果集裝在變量中

declare

--變量

cursor mycur is select table_name from tabs;

v_tablename tabs.table_name%type;

begin

open mycur;--打開游標

loop

fetch mycur into v_tablename;--獲取數據

exit when mycur%notfound;--退出循環

dbms_output.put_line('The table name is '||v_tablename);

end loop;

close mycur;--關閉游標

end;

方法三：將結果集裝在游標類型的row%type中

declare

--表行的記錄變量

cursor mycur is select table_name from tabs;

cur_result mycur%rowtype;

begin

open mycur;--打開游標

loop

fetch mycur into cur_result;--獲取數據

exit when mycur%notfound;--退出循環

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--關閉游標

end;

4.2 動態游標

靜態游標的聲明與定義必須在同一個塊中執行，動態游標可以在begin之前聲明游標，在打開游標時定義sql語句，即可以使用open cursor_name for sql_statement的形式打開游標。sql_statement可以是靜態的SQL語句，也可以是動態的SQL語句。

例一：靜態的SQL語句

declare

--表行的記錄變量

type cursor_type is ref cursor;

mycur cursor_type;

cur_result tabs%rowtype;

begin

open mycur for select * from tabs;--打開游標

loop

fetch mycur into cur_result;--獲取數據

exit when mycur%notfound;--退出循環

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--關閉游標

end;

例二：動態的SQL語句

declare

type cursor_type is ref cursor;

mycur cursor_type;

cur_result tabs%rowtype;

v_sql varchar2(1000);

begin

v_sql:='select * from tabs';

open mycur for v_sql;--打開游標

loop

fetch mycur into cur_result;--獲取數據

exit when mycur%notfound;--退出循環

dbms_output.put_line('The table name is '||cur_result.table_name);

end loop;

close mycur;--關閉游標

end;

五、其他說明

5.1 Oracle游標的官方文檔

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#LNPLS00602