Oracle中遊标（Cursor）介紹和使用

一概念

遊标是SQL的一個記憶體工作區，由系統或使用者以變量的形式定義。遊标的作用就是用于臨時存儲從資料庫中提取的資料塊。在某些情況下，需要把資料從存放在磁盤的表中調到計算機記憶體中進行處理，最後将處理結果顯示出來或最終寫回資料庫。這樣資料處理的速度才會提高，否則頻繁的磁盤資料交換會降低效率。

二類型

Cursor類型包含三種: 隐式Cursor，顯式Cursor和Ref Cursor（動态Cursor）。

1．隐式Cursor:

1).對于Select …INTO…語句，一次隻能從資料庫中擷取到一條資料，對于這種類型的DML Sql語句，就是隐式Cursor。例如：Select /Update / Insert/Delete操作。

2)作用：可以通過隐式Cusor的屬性來了解操作的狀态和結果，進而達到流程的控制。Cursor的屬性包含：

SQL%ROWCOUNT 整型代表DML語句成功執行的資料行數

SQL%FOUND 布爾型值為TRUE代表插入、删除、更新或單行查詢操作成功

SQL%NOTFOUND 布爾型與SQL%FOUND屬性傳回值相反

SQL%ISOPEN 布爾型 DML執行過程中為真，結束後為假

3) 隐式Cursor是系統自動打開和關閉Cursor.

下面是一個Sample：

Set Serveroutput on;  
  
begin  
    update t_contract_master set liability_state = 1 where policy_code = '123456789';  
      
    if SQL%Found then  
       dbms_output.put_line('the Policy is updated successfully.');  
       commit;  
    else  
      dbms_output.put_line('the policy is updated failed.');  
    end if;  
  
end;

2．顯式Cursor：

（1）對于從資料庫中提取多行資料，就需要使用顯式Cursor。顯式Cursor的屬性包含：

遊标的屬性傳回值類型意義

%ROWCOUNT 整型獲得FETCH語句傳回的資料行數

%FOUND 布爾型最近的FETCH語句傳回一行資料則為真，否則為假

%NOTFOUND 布爾型與%FOUND屬性傳回值相反

%ISOPEN 布爾型遊标已經打開時值為真，否則為假

（2）對于顯式遊标的運用分為四個步驟：

 定義遊标---Cursor [Cursor Name] IS;

 打開遊标---Open [Cursor Name];

 操作資料---Fetch [Cursor name]

 關閉遊标---Close [Cursor Name],這個Step絕對不可以遺漏。

（3）以下是三種常見顯式Cursor用法。

Set serveroutput on;  
  
declare   
    ---define Cursor  
    Cursor cur_policy is  
     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
     from t_contract_master cm  
     where cm.liability_state = 2  
     and cm.policy_type = 1  
     and cm.policy_cate in ('2','3','4')  
     and rownum < 5  
     order by cm.policy_code desc;  
    curPolicyInfo cur_policy%rowtype;---定義遊标變量  
Begin  
   open cur_policy; ---open cursor  
   Loop   
     --deal with extraction data from DB  
     Fetch cur_policy into curPolicyInfo;  
     Exit when cur_policy%notfound;  
           
     Dbms_Output.put_line(curPolicyInfo.policy_code);  
   end loop;  
   Exception   
     when others then  
         close cur_policy;  
         Dbms_Output.put_line(Sqlerrm);  
           
   if cur_policy%isopen then    
    --close cursor   
      close cur_policy;  
   end if;  
end;  
  
/  
------------------------  
  
Set serveroutput on;  
  
declare   
    Cursor cur_policy is  
     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
     from t_contract_master cm  
     where cm.liability_state = 2  
     and cm.policy_type = 1  
     and cm.policy_cate in ('2','3','4')  
     and rownum < 5  
     order by cm.policy_code desc;  
     v_policyCode t_contract_master.policy_code%type;  
     v_applicantId t_contract_master.applicant_id%type;  
     v_periodPrem t_contract_master.period_prem%type;  
     v_bankCode t_contract_master.bank_code%type;  
     v_bankAccount t_contract_master.bank_account%type;  
