遊標屬性介紹.txt

 

上次提到了sql遊標的四個屬性：sql%found、sql%notfound、sql%rowcount、sql%isopen。
這些屬性是針對遊標中的一種——————隱式遊標  來講的。
隱式遊標不需要顯式地開啟遊標、抽取資料和關閉遊標，因此也就不存在“遊標名稱”，其屬性的表示方式都是sql%xxxx。
隱式遊標會在執行dml語句、使用select  xxxx 【bulk collect】into ……語句時自動宣告和管理。

更一般地說，遊標可以分為顯式遊標、隱式遊標和動態遊標。

%found 反映遊標最後一次資料提取操作的狀態。如果有返回行，結果為true；沒有返回行，結果為false；
遊標沒開啟就試圖訪問該屬性，報錯invalid_cursor異常。

%notfound 與%found相反，可以用於退出（如：exit when  cursor_name%notfound）

%rowcount 返回遊標中取出的記錄數量；遊標沒開啟就試圖訪問該屬性，報錯invalid_cursor異常。

%isopen  如果是一個開啟的遊標，返回true；否則返回false

%bulk_rowcount 是用在forall語句中的，返回每次dml操作的行數，在語義上相當於關聯陣列。

%bulk_exceptions 用在forall語句中，返回每次dml語句執行時可能發生的異常。語義上相當於關聯陣列。

1.顯式遊標

對於顯式遊標，需要進行宣告、開啟、提取、關閉這幾個步驟。
在遊標開啟後，對資料所作的修改將不會影響到遊標中的資料。如：

SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7566 JONESa     MANAGER    7839 1997-3-24     3795.00               20
 7782 CLARK      MANAGER    7839 1982-6-4      3470.00               10
 7788 SCOTT      ANALYST    7566 1998-4-11     3820.00               30
 7839 KING       PRESIDENT       1992-11-9     5820.00               30
 7876 ADAMS      CLERK      7788 2003-5-14     1920.00               20
 7934 MILLER     CLERK      7782 1983-1-18     2320.00               10

6 rows selected

SQL>
SQL> declare
  2    cursor cur_a is select * from emp;
  3    type emp_type is table of emp%rowtype;
  4    emp_lst emp_type;
  5  begin
  6    open cur_a;
  7    update emp set ename=ename||'a';--在開啟遊標後，對原表資料進行更新
  8    fetch cur_a bulk collect into emp_lst;--然後提取資料
  9    for i in emp_lst.first..emp_lst.last loop
 10      dbms_output.put_line(emp_lst(i).ename);
 11    end loop;
 12    close cur_a;
 13    commit;
 14  end;
 15  /

JONESa
CLARK
SCOTT
KING
ADAMS
MILLER

PL/SQL procedure successfully completed

根據以上結果，遊標所提取的資料是open遊標時的資料。
SQL> select * from emp;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7566 JONESaa    MANAGER    7839 1997-3-24     3795.00               20
 7782 CLARKa     MANAGER    7839 1982-6-4      3470.00               10
 7788 SCOTTa     ANALYST    7566 1998-4-11     3820.00               30
 7839 KINGa      PRESIDENT       1992-11-9     5820.00               30
 7876 ADAMSa     CLERK      7788 2003-5-14     1920.00               20
 7934 MILLERa    CLERK      7782 1983-1-18     2320.00               10

6 rows selected

在提取時，提取超過最後一行後，不會報出異常，也不會將變數值設定為null。因此，永遠不要用監測into中變數值的方法來確定遊標的fetch操作是否成功；
而應該使用%found或%notfound判斷。

關閉遊標需要注意：
1）應該在程式結束時關閉已經開啟的遊標，否則可能出現記憶體洩露。
pl/sql會在一個函式呼叫、過程呼叫或匿名塊呼叫結束後檢查開啟的遊標並隱式關閉，但其開銷不容忽視，且ref cursor不會被隱式關閉。

2）包級別宣告的遊標，在塊或程式中開啟時，需要顯示關閉這個遊標或等到會話結束自動關閉，否則將一直開啟

3）對已經關閉的遊標再進行關閉操作，會報錯invalid_cursor異常

4）開啟遊標過多，超過open_cursors初始化引數，會報錯ora-01000

 

