瘋狂

我們在進行pl/sql編程時打交道最多的就是存儲過程了。存儲過程的結構是非常的簡單的，我們在這里除了學習存儲過程的基本結構外，還會學習編寫存儲過程時相關的一些實用的知識。如：游標的處理，異常的處理，集合的選擇等等

1.存儲過程結構
1.1 第一個存儲過程

Java代碼
1.create or replace procedure proc1(  
2.  p_para1 varchar2,  
3.  p_para2 out varchar2,  
4.  p_para3 in out varchar2  
5.)as   
6. v_name varchar2(20);  
7.begin  
8.  v_name := '張三豐';  
9.  p_para3 := v_name;  
10.  dbms_output.put_line('p_para3:'||p_para3);  
11.end; 
create or replace procedure proc1(
  p_para1 varchar2,
  p_para2 out varchar2,
  p_para3 in out varchar2
)as
 v_name varchar2(20);
begin
  v_name := '張三豐';
  p_para3 := v_name;
  dbms_output.put_line('p_para3:'||p_para3);
end;

上面就是一個最簡單的存儲過程。一個存儲過程大體分為這么幾個部分:
創建語句：create or replace procedure 存儲過程名
如果沒有or replace語句，則僅僅是新建一個存儲過程。如果系統存在該存儲過程，則會報錯。Create or replace procedure 如果系統中沒有此存儲過程就新建一個，如果系統中有此存儲過程則把原來刪除掉，重新創建一個存儲過程。
存儲過程名定義：包括存儲過程名和參數列表。參數名和參數類型。參數名不能重復， 參數傳遞方式:IN, OUT, IN OUT
IN 表示輸入參數，按值傳遞方式。
OUT 表示輸出參數，可以理解為按引用傳遞方式。可以作為存儲過程的輸出結果，供外部調用者使用。
IN OUT 即可作輸入參數，也可作輸出參數。
參數的數據類型只需要指明類型名即可，不需要指定寬度。
參數的寬度由外部調用者決定。
過程可以有參數，也可以沒有參數
變量聲明塊：緊跟著的as (is )關鍵字，可以理解為pl/sql的declare關鍵字，用于聲明變量。
變量聲明塊用于聲明該存儲過程需要用到的變量，它的作用域為該存儲過程。另外這里聲明的變量必須指定寬度。遵循PL/SQL的變量聲明規范。
過程語句塊：從begin 關鍵字開始為過程的語句塊。存儲過程的具體邏輯在這里來實現。
異常處理塊：關鍵字為exception ，為處理語句產生的異常。該部分為可選
結束塊：由end關鍵字結果。

1.2 存儲過程的參數傳遞方式
存儲過程的參數傳遞有三種方式:IN,OUT,IN OUT .
IN 按值傳遞，并且它不允許在存儲過程中被重新賦值。如果存儲過程的參數沒有指定存參數傳遞類型，默認為IN

Java代碼
1.create or replace procedure proc1(  
2.  p_para1 varchar2,  
3.  p_para2 out varchar2,  
4.  p_para3 in out varchar2  
5.)as   
6. v_name varchar2(20);  
7.begin  
8.  p_para1 :='aaa';  
9.  p_para2 :='bbb';  
10.  v_name := '張三豐';  
11.  p_para3 := v_name;  
12.  dbms_output.put_line('p_para3:'||p_para3);  
13.  null;  
14.end;  
15.      
16.Warning: Procedure created with compilation errors  
17. 
18.SQL> show error;  
19.Errors for PROCEDURE LIFEMAN.PROC1:  
20. 
21.LINE/COL ERROR  
22.-------- ----------------------------------------------------------------------  
23.8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target  
24.8/3      PL/SQL: Statement ignored 
create or replace procedure proc1(
  p_para1 varchar2,
  p_para2 out varchar2,
  p_para3 in out varchar2
)as
 v_name varchar2(20);
begin
  p_para1 :='aaa';
  p_para2 :='bbb';
  v_name := '張三豐';
  p_para3 := v_name;
  dbms_output.put_line('p_para3:'||p_para3);
  null;
end;
 
Warning: Procedure created with compilation errors

SQL> show error;
Errors for PROCEDURE LIFEMAN.PROC1:

LINE/COL ERROR
-------- ----------------------------------------------------------------------
8/3      PLS-00363: expression 'P_PARA1' cannot be used as an assignment target
8/3      PL/SQL: Statement ignored這一點與其它高級語言都不同。它相當于java在參數前面加上final關鍵字。

OUT 參數：作為輸出參數，需要注意，當一個參數被指定為OUT類型時，就算在調用存儲過程之前對該參數進行了賦值，在存儲過程中該參數的值仍然是null.

Java代碼
1.create or replace procedure proc1(  
2.  p_para1 varchar2,  
3.  p_para2 out varchar2,  
4.  p_para3 in out varchar2  
5.)as   
6. v_name varchar2(20);  
7.begin  
8.  v_name := '張三豐';  
9.  p_para3 := v_name;  
10.  dbms_output.put_line('p_para1:'||p_para1);  
11.  dbms_output.put_line('p_para2:'||p_para2);  
12.  dbms_output.put_line('p_para3:'||p_para3);  
13.end;  
14. 
15.SQL> var p1 varchar2(10);  
16.SQL> var p2 varchar2(10);  
17.SQL> var p3 varchar2(10);  
18.SQL> exec :p1 :='aaaa';  
19.SQL> exec :p2 :='bbbb';  
20.SQL> exec :p3 :='cccc';  
21.SQL> exec proc1(:p1,:p2,:p3);  
22.p_para1:aaaa  
23.p_para2:  
24.p_para3:張三豐  
25.SQL> exec dbms_output.put_line(:p2);  
26. 
27. 
28.PL/SQL procedure successfully completed  
29.p2  
30.--------- 
create or replace procedure proc1(
  p_para1 varchar2,
  p_para2 out varchar2,
  p_para3 in out varchar2
)as
 v_name varchar2(20);
begin
  v_name := '張三豐';
  p_para3 := v_name;
  dbms_output.put_line('p_para1:'||p_para1);
  dbms_output.put_line('p_para2:'||p_para2);
  dbms_output.put_line('p_para3:'||p_para3);
end;

SQL> var p1 varchar2(10);
SQL> var p2 varchar2(10);
SQL> var p3 varchar2(10);
SQL> exec :p1 :='aaaa';
SQL> exec :p2 :='bbbb';
SQL> exec :p3 :='cccc';
SQL> exec proc1(:p1,:p2,:p3);
p_para1:aaaa
p_para2:
p_para3:張三豐
SQL> exec dbms_output.put_line(:p2);

