Oracle存儲(chǔ)過(guò)程介紹***存儲(chǔ)過(guò)程介紹存儲(chǔ)過(guò)程概念介紹PL/SQL根底游標(biāo)存儲(chǔ)過(guò)程的用戶接口JAVA調(diào)用數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的異常處理存儲(chǔ)過(guò)程的包存儲(chǔ)過(guò)程的概念〔1〕過(guò)程就是高級(jí)程序設(shè)計(jì)語(yǔ)言中的模塊的概念,將一些內(nèi)部聯(lián)系的命令組成一個(gè)個(gè)過(guò)程,通過(guò)參數(shù)在過(guò)程之間傳遞數(shù)據(jù)是模塊化設(shè)計(jì)思想的重要內(nèi)容.存儲(chǔ)過(guò)程〔StoredProcedure〕是在大型數(shù)據(jù)庫(kù)系統(tǒng)中，一組為了完成特定功能的SQL語(yǔ)句集，存儲(chǔ)在數(shù)據(jù)庫(kù)中，經(jīng)過(guò)第一次編譯后再次調(diào)用不需要再次編譯，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)〔如果該存儲(chǔ)過(guò)程帶有參數(shù)〕來(lái)執(zhí)行它。存儲(chǔ)過(guò)程是數(shù)據(jù)庫(kù)中的一個(gè)重要對(duì)象，可以被有權(quán)用戶在任何需要的地方調(diào)用。在oracle數(shù)據(jù)庫(kù)中，存儲(chǔ)過(guò)程是Oracle的一種存儲(chǔ)過(guò)程的概念〔2〕對(duì)象，是一種帶名的PL/SQL過(guò)程程序塊。在ORACLE中，假設(shè)干個(gè)有聯(lián)系的過(guò)程可以組合在一起構(gòu)成程序包。存儲(chǔ)過(guò)程優(yōu)點(diǎn)〔1〕1〕提高效率：存儲(chǔ)過(guò)程是預(yù)編譯過(guò)的，并且經(jīng)優(yōu)化后存儲(chǔ)于SQL內(nèi)存中，使用時(shí)無(wú)需再次編譯，提高了工作效率；2〕減少網(wǎng)絡(luò)流量：存儲(chǔ)過(guò)程的代碼直接存放于數(shù)據(jù)庫(kù)中，一般由客戶端直接通過(guò)存儲(chǔ)過(guò)程的名字進(jìn)行調(diào)用，減少了網(wǎng)絡(luò)流量，加快了系統(tǒng)執(zhí)行速度，例如在進(jìn)行百萬(wàn)以上的大批量數(shù)據(jù)查詢時(shí)，使用存儲(chǔ)過(guò)程分頁(yè)要比其他方式分頁(yè)快得多；3〕平安性高：使用存儲(chǔ)過(guò)程可以減少SQL注入式攻擊，提高了系統(tǒng)的平安性，執(zhí)行存儲(chǔ)過(guò)程的用戶要具有一定的權(quán)限才能使用存儲(chǔ)過(guò)程，沒(méi)有數(shù)據(jù)操作權(quán)限的用戶只能在其控制下間接地存取數(shù)據(jù);存儲(chǔ)過(guò)程優(yōu)點(diǎn)〔2〕4〕重復(fù)使用。存儲(chǔ)過(guò)程可以重復(fù)使用，從而可以減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。5〕靈活：使用存儲(chǔ)過(guò)程，可以實(shí)現(xiàn)存儲(chǔ)過(guò)程設(shè)計(jì)和編碼工作分開(kāi)進(jìn)行，只要將存儲(chǔ)過(guò)程名、參數(shù)、及返回信息告訴編碼人員即可。存儲(chǔ)過(guò)程缺點(diǎn)〔1〕1〕移植性差：使用存儲(chǔ)過(guò)程封裝業(yè)務(wù)邏輯將限制應(yīng)用程序的可移植性；2〕維護(hù)本錢高：如果更改存儲(chǔ)過(guò)程的參數(shù)或者其返回的數(shù)據(jù)及類型的話，需要修改應(yīng)用程序的相關(guān)代碼，比較繁瑣。pl/sql根底PL/SQL塊的根本結(jié)構(gòu)〔1〕根本的PL/SQL塊由定義局部,執(zhí)行局部,異常處理局部組成:

