存儲過程詳解

1、存儲過程詳解存儲過程的in參數(shù)寬度又外部決定，而out和in out的寬度 是由存儲過程內(nèi)部決定。此外in后面還可以帶默認值，而out 和in out不能帶默認值。1. createorreplaceprocedureprocdefault(p1varchar2,p2varchar2defaultmark) 2. as 3. begin 4. dbms_output.put_line(p2); 5. end; 6. SQLsetserveroutputon; 7. SQLexecprocdefault(a);一、集合：索引表,也稱為pl/sql表，不能存儲于數(shù)據(jù)庫中，元素的個數(shù)沒有限制,下標可

2、以為負值。1. typet_tableistableofvarchar2(20)indexbybinary_integer; 2. v_studentt_table;嵌套表，索引表沒有 index by子句就是嵌套表，它可以存放于數(shù)據(jù)庫中，元素個數(shù)無限，下標從1開始，并且需要初始化1. typet_nestTableistableofvarchar2(20); 2. v_classt_nestTable;僅是這樣聲明是不能使用的，必須對嵌套表進行初始化，對嵌套表進行初始化可以使用它的構(gòu)造函數(shù)v_class:=t_nestTable(a,b,c);變長數(shù)組，變長數(shù)組與高級語言的數(shù)組類型非常相似，

3、下標以1開始，元素個數(shù)有限。也需要進行初始化。typet_arrayisvarray(20)ofvarchar2(20);由此可見，如果僅僅是在存儲過程中當作集合變量使用，索引表是最好的選擇。二、游標：顯示游標分為：普通游標，參數(shù)化游標和游標變量三種。游標循環(huán)最佳策略我們在進行PL/SQL編程時，經(jīng)常需要循環(huán)讀取結(jié)果集的數(shù)據(jù)。進行逐行處理，這個過程就需要對游標進行循環(huán)。對游標進行循環(huán)的方法有多種，我們在此一一分析。行PL/SQL編程時，經(jīng)常需要循環(huán)讀取結(jié)果集的數(shù)據(jù)。進行逐行處理，這個過程就需要對游標進行循環(huán)。對游標進行循環(huán)的方法有多種，我們在此一一分析。 Java代碼 1. createorr

4、eplaceprocedureproccycle(pvarchar2) 2. as 3. cursorc_postypeisselectpos_type,descriptionfrompos_type_tblwhererownum6; 4. v_postypevarchar2(20); 5. v_descriptionvarchar2(50); 6. begin 7. openc_postype; 8. ifc_postype%foundthen 9. dbms_output.put_line(foundtrue); 10. elsifc_postype%found=falsethen 11.

5、 dbms_output.put_line(foundfalse); 12. else13. dbms_output.put_line(foundnull); 14. endif; 15. -以下是loop循環(huán)-16. loop 17. fetchc_postypeintov_postype,v_description; 18. exitwhenc_postype%notfound; 19. dbms_output.put_line(postype:|v_postype|,description:|v_description); 20. endloop; 21. closec_postype;

6、 22. dbms_output.put_line(-loopend-); 23. openc_postype; 24. -以下是while循環(huán)-25. fetchc_postypeintov_postype,v_description; 26. whilec_postype%foundloop 27. dbms_output.put_line(postype:|v_postype|,description:|v_description); 28. fetchc_postypeintov_postype,v_description; 29. endloop; 30. closec_postyp

7、e; 31. dbms_output.put_line(-whileend-); 32. -以下是for循環(huán)-33. forv_posinc_postypeloop 34. v_postype:=v_pos.pos_type; 35. v_description:=v_pos.description; 36. dbms_output.put_line(postype:|v_postype|,description:|v_description); 37. endloop; 38. dbms_output.put_line(-forend-); 39. end;使用游標之前需要開打游標,open

8、 cursor，循環(huán)完后再關閉游標close cursor. 這是使用游標應該慎記于心的法則。第一種使用loop循環(huán)1. loop 2. fetchc_postypeintov_postype,v_description; 3. exitwhenc_postype%notfound; 4. 5. endloop這里需要注意，exit when語句一定要緊跟在fetch之后。必避免多余的數(shù)據(jù)處理。 處理邏輯需要跟在exit when之后。這一點需要多加小心。 循環(huán)結(jié)束后要記得關閉游標。第二種使用while循環(huán)1. fetchc_postypeintov_postype,v_description