PL/SQL procedure successfully completed
p2
---------
INOUT 是真正的按引用傳遞參數。即可作為傳入參數也可以作為傳出參數。

Java代碼
1.1.3 存儲過程參數寬度  
2.create or replace procedure proc1(  
3.  p_para1 varchar2,  
4.  p_para2 out varchar2,  
5.  p_para3 in out varchar2  
6.)as   
7. v_name varchar2(2);  
8.begin  
9.  v_name := p_para1;  
10.end;  
11. 
12.SQL> var p1 varchar2(10);  
13.SQL> var p2 varchar2(20);  
14.SQL> var p3 varchar2(30);  
15.SQL> exec :p1 :='aaaaaa';  
16.SQL> exec proc1(:p1,:p2,:p3);  
17.      
18.      
19.ORA-06502: PL/SQL: numeric or value error: character string buffer too small  
20.ORA-06512: at "LIFEMAN.PROC1", line 8 
21.ORA-06512: at line 1 
1.3 存儲過程參數寬度
create or replace procedure proc1(
  p_para1 varchar2,
  p_para2 out varchar2,
  p_para3 in out varchar2
)as
 v_name varchar2(2);
begin
  v_name := p_para1;
end;

SQL> var p1 varchar2(10);
SQL> var p2 varchar2(20);
SQL> var p3 varchar2(30);
SQL> exec :p1 :='aaaaaa';
SQL> exec proc1(:p1,:p2,:p3);
 
 
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "LIFEMAN.PROC1", line 8
ORA-06512: at line 1
首先，我們要明白，我們無法在存儲過程的定義中指定存儲參數的寬度，也就導致了我們無法在存儲過程中控制傳入變量的寬度。這個寬度是完全由外部傳入時決定的。
我們再來看看OUT類型的參數的寬度。

Java代碼
1.create or replace procedure proc1(  
2.  p_para1 varchar2,  
3.  p_para2 out varchar2,  
4.  p_para3 in out varchar2  
5.)as   
6. v_name varchar2(2);  
7.begin  
8.  p_para2 :='aaaaaaaaaaaaaaaaaaaa';  
9.end;  
10.SQL> var p1 varchar2(1);  
11.SQL> var p2 varchar2(1);  
12.SQL> var p3 varchar2(1);  
13.SQL> exec :p2 :='a';  
14.SQL> exec proc1(:p1,:p2,:p3); 
create or replace procedure proc1(
  p_para1 varchar2,
  p_para2 out varchar2,
  p_para3 in out varchar2
)as
 v_name varchar2(2);
begin
  p_para2 :='aaaaaaaaaaaaaaaaaaaa';
end;
SQL> var p1 varchar2(1);
SQL> var p2 varchar2(1);
SQL> var p3 varchar2(1);
SQL> exec :p2 :='a';
SQL> exec proc1(:p1,:p2,:p3);在該過程中，p_para2被賦予了20個字符a.
而在外部的調用過程中，p2這個參數僅僅被定義為varchar2(1).
而把p2作為參數調用這個過程，卻并沒有報錯。而且它的真實值就是20個a

Java代碼
1.SQL> select dump(:p2) from dual;  
2.DUMP(:P2)  
3.---------------------------------------------------------------------------  
4.Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97 
5.p2  
6.---------  
7.aaaaaaaaaaaaaaaaaaaa  
8.      
9.    再來看看IN OUT參數的寬度  
10.create or replace procedure proc1(  
11.  p_para1 varchar2,  
12.  p_para2 out varchar2,  
13.  p_para3 in out varchar2  
14.)as   
15. v_name varchar2(2);  
16.begin  
17.  p_para3 :='aaaaaaaaaaaaaaaaaaaa';  
18.end;  
19. 
20.SQL> var p1 varchar2(1);  
21.SQL> var p2 varchar2(1);  
22.SQL> var p3 varchar2(1);  
23.SQL> exec proc1(:p1,:p2,:p3); 
SQL> select dump(:p2) from dual;
DUMP(:P2)
---------------------------------------------------------------------------
Typ=1 Len=20: 97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97,97
p2
---------
aaaaaaaaaaaaaaaaaaaa
 
 再來看看IN OUT參數的寬度
create or replace procedure proc1(
  p_para1 varchar2,
  p_para2 out varchar2,
  p_para3 in out varchar2
)as
 v_name varchar2(2);
begin
  p_para3 :='aaaaaaaaaaaaaaaaaaaa';
end;

SQL> var p1 varchar2(1);
SQL> var p2 varchar2(1);
SQL> var p3 varchar2(1);
SQL> exec proc1(:p1,:p2,:p3);執行這個過程，仍然正確執行。

可見，對于IN參數，其寬度是由外部決定。
對于OUT 和IN OUT 參數，其寬度是由存儲過程內部決定。
因此，在寫存儲過程時，對參數的寬度進行說明是非常有必要的，最明智的方法就是參數的數據類型使用%type。這樣雙方就達成了一致。

1.3 參數的默認值
存儲過程的參數可以設置默認值

Java代碼
1.create or replace procedure procdefault(p1 varchar2,  
2.                                        p2 varchar2 default 'mark')  
3.as   
4.begin  
5.  dbms_output.put_line(p2);  
6.end;  
7. 
8.SQL> set serveroutput on;  
9.SQL> exec procdefault('a'); 
create or replace procedure procdefault(p1 varchar2,
                                        p2 varchar2 default 'mark')
as
begin
  dbms_output.put_line(p2);
end;

SQL> set serveroutput on;
SQL> exec procdefault('a');mark
可以通過default 關鍵字為存儲過程的參數指定默認值。在對存儲過程調用時，就可以省略默認值。
需要注意的是：默認值僅僅支持IN傳輸類型的參數。OUT 和 IN OUT不能指定默認值

對于有默認值的參數不是排在最后的情況。

Java代碼
1.create or replace procedure procdefault2(p1 varchar2 default 'remark',  
2.                                        p2 varchar2 )  
3.as   
4.begin  
5.  dbms_output.put_line(p1);  
6.end; 
create or replace procedure procdefault2(p1 varchar2 default 'remark',
                                        p2 varchar2 )
as
begin
  dbms_output.put_line(p1);
end;第一個參數有默認值，第二個參數沒有。如果我們想使用第一個參數的默認值時
exec procdefault2('aa');
這樣是會報錯的。
那怎么變呢？可以指定參數的值。