DECLARE定義局部BEGIN執(zhí)行局部EXCEPTION異常處理局部ENDPL/SQL塊的根本結(jié)構(gòu)〔2〕定義局部:定義在程序執(zhí)行局部使用的常量,變量,游標(biāo)和異常處理名稱可執(zhí)行局部包括數(shù)據(jù)庫(kù)操作語(yǔ)句和PL/SQL塊控制語(yǔ)句異常處理局部對(duì)執(zhí)行局部的所有PL/SQL語(yǔ)句的執(zhí)行進(jìn)行監(jiān)控,如執(zhí)行發(fā)生異常,那么程序跳到該局部執(zhí)行定義變量〔1〕在PLSQL中所使用的變量必須在變量定義局部明確定義.變量定義局部是包括在關(guān)鍵字DECLARE和BEGIN之間的局部,每條語(yǔ)句后用(;)結(jié)束.定義格式:變量標(biāo)示符[CONSTANT]數(shù)據(jù)類型[NOTNULL][:=缺省值或PLSQL表達(dá)式];變量標(biāo)示符命名規(guī)那么應(yīng)遵循SQL實(shí)體命名規(guī)那么定義變量〔2〕數(shù)據(jù)類型簡(jiǎn)單數(shù)據(jù)類型(標(biāo)量數(shù)據(jù)類型):NUMBER(m,n)

數(shù)字類型m為總長(zhǎng)度,n為小數(shù)長(zhǎng)度CHAR(m)

字符型m為變量長(zhǎng)度VARCHAR2(m)

可變長(zhǎng)字符型m為最大長(zhǎng)度DATE

日期型

LONG

長(zhǎng)型

BOONEAN

布爾型值為TRUEFALSENULL賦值變量〔1〕變量賦值時(shí)需使用PLSQL變量賦值操作符(:=)

常量賦值:變量名:=常量

變量賦值:變量名:=同類型變量

表達(dá)式賦值:變量名:=表達(dá)式或函數(shù)PLSQL中使用的SQL語(yǔ)句在PL/SQL塊中,所有對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)和操作還是要經(jīng)由SQL語(yǔ)言進(jìn)行,在PL/SQL塊中可以使用數(shù)據(jù)查詢語(yǔ)言,數(shù)據(jù)操縱語(yǔ)言和數(shù)據(jù)控制語(yǔ)言,但不能使用數(shù)據(jù)定義語(yǔ)言具體地說(shuō)可以使用select,insert,update,delete,commit,rollback,但不能使用create,alter,drop,grant,revoke.PL/SQL流程控制PL/SQL具有與高級(jí)語(yǔ)言類似的流程控制語(yǔ)句.PL/SQL主要控制語(yǔ)句有:

條件控制語(yǔ)句

循環(huán)控制語(yǔ)句

跳轉(zhuǎn)控制語(yǔ)句條件控制語(yǔ)句(1)IF_THEN語(yǔ)句語(yǔ)法:IF條件THEN

語(yǔ)句; ENDIF;條件可為ISNULL或NOTISNULL以及AND,OR,NOT,邏輯運(yùn)算符條件控制語(yǔ)句(2)IF_THEN_ELSE語(yǔ)句語(yǔ)法:IF條件THEN

語(yǔ)句; ELSE

語(yǔ)句; ENDIF;條件可為ISNULL或NOTISNULL以及AND,OR,NOT,邏輯運(yùn)算符條件控制語(yǔ)句(3)IF_THAN_ELSIF語(yǔ)句:

語(yǔ)法:IF條件THEN

語(yǔ)句; ELSIF條件THEN

語(yǔ)句; [ELSIF條件THEN語(yǔ)句;] [ELSE語(yǔ)句;] ENDIF循環(huán)控制語(yǔ)句(1)LOOP循環(huán):

語(yǔ)法:LOOP

語(yǔ)句; [EXIT[WHEN條件]]; ENDLOOP循環(huán)控制語(yǔ)句(2)for循環(huán):

語(yǔ)法:FOR計(jì)數(shù)器IN[REVERSE]下界…上界LOOP

語(yǔ)句;

ENDLOOP循環(huán)控制語(yǔ)句(3)WHILE循環(huán):

語(yǔ)法:WHILE條件LOOP

語(yǔ)句;

