Oracle專題12之游標

- 什么是游標？

• 游標是SQL的一個內存工作區，由系統或者用戶以變量的形式定義。
• 游標的作用就是用于臨時存儲從數據庫中提取的數據塊。
• 通俗的來講，游標就是一個結果集。
• 游標的類型分為顯式游標和隱式游標。
  

  1、顯式游標處理的四個步驟

• 顯式游標處理的語法：
  1、定義游標：CURSOR cursor_name[(parameter_name datatype)] IS select_statement;
  2、打開游標：OPEN cursor_name
  3、提取數據：FETCH cursor_name INTO variable1[, variable2, ...];
  4、關閉游標：CLOSE cursor_name。
  

• 代碼演示：查詢所有員工的員工號、姓名和職位的信息。

  DECLARE
      --定義游標
      CURSOR emp_cursor IS SELECT empno, ename, job FROM emp;
      v_empno emp.empno%TYPE;
      v_ename emp.ename%TYPE;
      v_job emp.job%TYPE;
  BEGIN
      --打開游標，執行查詢
      OPEN emp_cursor;
      --提取數據
      LOOP
               FETCH emp_cursor INTO v_empno, v_ename, v_job;
               DBMS_OUTPUT.PUT_LINE('員工號：' || v_empno || '，姓名' || v_ename || '，職位' || v_job);
               --什么時候退出循環？%FOUND,%NOTFOUND
               EXIT WHEN emp_cursor%NOTFOUND;  --EXIT WHEN NOT emp_cursor%FOUND;
      END LOOP;
      --關閉游標
      CLOSE emp_cursor;
  END;

• FETCH語句的說明：1、把當前指針指向的記錄返回；2、將指針指向下一條記錄。

2、顯式游標的四個屬性

1、%FOUND：該屬性用于檢測游標結果集是否存在數據，如果存在數據，返回TRUE。
2、%NOTFOUND：該屬性用于檢測結果集是否存在數據，如果不存在數據，返回TRUE。
3、%ISOPEN：該屬性用于檢測游標是否已經打開，如果已經打開返回TURE。
4、%ROWCOUNT：該屬性用于返回已經提取的實際行數。（如EXIT WHEN emp_cursor%ROWCOUNT=5;）

• 示例：按職工的職稱漲工資，總裁漲1000元，經理漲500元，其他員工漲300元。

  DECLARE
      --定義游標
      CURSOR emp01_cursor IS SELECT empno, job FROM emp01;
      v_empno emp01.empno%TYPE;
      v_job emp01.job%TYPE;
  BEGIN
      --打開游標，執行查詢
      OPEN emp01_cursor;
      --提取數據
      LOOP
               FETCH emp01_cursor INTO v_empno, v_job;
               IF v_job ='PRESIDENT' THEN
                   UPDATE emp01 SET sal = sal + 1000 WHERE empno = v_empno;
               ELSIF v_job = 'MANAGER' THEN
                   UPDATE emp01 SET sal = sal + 500 WHERE empno = v_empno;  
               ELSE
                   UPDATE emp01 SET sal = sal + 300 WHERE empno = v_empno; 
               END IF;
               --什么時候退出循環？%FOUND,%NOTFOUND
               EXIT WHEN NOT emp01_cursor%FOUND;
      END LOOP;
  
      COMMIT;
      CLOSE emp01_cursor;
      --關閉游標
  END;

  3、游標的FOR循環

  a、游標FOR循環的語法

• 當使用游標FOR循環時，Oracle會隱含地打開游標，提取數據并關閉游標。
• 語法如下：

  FOR record_name IN cursor_name（或者可以使用子查詢） LOOP statement; 
  END LOOP;

• 代碼演示：查詢員工的員工號、姓名和職位。

  DECLARE 
      CURSOR emp_cursor IS SELECT empno, ename, job FROM emp;
  BEGIN
      FOR emp_record IN emp_cursor LOOP 
          DBMS_OUTPUT.put_line('員工號：' || emp_record.empno || '， 姓名' || '，職位' || emp_record.job);
          END LOOP;
  END;

  BEGIN
      FOR emp_record IN (SELECT empno, ename, job FROM emp) LOOP 
          DBMS_OUTPUT.put_line('員工號：' || emp_record.empno || '， 姓名' || '，職位' || emp_record.job);
          END LOOP;
  END;

• 示例：按職工的職稱漲工資，總裁漲1000元，經理漲500元，其他員工漲300元。

  DECLARE 
      --定義游標
      CURSOR emp01_cursor IS SELECT empno, job FROM emp01;
  BEGIN
      FOR emp01_record IN emp01_cursor LOOP
          DBMS_OUTPUT.put_line(emp01_record.empno || '----' || emp01_record.job);
          IF emp01_record.job = 'PRECIDENT' THEN
              UPDATE emp01 SET sal = sal + 1000 WHERE empno = emp01_record.empno;
          ELSIF emp01_record.job = 'MANAGER' THEN
              UPDATE emp01 SET sal = sal + 500 WHERE empno = emp01_record.empno;
          ELSE
              UPDATE emp01 SET sal = sal + 300 WHERE empno = emp01_record.empno;
          END IF;
      END LOOP;
      COMMIT;
  END;

  b、參數游標

• 參數游標是指帶有參數的游標。通過使用參數游標，使用不同參數值可以生成不同的游標結果集。
• 定義和打開參數游標的語法如下：

  CURSOR cursor_name (parameter_name datatype) IS select_statement;
  OPEN cursor_name (parameter_value);

