當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

Oracle 11g Release 1 (11.1) 游标——显式游标

發布時間：2025/4/16 编程问答 22 豆豆

生活随笔收集整理的這篇文章主要介紹了 Oracle 11g Release 1 (11.1) 游标——显式游标小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/static.htm#CIHCAHJA

本文內容

聲明一個游標
打開一個游標
用游標獲取一個（Fetch）數據
用游標獲取全部（Fetch Bulk）數據
關閉一個游標
顯式游標的屬性

當需要準確控制查詢時，可以在任何 PL/SQL 塊（block）、子程序（subprogram）或包（package）的聲明部分里聲明一個顯式游標。

可以使用三個語句來控制游標：OPEN、FETCH 和 CLOSE。

首先，用 OPEN 語句初始化游標，確定結果集；
之后，重復執行 FETCH 語句，知道所有數據行已經被檢索，或使用 BULK COLLECT 子句一次性獲取所有數據；
當最后一行數據已經被處理，可以用 CLOSE 語句釋放游標。

該技術需要比其他技術，如 SQL 游標 FOR LOOP，更多的代碼。但它的優點是靈活。你可以：

通過聲明和打開多個游標，來并行處理很多查詢
在一個循環迭代中處理多個行，掠過行，把處理分割到多個循環

本文示例使用的表，位于 Oracle OE Schema 示例。另外，示例在 SQL*PLUS 下運行。示例最后 "/" 符號可讓 SQL*PLUS 編譯代碼；而在 PL/SQL Developer 下則可有可無。

聲明一個游標

在語句中引用游標前，必須聲明游標。給出游標的名稱，并與指定查詢關聯。另外，也可以為游標聲明一個返回類型，如 table_name%ROWTYPE。也可以在 WHERE 子句指定游標參數（不是本地變量）。游標參數可以有默認值。

示例 1：演示聲明一個游標。

DECLARE my_emp_id NUMBER(6); -- variable for employee_id my_job_id VARCHAR2(10); -- variable for job_id my_sal NUMBER(8, 2); -- variable for salary CURSOR c1 IS SELECT employee_id, job_id, salary FROM employees WHERE salary > 2000; my_dept departments%ROWTYPE; -- variable for departments row CURSOR c2 RETURN departments%ROWTYPE IS SELECT * FROM departments WHERE department_id = 110; BEGIN NULL; END; /

游標不是 PL/SQL 變量，因此，不能分配一個值給游標，或是在表達式中使用游標。
游標和變量遵循相同的作用域規則。
游標可以帶參數，可以出現在與其相關的查詢中。游標的形參（formal parameters ）必須是 IN 參數（輸入參數）；參數為查詢提供值，但不能從查詢中返回任何值。不能在游標參數上強加 NOT NULL 約束。

示例 2：演示游標參數。你可以初始化游標參數為默認值。可以傳遞不同數量的實參（actual parameters ）給游標。也可以添加新的行參，而無需改變現有的游標引用。

DECLARE CURSOR c1(low NUMBER DEFAULT 0, high NUMBER DEFAULT 99) IS SELECT * FROM departments WHERE department_id > low AND department_id < high; BEGIN NULL; END; /

打開一個游標

打開游標執行查詢，并確定結果集。

對于用 FOR UPDATE 子句聲明的游標，OPEN 語句還會鎖定那些行。

示例 3：演示打開一個游標。

DECLARE CURSOR c1 IS SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary > 2000; BEGIN OPEN c1; END; /

用游標獲取一個（Fetch）數據

除非使用 BULK COLLECT 子句，否則，FETCH 語句每次在結果集中取出一行。每次取出當前行，游標在結果集中前進到下一行。你可以把每列存儲到單獨變量。也可以存儲整行，通常變量用 %ROWTYPE 聲明。

對于與游標關聯的查詢返回的每列，在 INTO 列表中必須是相對應的、類型兼容的變量。一般，使用帶 LOOP 和 EXIT WHEN NOTFOUND 的 FETCH 語句。

示例 4：演示用游標獲取一個（Fetch）數據。