Java代碼
1.SQL> exec procdefault2(p2 =>'aa'); 
SQL> exec procdefault2(p2 =>'aa');
remark
這樣就OK了，指定aa傳給參數p2

2. 存儲過程內部塊
2.1 內部塊
我們知道了存儲過程的結構，語句塊由begin開始，以end結束。這些塊是可以嵌套。在語句塊中可以嵌套任何以下的塊。

Java代碼
1.Declare … begin … exception … end;  
2.create or replace procedure innerBlock(p1 varchar2)  
3.as   
4.  o1 varchar2(10) := 'out1';  
5.begin  
6.  dbms_output.put_line(o1);  
7.  declare   
8.    inner1 varchar2(20);  
9.  begin  
10.    inner1 :='inner1';  
11.    dbms_output.put_line(inner1);  
12. 
13.    declare   
14.      inner2 varchar2(20);  
15.    begin  
16.      inner2 := 'inner2';  
17.      dbms_output.put_line(inner2);  
18.    end;  
19.  exception   
20.    when others then  
21.      null;  
22.  end;  
23.end; 
Declare … begin … exception … end;
create or replace procedure innerBlock(p1 varchar2)
as
  o1 varchar2(10) := 'out1';
begin
  dbms_output.put_line(o1);
  declare
    inner1 varchar2(20);
  begin
    inner1 :='inner1';
    dbms_output.put_line(inner1);

    declare
      inner2 varchar2(20);
    begin
      inner2 := 'inner2';
      dbms_output.put_line(inner2);
    end;
  exception
    when others then
      null;
  end;
end;需要注意變量的作用域。

3.存儲過程的常用技巧
3.1 哪種集合?
我們在使用存儲過程的時候經常需要處理記錄集，也就是多條數據記錄。分為單列多行和多列多行，這些類型都可以稱為集合類型。我們在這里進行比較這些集合類型，以便于在編程時做出正確的選擇。
索引表,也稱為pl/sql表，不能存儲于數據庫中，元素的個數沒有限制,下標可以為負值。

Java代碼
1.type t_table is table of varchar2(20) index by binary_integer;  
2. v_student t_table; 
type t_table is table of varchar2(20) index by binary_integer;
 v_student t_table;varchar2(20)表示存放元素的數據類型，binary_integer表示元素下標的數據類型。
嵌套表，索引表沒有 index by子句就是嵌套表，它可以存放于數據中，元素個數無限，下標從1開始，并且需要初始化

Java代碼
1.type t_nestTable is table of varchar2(20);  
2.v_class t_nestTable ; 
type t_nestTable is table of varchar2(20);
v_class t_nestTable ;僅是這樣聲明是不能使用的，必須對嵌套表進行初始化，對嵌套表進行初始化可以使用它的構造函數

Java代碼
1.v_class :=t_nestTable('a','b','c'); 
v_class :=t_nestTable('a','b','c');變長數組，變長數組與高級語言的數組類型非常相似，下標以1開始，元素個數有限。

Java代碼
1.type t_array is varray (20) of varchar2(20); 
type t_array is varray (20) of varchar2(20);
varray(20)就定義了變長數組的最大元素個數是20個
變長數組與嵌套表一樣，也可以是數據表列的數據類型。
同時，變長數組的使用也需要事先初始化。

類型 可存儲于數據庫 元素個數 是否需初始化 初始下標值
索引表 否 無限 不需
嵌套表 可 無限 需 1
可變數組 可 有限(自定義) 需 1

由此可見，如果僅僅是在存儲過程中當作集合變量使用，索引表是最好的選擇。

3.2 選用何種游標?
顯示游標分為：普通游標，參數化游標和游標變量三種。
下面以一個過程來進行說明

Java代碼
1.create or replace procedure proccursor(p varchar2)  
2.as   
3.v_rownum number(10) := 1;  
4.cursor c_postype is select pos_type from pos_type_tbl where rownum =1;  
5.cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;  
6.cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;  
7.type t_postype is ref cursor ;  
8.c_postype3 t_postype;  
9.v_postype varchar2(20);  
10.begin  
11.  open c_postype;  
12.  fetch c_postype into v_postype;  
13.  dbms_output.put_line(v_postype);  
14.  close c_postype;  
15.  open c_postype1;  
16.  fetch c_postype1 into v_postype;  
17.  dbms_output.put_line(v_postype);  
18.  close c_postype1;  
19.  open c_postype2(1);  
20.  fetch c_postype2 into v_postype;  
21.  dbms_output.put_line(v_postype);  
22.  close c_postype2;  
23.  open c_postype3 for select pos_type from pos_type_tbl where rownum =1;  
24.  fetch c_postype3 into v_postype;  
25.  dbms_output.put_line(v_postype);  
26.  close c_postype3;  
27.end; 
create or replace procedure proccursor(p varchar2)
as
v_rownum number(10) := 1;
cursor c_postype is select pos_type from pos_type_tbl where rownum =1;
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
type t_postype is ref cursor ;
c_postype3 t_postype;
v_postype varchar2(20);
begin
  open c_postype;
  fetch c_postype into v_postype;
  dbms_output.put_line(v_postype);
  close c_postype;
  open c_postype1;
  fetch c_postype1 into v_postype;
  dbms_output.put_line(v_postype);
  close c_postype1;
  open c_postype2(1);
  fetch c_postype2 into v_postype;
  dbms_output.put_line(v_postype);
  close c_postype2;
  open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
  fetch c_postype3 into v_postype;
  dbms_output.put_line(v_postype);
  close c_postype3;
end;
cursor c_postype is select pos_type from pos_type_tbl where rownum =1
這一句是定義了一個最普通的游標，把整個查詢已經寫死,調用時不可以作任何改變。
cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum;
這一句并沒有寫死，查詢參數由變量v_rownum來決定。需要注意的是v_rownum必須在這個游標定義之前聲明。
cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum;
這一條語句與第二條作用相似，都是可以為游標實現動態的查詢。但是它進一步的縮小了參數的作用域范圍。但是可讀性降低了不少。
type t_postype is ref cursor ;
c_postype3 t_postype;
先定義了一個引用游標類型，然后再聲明了一個游標變量。
open c_postype3 for select pos_type from pos_type_tbl where rownum =1;
然后再用open for 來打開一個查詢。需要注意的是它可以多次使用,用來打開不同的查詢。
從動態性來說，游標變量是最好用的，但是閱讀性也是最差的。
注意，游標的定義只能用使關鍵字IS，它與AS不通用。