ENDLOOP跳轉(zhuǎn)控制語(yǔ)句語(yǔ)法:《標(biāo)號(hào)》其他語(yǔ)句； GOTO標(biāo)號(hào);說(shuō)明:〔1〕跳轉(zhuǎn)語(yǔ)句可在同一塊語(yǔ)句間跳轉(zhuǎn)〔2〕跳轉(zhuǎn)語(yǔ)句可從子塊跳轉(zhuǎn)倒父塊中，但不能從父塊跳轉(zhuǎn)到子塊中〔3〕跳轉(zhuǎn)語(yǔ)句不能在IF語(yǔ)句體外跳到IF體內(nèi)〔4〕跳轉(zhuǎn)語(yǔ)句不能從循環(huán)體外跳到循環(huán)體內(nèi)游標(biāo)〔cursor〕為什么要使用游標(biāo)〔1〕SQL語(yǔ)言與主語(yǔ)言具有不同數(shù)據(jù)處理方式SQL語(yǔ)言是面向集合的，一條SQL語(yǔ)句原那么上可以產(chǎn)生或處理多條記錄主語(yǔ)言是面向記錄的，一組主變量一次只能存放一條記錄為什么要使用游標(biāo)〔2〕僅使用主變量并不能完全滿足SQL語(yǔ)句向應(yīng)用程序輸出數(shù)據(jù)的要求引入游標(biāo)的概念，用來(lái)協(xié)調(diào)這兩種不同的處理方式

游標(biāo)游標(biāo)是系統(tǒng)為用戶開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果每個(gè)游標(biāo)區(qū)都有一個(gè)名字用戶可以用SQL語(yǔ)句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語(yǔ)言進(jìn)一步處理定義游標(biāo)語(yǔ)法:CURSOR游標(biāo)名稱IS SELECT語(yǔ)句;定義游標(biāo)應(yīng)寫在PL/SQL語(yǔ)句的DECLARE變量定義局部定義游標(biāo)時(shí)SELECT語(yǔ)句中不可有INTO子語(yǔ)句在SELECT語(yǔ)句中使用的變量必須在定義游標(biāo)前定義翻開(kāi)游標(biāo)語(yǔ)法:OPEN游標(biāo)名;在BEGIN語(yǔ)句之后,可以翻開(kāi)游標(biāo),在翻開(kāi)游標(biāo)之前,必須對(duì)游標(biāo)所涉及到的變量賦值利用游標(biāo)提取數(shù)據(jù)語(yǔ)法:FETCH游標(biāo)名INTO變量1,變量2,…..;游標(biāo)每次只能取到一條數(shù)據(jù),同時(shí)游標(biāo)指針下移,等待取下一條數(shù)據(jù).該條語(yǔ)句變量列表應(yīng)與定義游標(biāo)時(shí)的參數(shù)列表一致關(guān)閉游標(biāo)語(yǔ)法:CLOSE游標(biāo)名;關(guān)閉游標(biāo),釋放資源,游標(biāo)關(guān)閉后不能再提取數(shù)據(jù).存儲(chǔ)過(guò)程的用戶接口創(chuàng)立存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程創(chuàng)立存儲(chǔ)過(guò)程創(chuàng)立存儲(chǔ)過(guò)程語(yǔ)法：CREATEProcedure過(guò)程名〔［參數(shù)1，參數(shù)2，...］〕AS/IS <PL/SQL塊>；過(guò)程名：數(shù)據(jù)庫(kù)效勞器合法的對(duì)象標(biāo)識(shí)參數(shù)列表：用名字來(lái)標(biāo)識(shí)調(diào)用時(shí)給出的參數(shù)值，必須指定值的數(shù)據(jù)類型。參數(shù)也可以定義輸入?yún)?shù)、輸出參數(shù)或輸入/輸出參數(shù)。默認(rèn)為輸入?yún)?shù)。過(guò)程體：是一個(gè)<PL/SQL塊>。包括聲明局部和可執(zhí)行語(yǔ)句局部;不用declare語(yǔ)句創(chuàng)立存儲(chǔ)過(guò)程(2)例子：［例1］利用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)下面的應(yīng)用:從一個(gè)賬戶轉(zhuǎn)指定數(shù)額的款項(xiàng)到另一個(gè)賬戶中。

CREATEPROCEDURETRANSFER(inAccountINT，outAccountINT，amountFLOAT) AS totalDepositFLOAT; BEGIN/*檢查轉(zhuǎn)出賬戶的余額*/ SELECTtotalINTOtotalDepositFROMACCOUNTWHEREACCOUNTNUM=outAccount; IFtotalDepositISNULLTHEN/*賬戶不存在或賬戶中沒(méi)有存款*/ ROLLBACK; RETURN; ENDIF;