DECLARE v_jobid employees.job_id%TYPE; -- variable for job_id v_lastname employees.last_name%TYPE; -- variable for last_name CURSOR c1 IS SELECT last_name, job_id FROM employees WHERE REGEXP_LIKE(job_id, 'S[HT]_CLERK'); v_employees employees%ROWTYPE; -- record variable for row CURSOR c2 is SELECT * FROM employees WHERE REGEXP_LIKE(job_id, '[ACADFIMKSA]_M[ANGR]'); BEGIN OPEN c1; -- open the cursor before fetching LOOP -- Fetches 2 columns into variables FETCH c1 INTO v_lastname, v_jobid; EXIT WHEN c1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(RPAD(v_lastname, 25, ' ') || v_jobid); END LOOP; CLOSE c1; DBMS_OUTPUT.PUT_LINE('-------------------------------------'); OPEN c2; LOOP -- Fetches entire row into the v_employees record FETCH c2 INTO v_employees; EXIT WHEN c2%NOTFOUND; DBMS_OUTPUT.PUT_LINE(RPAD(v_employees.last_name, 25, ' ') || v_employees.job_id); END LOOP; CLOSE c2; END; /

輸出：

Nayer ST_CLERK Mikkilineni ST_CLERK Landry ST_CLERK Markle ST_CLERK Bissot ST_CLERK Atkinson ST_CLERK Marlow ST_CLERK Olson ST_CLERK Mallin ST_CLERK Rogers ST_CLERK Gee ST_CLERK Philtanker ST_CLERK Ladwig ST_CLERK Stiles ST_CLERK Seo ST_CLERK Patel ST_CLERK Rajs ST_CLERK Davies ST_CLERK Matos ST_CLERK Vargas ST_CLERK Taylor SH_CLERK Fleaur SH_CLERK Sullivan SH_CLERK Geoni SH_CLERK Sarchand SH_CLERK Bull SH_CLERK Dellinger SH_CLERK Cabrio SH_CLERK Chung SH_CLERK Dilly SH_CLERK Gates SH_CLERK Perkins SH_CLERK Bell SH_CLERK Everett SH_CLERK McCain SH_CLERK Jones SH_CLERK Walsh SH_CLERK Feeney SH_CLERK OConnell SH_CLERK Grant SH_CLERK ------------------------------------- Greenberg FI_MGR Russell SA_MAN Partners SA_MAN Errazuriz SA_MAN Cambrault SA_MAN Zlotkey SA_MAN Hartstein MK_MAN Higgins AC_MGR

可以在與游標關聯的查詢中，在游標作用域內引用 PL/SQL 變量。當游標打開時，計算查詢中的所有變量。

示例 5：查詢可以在游標的作用域內引用 PL/SQL 變量。每次取出的工資 salary 都被乘以 2，即使每次取出后，factor 變量自增 1。

DECLARE my_sal employees.salary%TYPE; my_job employees.job_id%TYPE; factor INTEGER := 2; CURSOR c1 IS SELECT factor * salary FROM employees WHERE job_id = my_job; BEGIN OPEN c1; -- factor initially equals 2 LOOP FETCH c1 INTO my_sal; EXIT WHEN c1%NOTFOUND; factor := factor + 1; -- does not affect FETCH END LOOP; CLOSE c1; END; /

若想改變查詢結果集或變量的值，必須關閉并用輸入變量重新打開游標來設置新值。你可以用同一個游標，使用不同的 INTO 列表來單獨獲取。

示例 6：演示獲取到不同的變量。

DECLARE CURSOR c1 IS SELECT last_name FROM employees ORDER BY last_name; name1 employees.last_name%TYPE; name2 employees.last_name%TYPE; name3 employees.last_name%TYPE; BEGIN OPEN c1; FETCH c1 INTO name1; -- this fetches first row FETCH c1 INTO name2; -- this fetches second row FETCH c1 INTO name3; -- this fetches third row CLOSE c1; END; /

若獲取結果集中最后一行，不會定義目標變量的值。最終，FETCH 語句不會返回行。此時，不會產生異常。可以使用游標屬性 %FOUND 或 %NOTFOUND 來檢測。參考 Using Cursor Expressions。

用游標獲取全部（Fetch Bulk）數據

BULK COLLECT 子句可以一次性從結果集獲取所有行。參考 See Retrieving Query Results into Collections (BULK COLLECT Clause)。

示例 7：演示用游標獲取全部（Fetch Bulk）數據。演示從一個游標獲取全部數據到兩個集合。