3.3 游標循環最佳策略
我們在進行PL/SQL編程時，經常需要循環讀取結果集的數據。進行逐行處理，這個過程就需要對游標進行循環。對游標進行循環的方法有多種，我們在此一一分析。

Java代碼
1.create or replace procedure proccycle(p varchar2)  
2.as   
3.cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6;  
4.v_postype varchar2(20);  
5.v_description varchar2(50);  
6.begin  
7.open c_postype;  
8.  if c_postype%found then  
9.    dbms_output.put_line('found true');  
10.  elsif c_postype%found = false then  
11.    dbms_output.put_line('found false');  
12.  else 
13.    dbms_output.put_line('found null');  
14.  end if;  
15.  loop  
16.   fetch c_postype into v_postype,v_description ;  
17.   exit when c_postype%notfound;  
18.   dbms_output.put_line('postype:'||v_postype||',description:'||v_description);  
19.  end loop;  
20.  close c_postype;  
21.dbms_output.put_line('---loop end---');  
22.  open c_postype;  
23.    fetch c_postype into v_postype,v_description;  
24.    while c_postype%found loop  
25.      dbms_output.put_line('postype:'||v_postype||',description:'||v_description);  
26.      fetch c_postype into v_postype,v_description ;  
27.    end loop;  
28. 
29.  close c_postype;  
30.dbms_output.put_line('---while end---');  
31.  for v_pos in c_postype loop  
32.    v_postype := v_pos.pos_type;  
33.    v_description := v_pos.description;  
34.    dbms_output.put_line('postype:'||v_postype||',description:'||v_description);  
35.  end loop;  
36.  dbms_output.put_line('---for end---');  
37.end; 
create or replace procedure proccycle(p varchar2)
as
cursor c_postype is select pos_type, description from pos_type_tbl where rownum < 6;
v_postype varchar2(20);
v_description varchar2(50);
begin
open c_postype;
  if c_postype%found then
    dbms_output.put_line('found true');
  elsif c_postype%found = false then
    dbms_output.put_line('found false');
  else
    dbms_output.put_line('found null');
  end if;
  loop
   fetch c_postype into v_postype,v_description ;
   exit when c_postype%notfound;
   dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
  end loop;
  close c_postype;
dbms_output.put_line('---loop end---');
  open c_postype;
    fetch c_postype into v_postype,v_description;
    while c_postype%found loop
      dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
      fetch c_postype into v_postype,v_description ;
    end loop;

  close c_postype;
dbms_output.put_line('---while end---');
  for v_pos in c_postype loop
    v_postype := v_pos.pos_type;
    v_description := v_pos.description;
    dbms_output.put_line('postype:'||v_postype||',description:'||v_description);
  end loop;
  dbms_output.put_line('---for end---');
end;
使用游標之前需要開打游標,open cursor，循環完后再關閉游標close cursor.
這是使用游標應該慎記于心的法則。
上面的過程演示了游標循環的三種方法。
在討論循環方法之前，我們先看看%found和%notfound這些游標的屬性。

Java代碼
1.open c_postype;  
2. if c_postype%found then  
3.   dbms_output.put_line('found true');  
4. elsif c_postype%found = false then  
5.   dbms_output.put_line('found false');  
6. else 
7.   dbms_output.put_line('found null');  
8. end if; 
 open c_postype;
  if c_postype%found then
    dbms_output.put_line('found true');
  elsif c_postype%found = false then
    dbms_output.put_line('found false');
  else
    dbms_output.put_line('found null');
  end if;在打開一個游標之后，馬上檢查它的%found或%notfound屬性，它得到的結果即不是true也不是false.而是null.必須執行一條fetch語句后，這些屬性才有值。

第一種使用loop 循環

Java代碼
1.loop  
2.   fetch c_postype into v_postype,v_description ;  
3.   exit when c_postype%notfound;  
4.   ……  
5.end loop 
loop
   fetch c_postype into v_postype,v_description ;
   exit when c_postype%notfound;
   ……
end loop這里需要注意，exit when語句一定要緊跟在fetch之后。必避免多余的數據處理。
處理邏輯需要跟在exit when之后。這一點需要多加小心。
循環結束后要記得關閉游標。

第二種使用while循環。

Java代碼
1.   fetch c_postype into v_postype,v_description;  
2.while c_postype%found loop  
3.   ……  
4.      fetch c_postype into v_postype,v_description ;  
5.end loop; 
   fetch c_postype into v_postype,v_description;
while c_postype%found loop
   ……
      fetch c_postype into v_postype,v_description ;
end loop;
我們知道了一個游標打開后，必須執行一次fetch語句，游標的屬性才會起作用。所以使用while 循環時，就需要在循環之前進行一次fetch動作。
而且數據處理動作必須放在循環體內的fetch方法之前。循環體內的fetch方法要放在最后。否則就會多處理一次。這一點也要非常的小心。
總之，使用while來循環處理游標是最復雜的方法。

第三種 for循環

Java代碼
1.for v_pos in c_postype loop  
2.   v_postype := v_pos.pos_type;  
3.   v_description := v_pos.description;  
4.   …  
5. end loop; 
 for v_pos in c_postype loop
    v_postype := v_pos.pos_type;
    v_description := v_pos.description;
    …
  end loop;可見for循環是比較簡單實用的方法。
首先，它會自動open和close游標。解決了你忘記打開或關閉游標的煩惱。
其它,自動定義了一個記錄類型及聲明該類型的變量，并自動fetch數據到這個變量中。
我們需要注意v_pos 這個變量無需要在循環外進行聲明，無需要為其指定數據類型。
它應該是一個記錄類型，具體的結構是由游標決定的。
這個變量的作用域僅僅是在循環體內。
把v_pos看作一個記錄變量就可以了，如果要獲得某一個值就像調用記錄一樣就可以了。
如v_pos.pos_type
由此可見，for循環是用來循環游標的最好方法。高效，簡潔，安全。
但遺憾的是，常常見到的卻是第一種方法。所以從今之后得改變這個習慣了。

3.4 select into不可乎視的問題
我們知道在pl/sql中要想從數據表中向變量賦值，需要使用select into 子句。
但是它會帶動來一些問題，如果查詢沒有記錄時，會拋出no_data_found異常。
如果有多條記錄時，會拋出too_many_rows異常。
這個是比較糟糕的。一旦拋出了異常，就會讓過程中斷。特別是no_data_found這種異常，沒有嚴重到要讓程序中斷的地步，可以完全交給由程序進行處理。