創(chuàng)立存儲(chǔ)過(guò)程(3)IFtotalDeposit<amountTHEN /*賬戶賬戶存款缺乏*/ ROLLBACK; RETURN; ENDIF; UPDATEaccountSETtotal=total-amount

WHEREACCOUNTNUM=outAccount; /*修改轉(zhuǎn)出賬戶，減去轉(zhuǎn)出額*/UPDATEaccountSETtotal=total+amountWHEREACCOUNTNUM=inAccount; /*修改轉(zhuǎn)入賬戶，增加轉(zhuǎn)出額*/ COMMIT; /*提交轉(zhuǎn)賬事務(wù)*/ END;

執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程語(yǔ)法：CALL/PERFORMProcedure過(guò)程名(［參數(shù)1，參數(shù)2，...］)；在PL/SQL中，數(shù)據(jù)庫(kù)效勞器支持在過(guò)程體中調(diào)用其他存儲(chǔ)過(guò)程使用CALL或者PERFORM等方式激活存儲(chǔ)過(guò)程的執(zhí)行。調(diào)用時(shí)”()”是不可少的，無(wú)論是有參數(shù)還是無(wú)參數(shù)。執(zhí)行存儲(chǔ)過(guò)程〔2〕例子：［例2］從賬戶轉(zhuǎn)一萬(wàn)元到賬戶中。

CALLProcedureTRANSFER(01003813828，，10000)；刪除存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程語(yǔ)法：DROPPROCEDURE過(guò)程名；JAVA調(diào)用數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程前面我們已經(jīng)講述了有關(guān)oracle數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程的相關(guān)知識(shí)，下面我將根據(jù)上面存儲(chǔ)過(guò)程的實(shí)例來(lái)舉出JAVA對(duì)oracle存儲(chǔ)過(guò)程的調(diào)用僅有返回值的過(guò)程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc2(?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.registerOutParameter(1,Types.VARCHAR);statement.execute();Stringpname=statement.getString(1);System.out.println(pname);}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}JAVA調(diào)用實(shí)例既有輸入?yún)?shù)又有輸出參數(shù)的過(guò)程public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc3(?,?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.setInt(1,1);statement.registerOutParameter(2,Types.VARCHAR);statement.execute();Stringpname=statement.getString(2);System.out.println(pname);}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}JAVA調(diào)用實(shí)例下面將舉出無(wú)out參數(shù)的調(diào)用實(shí)例這種參數(shù)不適于用在查詢語(yǔ)句上，因?yàn)椴樵冋Z(yǔ)句需要有返回值才能被JAVA調(diào)用返回到OUT參數(shù)中的值可能會(huì)是JDBCNULL。當(dāng)出現(xiàn)這種情形時(shí)，將對(duì)JDBCNULL值進(jìn)行轉(zhuǎn)換以使getXXX方法所返回的值為null、0或false，這取決于getXXX方法類型。對(duì)于ResultSet對(duì)象，要知道0或false是否源于JDBCNULL的唯一方法，是用方法wasNull進(jìn)行檢測(cè)。如果getXXX方法讀取的最后一個(gè)值是JDBCNULL，那么該方法返回true，否那么返回flaseJAVA調(diào)用實(shí)例僅有參數(shù)的過(guò)程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc1(?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.setInt(1,1);statement.execute();System.out.println(statement.execute());}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}finally{try{conn.close();}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}}JAVA調(diào)用實(shí)例無(wú)參數(shù)過(guò)程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{callstu_proc()}";try{CallableStatementstatement=conn.prepareCall(sql);statement.execute();System.out.println(statement.execute());}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}finally{try{conn.close();}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();} }}JAVA調(diào)用實(shí)例由于oracle存儲(chǔ)過(guò)程沒(méi)有返回值，它的所有返回值都是通過(guò)out參數(shù)來(lái)替代的，列表同樣也不例外，但由于是集合，所以不能用一般的參數(shù)，必須要用pagkage了.所以要分兩局部，

1.建一個(gè)程序包。如下：

CREATEORREPLACEPACKAGEMYPACKAGE

AS

TYPEMY_CURSORISREFCURSOR;

endMYPACKAGE;

2.建立存儲(chǔ)過(guò)程，如下：

CREATEORREPLACEPROCEDUREPRO_3(p_CURSORoutMYPACKAGE.MY_CURSOR)IS

BEGIN

OPENp_CURSORFORSELECT*FROMDBOEMP;

ENDPRO_3;

