Oracle資料庫之PL/SQL遊標學習筆記

1. 遊標概念

字面意思是遊動的游標，是指向上下文地區的控制代碼或指標。

在PL/SQL塊中執行CRUD操作時，ORACLE會在記憶體中為其分配上下文區。用資料庫語言來描述遊標就是：映射在上下文區結果集中一行資料上的位置實體。

使用者可以使用遊標訪問結果集中的任意一行資料，將遊標指向某行後，即可對該行資料進行操作。遊標為應用提供了一種對具有多行資料查詢結果集中的每一行資料分別進行單獨處理的方法，是設計嵌入式SQL語句的應用程式的常用編程方式。

在每個使用者會話中，可以同時開啟多個遊標，其最大數量由資料庫初始化參數檔案中的OPEN_CURSORS參數定義。

遊標可分為顯式遊標和隱式遊標兩類。

2. 顯式遊標

顯式遊標使用主要有四個步驟：

聲明/定義遊標
開啟遊標
讀取資料
關閉遊標
2.1 聲明/定義遊標

文法：

CURSOR cursor_name
  [(parameter_dec [, parameter_dec ]…)]
    [RETURN datatype]
      IS
        select_statement;
樣本：

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

  CURSOR c2 IS                             -- 聲明C2遊標並定義
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary > 2000;

  CURSOR c1 RETURN departments%ROWTYPE IS  -- 定義C1遊標
    SELECT * FROM departments
    WHERE department_id = 110;

  CURSOR c3 RETURN locations%ROWTYPE;      -- 聲明C3遊標

  CURSOR c3 IS                             -- 定義C3遊標
    SELECT * FROM locations
    WHERE country_id = 'JP';

  CURSOR c4(sal number) IS                             -- 聲明C4遊標並定義
    SELECT employee_id, job_id, salary FROM employees
    WHERE salary > sal;
BEGIN
  NULL;
END;
說明：

在指定參數資料類型時，不能使用長度約束，如C4遊標的參數，不能寫為number(10,4)這種結構。

[RETURN datatype]是可選的，表示遊標返回資料的資料。如果選擇，則應該嚴格與select_statement中的挑選清單在次序和資料類型上匹配。一般是記錄資料類型（RECORD）或帶“%ROWTYPE”的資料。

2.2 開啟遊標

執行遊標所對應的SELECT語句，將其查詢結果放入工作區，並且指標指向工作區的首部，標識遊標結果集。

文法：

OPEN cursor_name [ ( cursor_parameter [ [,] actual_cursor_parameter ]... ) ]
樣本：

OPEN c4 (1300);
2.3 讀取資料

檢索結果集合中的資料行，放入指定的輸出變數中。

文法：

FETCH { cursor | cursor_variable | :host_cursor_variable }
  { into_clause | bulk_collect_into_clause [ LIMIT numeric_expression ] } ;
執行FETCH語句時，每次返回一個資料行，然後自動將遊標移動指向下一個資料行。當檢索到最後一行資料時，如果再次執行FETCH語句，將操作失敗，並將遊標屬性%NOTFOUND置為TRUE。所以每次執行完FETCH語句後，檢查遊標屬性%NOTFOUND就可以判斷FETCH語句是否執行成功並返回一個資料行，以便確定是否給對應的變數賦了值。

樣本：

fetch c4 into eid, jid, sal;
2.4 關閉遊標

當處理完遊標結果集合資料後，應及時關閉遊標，以釋放該遊標所佔用的系統資源。

關閉遊標後不能再使用FETCH語句擷取其中資料。關閉後的遊標可以使用OPEN語句重新開啟。

文法：

CLOSE cursor_name;
完整樣本1：

DECLARE
   -- 定義遊標
   CURSOR c_cursor IS
   SELECT first_name || last_name, Salary FROM EMPLOYEES WHERE rownum<11;  

   -- 聲明變數
   v_ename  EMPLOYEES.first_name%TYPE;
   v_sal    EMPLOYEES.Salary%TYPE;  
BEGIN
  -- 開啟遊標
  OPEN c_cursor;
  -- 擷取資料
  FETCH c_cursor INTO v_ename, v_sal;
  -- 處理資料
  WHILE c_cursor%FOUND LOOP
     DBMS_OUTPUT.PUT_LINE(v_ename||'---'||to_char(v_sal) );
     FETCH c_cursor INTO v_ename, v_sal;
  END LOOP;
  -- 關閉遊標
  CLOSE c_cursor;
END;
完整樣本2：