Java代碼
1.create or replace procedure procexception(p varchar2)  
2.as   
3.  v_postype varchar2(20);  
4.begin  
5.   select pos_type into v_postype from pos_type_tbl where 1=0;  
6.    dbms_output.put_line(v_postype);  
7.end;  
8.     
create or replace procedure procexception(p varchar2)
as
  v_postype varchar2(20);
begin
   select pos_type into v_postype from pos_type_tbl where 1=0;
    dbms_output.put_line(v_postype);
end;
 執行這個過程

Java代碼
1.SQL> exec procexception('a');  
2.報錯  
3.ORA-01403: no data found  
4.ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6 
5.ORA-06512: at line 1 
SQL> exec procexception('a');
報錯
ORA-01403: no data found
ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6
ORA-06512: at line 1
處理這個有三個辦法
1． 直接加上異常處理。

Java代碼
1.create or replace procedure procexception(p varchar2)  
2.as   
3.  v_postype varchar2(20);  
4.    
5.begin  
6.   select pos_type into v_postype from pos_type_tbl where 1=0;  
7.    dbms_output.put_line(v_postype);  
8.exception   
9.  when no_data_found then  
10.    dbms_output.put_line('沒找到數據');  
11.end; 
create or replace procedure procexception(p varchar2)
as
  v_postype varchar2(20);
 
begin
   select pos_type into v_postype from pos_type_tbl where 1=0;
    dbms_output.put_line(v_postype);
exception
  when no_data_found then
    dbms_output.put_line('沒找到數據');
end;這樣做換湯不換藥，程序仍然被中斷。可能這樣不是我們所想要的。
2. select into做為一個獨立的塊，在這個塊中進行異常處理

Java代碼
1.create or replace procedure procexception(p varchar2)  
2.as   
3.  v_postype varchar2(20);  
4.    
5.begin  
6.  begin  
7.   select pos_type into v_postype from pos_type_tbl where 1=0;  
8.    dbms_output.put_line(v_postype);  
9. exception   
10.  when no_data_found then  
11.    v_postype := '';  
12.  end;  
13.  dbms_output.put_line(v_postype);  
14.end; 
create or replace procedure procexception(p varchar2)
as
  v_postype varchar2(20);
 
begin
  begin
   select pos_type into v_postype from pos_type_tbl where 1=0;
    dbms_output.put_line(v_postype);
 exception
  when no_data_found then
    v_postype := '';
  end;
  dbms_output.put_line(v_postype);
end;這是一種比較好的處理方式了。不會因為這個異常而引起程序中斷。
3.使用游標

Java代碼
1.create or replace procedure procexception(p varchar2)  
2.as   
3.  v_postype varchar2(20);  
4.  cursor c_postype is select pos_type  from pos_type_tbl where 1=0;  
5.begin  
6.  open c_postype;  
7.    fetch c_postype into v_postype;  
8.  close c_postype;  
9.  dbms_output.put_line(v_postype);  
10.end; 
create or replace procedure procexception(p varchar2)
as
  v_postype varchar2(20);
  cursor c_postype is select pos_type  from pos_type_tbl where 1=0;
begin
  open c_postype;
    fetch c_postype into v_postype;
  close c_postype;
  dbms_output.put_line(v_postype);
end;這樣就完全的避免了no_data_found異常。完全交由程序員來進行控制了。

第二種情況是too_many_rows 異常的問題。
Too_many_rows 這個問題比起no_data_found要復雜一些。
給一個變量賦值時，但是查詢結果有多個記錄。
處理這種問題也有兩種情況：
1． 多條數據是可以接受的，也就是說從結果集中隨便取一個值就行。這種情況應該很極端了吧，如果出現這種情況，也說明了程序的嚴謹性存在問題。
2． 多條數據是不可以被接受的，在這種情況肯定是程序的邏輯出了問題，也說是說原來根本就不會想到它會產生多條記錄。
對于第一種情況，就必須采用游標來處理，而對于第二種情況就必須使用內部塊來處理，重新拋出異常。
多條數據可以接受,隨便取一條，這個跟no_data_found的處理方式一樣，使用游標。
我這里僅說第二種情況，不可接受多條數據，但是不要忘了處理no_data_found哦。這就不能使用游標了，必須使用內部塊。

Java代碼
1.create or replace procedure procexception2(p varchar2)  
2.as   
3.  v_postype varchar2(20);  
4.   
5.begin  
6.  begin  
7.    select pos_type into v_postype from pos_type_tbl where rownum < 5;  
8.  exception  
9.    when no_data_found then  
10.      v_postype :=null;  
11.    when too_many_rows then  
12.      raise_application_error(-20000,'對v_postype賦值時，找到多條數據');  
13.  end;  
14. dbms_output.put_line(v_postype);  
15.end; 
create or replace procedure procexception2(p varchar2)
as
  v_postype varchar2(20);
 
begin
  begin
    select pos_type into v_postype from pos_type_tbl where rownum < 5;
  exception
    when no_data_found then
      v_postype :=null;
    when too_many_rows then
      raise_application_error(-20000,'對v_postype賦值時，找到多條數據');
  end;
 dbms_output.put_line(v_postype);
end;需要注意的是一定要加上對no_data_found的處理，對出現多條記錄的情況則繼續拋出異常，讓上一層來處理。
總之對于select into的語句需要注意這兩種情況了。需要妥當處理啊。

3.5 在存儲過程中返回結果集
我們使用存儲過程都是返回值都是單一的，有時我們需要從過程中返回一個集合。即多條數據。這有幾種解決方案。比較簡單的做法是寫臨時表，但是這種做法不靈活。而且維護麻煩。我們可以使用嵌套表來實現.沒有一個集合類型能夠與java的jdbc類型匹配。這就是對象與關系數據庫的阻抗吧。數據庫的對象并不能夠完全轉換為編程語言的對象，還必須使用關系數據庫的處理方式。

Java代碼
1.create or replace package procpkg is  
2.   type refcursor is ref cursor;  
3.   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  
4.end procpkg;  
5. 
6.create or replace package body procpkg is  
7.  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)  
8.  is  
9.    v_posTypeList PosTypeTable;  
10.  begin  
11.    v_posTypeList :=PosTypeTable();--初始化嵌套表  
12.    v_posTypeList.extend;  
13.    v_posTypeList(1) := PosType('A001','客戶資料變更');  
14.    v_posTypeList.extend;  
15.    v_posTypeList(2) := PosType('A002','團體資料變更');  
16.    v_posTypeList.extend;  
17.    v_posTypeList(3) := PosType('A003','受益人變更');  
18.    v_posTypeList.extend;  
19.    v_posTypeList(4) := PosType('A004','續期交費方式變更');  
20.    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));  
21.  end;  
22.end procpkg; 
create or replace package procpkg is
   type refcursor is ref cursor;
   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);