Begin  
   open cur_policy;  
   Loop   
     Fetch cur_policy into v_policyCode,  
                           v_applicantId,  
                           v_periodPrem,  
                           v_bankCode,  
                           v_bankAccount;  
     Exit when cur_policy%notfound;  
           
     Dbms_Output.put_line(v_policyCode);  
   end loop;  
   Exception   
     when others then  
         close cur_policy;  
         Dbms_Output.put_line(Sqlerrm);  
           
   if cur_policy%isopen then     
      close cur_policy;  
   end if;  
end;  
/  
--------------------------------  
Set serveroutput on;  
  
declare   
    Cursor cur_policy is  
     select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account  
     from t_contract_master cm  
     where cm.liability_state = 2  
     and cm.policy_type = 1  
     and cm.policy_cate in ('2','3','4')  
     and rownum < 5  
     order by cm.policy_code desc;  
Begin  
   For rec_Policy in cur_policy loop  
       Dbms_Output.put_line(rec_policy.policy_code);  
   end loop;  
   Exception   
     when others then  
         Dbms_Output.put_line(Sqlerrm);  
end;

3． Ref Cursor（動态遊标）：

1）與隐式Cursor,顯式Cursor的差別：Ref Cursor是可以通過在運作期間傳遞參數來擷取資料結果集。而另外兩種Cursor，是靜态的，在編譯期間就決定資料結果集。

2） Ref cursor的使用:

 Type [Cursor type name] is ref cursor

 Define 動态的Sql語句

 Open cursor

 操作資料---Fetch [Cursor name]

 Close Cursor

下面是一個Sample：

Set serveroutput on;  
  
Declare  
    ---define cursor type name  
    type cur_type is ref cursor;  
    cur_policy cur_type;  
    sqlStr varchar2(500);  
    rec_policy t_contract_master%rowtype;  
begin  
   ---define 動态Sql  
   sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm  
     where cm.liability_state = 2   
     and cm.policy_type = 1   
     and cm.policy_cate in (2,3,4)   
     and rownum < 5   
     order by cm.policy_code desc ';  
---Open Cursor  
  open cur_policy for sqlStr;  
  loop  
       fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;  
       exit when cur_policy%notfound;  
         
       Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);  
    
  end loop;  
close cur_policy;      
end;

4.常見Exception

1.  錯 誤 名 稱 錯誤代碼    錯 誤 含 義     
2.  CURSOR_ALREADY_OPEN ORA_06511   試圖打開已經打開的遊标     
3.  INVALID_CURSOR  ORA_01001   試圖使用沒有打開的遊标     
4.  DUP_VAL_ON_INDEX    ORA_00001   儲存重複值到惟一索引限制的列中     
5.  ZERO_DIVIDE ORA_01476   發生除數為零的除法錯誤     
6.  INVALID_NUMBER  ORA_01722   試圖對無效字元進行數值轉換     
7.  ROWTYPE_MISMATCH    ORA_06504   主變量和遊标的類型不相容     
8.  VALUE_ERROR ORA_06502   轉換、截斷或算術運算發生錯誤     
9.  TOO_MANY_ROWS   ORA_01422   SELECT…INTO…語句傳回多于一行的資料     
10. NO_DATA_FOUND   ORA_01403   SELECT…INTO…語句沒有資料傳回     
11. TIMEOUT_ON_RESOURCE ORA_00051   等待資源時發生逾時錯誤     
12. TRANSACTION_BACKED_OUT  ORA_00060   由于死鎖，送出失敗     
13. STORAGE_ERROR   ORA_06500   發生記憶體錯誤     
14. PROGRAM_ERROR   ORA_06501   發生PL/SQL内部錯誤     
15. NOT_LOGGED_ON   ORA_01012   試圖操作未連接配接的資料庫     
16. LOGIN_DENIED    ORA_01017   在連接配接時提供了無效使用者名或密碼

附：使用遊标

15-1:使用标量變量接收遊标資料  
    DECLARE  
      CURSOR emp_cursor IS  
        SELECT ename,job,sal FROM emp WHERE deptno=&dno;  
      v_ename emp.ename%TYPE;  
      v_sal emp.sal%TYPE;  
      v_job emp.job%TYPE;  
    BEGIN  
      OPEN emp_cursor;  
      LOOP  
        FETCH emp_cursor INTO v_ename,v_job,v_sal;  
        EXIT WHEN emp_cursor%NOTFOUND;  
        dbms_output.put_line('姓名：'||v_ename||'，崗位：'||v_job||'，工資：'||v_sal);  
      END LOOP;  
      CLOSE emp_cursor;  
    END;  
    /  