關於引數遊標：
對於不同條件形成不同遊標，可以使用引數遊標。並且引數可以出現在select列表中。
引數遊標的使用例項如下：
SQL> declare
  2    cursor cur_a(deptno_lst emp.deptno%type)
  3    is
  4    select * from emp where deptno=deptno_lst;
  5 
  6    type emp_type is table of emp%rowtype;
  7    emp_lst emp_type;
  8  begin
  9    open cur_a('10');
 10    fetch cur_a bulk collect into emp_lst;
 11    for i in emp_lst.first..emp_lst.last loop
 12      dbms_output.put_line(emp_lst(i).ename);
 13    end loop;
 14    close cur_a;
 15  end;
 16  /

CLARKaa
MILLERaa

PL/SQL procedure successfully completed

注意：宣告部分和open時需要指定引數名，fetch時不需要再重複引數名了。
如果宣告部分宣告瞭遊標中引數的預設值，fetch時不指定引數值，就使用預設值。

 

2.select for update

宣告的遊標可以使用for update選項，避免開啟遊標後其他會話對資料進行更改；
更細粒度地，可以使用for update of column_name，防止其他回話更新特定列；
使用nowait選項，在其他會話已經鎖定資料時不發生等待，或使用wait xx表示等待的最大秒數，
若不指定該選項，則等待直到初始化引數distributed_lock_timeout的值。

在帶有for update的遊標開啟後，關閉前不要多次進行提交、回滾操作，否則報錯ora-01002.

使用where current of子句，可以指定對最後取出來的資料行指定update、delete操作。避免了重複編碼來定位記錄的同一條件。

 

3.遊標變數和ref cursor

遊標變數是一個指向或引用底層遊標的變數。
可以用於開啟任何一個查詢，甚至在一個程式中執行多個不同查詢。
遊標變數可以在不同pl/sql程式間傳遞查詢的結果集。

使用遊標變數的好處：
1）把一個遊標變數在不同時刻關聯不同的查詢語句
2）把遊標變數作為引數傳遞給一個過程或者函式，相當於通過傳遞記錄集的引用來共享遊標的結果集
3）使用pl/sql的靜態遊標的完整功能集
4）把一個遊標的內容賦值給另一個遊標變數。

一般用法：
declare
  type var_cur_type is ref cursor;
  var_cur var_cur_type;  --這兩個操作也可以用"var_cur sys_refcursor;"代替
begin
  open var_cur for ……
  ……
  close var_cur;
end;

上面是弱型別的遊標變數，沒有return子句，
如果type ……語句中有return 子句，就是強型別。

4.關於遊標的%bulk_rowcount、%bulk_exceptions屬性，以下舉一個例子慢慢體會，不詳細說明了。
可以參考：
http://space.itpub.net/24908207/viewspace-683730
http://wenku.baidu.com/view/a0ba5fea81c758f5f61f676a.html
http://hwhuang.iteye.com/blog/537087

SQL> declare
  2    cursor cur_a
  3    is
  4    select empno,ename,deptno from emp;
  5 
  6    type empno_typ is table of emp.empno%type;
  7    type ename_typ is table of emp.ename%type;
  8    type deptno_typ is table of emp.deptno%type;
  9    empno_lst empno_typ;
 10    ename_lst ename_typ;
 11    deptno_lst deptno_typ;
 12 
 13  begin
 14 
 15    open cur_a;
 16    loop
 17    fetch cur_a bulk collect into empno_lst,ename_lst,deptno_lst limit 2;
 18    exit when cur_a%notfound;
 19    forall i in empno_lst.first..empno_lst.last save exceptions --必須有save exceptions，才能呼叫bulk_exceptions
 20      insert into emp1(empno,ename,deptno)
 21      values(empno_lst(i),ename_lst(i),deptno_lst(i));
 22    end loop;
 23 
 24    dbms_output.put_line('cur_a%rowcount:'||cur_a%rowcount);
 25    dbms_output.put_line('cur_a%bulk_rowcount(1):'||cur_a%bulk_rowcount(1));
 26   
 27    dbms_output.put_line('sql%bulk_exceptions.count:'||sql%bulk_exceptions.count);--除count之外還有error_code、error_index屬性
 28    close cur_a;
 29  end;
 30  /

cur_a%rowcount:6
cur_a%bulk_rowcount(1):6
sql%bulk_exceptions.count:0

PL/SQL procedure successfully completed

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-768743/，如需轉載，請註明出處，否則將追究法律責任。