oracle存儲(chǔ)過程(超級(jí)有用)教學(xué)課件

Oracle存儲(chǔ)過程基礎(chǔ)和實(shí)例2020/12/111存儲(chǔ)過程特點(diǎn)1）存儲(chǔ)過程是預(yù)編譯過的，并且經(jīng)優(yōu)化后存儲(chǔ)于SQL內(nèi)存中，使用時(shí)無需再次編譯，提高了工作效率；2）存儲(chǔ)過程的代碼直接存放于數(shù)據(jù)庫中，一般由客戶端直接通過存儲(chǔ)過程的名字進(jìn)行調(diào)用，減少了網(wǎng)絡(luò)流量，加快了系統(tǒng)執(zhí)行速度，例如在進(jìn)行百萬以上的大批量數(shù)據(jù)查詢時(shí)，使用存儲(chǔ)過程分頁要比其他方式分頁快得多；3）使用存儲(chǔ)過程可以減少SQL注入式攻擊，提高了系統(tǒng)的安全性，執(zhí)行存儲(chǔ)過程的用戶要具有一定的權(quán)限才能使用存儲(chǔ)過程，沒有數(shù)據(jù)操作權(quán)限的用戶只能在其控制下間接地存取數(shù)據(jù);2020/12/112存儲(chǔ)過程特點(diǎn)4）在同時(shí)進(jìn)行主、從表及多表間的數(shù)據(jù)維護(hù)及有效性驗(yàn)證時(shí)，使用存儲(chǔ)過程比較方便，而且可以有效利用SQL中的事務(wù)處理的機(jī)制；5）使用存儲(chǔ)過程，可以實(shí)現(xiàn)存儲(chǔ)過程設(shè)計(jì)和編碼工作分開進(jìn)行，只要將存儲(chǔ)過程名、參數(shù)、及返回信息告訴編碼人員即可；6）但使用存儲(chǔ)過程封裝業(yè)務(wù)邏輯將限制應(yīng)用程序的可移植性；另外，如果更改存儲(chǔ)過程的參數(shù)或者其返回的數(shù)據(jù)及類型的話，需要修改應(yīng)用程序的相關(guān)代碼，比較繁瑣。2020/12/113過程的語法結(jié)構(gòu)完整的過程結(jié)構(gòu)如下:

createorreplaceprocedure過程名as

聲明語句段;

begin

執(zhí)行語句段;

exception

異常處理語句段;

end;過程是有名稱的程序塊,as關(guān)鍵詞代替了無名塊的declare.2020/12/114創(chuàng)建過程實(shí)例創(chuàng)建名為stu_proc的過程,create是創(chuàng)建過程的標(biāo)識(shí)符,replace表示若同名過程存在將覆蓋原過程.該過程定義了一個(gè)變量,其類型和student數(shù)據(jù)表中的sname字段類型相同,都是字符型,將數(shù)據(jù)表中的sno字段為1的sname字段內(nèi)容送入變量中,然后輸出結(jié)果.2020/12/115參數(shù)SQL中調(diào)用存儲(chǔ)過程語句：call

procedure_name();調(diào)用時(shí)”()”是不可少的，無論是有參數(shù)還是無參數(shù)。定義對(duì)數(shù)據(jù)庫過程的調(diào)用時(shí)

無參數(shù)過程:{

call

procedure_name}

僅有輸入?yún)?shù)的過程：{call

procedure_name(?,?...)}

這里?表示輸入?yún)?shù)，創(chuàng)建存儲(chǔ)過程時(shí)用in表示輸入?yún)?shù)

僅有輸出參數(shù)的過程:{

Call

procedure_name(?,?...)}

這里的？表示輸出參數(shù)，創(chuàng)建存儲(chǔ)過程時(shí)用out表示輸入?yún)?shù)

既有輸入?yún)?shù)又有輸出參數(shù)的過程 {call

procedure_name(?,?...)}

這里的？有表示輸出參數(shù)的，也有表示輸入?yún)?shù)的