DECLARE
   -- 定義RECORD記錄類型
   TYPE emp_record_type IS RECORD(
        f_name   employees.first_name%TYPE,
        h_date   employees.hire_date%TYPE);
   -- 聲明記錄變數
   v_emp_record EMP_RECORD_TYPE;
   -- 定義遊標，有參數與傳回值
   CURSOR c3(dept_id NUMBER, j_id VARCHAR2)
      RETURN EMP_RECORD_TYPE
   IS
      SELECT first_name, hire_date FROM employees
      WHERE department_id = dept_id AND job_id = j_id;

BEGIN
   -- 開啟遊標，傳遞參數值
   OPEN c3(j_id => 'AD_VP', dept_id => 90);
   LOOP
      FETCH c3 INTO v_emp_record;    -- 擷取資料
      IF c3%FOUND THEN
         DBMS_OUTPUT.PUT_LINE(v_emp_record.f_name||'的僱傭日期是'||v_emp_record.h_date);
      ELSE
         DBMS_OUTPUT.PUT_LINE('已經處理完結果集了');
         EXIT; -- 處理完則退出迴圈
      END IF;
   END LOOP;
   CLOSE c3;   --關閉遊標
END;
3. 顯式遊標屬性

遊標的狀態（如是否開啟，擷取了多少行資料等）可以使用遊標屬性來擷取。

遊標屬性以“%屬性名稱”的形式加在遊標名之後。顯式遊標屬性有：

屬性名稱 說明
%FOUND 如果記錄成功擷取，返回TRUE，否則返回FALSE
%NOTFOUND 如果記錄擷取失敗，返回TRUE，否則返回FALSE
%ROWCOUNT 返回已經從遊標中擷取的記錄數
%ISOPEN 如果遊標是開啟的，返回TRUE，否則返回FALSE
樣本：

DECLARE
   v_empno  EMPLOYEES.EMPLOYEE_ID%TYPE;
   v_sal      EMPLOYEES.Salary%TYPE;
   -- 定義遊標
   CURSOR c_cursor IS SELECT EMPLOYEE_ID, Salary FROM EMPLOYEES;
BEGIN
   -- 開啟遊標
   OPEN c_cursor;
   LOOP
      -- 擷取資料
      FETCH c_cursor INTO v_empno, v_sal;
      EXIT WHEN c_cursor%NOTFOUND;  -- 未讀取到記錄，則退出迴圈
      IF v_sal<=1200 THEN
            UPDATE EMPLOYEES SET Salary=Salary+50 WHERE EMPLOYEE_ID=v_empno;
            DBMS_OUTPUT.PUT_LINE('編碼為'||v_empno||'工資已更新!');
      END IF;
      DBMS_OUTPUT.PUT_LINE('記錄數:'|| c_cursor %ROWCOUNT);
   END LOOP;
   -- 關閉遊標
   CLOSE c_cursor;
END;
4. 基於遊標定義記錄變數

使用%ROWTYPE屬性不僅可以基於表和視圖定義記錄變數，也可以基於遊標定義記錄變數。當基於遊標定義記錄變數時，記錄成員名實際就是SELECT語句的列名和列別名。

為了簡化顯式遊標的資料處理，建議使用基於遊標的記錄變數存放遊標資料。基於遊標定義記錄變數，比聲明記錄類型變數要方便，不容易出錯。

樣本：

DECLARE
   -- 定義遊標
   CURSOR emp_cursor IS SELECT ename,sal FROM emp；
   emp_reocrd emp_cursor%ROWTYPE；-- 遊標變數
BEGIN
   -- 開啟遊標
   OPEN emp_cursor；
   LOOP
      -- 擷取記錄
      FETCH emp_cursor INTO emp_record；
      EXIT WHEN emp_record%NOTFOUND；
      dbms_ouput.put_line('僱員名:'||emp_record.ename||',僱員工資:'||emp_record.sal)；
   END LOOP；
   -- 關閉遊標
   CLOSE emp_cursor；
END；
5. 隱式遊標

如果在PL/SQL塊中使用了SELECT語句進行操作，PL/SQL會隱含處理遊標定義，而對於非查詢語句，如修改、刪除操作，則由ORACLE系統自動地為這些操作設定遊標並建立其工作區。由系統隱含建立的遊標稱為隱式遊標，隱式遊標的名字為SQL。

對於隱式遊標的操作，如定義、開啟、取值及關閉操作，都由ORACLE 系統自動地完成，無需使用者進行處理。使用者只能通過隱式遊標的相關屬性，來完成相應的操作。在隱式遊標的工作區中，所存放的資料是與使用者自訂的顯示遊標無關的、最新處理的一條SQL語句所包含的資料。