9、; 2. whilec_postype%foundloop 3. 4. fetchc_postypeintov_postype,v_description; 5. endloop;我們知道了一個游標打開后，必須執(zhí)行一次fetch語句，游標的屬性才會起作用。所以使用while 循環(huán)時，就需要在循環(huán)之前進行一次fetch動作。 而且數(shù)據(jù)處理動作必須放在循環(huán)體內(nèi)的fetch方法之前。循環(huán)體內(nèi)的fetch方法要放在最后。否則就會多處理一次。這一點也要非常的小心。 總之，使用while來循環(huán)處理游標是最復雜的方法。第三種for循環(huán)1. forv_posinc_postypeloop 2. v_posty

10、pe:=v_pos.pos_type; 3. v_description:=v_pos.description; 4. 5. endloop;可見for循環(huán)是比較簡單實用的方法。 首先，它會自動open和close游標。解決了你忘記打開或關閉游標的煩惱。 其它,自動定義了一個記錄類型及聲明該類型的變量，并自動fetch數(shù)據(jù)到這個變量中。 我們需要注意v_pos 這個變量無需要在循環(huán)外進行聲明，無需要為其指定數(shù)據(jù)類型。 它應該是一個記錄類型，具體的結(jié)構(gòu)是由游標決定的。 這個變量的作用域僅僅是在循環(huán)體內(nèi)。 把v_pos看作一個記錄變量就可以了，如果要獲得某一個值就像調(diào)用記錄一樣就可以了。 如v_p

11、os.pos_type 由此可見，for循環(huán)是用來循環(huán)游標的最好方法。高效，簡潔，安全。 但遺憾的是，常常見到的卻是第一種方法。所以從今之后得改變這個習慣了。三、select into 不可忽視的問題我們知道在pl/sql中要想從數(shù)據(jù)表中向變量賦值，需要使用select into 子句。 但是它會帶動來一些問題，如果查詢沒有記錄時，會拋出no_data_found異常。 如果有多條記錄時，會拋出too_many_rows異常。 這個是比較糟糕的。一旦拋出了異常，就會讓過程中斷。特別是no_data_found這種異常，沒有嚴重到要讓程序中斷的地步，可以完全交給由程序進行處理。1. create

12、orreplaceprocedureprocexception(pvarchar2) 2. as 3. v_postypevarchar2(20); 4. begin 5. selectpos_typeintov_postypefrompos_type_tblwhere1=0; 6. dbms_output.put_line(v_postype); 7. end; 執(zhí)行這個程序會報no_date_found異常。處理這個異常有三個辦法：1直接加上異常處理1. createorreplaceprocedureprocexception(pvarchar2) 2. as 3. v_postypev

13、archar2(20); 4. begin 5. selectpos_typeintov_postypefrompos_type_tblwhere1=0; 6. dbms_output.put_line(v_postype); 7. exception 8. whenno_data_foundthen 9. dbms_output.put_line(沒找到數(shù)據(jù)); 10. end;這樣做換湯不換藥，程序仍然被中斷?？赡苓@樣不是我們所想要的。2. select into做為一個獨立的塊，在這個塊中進行異常處理1. createorreplaceprocedureprocexception(pva

14、rchar2) 2. as 3. v_postypevarchar2(20); 4. begin 5. begin 6. selectpos_typeintov_postypefrompos_type_tblwhere1=0; 7. dbms_output.put_line(v_postype); 8. exception 9. whenno_data_foundthen 10. v_postype:=; 11. end; 12. dbms_output.put_line(v_postype); 13. end;這是一種比較好的處理方式了。不會因為這個異常而引起程序中斷。3.使用游標1. cr

15、eateorreplaceprocedureprocexception(pvarchar2) 2. as 3. v_postypevarchar2(20); 4. cursorc_postypeisselectpos_typefrompos_type_tblwhere1=0; 5. begin 6. openc_postype; 7. fetchc_postypeintov_postype; 8. closec_postype; 9. dbms_output.put_line(v_postype); 10. end;這樣就完全的避免了no_data_found異常。完全交由程序員來進行控制了。

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

17、ound的處理方式一樣，使用游標。 我這里僅說第二種情況，不可接受多條數(shù)據(jù)，但是不要忘了處理no_data_found哦。這就不能使用游標了，必須使用內(nèi)部塊。1. createorreplaceprocedureprocexception2(pvarchar2) 2. as 3. v_postypevarchar2(20); 4. begin 5. begin 6. selectpos_typeintov_postypefrompos_type_tblwhererownum5; 7. exception 8. whenno_data_foundthen 9. v_postype:=null;