• 代碼演示：顯示10號部門的所有員工。

  DECLARE 
      CURSOR emp_cursor(dno NUMBER) IS SELECT empno, ename, job FROM emp WHERE deptno = dno;
  BEGIN
      FOR emp_record IN emp_cursor(10) LOOP
          DBMS_OUTPUT.put_line('員工號：' || emp_record.empno || '，姓名：' || emp_record.ename || '，職位：' || emp_record.job);
      END LOOP;
  END;

  4、隱式游標的處理

  a、顯式游標和隱式游標

• 顯式游標是用戶自定義的顯式創建的游標，主要是用于對查詢語句的處理。
• 隱式游標是由系統隱含創建的游標。主要是用于對非查詢語句，如修改，刪除等操作，則有Oracle系統自動地為這些操作設置游標并創建其工作區，對于隱式游標的操作，如定義、打開、取值以及關閉操作，都有Oracle系統自動完成，無需用戶進行操作。
• 隱式游標的名字為SQL，這是由Oracle系統定義的。

  b、隱式游標的詳細說明

• 如DML操作和單行SELECT語句會使用隱式游標，他們是：
• 插入操作：INSERT、更新操作：UPDATE、刪除操作：DELECT、單行查詢操作：SELECT ... INTO。
• 當系統使用一個隱式游標時，可以通過隱式游標的屬性來了解操作的狀態和結果，進而控制程序的流程。（需要注意：通過SQL游標名總是只能訪問前一個DML操作或者單行SELECT操作的游標屬性。）
• 隱式游標的屬性有：

  SQL%FOUND
  SQL%NOTFOUND
  SQL%ISOPEN
  SQL%ROWCOUNT

• 代碼演示：根據用戶輸入的員工號，更新指定員工的工資。（比如說工資漲100）

  BEGIN
      UPDATE emp01 SET sal = 100 + sal WHERE empno = &n1;
      IF SQL%FOUND THEN
          dbms_output.put_line('成功修改員工的工資');
      ELSE 
          dbms_output.put_line('修改員工工資失敗');
          ROLLBACK;
      END IF;
  END;

  5、使用游標修改或者刪除數據1：對提取出來的數據進行行級鎖定

• 為了對正在處理（查詢）的行不被另外的用戶改動，oracle提供一個FOR UPDATE子句來對所選擇的行進行鎖住。
• 如果當前用戶鎖定所選擇的行，則其他用戶不能進行對所選行進行修改，直到當前用戶數據提交。
• 因此如果創建的游標需要執行更新或者刪除的操作的時候必須帶有FOR UPDATE子句。
• FOR UPDATE子句會將游標提取出來的數據進行行級鎖定，這樣在本會話更新期間，其他用戶的會話就不能對當前游標中的數據行進行更新操作。
• 語法格式：

  CURSOR cursor_name IS select_statement FOR UPDATE [OF column_reference] [NOWAIT];
  UPDATE table_name SET column = ... WHERE CURRENT OF cursor_name;
  DELETE FROM table_name WHERE CURRENT OF cursor_name;

• 示例：按職工的職稱漲工資，總裁漲1000元，經理漲500元，其他員工漲300元。

  DECLARE 
          --定義游標
          CURSOR emp01_cursor IS SELECT empno, job FROM emp01 FOR UPDATE;
      BEGIN
          FOR emp01_record IN emp01_cursor LOOP
              DBMS_OUTPUT.put_line(emp01_record.empno || '----' || emp01_record.job);
              IF emp01_record.job = 'PRECIDENT' THEN
                  UPDATE emp01 SET sal = sal + 1000 WHERE CURRENT OF emp01_cursor;
              ELSIF emp01_record.job = 'MANAGER' THEN
                  UPDATE emp01 SET sal = sal + 500 WHERE CURRENT OF emp01_cursor;
              ELSE
                  UPDATE emp01 SET sal = sal + 300 WHERE  CURRENT OF emp01_cursor;
              END IF;
          END LOOP;
          COMMIT;
      END;

• WHERE CURRENT OF cursor_name子句表示當前游標提取的結果集中對應條件的數據行。

  6、使用游標修改或者刪除數據2：死鎖狀態

• 當其他用戶正在對所選行進行修改，而當前用戶也對當前行進行鎖定，則會出現死鎖狀態。（一直等待，無法鎖定）

  a、NOWAIT子句

• 用于指定不等待鎖，如果發現所操作的數據行已經鎖定，將不會等待，立即返回。（可以避免死鎖的狀態）

  b、使用of子句在特定表上加行共享鎖

• 當游標子查詢涉及到多張表時，如果在特定表上加行共享鎖，那么需要使用OF子句。
• 需求：輸入部門號，顯示該部門名稱以及員工的姓名，并刪除該部門下的這些員工。

  DECLARE
      CURSOR emp01_cursor IS
                   SELECT d.dname dname, e.ename ename 
                   FROM emp01 e join dept d on e.deptno = d.deptno 
                   WHERE e.deptno = &deptno 
                   FOR UPDATE OF e.deptno;
  BEGIN
      FOR emp01_record IN emp01_cursor LOOP
          dbms_output.put_line('部門名稱：' || emp01_record.dname || '，員工名：' || emp01_record.ename);
          DELETE FROM emp01 WHERE CURRENT OF emp01_cursor;
          END LOOP;
          COMMIT;
  END;