隱式遊標的屬性：

屬性名稱 說明
SQL%FOUND 如果記錄成功擷取，返回TRUE，否則返回FALSE
SQL%NOTFOUND 如果記錄擷取失敗，返回TRUE，否則返回FALSE
SQL%ROWCOUNT 返回已經從遊標中擷取的記錄數
SQL%ISOPEN 如果遊標是開啟的，返回TRUE，否則返回FALSE
隱式遊標在INSERT，UPDATE，DELETE，SELECT語句中不必明確定義遊標。

樣本：

DECLARE
   v_rows NUMBER;
BEGIN
   -- 更新表資料
   UPDATE employees SET salary = 5000 WHERE department_id = 90 AND job_id = 'AD_VP';
   -- 擷取受影響行數
   v_rows := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'個員工的工資');
END;
6. 遊標FOR迴圈

遊標FOR迴圈和顯示遊標的一種快捷使用方式，它使用FOR迴圈依次讀取結果集中的行資料，當FOR迴圈開始時，遊標自動開啟（不需要OPEN）,每迴圈一次系統自動讀取遊標當前行的資料（不需要FETCH)，當退出FOR迴圈時，遊標被自動關閉（不需要使用CLOSE）使用遊標FOR迴圈的時候不能使用OPEN語句，FETCH語句和CLOSE語句，否則會產生錯誤。

文法：

FOR index_variable IN cursor_name[(value[, value]…)] LOOP
    -- 遊標處理語句
END LOOP;
樣本：

DECLARE
    CURSOR emp_cur(vartype number) IS
        SELECT emp_no,emp_zc FROM cus_emp_basic WHERE com_no=vartype;
BEGIN
    FOR person IN emp_cur(123) LOOP
        DBMS_OUTPUT.PUT_LINE('編號:'||person.emp_no||',地址:'||person.emp_zc);
END LOOP;
END;
7. 使用顯示遊標修改資料

在PL/SQL中依然可以使用UPDATE和DELETE語句更新或刪除資料行。顯式遊標只有在需要獲得多行資料的情況下使用。PL/SQL提供了僅僅使用遊標就可以執行刪除或更新記錄的方法。

UPDATE或DELETE語句中的WHERE CURRENT OF子句專門處理要執行UPDATE或DELETE操作的表中取出的最近的資料。要使用這個方法，在聲明遊標時必須使用FOR UPDATE子句，當使用FOR UPDATE子句開啟一個遊標時，所有返回集中的資料行都將處於行級（ROW-LEVEL)獨佔式鎖定，其他對象只能查詢這些資料行，不能進行UPDATE、DELETE或SELECT…FOR UPDATE操作。

文法：

FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[NOWAIT]
在多表查詢中，使用OF子句來鎖定特定的表,如果忽略了OF子句，那麼所有表中選擇的資料行都將被鎖定。如果這些資料行已經被其他會話鎖定，那麼正常情況下ORACLE將等待，直到資料行解鎖。當加上NOWAIT子句時，如果這些行真的被另一個會話鎖定，則OPEN立即返回並給出：

ORA-00054 ：resource busy  and  acquire with nowait specified.
在UPDATE和DELETE中使用WHERE CURRENT OF子串的文法如下：

WHERE{CURRENT OF cursor_name|search_condition}
樣本：

DELCARE
    CURSOR c1 IS
        SELECT empno,salary FROM emp
        WHERE comm IS NULL
        FOR UPDATE OF comm;
    v_comm NUMBER(10,2);
BEGIN
    FOR r1 IN c1 LOOP
        IF r1.salary<500 THEN
            v_comm:=r1.salary*0.25;
　　      ELSEIF r1.salary<1000 THEN
            v_comm:=r1.salary*0.20;
　　      ELSEIF r1.salary<3000 THEN
            v_comm:=r1.salary*0.15;
        ELSE
            v_comm:=r1.salary*0.12;
        END IF;
        UPDATE emp SET comm=v_comm WHERE CURRENT OF c1;
    END LOOP;
END
8. 遊標變數

與遊標類似，遊標變數指向多行查詢的結果集的當前行。但是，遊標與遊標變數是不同的，就像常量和變數的關係一樣。遊標是靜態，遊標變數是動態，因為它不與特定的查詢繫結在一起。

8.1 聲明遊標變數

文法：

TYPE ref_type_name IS REF CURSOR
    [ RETURN return_type];
說明：

遊標變數類型有強型別定義和弱類型定義兩種。強型別定義必須指定遊標變數的傳回值類型，而弱類型定義則不說明傳回值類型。

return_type為遊標變數的傳回值類型，它必須為記錄變數。