end procpkg;

create or replace package body procpkg is
  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)
  is
    v_posTypeList PosTypeTable;
  begin
    v_posTypeList :=PosTypeTable();--初始化嵌套表
    v_posTypeList.extend;
    v_posTypeList(1) := PosType('A001','客戶資料變更');
    v_posTypeList.extend;
    v_posTypeList(2) := PosType('A002','團體資料變更');
    v_posTypeList.extend;
    v_posTypeList(3) := PosType('A003','受益人變更');
    v_posTypeList.extend;
    v_posTypeList(4) := PosType('A004','續期交費方式變更');
    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));
  end;
end procpkg;
在包頭中定義了一個游標變量,并把它作為存儲過程的參數類型。
在存儲過程中定義了一個嵌套表變量，對數據寫進嵌套表中，然后把嵌套表進行類型轉換為table,游標變量從這個嵌套表中進行查詢。外部程序調用這個游標。
所以這個過程需要定義兩個類型。

Java代碼
1.create or replace type PosType as Object (  
2.  posType varchar2(20),  
3.  description varchar2(50)  
4.); 
create or replace type PosType as Object (
  posType varchar2(20),
  description varchar2(50)
);create or replace type PosTypeTable is table of PosType;
需要注意，這兩個類型不能定義在包頭中，必須單獨定義，這樣java層才能使用。

在外部通過pl/sql來調用這個過程非常簡單。

Java代碼
1.set serveroutput on;  
2.declare   
3.  type refcursor is ref cursor;  
4.  v_ref_postype refcursor;  
5.  v_postype varchar2(20);  
6.  v_desc varchar2(50);  
7.begin  
8.  procpkg.procrefcursor('a',v_ref_postype);  
9.  loop  
10.    fetch  v_ref_postype into v_postype,v_desc;  
11.    exit when v_ref_postype%notfound;  
12.    dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc);  
13.  end loop;  
14.end; 
set serveroutput on;
declare
  type refcursor is ref cursor;
  v_ref_postype refcursor;
  v_postype varchar2(20);
  v_desc varchar2(50);
begin
  procpkg.procrefcursor('a',v_ref_postype);
  loop
    fetch  v_ref_postype into v_postype,v_desc;
    exit when v_ref_postype%notfound;
    dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc);
  end loop;
end;
注意：對于游標變量，不能使用for循環來處理。因為for循環會隱式的執行open動作。而通過open for來打開的游標%isopen是為true的。也就是默認打開的。Open一個已經open的游標是錯誤的。所以不能使用for循環來處理游標變量。

我們主要討論的是如何通過jdbc調用來處理這個輸出參數。

Java代碼
1.conn = this.getDataSource().getConnection();  
2.CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}");  
3.call.setString(1, null);  
4.call.registerOutParameter(2, OracleTypes.CURSOR);  
5.call.execute();  
6.ResultSet rsResult = (ResultSet) call.getObject(2);  
7.while (rsResult.next()) {  
8.  String posType = rsResult.getString("posType");  
9.  String description = rsResult.getString("description");  
10.  ......  
11.} 
conn = this.getDataSource().getConnection();
CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}");
call.setString(1, null);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
ResultSet rsResult = (ResultSet) call.getObject(2);
while (rsResult.next()) {
  String posType = rsResult.getString("posType");
  String description = rsResult.getString("description");
  ......
}
這就是jdbc的處理方法。

Ibatis處理方法：
1.參數配置

Java代碼
1.<parameterMap id="PosTypeMAP" class="java.util.Map">   
2. <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" />   
3. <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" />   
4.</parameterMap>  
5. 
6.2.調用過程  
7.  <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">  
8.      {call procpkg.procrefcursor(?,?)}  
9.  </procedure>  
10. 
11.3.定義自己的處理器  
12.  public class CursorHandlerCallBack implements TypeHandler{  
13.    public Object getResult(CallableStatement cs, int index) throws SQLException {  
14.        ResultSet rs = (ResultSet)cs.getObject(index);  
15.        List result = new ArrayList();  
16.        while(rs.next()) {  
17.            String postype =rs.getString(1);  
18.            String description = rs.getString(2);  
19.            CodeTableItemDTO posTypeItem = new CodeTableItemDTO();  
20.            posTypeItem.setCode(postype);  
21.            posTypeItem.setDescription(description);  
22.            result.add(posTypeItem);  
23.        }  
24.        return result;  
25.    }  
26. 
27. 
28. 
29.4. dao方法  
30.    public List procPostype() {  
31.        String p = "";  
32.        Map para = new HashMap();  
33.        para.put("p",p);  
34.        para.put("p_ref_postypeList",null);  
35.         this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);  
36.         return (List)para.get("p_ref_postypeList");  
37.    } 
<parameterMap id="PosTypeMAP" class="java.util.Map">
 <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" />
 <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" />
</parameterMap>

2.調用過程
  <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">
      {call procpkg.procrefcursor(?,?)}
  </procedure>

3.定義自己的處理器
  public class CursorHandlerCallBack implements TypeHandler{
 public Object getResult(CallableStatement cs, int index) throws SQLException {
  ResultSet rs = (ResultSet)cs.getObject(index);
        List result = new ArrayList();
  while(rs.next()) {
   String postype =rs.getString(1);
   String description = rs.getString(2);
   CodeTableItemDTO posTypeItem = new CodeTableItemDTO();
   posTypeItem.setCode(postype);
   posTypeItem.setDescription(description);
   result.add(posTypeItem);
  }
  return result;
 }

4. dao方法
 public List procPostype() {
  String p = "";
  Map para = new HashMap();
  para.put("p",p);
  para.put("p_ref_postypeList",null);
   this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);
   return (List)para.get("p_ref_postypeList");
 }
這個跟jdbc的方式非常的相似.
我們使用的是ibatis的2.0版本，比較麻煩。
如果是使用2.2以上版本就非常簡單的。
因為可以在parameterMap中定義一個resultMap.這樣就無需要自己定義處理器了。
可以從分析2.0和2.0的dtd文件知道。