15-2:使用pl/sql紀錄變量接收遊标資料  
    DECLARE  
      CURSOR emp_cursor IS  
        SELECT ename,sal FROM emp ORDER BY sal DESC;  
      emp_record emp_cursor%ROWTYPE;  
    BEGIN  
      OPEN emp_cursor;  
      LOOP  
        FETCH emp_cursor INTO emp_record;  
        EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT>&n;  
        dbms_output.put_line('姓名：'||emp_record.ename||  
         '，工資：'||emp_record.sal);  
      END LOOP;  
      CLOSE emp_cursor;  
    END;  
    /  
15-3:使用pl/sql集合變量接收遊标資料  
    DECLARE  
      CURSOR emp_cursor IS SELECT ename,sal FROM emp  
        WHERE lower(job)=lower('&job');  
      TYPE emp_table_type IS TABLE OF emp_cursor%ROWTYPE  
        INDEX BY BINARY_INTEGER;  
      emp_table emp_table_type;  
      i INT;  
    BEGIN  
      OPEN emp_cursor;  
      LOOP  
        i:=emp_cursor%ROWCOUNT+1;  
        FETCH emp_cursor INTO emp_table(i);  
        EXIT WHEN emp_cursor%NOTFOUND;  
        dbms_output.put_line('姓名：'||emp_table(i).ename||  
          '，工資：'||emp_table(i).sal);  
      END LOOP;  
      CLOSE emp_cursor;  
    END;  
    /  
15-4:在遊标for循環中引用已定義遊标  
    DECLARE  
      CURSOR emp_cursor IS SELECT ename,hiredate FROM emp  
        ORDER BY hiredate DESC;  
    BEGIN  
      FOR emp_record IN emp_cursor LOOP  
        dbms_output.put_line('姓名：'||emp_record.ename  
          ||',工作日期：'||emp_record.hiredate);  
        EXIT WHEN emp_cursor%ROWCOUNT=&n;  
      END LOOP;  
    END;  
    /  
15-5:在遊标for循環中直接引用子查詢  
    BEGIN  
      FOR emp_record IN (SELECT ename,hiredate,rownum FROM emp  
        ORDER BY hiredate) LOOP  
        dbms_output.put_line('姓名：'||emp_record.ename  
         ||',工作日期：'||emp_record.hiredate);  
        EXIT WHEN emp_record.rownum=&n;  
      END LOOP;  
    END;  
    /  
15-6:參數遊标  
    DECLARE  
      CURSOR emp_cursor(dno NUMBER) IS  
        SELECT ename,job FROM emp WHERE deptno=dno;  
    BEGIN  
      FOR emp_record IN emp_cursor(&dno) LOOP  
        dbms_output.put_line('姓名：'||emp_record.ename  
          ||',崗位：'||emp_record.job);  
      END LOOP;  
    END;  
    /  
15-7:更新遊标行  
    DECLARE  
      CURSOR emp_cursor IS  
        SELECT ename,sal,deptno FROM emp FOR UPDATE;  
      dno INT:=&no;  
    BEGIN  
      FOR emp_record IN emp_cursor LOOP  
        IF emp_record.deptno=dno THEN  
           dbms_output.put_line('姓名:'||emp_record.ename  
            ||',原工資:'||emp_record.sal);  
           UPDATE emp SET sal=sal*1.1 WHERE CURRENT OF emp_cursor;  
        END IF;  
      END LOOP;  
    END;  
    /  
15-8:删除遊标行  
    DECLARE  
      CURSOR emp_cursor IS  
        SELECT ename FROM emp FOR UPDATE;  
      name VARCHAR2(10):=lower('&name');  
    BEGIN  
      FOR emp_record IN emp_cursor LOOP  
        IF lower(emp_record.ename)=name THEN  
           DELETE FROM emp WHERE CURRENT OF emp_cursor;  
        ELSE  
           dbms_output.put_line('姓名:'||emp_record.ename);  
        END IF;  
      END LOOP;  
    END;  
    /  