樣本：

-- 定義一個REF CURSOU類型
TYPE ref_cursor_type IS REF CURSOR;
-- 聲明一個遊標變數
cv_ref REF_CURSOR_TYPE;
8.2 遊標變數的使用

與遊標一樣，遊標變數操作也包括開啟、提取和關閉三個步驟。

8.2.1 開啟遊標變數

文法：

OPEN {cursor_variable_name | :host_cursor_variable_name}
FOR select_statement;
說明：

host_cursor_variable_name為PL/SQL主機環境（如OCI: ORACLE Call Interface，Pro*c 程式等）中聲明的遊標變數。

OPEN…FOR 語句可以在關閉當前的遊標變數之前重新開啟遊標變數，而不會導致CURSOR_ALREAD_OPEN異常錯誤。新開啟遊標變數時，前一個查詢的記憶體處理區將被釋放。

8.2.2 提取資料

文法：

FETCH {cursor_variable_name | :host_cursor_variable_name}
INTO {variable [, variable]…| record_variable};
說明：

將提取到的資料放入普通變數和記錄變數中存放。

8.2.3 關閉遊標

文法：

CLOSE {cursor_variable_name | :host_cursor_variable_name}
說明：

如果應用程式試圖關閉一個未開啟的遊標變數，則將導致INVALID_CURSOR異常錯誤。

樣本1：

DECLARE
TYPE ref_type_table IS REF CURSOR;
v_cursor            ref_type_table;
emp_record          emp%rowtype;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('員工號：'||emp_record.ename||'部門號：'||emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;
樣本2：

DECLARE
    emp_record          emp%rowtype;
    TYPE ref_type_table IS REF CURSOR RETURN emp%rowtype;
    v_cursor            ref_type_table;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('員工號：'||emp_record.ename||'部門號：'||emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;

DECLARE
Type emp_record_type IS RECORD(
       ename emp.ename%TYPE,
       salary emp.sal%TYPE,
       deptno emp.deptno%TYPE);
emp_record emp_record_type;

TYPE ref_type_table IS REF CURSOR RETURN emp_record_type;
v_cursor            ref_type_table;
BEGIN
     OPEN v_cursor FOR select ename,sal,deptno from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('員工號：'||emp_record.ename||'，部門號：'||emp_record.deptno||'，工資：'||emp_record.salary);
     END LOOP;
     CLOSE v_cursor;
END;
9. 使用遊標批量擷取

文法：

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];
說明：

使用BULK COLLECT，我們可以用對資料庫的一個來回，返回多行資料。BULK COLLECT減少了PL/SQL和SQL引擎之間的上下文開關數目，因而加速了資料擷取的速度。

樣本：

DECLARE
     CURSOR emp_cursor(v_deptno number) IS SELECT * FROM EMP WHERE deptno = v_deptno;
     TYPE type_emp_table IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
     emp_table type_emp_table;
     v_dno emp.deptno%TYPE;
BEGIN
     v_dno := &no;
     OPEN emp_cursor(v_dno);
     FETCH emp_cursor BULK COLLECT INTO emp_table;
     CLOSE emp_cursor;
     FOR i IN 1..emp_table.COUNT LOOP
         dbms_output.put_line('員工號：'||emp_table(i).ename||'工資：'||emp_table(i).sal);
     END LOOP;
     CLOSE emp_cursor;
END;
10. 遊標運算式

遊標運算式作用是用於返回嵌套遊標。文法：

CURSOR(sub_query)
樣本：

DECLARE
       CURSOR dept_emp_cursor(v_deptno number) IS
   SELECT dname,cursor(SELECT * FROM emp e WHERE e.deptno = d.deptno)
   FROM dept d WHERE deptno = v_deptno;
       TYPE emp_cursor_type IS REF CURSOR;
       emp_cursor emp_cursor_type;
       emp_record emp%ROWTYPE;
       v_name dept.dname%TYPE;
       v_dno emp.deptno%TYPE;
BEGIN
     v_dno := &no;
     OPEN dept_emp_cursor(v_dno);
     loop
          FETCH dept_emp_cursor INTO v_name,emp_cursor;
          EXIT WHEN dept_emp_cursor%NOTFOUND;
          dbms_output.put_line('部門名稱：'||v_name);
          LOOP
              FETCH emp_cursor INTO emp_record;           
              EXIT WHEN emp_cursor%NOTFOUND;
              dbms_output.put_line('員工名稱：'||emp_record.ename||'，工資：'||emp_record.sal);
          END LOOP;
     end loop;
     CLOSE dept_emp_cursor;
END;