下面將會(huì)對(duì)這4種情況分別舉出實(shí)例?。。?020/12/116參數(shù)過程實(shí)例無參數(shù)存儲(chǔ)過程：createorreplaceprocedurestu_procaspnamevarchar2(25);beginselectsnameintopnamefromstudentwheresno=1;dbms_output.put_line(pname);end;或者createorreplaceprocedurestu_procaspnamestudent.sname%type;beginselectsnameintop_namefromstudentwheresno=1;dbms_output.put_line(pname);end;2020/12/117僅有輸入?yún)?shù)的過程createorreplaceprocedurestu_proc1(pnoinstudent.sno%type)aspnamevarchar2(25);beginselectsnameintopnamefromstudentwheresno=pno;dbms_output.put_line(pname);end;參數(shù)過程實(shí)例2020/12/118僅有輸出參數(shù)的存儲(chǔ)過程createorreplaceprocedurestu_proc2(pnameoutstudent.sname%type)asbeginselectsnameintopnamefromstudentwheresno=1;dbms_output.put_line(pname);end;此種存儲(chǔ)過程不能直接用call來調(diào)用，這種情況的調(diào)用將在下面oracle函數(shù)調(diào)用中說明參數(shù)過程實(shí)例2020/12/119有輸入\輸出參數(shù)的存儲(chǔ)過程：createorreplaceprocedurestu_proc3(pnoinstudent.sno%type,pnameoutstudent.sname%type)asbeginselectsnameintopnamefromstudentwheresno=pno;dbms_output.put_line(pname);end;此種存儲(chǔ)過程不能直接用call來調(diào)用，這種情況的調(diào)用將在下面oracle函數(shù)調(diào)用中說明參數(shù)過程實(shí)例2020/12/1110Oracle函數(shù)調(diào)用存儲(chǔ)過程我們已經(jīng)學(xué)習(xí)了oracle函數(shù)，下面就針對(duì)參數(shù)的4種情況分別舉出實(shí)例說明函數(shù)對(duì)存儲(chǔ)過程的調(diào)用2020/12/1111對(duì)無參數(shù)過程的調(diào)用：

--函數(shù)createorreplacefunctionget_pnamereturnvarchar2ispnamevarchar2(20);beginstu_proc;selectsnameintopnamefromstudentwheresno=1;returnpname;end;--調(diào)用declarebegindbms_output.put_line('在PL/SQL中打印的結(jié)果：'||get_pname);end;函數(shù)調(diào)用存儲(chǔ)過程實(shí)例2020/12/1112對(duì)有輸入?yún)?shù)過程的調(diào)用：createorreplacefunctionget_pname1(pnoinnumber)returnvarchar2ispnamevarchar2(20);beginstu_proc1(pnoinstudent.sno%type)selectsnameintopnamefromstudentwheresno=pno;returnpname;end;--調(diào)用declarebegindbms_output.put_line('在PL/SQL中打印的結(jié)果：'||get_pname1(2));end;函數(shù)調(diào)用存儲(chǔ)過程實(shí)例2020/12/1113對(duì)有輸出參數(shù)過程的調(diào)用:createorreplacefunctionget_pname2(pnameoutvarchar2)returnvarchar2isbeginstu_proc2(pnameoutstudent.sname%type);returnpname;end;--調(diào)用declarepnamestudent.sname%type;begindbms_output.put_line('在PL/SQL中打印的結(jié)果：'||get_pname2(pname));end;函數(shù)調(diào)用存儲(chǔ)過程實(shí)例2020/12/1114對(duì)有輸入\輸出參數(shù)過程的調(diào)用:createorreplacefunctionget_pname3(pnoinnumber,pnameoutvarchar2)returnvarchar2isbeginstu_proc3(pnoinstudent.sno%type,pnameoutstudent.sname%type);returnpname;end;--調(diào)用declarepnamestudent.sname%type;begindbms_output.put_line('在PL/SQL中打印的結(jié)果：'||get_pname3(2,pname));end;

函數(shù)調(diào)用存儲(chǔ)過程實(shí)例2020/12/1115JAVA調(diào)用數(shù)據(jù)庫存儲(chǔ)過程前面我們已經(jīng)講述了有關(guān)oracle數(shù)據(jù)庫的存儲(chǔ)過程的幾種形式，以及oracle函數(shù)對(duì)存儲(chǔ)過程的調(diào)用，下面我將根據(jù)上面存儲(chǔ)過程的實(shí)例來舉出JAVA對(duì)oracle存儲(chǔ)過程的調(diào)用2020/12/1116僅有返回值的過程：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í)例2020/12/1117既有輸入?yún)?shù)又有輸出參數(shù)的過程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í)例2020/12/1118下面將舉出無out參數(shù)的調(diào)用實(shí)例這種參數(shù)不適于用在查詢語句上，因?yàn)椴樵冋Z句需要有返回值才能被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，否則返回flase