15-9:使用of子句在特定表上加行共享鎖．  
    DECLARE  
      CURSOR emp_cursor IS  
        SELECT a.dname,b.ename FROM dept a JOIN emp b  
        ON a.deptno=b.deptno  
        FOR UPDATE OF b.deptno;  
      name VARCHAR2(10):=LOWER('&name');  
    BEGIN  
      FOR emp_record IN emp_cursor LOOP  
        IF LOWER(emp_record.dname)=name THEN  
          dbms_output.put_line('姓名:'||emp_record.ename);  
          DELETE FROM emp WHERE CURRENT OF emp_cursor;  
        END IF;  
      END LOOP;  
    END;  
    /  
15-10:使用無傳回類型的遊标變量  
    DECLARE  
      TYPE ref_cursor_type IS REF CURSOR;  
      ref_cursor ref_cursor_type;  
      v1 NUMBER(6);  
      v2 VARCHAR2(10);  
    BEGIN  
      OPEN ref_cursor FOR  
        SELECT &col1 col1,&col2 col2 FROM &table WHERE &cond;  
      LOOP  
        FETCH ref_cursor INTO v1,v2;  
        EXIT WHEN ref_cursor%NOTFOUND;  
        dbms_output.put_line('col1='||v1||',col2='||v2);  
      END LOOP;  
      CLOSE ref_cursor;  
    END;  
    /  
15-11:使用有傳回類型的遊标變量  
    DECLARE  
      TYPE emp_cursor_type IS REF CURSOR RETURN emp%ROWTYPE;  
      emp_cursor emp_cursor_type;  
      emp_record emp%ROWTYPE;  
    BEGIN  
      OPEN emp_cursor FOR SELECT * FROM emp  
        WHERE deptno=&dno;  
      LOOP  
        FETCH emp_cursor INTO emp_record;  
        EXIT WHEN emp_cursor%NOTFOUND;  
        dbms_output.put_line('姓名:'||emp_record.ename||  
          ',工資:'||emp_record.sal);  
      END LOOP;  
      CLOSE emp_cursor;  
    END;  
    /  
15-12:使用fetch...bulk collect提取所有資料  
    DECLARE  
      CURSOR emp_cursor IS  
        SELECT * FROM emp WHERE LOWER(job)=LOWER('&job');  
      TYPE emp_table_type IS TABLE OF emp%ROWTYPE;  
      emp_table emp_table_type;  
    BEGIN  
      OPEN emp_cursor;  
      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;  
    END;  
    /  
15-13:使用limit子句限制提取行數  
    DECLARE  
      CURSOR emp_cursor IS SELECT * FROM emp;  
      TYPE emp_array_type IS VARRAY(5) OF emp%ROWTYPE;  
      emp_array emp_array_type;  
    BEGIN  
      OPEN emp_cursor;  
      LOOP  
        FETCH emp_cursor BULK COLLECT INTO emp_array LIMIT &rows;  
        FOR i IN 1..emp_array.COUNT LOOP  
          dbms_output.put_line('姓名:'||emp_array(i).ename  
            ||',工資:'||emp_array(i).sal);  
        END LOOP;  
        EXIT WHEN emp_cursor%NOTFOUND;  
      END LOOP;  
      CLOSE emp_cursor;  
    END;  
    /  
15-14:使用cursor表達式  
    DECLARE  
      CURSOR dept_cursor(no NUMBER) IS  
         SELECT a.dname,CURSOR(SELECT * FROM emp  
         WHERE deptno=a.deptno)  
         FROM dept a WHERE a.deptno=no;  
      TYPE ref_cursor_type IS REF CURSOR;  
      emp_cursor ref_cursor_type;  
      emp_record emp%ROWTYPE;  
      v_dname dept.dname%TYPE;  
    BEGIN  
      OPEN dept_cursor(&dno);  
      LOOP  
         FETCH dept_cursor INTO v_dname,emp_cursor;  
         EXIT WHEN dept_cursor%NOTFOUND;  
         dbms_output.put_line('部門名:'||v_dname);  
         LOOP  
           FETCH emp_cursor INTO emp_record;  
           EXIT WHEN emp_cursor%NOTFOUND;  
           dbms_output.put_line('----雇員名:'||emp_record.ename  
            ||',崗位:'||emp_record.job);  
         END LOOP;  
      END LOOP;  
      CLOSE dept_cursor;  
    END;

Oracle中遊标（Cursor）介紹和使用

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述

Oracle 批量查詢傳入List 傳回List