上面的兩種方式都是非常的復雜，如果僅僅是需要返回一個結果集，那就完全可以使用函數來實現了。

Java代碼
1.create or replace package procpkg is  
2.   type refcursor is ref cursor;  
3.   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);  
4.   function procpostype(p varchar2) return PosTypeTable;   
5.end procpkg;  
6. 
7.create or replace package body procpkg is  
8.  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)  
9.  is  
10.    v_posTypeList PosTypeTable;  
11.  begin  
12.    v_posTypeList :=PosTypeTable();--初始化嵌套表  
13.    v_posTypeList.extend;  
14.    v_posTypeList(1) := PosType('A001','客戶資料變更');  
15.    v_posTypeList.extend;  
16.    v_posTypeList(2) := PosType('A002','團體資料變更');  
17.    v_posTypeList.extend;  
18.    v_posTypeList(3) := PosType('A003','受益人變更');  
19.    v_posTypeList.extend;  
20.    v_posTypeList(4) := PosType('A004','續期交費方式變更');  
21.    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));  
22.  end;  
23. 
24.  function procpostype(p varchar2) return PosTypeTable  
25.  as  
26.   v_posTypeList PosTypeTable;  
27.  begin  
28.      v_posTypeList :=PosTypeTable();--初始化嵌套表  
29.    v_posTypeList.extend;  
30.    v_posTypeList(1) := PosType('A001','客戶資料變更');  
31.    v_posTypeList.extend;  
32.    v_posTypeList(2) := PosType('A002','團體資料變更');  
33.    v_posTypeList.extend;  
34.    v_posTypeList(3) := PosType('A003','受益人變更');  
35.    v_posTypeList.extend;  
36.    v_posTypeList(4) := PosType('A004','續期交費方式變更');  
37.    return  v_posTypeList;  
38.  end;  
39.end procpkg; 
create or replace package procpkg is
   type refcursor is ref cursor;
   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);
   function procpostype(p varchar2) return PosTypeTable;
end procpkg;

create or replace package body procpkg is
  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)
  is
    v_posTypeList PosTypeTable;
  begin
    v_posTypeList :=PosTypeTable();--初始化嵌套表
    v_posTypeList.extend;
    v_posTypeList(1) := PosType('A001','客戶資料變更');
    v_posTypeList.extend;
    v_posTypeList(2) := PosType('A002','團體資料變更');
    v_posTypeList.extend;
    v_posTypeList(3) := PosType('A003','受益人變更');
    v_posTypeList.extend;
    v_posTypeList(4) := PosType('A004','續期交費方式變更');
    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));
  end;

  function procpostype(p varchar2) return PosTypeTable
  as
   v_posTypeList PosTypeTable;
  begin
      v_posTypeList :=PosTypeTable();--初始化嵌套表
    v_posTypeList.extend;
    v_posTypeList(1) := PosType('A001','客戶資料變更');
    v_posTypeList.extend;
    v_posTypeList(2) := PosType('A002','團體資料變更');
    v_posTypeList.extend;
    v_posTypeList(3) := PosType('A003','受益人變更');
    v_posTypeList.extend;
    v_posTypeList(4) := PosType('A004','續期交費方式變更');
    return  v_posTypeList;
  end;
end procpkg;
ibatis配置