18、10. whentoo_many_rowsthen 11. raise_application_error(-20000,對v_postype賦值時，找到多條數(shù)據(jù)); 12. end; 13. dbms_output.put_line(v_postype); 14. end;需要注意的是一定要加上對no_data_found的處理，對出現(xiàn)多條記錄的情況則繼續(xù)拋出異常，讓上一層來處理。 總之對于select into的語句需要注意這兩種情況了。需要妥當處理啊。四、在存儲過程中返回結(jié)果集我們使用存儲過程都是返回值都是單一的，有時我們需要從過程中返回一個集合。即多條數(shù)據(jù)。這有幾種解決方案。比較簡單的

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

20、eorreplacepackageprocpkgis 2. typerefcursorisrefcursor; 3. procedureprocrefcursor(pvarchar2,p_ref_postypeListoutrefcursor); 4. endprocpkg; 5. 6. createorreplacepackagebodyprocpkgis 7. procedureprocrefcursor(pvarchar2,p_ref_postypeListoutrefcursor) 8. is 9. v_posTypeListPosTypeTable; 10. begin 11. v_

21、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

22、(A004,續(xù)期交費方式變更); 20. openp_ref_postypeListforselect*fromtable(cast(v_posTypeListasPosTypeTable); 21. end; 22. endprocpkg;在包頭中定義了一個游標變量,并把它作為存儲過程的參數(shù)類型。 在存儲過程中定義了一個嵌套表變量，對數(shù)據(jù)寫進嵌套表中，然后把嵌套表進行類型轉(zhuǎn)換為table,游標變量從這個嵌套表中進行查詢。外部程序調(diào)用這個游標。 所以這個過程需要定義兩個類型。1. createorreplacetypePosTypeasObject( 2. posTypevarchar2(20

23、), 3. descriptionvarchar2(50) 4. );create or replace type PosTypeTable is table of PosType; 需要注意，這兩個類型不能定義在包頭中，必須單獨定義，這樣java層才能使用。在pl/sql中調(diào)用該存儲過程1. setserveroutputon; 2. declare 3. typerefcursorisrefcursor; 4. v_ref_postyperefcursor; 5. v_postypevarchar2(20); 6. v_descvarchar2(50); 7. begin 8. procp

24、crefcursor(a,v_ref_postype); 9. loop 10. fetchv_ref_postypeintov_postype,v_desc; 11. exitwhenv_ref_postype%notfound; 12. dbms_output.put_line(posType:|v_postype|;description:|v_desc); 13. endloop; 14. end;四、再述游標一、游標的類型：1、隱式游標：在pl/sql程序中執(zhí)行DML SQL時自動創(chuàng)建隱式游標，名字固定叫sql。2、顯式游標：顯式游標用于處理返回多行的查詢。3、ref游標

25、：ref游標用于處理運行時才能確定的動態(tài)SQL查詢的結(jié)果。隱式游標：q在PL/SQL中使用DML語句時自動創(chuàng)建隱式游標，q隱式游標自動聲明、打開和關閉，其名為SQL,q通過檢查隱式游標的屬性可以獲得最近執(zhí)行的DML語句的信息，q隱式游標的屬性有：q%FOUND(SQL語句影響了一行或多行時為true)，q%NOTFOUND(SQL語句沒有影響任何行時為true)，q%ROWCOUNT(SQL語句影響的行數(shù))，q%ISOPEN(游標是否打開，始終為FALSE)beginupdate emp set sal = sal + 100 where empno = 9999;if sql%found t

26、hendbms_output.put_line(這次更新了|sql%rowcount);else dbms_output.put_line(一行也沒有更新！);end if;end;顯式游標：顯式游標的使用：-無參數(shù)游標 -declare v_ename emp.ename%type;-聲明變量cursor c_cursor is select ename from emp; -聲明游標 beginopen c_cursor;fetch c_cursor into v_ename;while c_cursor%foundloopdbms_output.put_line(v_ename);fetch c_cursor into v_ename;end loop;close c_cursor; end;REF游標：REF 游標和游標變量用于處理運行時動態(tài)執(zhí)行的 SQL 查詢,創(chuàng)建游標變量需要兩個步驟： (1)聲明 REF 游標類型,(2).聲