DECLARE TYPE IdsTab IS TABLE OF employees.employee_id%TYPE; TYPE NameTab IS TABLE OF employees.last_name%TYPE; ids IdsTab; names NameTab; CURSOR c1 IS SELECT employee_id, last_name FROM employees WHERE job_id = 'ST_CLERK'; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO ids, names; CLOsE c1; -- Here is where you process the elements in the collections FOR i IN ids.FIRST .. ids.LAST LOOP IF ids(i) > 140 THEN DBMS_OUTPUT.PUT_LINE(ids(i)); END IF; END LOOP; FOR i IN names.FIRST .. names.LAST LOOP IF names(i) LIKE '%Ma%' THEN DBMS_OUTPUT.PUT_LINE(names(i)); END IF; END LOOP; END; /

輸出：

141 142 143 144 Markle Marlow Mallin Matos

關閉一個游標

CLOSE 語句禁用游標，并使結果集為未定義。可以重新打開已關閉的游標，用所有游標參數的值，和 WHERE 子句引用的變量，再次執行查詢。任何在一個已關閉的游標上的操作都會產生預定義異常 INVALID_CURSOR。

顯式游標的屬性

每個顯式游標和游標變量都有四個屬性：%FOUND、%ISOPEN、%NOTFOUND 和 %ROWCOUNT。當追加到游標或游標變量名時，這些屬性返回關于一個 SQL 語句執行很有用的信息。你可以在過程語句中使用游標屬性，而不能在 SQL 語句。

顯式游標屬性返回執行一個多行查詢的執行信息。當打開一個顯式游標或一個游標變量時，確定滿足與其關聯查詢的行，并形成結果集。行被從結果集中取出來。

%FOUND 屬性：一個行已被取出？

打開一個游標或游標變量后，第一次取出前，%FOUND 返回 NULL。取出后，若最后最后取出已返回一行，則返回 %FOUND 返回 TRUE；若接下來的取出沒有返回行，則返回 FALSE。

示例 8：演示 %FOUND。

DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%FOUND THEN -- fetch succeeded DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary); ELSE -- fetch failed, so exit loop EXIT; END IF; END LOOP; END; /

輸出：

Name = King, salary = 24000 Name = Kochhar, salary = 17000 Name = De Haan, salary = 17000 Name = Hunold, salary = 9000 Name = Ernst, salary = 6000 Name = Austin, salary = 4800 Name = Pataballa, salary = 4800 Name = Lorentz, salary = 4200 Name = Greenberg, salary = 12000 Name = Faviet, salary = 9000

引用沒有打開的游標或游標變量，會產生預定義異常 INVALID_CURSOR。

%ISOPEN 屬性：游標是打開的？

若游標或游標變量是打開的，則 %ISOPEN 返回 TRUE；否則，返回 FALSE。

示例 9：演示 %ISOPEN。

DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; the_name employees.last_name%TYPE; the_salary employees.salary%TYPE; BEGIN IF c1%ISOPEN = FALSE THEN -- cursor was not already open OPEN c1; END IF; FETCH c1 INTO the_name, the_salary; CLOSE c1; END; /

%NOTFOUND 屬性：沒有獲取行？

%NOTFOUND 與 %FOUND 相反。若接下來的取出返回一行，則 %NOTFOUND 為 FALSE；否則為 TRUE。

示例 10：演示 %NOTFOUND。當沒有獲取一行時，用 %NOTFOUND 退出循環。

DECLARE CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11; my_ename employees.last_name%TYPE; my_salary employees.salary%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO my_ename, my_salary; IF c1%NOTFOUND THEN -- fetch failed, so exit loop -- Another form of this test is -- "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;" EXIT; ELSE -- fetch succeeded DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary); END IF; END LOOP; END; /

輸出：

在第一次獲取前，%NOTFOUND 返回 NULL。若 FETCH 沒有執行成功，則循環不能被退出，因為，只要 WHEN 條件為 TRUE，EXTI WHEN 語句就執行。安全起見，你可以使用下面語句：

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

引用沒有開發的游標或游標變量，會產生預定義異常 INVALID_CURSOR。

%ROWCOUNT 屬性：目前為止受影響的行有多少？

當打開游標或游標變量時，%ROWCOUNT 為 0。在第一次取出前，%ROWCOUNT 也為 0。此后，它為目前為止受影響行的數量。若接下來的取出返回一行，則數量是遞增的。

示例 11：演示 %ROWCOUNT。

DECLARE CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11; name employees.last_name%TYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO name; EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL; DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name); IF c1%ROWCOUNT = 5 THEN DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---'); END IF; END LOOP; CLOSE c1; END; /

輸出：

1. King 2. Kochhar 3. De Haan 4. Hunold 5. Ernst --- Fetched 5th record --- 6. Austin 7. Pataballa 8. Lorentz 9. Greenberg 10. Faviet