可以看到，它是把游標(biāo)〔可以理解為一個(gè)指針〕，作為一個(gè)out參數(shù)來(lái)返回值的?？梢杂胹ys_refcursor替換MYPACKAGE.MY_CURSOR；但sys_refcursor是oracle9i以后系統(tǒng)定義的一個(gè)refcursor,主要用在過(guò)程中返回結(jié)果集。JAVA調(diào)用實(shí)例(返回結(jié)果集-1)Java代碼：publicstaticvoidmain(String[]args){

Stringdriver="oracle.jdbc.driver.OracleDriver";

Stringurl="jdbc:oracle:thin:@:1521:orcl";

Stringuser="admin";

Stringpwd="password";

Connectionconn=null;

CallableStatementcs=null;

ResultSetrs=null;

try{

Class.forName(driver);

conn=DriverManager.getConnection(url,user,pwd);

cs=conn.prepareCall("{callDBO.PRO_3(?)}");

cs.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

rs=(ResultSet)cs.getObject(1);

while(rs.next()){

System.out.println("\t"+rs.getString(1)+"\t"

+rs.getString(2)+"\t");

}

}catch(SQLExceptione){

e.printStackTrace();JAVA調(diào)用實(shí)例(返回結(jié)果集-2)

}catch(Exceptione){

e.printStackTrace();

}finally{

try{

if(rs!=null){

rs.close();

if(cs!=null){

cs.close();

}

if(conn!=null){

conn.close();

}

}

}catch(SQLExceptione){

}

}}}JAVA調(diào)用實(shí)例(返回結(jié)果集-3)存儲(chǔ)過(guò)程的異常處理為了提高存儲(chǔ)過(guò)程的健壯性，防止運(yùn)行錯(cuò)誤，當(dāng)建立存儲(chǔ)過(guò)程時(shí)應(yīng)包含異常處理局部。異常(EXCEPTION)是一種PL/SQL標(biāo)識(shí)符，包括預(yù)定義異常、非預(yù)定義異常和自定義異常；預(yù)定義異常是指由PL/SQL提供的系統(tǒng)異常；非預(yù)定義異常用于處理與預(yù)定義異常無(wú)關(guān)的Oracle錯(cuò)誤(如完整性約束等)；自定義異常用于處理與Oracle錯(cuò)誤的其他異常情況。RAISE_APPLICATION_ERROR用于自定義錯(cuò)誤消息，并且消息號(hào)必須在－20000～－20999之間Oracle數(shù)據(jù)庫(kù)中提供了一些異常處理的方法，下面通過(guò)一個(gè)實(shí)例來(lái)說(shuō)明createorreplaceprocedurestu_proc6(pnoinstudent.sno%type,pnameoutstudent.sname%type)isbeginselectsnameintopnamefromstudentwheresno=pno;EXCEPTIONwhenNO_DATA_FOUNDthenRAISE_APPLICATION_ERROR(-20011,'ERROR：不存在！');end;存儲(chǔ)過(guò)程的異常處理實(shí)例命名的系統(tǒng)異常 產(chǎn)生原因ACCESS_INTO_NULL 未定義對(duì)象CASE_NOT_FOUND CASE中假設(shè)未包含相應(yīng)的WHEN，并且沒(méi)有設(shè)置COLLECTION_IS_NULL 集合元素未初始化CURSER_ALREADY_OPEN

游標(biāo)已經(jīng)翻開(kāi)DUP_VAL_ON_INDEX 唯一索引對(duì)應(yīng)的列上有重復(fù)的值INVALID_CURSOR 在不合法的游標(biāo)上進(jìn)行操作INVALID_NUMBER 內(nèi)嵌的SQL語(yǔ)句不能將字符轉(zhuǎn)換為數(shù)字NO_DATA_FOUND 使用selectinto未返回行，或應(yīng)用索引表未初始化的

TOO_MANY_ROWS 執(zhí)行selectinto時(shí)，結(jié)果集超過(guò)一行ZERO_DIVIDE 除數(shù)為0SUBSCRIPT_BEYOND_COUNT 元素下標(biāo)超過(guò)嵌套表或VARRAY的最大值SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或VARRAY時(shí)，將下標(biāo)指定為負(fù)數(shù)VALUE_ERROR 賦值時(shí)，變量長(zhǎng)度缺乏以容納實(shí)際數(shù)據(jù)LOGIN_DENIED

PL/SQL應(yīng)用程序連接到oracle數(shù)據(jù)庫(kù)時(shí)，提供了不正 確的用戶名或密碼NOT_LOGGED_