我們?cè)谶M(jìn)行pl/sql編程時(shí)打交道最多的就是存儲(chǔ)過程了。存儲(chǔ)過程的結(jié)構(gòu)是非常的簡(jiǎn)單的，我們?cè)谶@里除了學(xué)習(xí)存儲(chǔ)過程的基本結(jié)構(gòu)外，還會(huì)學(xué)習(xí)編寫存儲(chǔ)過程時(shí)相關(guān)的一些實(shí)用的知識(shí)。如：游標(biāo)的處理，異常的處理，集合的選擇等等 1.存儲(chǔ)過程結(jié)構(gòu) 1.1 第一個(gè)存儲(chǔ)過程 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; 上面就是一個(gè)最簡(jiǎn)單的存儲(chǔ)過程。一個(gè)存儲(chǔ)過程大體分為這么幾個(gè)部分: 創(chuàng)建語句：create or replace procedure 存儲(chǔ)過程名 如果沒有or replace語句，則僅僅是新建一個(gè)存儲(chǔ)過程。如果系統(tǒng)存在該存儲(chǔ)過程，則會(huì)報(bào)錯(cuò)。Create or replace procedure 如果系統(tǒng)中沒有此存儲(chǔ)過程就新建一個(gè)，如果系統(tǒng)中有此存儲(chǔ)過程則把原來刪除掉，重新創(chuàng)建一個(gè)存儲(chǔ)過程。 存儲(chǔ)過程名定義：包括存儲(chǔ)過程名和參數(shù)列表。參數(shù)名和參數(shù)類型。參數(shù)名不能重復(fù)， 參數(shù)傳遞方式:IN, OUT, IN OUT IN 表示輸入?yún)?shù)，按值傳遞方式。 OUT 表示輸出參數(shù)，可以理解為按引用傳遞方式。可以作為存儲(chǔ)過程的輸出結(jié)果，供外部調(diào)用者使用。 IN OUT 即可作輸入?yún)?shù)，也可作輸出參數(shù)。 參數(shù)的數(shù)據(jù)類型只需要指明類型名即可，不需要指定寬度。 參數(shù)的寬度由外部調(diào)用者決定。 過程可以有參數(shù)，也可以沒有參數(shù) 變量聲明塊：緊跟著的as (is )關(guān)鍵字，可以理解為pl/sql的declare關(guān)鍵字，用于聲明變量。 變量聲明塊用于聲明該存儲(chǔ)過程需要用到的變量，它的作用域?yàn)樵摯鎯?chǔ)過程。另外這里聲明的變量必須指定寬度。遵循PL/SQL的變量聲明規(guī)范。 過程語句塊：從begin 關(guān)鍵字開始為過程的語句塊。存儲(chǔ)過程的具體邏輯在這里來實(shí)現(xiàn)。 異常處理塊：關(guān)鍵字為exception ，為處理語句產(chǎn)生的異常。該部分為可選 結(jié)束塊：由end關(guān)鍵字結(jié)果。 1.2 存儲(chǔ)過程的參數(shù)傳遞方式 存儲(chǔ)過程的參數(shù)傳遞有三種方式:IN,OUT,IN OUT . IN 按值傳遞，并且它不允許在存儲(chǔ)過程中被重新賦值。如果存儲(chǔ)過程的參數(shù)沒有指定存參數(shù)傳遞類型，默認(rèn)為IN 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 這一點(diǎn)與其它高級(jí)語言都不同。它相當(dāng)于java在參數(shù)前面加上final關(guān)鍵字。 OUT 參數(shù)：作為輸出參數(shù)，需要注意，當(dāng)一個(gè)參數(shù)被指定為OUT類型時(shí)，就算在調(diào)用存儲(chǔ)過程之前對(duì)該參數(shù)進(jìn)行了賦值，在存儲(chǔ)過程中該參數(shù)的值仍然是null. 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 是真正的按引用傳遞參數(shù)。即可作為傳入?yún)?shù)也可以作為傳出參數(shù)。 1.3 存儲(chǔ)過程參數(shù)寬度 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 首先，我們要明白，我們無法在存儲(chǔ)過程的定義中指定存儲(chǔ)參數(shù)的寬度，也就導(dǎo)致了我們無法在存儲(chǔ)過程中控制傳入變量的寬度。這個(gè)寬度是完全由外部傳入時(shí)決定的。 我們?cè)賮砜纯碠UT類型的參數(shù)的寬度。 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個(gè)字符a. 而在外部的調(diào)用過程中，p2這個(gè)參數(shù)僅僅被定義為varchar2(1). 而把p2作為參數(shù)調(diào)用這個(gè)過程，卻并沒有報(bào)錯(cuò)。而且它的真實(shí)值就是20個(gè)a 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參數(shù)的寬度 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); 執(zhí)行這個(gè)過程，仍然正確執(zhí)行。 可見，對(duì)于IN參數(shù)，其寬度是由外部決定。 對(duì)于OUT 和IN OUT 參數(shù)，其寬度是由存儲(chǔ)過程內(nèi)部決定。 因此，在寫存儲(chǔ)過程時(shí)，對(duì)參數(shù)的寬度進(jìn)行說明是非常有必要的，最明智的方法就是參數(shù)的數(shù)據(jù)類型使用%type。這樣雙方就達(dá)成了一致。 1.3 參數(shù)的默認(rèn)值 存儲(chǔ)過程的參數(shù)可以設(shè)置默認(rèn)值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 關(guān)鍵字為存儲(chǔ)過程的參數(shù)指定默認(rèn)值。在對(duì)存儲(chǔ)過程調(diào)用時(shí)，就可以省略默認(rèn)值。 需要注意的是：默認(rèn)值僅僅支持IN傳輸類型的參數(shù)。OUT 和 IN OUT不能指定默認(rèn)值 對(duì)于有默認(rèn)值的參數(shù)不是排在最后的情況。create or replace procedure procdefault2(p1 varchar2 default remark, p2 varchar2 )as begin dbms_output.put_line(p1);end;第一個(gè)參數(shù)有默認(rèn)值，第二個(gè)參數(shù)沒有。如果我們想使用第一個(gè)參數(shù)的默認(rèn)值時(shí) exec procdefault2(aa); 這樣是會(huì)報(bào)錯(cuò)的。 那怎么變呢？可以指定參數(shù)的值。SQL exec procdefault2(p2 =aa);remark 這樣就OK了，指定aa傳給參數(shù)p2 2. 存儲(chǔ)過程內(nèi)部塊 2.1 內(nèi)部塊 我們知道了存儲(chǔ)過程的結(jié)構(gòu)，語句塊由begin開始，以end結(jié)束。這些塊是可以嵌套。在語句塊中可以嵌套任何以下的塊。 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.存儲(chǔ)過程的常用技巧 3.1 哪種集合? 我們?cè)谑褂么鎯?chǔ)過程的時(shí)候經(jīng)常需要處理記錄集，也就是多條數(shù)據(jù)記錄。分為單列多行和多列多行，這些類型都可以稱為集合類型。我們?cè)谶@里進(jìn)行比較這些集合類型，以便于在編程時(shí)做出正確的選擇。 索引表,也稱為pl/sql表，不能存儲(chǔ)于數(shù)據(jù)庫(kù)中，元素的個(gè)數(shù)沒有限制,下標(biāo)可以為負(fù)值。 type t_table is table of varchar2(20) index by binary_integer; v_student t_table;varchar2(20)表示存放元素的數(shù)據(jù)類型，binary_integer表示元素下標(biāo)的數(shù)據(jù)類型。 嵌套表，索引表沒有 index by子句就是嵌套表，它可以存放于數(shù)據(jù)中，元素個(gè)數(shù)無限，下標(biāo)從1開始，并且需要初始化 type t_nestTable is table of varchar2(20);v_class t_nestTable ;僅是這樣聲明是不能使用的，必須對(duì)嵌套表進(jìn)行初始化，對(duì)嵌套表進(jìn)行初始化可以使用它的構(gòu)造函數(shù) v_class :=t_nestTable(a,b,c);變長(zhǎng)數(shù)組，變長(zhǎng)數(shù)組與高級(jí)語言的數(shù)組類型非常相似，下標(biāo)以1開始，元素個(gè)數(shù)有限。 type t_array is varray (20) of varchar2(20);varray(20)就定義了變長(zhǎng)數(shù)組的最大元素個(gè)數(shù)是20個(gè) 變長(zhǎng)數(shù)組與嵌套表一樣，也可以是數(shù)據(jù)表列的數(shù)據(jù)類型。 同時(shí)，變長(zhǎng)數(shù)組的使用也需要事先初始化。 類型 可存儲(chǔ)于數(shù)據(jù)庫(kù) 元素個(gè)數(shù) 是否需初始化 初始下標(biāo)值 索引表 否 無限 不需 嵌套表 可 無限 需 1 可變數(shù)組 可 有限(自定義) 需 1 由此可見，如果僅僅是在存儲(chǔ)過程中當(dāng)作集合變量使用，索引表是最好的選擇。 3.2 選用何種游標(biāo)? 顯示游標(biāo)分為：普通游標(biāo)，參數(shù)化游標(biāo)和游標(biāo)變量三種。 下面以一個(gè)過程來進(jìn)行說明 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 這一句是定義了一個(gè)最普通的游標(biāo)，把整個(gè)查詢已經(jīng)寫死,調(diào)用時(shí)不可以作任何改變。 cursor c_postype1 is select pos_type from pos_type_tbl where rownum = v_rownum; 這一句并沒有寫死，查詢參數(shù)由變量v_rownum來決定。需要注意的是v_rownum必須在這個(gè)游標(biāo)定義之前聲明。 cursor c_postype2(p_rownum number) is select pos_type from pos_type_tbl where rownum = p_rownum; 這一條語句與第二條作用相似，都是可以為游標(biāo)實(shí)現(xiàn)動(dòng)態(tài)的查詢。但是它進(jìn)一步的縮小了參數(shù)的作用域范圍。但是可讀性降低了不少。 type t_postype is ref cursor ; c_postype3 t_postype; 先定義了一個(gè)引用游標(biāo)類型，然后再聲明了一個(gè)游標(biāo)變量。 open c_postype3 for select pos_type from pos_type_tbl where rownum =1; 然后再用open for 來打開一個(gè)查詢。需要注意的是它可以多次使用,用來打開不同的查詢。 從動(dòng)態(tài)性來說，游標(biāo)變量是最好用的，但是閱讀性也是最差的。 注意，游標(biāo)的定義只能用使關(guān)鍵字IS，它與AS不通用。 3.3 游標(biāo)循環(huán)最佳策略 我們?cè)谶M(jìn)行PL/SQL編程時(shí)，經(jīng)常需要循環(huán)讀取結(jié)果集的數(shù)據(jù)。進(jìn)行逐行處理，這個(gè)過程就需要對(duì)游標(biāo)進(jìn)行循環(huán)。對(duì)游標(biāo)進(jìn)行循環(huán)的方法有多種，我們?cè)诖艘灰环治觥?create or replace procedure proccycle(p varchar2)as cursor c_postype is select pos_type, description from pos_type_tbl where rownum exec procexception(a);報(bào)錯(cuò)ORA-01403: no data foundORA-06512: at LIFEMAN.PROCEXCEPTION, line 6ORA-06512: at line 1處理這個(gè)有三個(gè)辦法 1 直接加上異常處理。 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(沒找到數(shù)據(jù));end;這樣做換湯不換藥，程序仍然被中斷。可能這樣不是我們所想要的。 2. select into做為一個(gè)獨(dú)立的塊，在這個(gè)塊中進(jìn)行異常處理 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;這是一種比較好的處理方式了。不會(huì)因?yàn)檫@個(gè)異常而引起程序中斷。 3.使用游標(biāo) 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異常。完全交由程序員來進(jìn)行控制了。 第二種情況是too_many_rows 異常的問題。 Too_many_rows 這個(gè)問題比起no_data_found要復(fù)雜一些。 給一個(gè)變量賦值時(shí)，但是查詢結(jié)果有多個(gè)記錄。 處理這種問題也有兩種情況： 1 多條數(shù)據(jù)是可以接受的，也就是說從結(jié)果集中隨便取一個(gè)值就行。這種情況應(yīng)該很極端了吧，如果出現(xiàn)這種情況，也說明了程序的嚴(yán)謹(jǐn)性存在問題。 2 多條數(shù)據(jù)是不可以被接受的，在這種情況肯定是程序的邏輯出了問題，也說是說原來根本就不會(huì)想到它會(huì)產(chǎn)生多條記錄。 對(duì)于第一種情況，就必須采用游標(biāo)來處理，而對(duì)于第二種情況就必須使用內(nèi)部塊來處理，重新拋出異常。 多條數(shù)據(jù)可以接受,隨便取一條，這個(gè)跟no_data_found的處理方式一樣，使用游標(biāo)。 我這里僅說第二種情況，不可接受多條數(shù)據(jù)，但是不要忘了處理no_data_found哦。這就不能使用游標(biāo)了，必須使用內(nèi)部塊。 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,對(duì)v_postype賦值時(shí)，找到多條數(shù)據(jù)); end; dbms_output.put_line(v_postype);end;需要注意的是一定要加上對(duì)no_data_found的處理，對(duì)出現(xiàn)多條記錄的情況則繼續(xù)拋出異常，讓上一層來處理。 總之對(duì)于select into的語句需要注意這兩種情況了。需要妥當(dāng)處理啊。 3.5 在存儲(chǔ)過程中返回結(jié)果集 我們使用存儲(chǔ)過程都是返回值都是單一的，有時(shí)我們需要從過程中返回一個(gè)集合。即多條數(shù)據(jù)。這有幾種解決方案。比較簡(jiǎn)單的做法是寫臨時(shí)表，但是這種 做法不靈活。而且維護(hù)麻煩。我們可以使用嵌套表來實(shí)現(xiàn).沒有一個(gè)集合類型能夠與java的jdbc類型匹配。這就是對(duì)象與關(guān)系數(shù)據(jù)庫(kù)的阻抗吧。數(shù)據(jù)庫(kù)的對(duì) 象并不能夠完全轉(zhuǎn)換為編程語言的對(duì)象，還必須使用關(guān)系數(shù)據(jù)庫(kù)的處理方式。 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,團(tuán)體資料變更); v_posTypeList.extend; v_posTypeList(3) := PosType(A003,受益人變更); v_posTypeList.extend; v_posTypeList(4) := PosType(A004,續(xù)期交費(fèi)方式變更); open p_ref_postypeList for select * from table(cast (v_posTypeList as PosTypeTable); end;end procpkg;在包頭中定義了一個(gè)游標(biāo)變量,并把它作為存儲(chǔ)過程的參數(shù)類型。 在存儲(chǔ)過程中定義了一個(gè)嵌套表變量，對(duì)數(shù)據(jù)寫進(jìn)嵌套表中，然后把嵌套表進(jìn)行類型轉(zhuǎn)換為table,游標(biāo)變量從這個(gè)嵌套表中進(jìn)行查詢。外部程序調(diào)用這個(gè)游標(biāo)。 所以這個(gè)過程需要定義兩個(gè)類型。 create or replace type PosType as Object ( posType varchar2(20), description varchar2(50);create or replace type PosTypeTable is table of PosType; 需要注意，這兩個(gè)類型不能定義在包頭中，必須單獨(dú)定義，這樣java層才能使用。 在外部通過pl/sql來調(diào)用這個(gè)過程非常簡(jiǎn)單。 set serveroutput on;declare type refcursor is ref cursor; v_ref_postype refcursor; v_postype varchar2(20); v_desc varchar2(50);begin crefcursor(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;注意：對(duì)于游標(biāo)變量，不能使用for循環(huán)來處理。因?yàn)閒or循環(huán)會(huì)隱式的執(zhí)行open動(dòng)作。而通過open for來打開的游標(biāo)%isopen是為true的。也就是默認(rèn)打開的。Open一個(gè)已經(jīng)open的游標(biāo)是錯(cuò)誤的。所以不能使用for循環(huán)來處理游標(biāo)變量。 我們主要討論的是如何通過jdbc調(diào)用來處理這個(gè)輸出參數(shù)。 conn = this.getDataSource().getConnection();CallableStatement call = conn.prepareCall(call crefcursor(?,?);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.參數(shù)配置 2.調(diào)用過程 call crefcursor(?,?) 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_crefcursor, para); return (List)para.get(p_ref_postypeList); 這個(gè)跟jdbc的方式非常的相似. 我們使用的是ibatis的2.0版本，比較麻煩。 如果是使用2.2以上版本就非常簡(jiǎn)單的。 因?yàn)榭梢栽趐arameterMap中定義一個(gè)resultMap.這樣就無需要自己定義處理器了。 可以從分析2.0和2.0的dtd文件知道。 上面的兩種方式都是非常的復(fù)雜，如果僅僅是需要返回一個(gè)結(jié)果集，那就完全可以使用函數(shù)來實(shí)現(xiàn)了。 create or replace package procpkg is type refcursor i