Java代碼
1.<resultMap id="posTypeResultMap" class="com.palic.elis.pos.common.dto.CodeTableItemDTO">  
2.   <result property="code" column="posType"/>  
3.   <result property="description" column="description"/>  
4. </resultMap>  
5. 
6.  <select id="procPostype" resultMap="posTypeResultMap">  
7.    select * from table(cast (procpkg.procpostype(#value#) as PosTypeTable))  
8.  </select> 
<resultMap id="posTypeResultMap" class="com.palic.elis.pos.common.dto.CodeTableItemDTO">
   <result property="code" column="posType"/>
   <result property="description" column="description"/>
 </resultMap>

  <select id="procPostype" resultMap="posTypeResultMap">
    select * from table(cast (procpkg.procpostype(#value#) as PosTypeTable))
  </select>Dao的寫法跟普通查詢一樣

Java代碼
1.public List queryPostype() {  
2.  return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);  
3.} 
public List queryPostype() {
  return this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procPostype", null);
}
有幾點需要注意，這里不能使用索引表，而是嵌套表。
另外就是把嵌套表強制轉換為普通表。
轉載自：http://www.javaeye.com/topic/311176

Oracle中Cursor介紹
轉自：http://www.javaeye.com/topic/649874
一  概念
游標是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：

Sql代碼

1. Set Serveroutput on;   
2.   
3. begin  
4.     update t_contract_master set liability_state = 1 where policy_code = '123456789';   
5.        
6.     if SQL%Found then  
7.        dbms_output.put_line('the Policy is updated successfully.');   
8.        commit;   
9.     else  
10.       dbms_output.put_line('the policy is updated failed.');   
11.     end if;   
12.   
13. end;   
14.   
15. /  

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;
/

在PL/SQL中run:

Sql代碼

1. SQL>    
2.     
3. the policy is updated failed.   
4.     
5. PL/SQL procedure successfully completed  

SQL>
the policy is updated failed.
PL/SQL procedure successfully completed

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用法。
1）

Sql代碼

1. Set serveroutput on;   
2.   
3. declare    
4.     ---define Cursor   
5.     Cursor cur_policy is  
6.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
7.      from t_contract_master cm   
8.      where cm.liability_state = 2   
9.      and cm.policy_type = 1   
10.      and cm.policy_cate in ('2','3','4')   
11.      and rownum < 5   
12.      order by cm.policy_code desc;   
13.     curPolicyInfo cur_policy%rowtype;---定義游標變量   
14. Begin  
15.    open cur_policy; ---open cursor   
16.    Loop    
17.      --deal with extraction data from DB   
18.      Fetch cur_policy into curPolicyInfo;   
19.      Exit when cur_policy%notfound;   
20.             
21.      Dbms_Output.put_line(curPolicyInfo.policy_code);   
22.    end loop;   
23.    Exception    
24.      when others then  
25.          close cur_policy;   
26.          Dbms_Output.put_line(Sqlerrm);   
27.             
28.    if cur_policy%isopen then     
29.     --close cursor    
30.       close cur_policy;   
31.    end if;   
32. end;   
33.   
34. /  

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;
/

2）

Sql代碼

1. Set serveroutput on;   
2.   
3. declare    
4.     Cursor cur_policy is  
5.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
6.      from t_contract_master cm   
7.      where cm.liability_state = 2   
8.      and cm.policy_type = 1   
9.      and cm.policy_cate in ('2','3','4')   
10.      and rownum < 5   
11.      order by cm.policy_code desc;   
12.      v_policyCode t_contract_master.policy_code%type;   
13.      v_applicantId t_contract_master.applicant_id%type;   
14.      v_periodPrem t_contract_master.period_prem%type;   
15.      v_bankCode t_contract_master.bank_code%type;   
16.      v_bankAccount t_contract_master.bank_account%type;   
17. Begin  
18.    open cur_policy;   
19.    Loop    
20.      Fetch cur_policy into v_policyCode,   
21.                            v_applicantId,   
22.                            v_periodPrem,   
23.                            v_bankCode,   
24.                            v_bankAccount;   
25.      Exit when cur_policy%notfound;   
26.             
27.      Dbms_Output.put_line(v_policyCode);   
28.    end loop;   
29.    Exception    
30.      when others then  
31.          close cur_policy;   
32.          Dbms_Output.put_line(Sqlerrm);   
33.             
34.    if cur_policy%isopen then      
35.       close cur_policy;   
36.    end if;   
37. end;   
38. /  

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;
/

3）

Sql代碼

1. Set serveroutput on;   
2.   
3. declare    
4.     Cursor cur_policy is  
5.      select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account   
6.      from t_contract_master cm   
7.      where cm.liability_state = 2   
8.      and cm.policy_type = 1   
9.      and cm.policy_cate in ('2','3','4')   
10.      and rownum < 5   
11.      order by cm.policy_code desc;   
12. Begin  
13.    For rec_Policy in cur_policy loop   
14.        Dbms_Output.put_line(rec_policy.policy_code);   
15.    end loop;   
16.    Exception    
17.      when others then  
18.          Dbms_Output.put_line(Sqlerrm);   
19.             
20. end;   
21.   
22. /  

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;
/

run pl/sql,執行結果如下：

Sql代碼

1. SQL>    
2.     
3. 8780203932   
4. 8780203227   
5. 8780203218   
6. 8771289268   
7.     
8. PL/SQL procedure successfully completed  

SQL>
8780203932
8780203227
8780203218
8771289268
PL/SQL procedure successfully completed

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：

Sql代碼

1. Set serveroutput on;   
2.   
3. Declare  
4.     ---define cursor type name   
5.     type cur_type is ref cursor;   
6.     cur_policy cur_type;   
7.     sqlStr varchar2(500);   
8.     rec_policy t_contract_master%rowtype;   
9. begin  
10.    ---define 動態Sql   
11.    sqlStr := 'select cm.policy_code, cm.applicant_id, cm.period_prem,cm.bank_code,cm.bank_account from t_contract_master cm   
12.      where cm.liability_state = 2    
13.      and cm.policy_type = 1    
14.      and cm.policy_cate in (2,3,4)    
15.      and rownum < 5    
16.      order by cm.policy_code desc ';   
17. ---Open Cursor   
18.   open cur_policy for sqlStr;   
19.   loop   
20.        fetch cur_policy into rec_policy.policy_code, rec_policy.applicant_id, rec_policy.period_prem,rec_policy.bank_code,rec_policy.bank_account;   
21.        exit when cur_policy%notfound;   
22.           
23.        Dbms_Output.put_line('Policy_code:'||rec_policy.policy_code);   
24.      
25.   end loop;   
26. close cur_policy;       
27.   
28. end;   
29. /  

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

Sql代碼

1. 1.  錯 誤 名 稱 錯誤代碼    錯 誤 含 義      
2. 2.  CURSOR_ALREADY_OPEN ORA_06511   試圖打開已經打開的游標      
3. 3.  INVALID_CURSOR  ORA_01001   試圖使用沒有打開的游標      
4. 4.  DUP_VAL_ON_INDEX    ORA_00001   保存重復值到惟一索引約束的列中      
5. 5.  ZERO_DIVIDE ORA_01476   發生除數為零的除法錯誤      
6. 6.  INVALID_NUMBER  ORA_01722   試圖對無效字符進行數值轉換      
7. 7.  ROWTYPE_MISMATCH    ORA_06504   主變量和游標的類型不兼容      
8. 8.  VALUE_ERROR ORA_06502   轉換、截斷或算術運算發生錯誤      
9. 9.  TOO_MANY_ROWS   ORA_01422   SELECT…INTO…語句返回多于一行的數據      
10. 10. NO_DATA_FOUND   ORA_01403   SELECT…INTO…語句沒有數據返回      
11. 11. TIMEOUT_ON_RESOURCE ORA_00051   等待資源時發生超時錯誤      
12. 12. TRANSACTION_BACKED_OUT  ORA_00060   由于死鎖，提交失敗      
13. 13. STORAGE_ERROR   ORA_06500   發生內存錯誤      
14. 14. PROGRAM_ERROR   ORA_06501   發生PL/SQL內部錯誤      
15. 15. NOT_LOGGED_ON   ORA_01012   試圖操作未連接的數據庫      
16. 16. LOGIN_DENIED    ORA_01017   在連接時提供了無效用戶名或口令   

函數里面進行update操作，發現這樣子的錯誤會拋以下的DML錯誤，如下：
ORA-14551: cannot perform. a DML operation inside a query -

查詢資料發現：
對數據庫有寫操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的函數,是無法簡單的用SQL來調用的.

解決辦法如下，只需在聲明中添加 PRAGMA AUTONOMOUS_TRANSACTION

Java代碼
1.CREATE OR REPLACE FUNCTION FUN_SET_PROJECT_COST (V_BUDGET_OBJECT_ID  IN VARCHAR2,V_ADJUST_ID  IN VARCHAR2)  
2.RETURN VARCHAR2  
3.IS  
4.PRAGMA AUTONOMOUS_TRANSACTION; 
CREATE OR REPLACE FUNCTION FUN_SET_PROJECT_COST (V_BUDGET_OBJECT_ID  IN VARCHAR2,V_ADJUST_ID  IN VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION;

PRAGMA AUTONOMOUS_TRANSACTION自治事務

當前的存儲過程作為已有事務的子事務運行，子事務的commit，rollback操作不影響父事務的狀態

在你的一個事務（外層事務）中可以定義一個或幾個自治事務。自治事務可以獨立commit,不對外層事務產生影響，同樣外層事務的 rollback 也對自治事務沒有影響。通常可以考慮將自治事務定義成一個過程，在外層的事務中調用。


---------------