JAVA調(diào)用實(shí)例2020/12/1119僅有參數(shù)的過程：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í)例2020/12/1120無參數(shù)過程：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í)例2020/12/1121通過調(diào)用數(shù)據(jù)庫函數(shù)調(diào)用存儲(chǔ)過程下面將舉一個(gè)通過數(shù)據(jù)庫函數(shù)來調(diào)用存儲(chǔ)過程：public

static

voidmain(String[]args){Connectionconn=BBConnection.getConnection();Stringsql="{?=callget_pname1(?)}";try{CallableStatementstatement=conn.prepareCall(sql);statement.registerOutParameter(1,Types.VARCHAR);statement.setInt(2,1);statement.execute();System.out.println(statement.getString(1));}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}finally{try{conn.close();}catch(SQLExceptione){//TODOAuto-generatedcatchblocke.printStackTrace();}}}}？表示函數(shù)return的值，get_pname1是數(shù)據(jù)庫函數(shù)名存儲(chǔ)過程的out和in都是以參數(shù)傳進(jìn)，這就是函數(shù)和存儲(chǔ)過程的區(qū)別之一2020/12/1122存儲(chǔ)過程的異常處理為了提高存儲(chǔ)過程的健壯性，避免運(yùn)行錯(cuò)誤，當(dāng)建立存儲(chǔ)過程時(shí)應(yīng)包含異常處理部分。異常(EXCEPTION)是一種PL/SQL標(biāo)識(shí)符，包括預(yù)定義異常、非預(yù)定義異常和自定義異常；預(yù)定義異常是指由PL/SQL提供的系統(tǒng)異常；非預(yù)定義異常用于處理與預(yù)定義異常無關(guān)的Oracle錯(cuò)誤(如完整性約束等)；自定義異常用于處理與Oracle錯(cuò)誤的其他異常情況。RAISE_APPLICATION_ERROR用于自定義錯(cuò)誤消息，并且消息號(hào)必須在－20000～－20999之間2020/12/1123Oracle數(shù)據(jù)庫中提供了一些異常處理的方法，下面通過一個(gè)實(shí)例來說明createorreplaceprocedurestu_proc6(pnoinstudent.sno%type,pnameoutstudent.sname%type)isbeginselectsnameintopnamefromstudentwheresno=pno;EXCEPTIONwhenNO_DATA_FOUNDthenRAISE_APPLICATION_ERROR(-20011,'ERROR：不存在！');end;存儲(chǔ)過程的異常處理實(shí)例2020/12/1124

命名的系統(tǒng)異常

產(chǎn)生原因ACCESS_INTO_NULL 未定義對(duì)象CASE_NOT_FOUND CASE中若未包含相應(yīng)的WHEN，并且沒有設(shè)置COLLECTION_IS_NULL 集合元素未初始化CURSER_ALREADY_OPEN

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

TOO_MANY_ROWS 執(zhí)行selectinto時(shí)，結(jié)果集超過一行ZERO_DIVIDE 除數(shù)為0SUBSCRIPT_BEYOND_COUNT 元素下標(biāo)超過嵌套表或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ù)庫時(shí)，提供了不正 確的用戶名或密碼NOT_LOGGED_ON PL/SQL應(yīng)用程序在沒有連接oralce數(shù)據(jù)庫的情況下訪 問數(shù)據(jù)PROGRAM_ERROR PL/SQL內(nèi)部問題，可能需要重裝數(shù)據(jù)字典＆pl./SQL系 統(tǒng)包ROWTYPE_MISMATCH 主游標(biāo)變量與PL/SQL游標(biāo)變量的返回類型不兼容SELF_IS_NULL 使用對(duì)象類型時(shí)，在null對(duì)象上調(diào)用對(duì)象方法STORAGE_ERROR 運(yùn)行PL/SQL時(shí)，超出內(nèi)存空間SYS_INVALID_ID 無效的ROWID字符串TIMEOUT_ON_RESOURCE Oracle在等待資源時(shí)超時(shí)Oracle提供的異常處理2020/12/1125自定義異常處理：createorreplaceprocedurestu_proc7(pnoinstudent.sno%type,poninstudent.sno%type)is

v_raiseexception;v_namestudent.sname%type;begin

ifpno=101thenraisev_raise;endif;selectsnameintov_namefromstudentwheresno=111111;exception

whenv_raisethenRAISE_APPLICATION_ERROR(-20010,'ERROR:notexisted!');whenno_data_foundthenRAISE_APPLICATION_ERROR(-20011,'ERROR：不存在！');end;存儲(chǔ)過程的異常處理實(shí)例2020/12/1126存儲(chǔ)過程的事務(wù)處理事務(wù)用于確保數(shù)據(jù)的一致性，由一組相關(guān)的DML語句組成，該組DML語句所執(zhí)行的操作要么全部確認(rèn)，要么全部取消。當(dāng)執(zhí)行事務(wù)操作(DML)時(shí)，Oracle會(huì)在被作用的表上加鎖，以防止其他用戶改變表結(jié)構(gòu)，同時(shí)也會(huì)在被作用的行上加行鎖，以防止其他事務(wù)在相應(yīng)行上執(zhí)行DML操作。當(dāng)執(zhí)行事務(wù)提交或事務(wù)回滾時(shí)，Oracle會(huì)確認(rèn)事務(wù)變化或回滾事務(wù)、結(jié)束事務(wù)、刪除保存點(diǎn)、釋放鎖。2020/12/1127提交事務(wù)（COMMIT）確認(rèn)事務(wù)變化，結(jié)束當(dāng)前事務(wù)、刪除保存點(diǎn)，釋放鎖，使得當(dāng)前事務(wù)中所有未決的數(shù)據(jù)永久改變。保存點(diǎn)（SAVEPOINT）在當(dāng)前事務(wù)中，標(biāo)記事務(wù)的保存點(diǎn)。回滾事務(wù)（ROLLBACK）回滾整個(gè)事務(wù)，刪除該事務(wù)所定義的所有保存點(diǎn)，釋放鎖，丟棄所有未決的數(shù)據(jù)改變。回滾事務(wù)到指定的保存點(diǎn)（ROLLBACKTOSAVEPOINT）回滾當(dāng)前事務(wù)到指定的保存點(diǎn)，丟棄該保存點(diǎn)創(chuàng)建后的任何改變，釋放鎖。存儲(chǔ)過程的事務(wù)處理2020/12/1128當(dāng)執(zhí)行DDL、DCL語句，或退出SQL*PLUS時(shí)，會(huì)自動(dòng)提交事務(wù)；事務(wù)期間應(yīng)避免與使用者互動(dòng)；查詢數(shù)據(jù)期間，盡量不要啟動(dòng)事務(wù)；盡可能讓事務(wù)持續(xù)地越短越好；在事務(wù)中盡可能存取最少的數(shù)據(jù)量。存儲(chǔ)過程的事務(wù)處理2020/12/1129事務(wù)處理實(shí)例存儲(chǔ)過程事務(wù)處理實(shí)例：createorreplaceprocedurestu_proc8isbegininsertintostudentvalues(102,'sky','m',22,'gong');savepointsavepoint1;insertintostudentvalues(102,'good','w',20,'wang');dbms_output.put_line('error');updatestudentsetsno=103wheresname='good';commit;exceptionwhendup_val_on_indexthenrollbacktosavepointsavepoint1;RAISE_APPLICATION_ERROR(-20010,'ERROR：違反唯一索引約束！');end;2020/12/1130數(shù)據(jù)庫函數(shù)和存儲(chǔ)過程的包創(chuàng)建包（package）函數(shù)：createorreplacepackage包名as函數(shù)注意：as后可加多個(gè)函數(shù)存儲(chǔ)過程：Createorreplacepackage包名as存儲(chǔ)過程注意：as后可加多個(gè)存儲(chǔ)過程2020/12/1131函數(shù)的包調(diào)用：call包名.函數(shù)名；存儲(chǔ)過程的包調(diào)用：call包名.存儲(chǔ)過程名；包的調(diào)用2020/12/1132無參數(shù)存儲(chǔ)過程--無參數(shù)存儲(chǔ)過程createprocedurestu_proc1asp_namevarchar2(32);beginselectsnameintop_namefromst