oracle存儲(chǔ)過(guò)程與函數(shù)省公開(kāi)課一等獎(jiǎng)全國(guó)示范課微課金獎(jiǎng)?wù)n件

第十四章存放過(guò)程與函數(shù)第1頁(yè)第十三章存放過(guò)程與函數(shù)定義：過(guò)程（函數(shù)）是在服務(wù)器端運(yùn)行完成一定功效一段pl/sql程序。優(yōu)點(diǎn)：存放過(guò)程因?yàn)镾QL語(yǔ)句已經(jīng)預(yù)編繹過(guò)了，所以運(yùn)行速度比較快。存放過(guò)程能夠接收參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及返回值。能夠向程序返回錯(cuò)誤原因。存放過(guò)程運(yùn)行比較穩(wěn)定，不會(huì)有太多錯(cuò)誤。只要一次成功，以后都會(huì)按這個(gè)程序運(yùn)行。

第2頁(yè)第十三章存放過(guò)程與函數(shù)存放過(guò)程主要是在服務(wù)器上運(yùn)行，降低對(duì)客戶機(jī)壓力。存放過(guò)程能夠包含程序流、邏輯以及對(duì)數(shù)據(jù)庫(kù)查詢。同時(shí)能夠?qū)嶓w封裝和隱藏了數(shù)據(jù)邏輯。存放過(guò)程能夠在單個(gè)存放過(guò)程中執(zhí)行一系列SQL語(yǔ)句。存放過(guò)程能夠從自己存放過(guò)程內(nèi)引用其它存放過(guò)程，這能夠簡(jiǎn)化一系列復(fù)雜語(yǔ)句。第3頁(yè)14.1存放過(guò)程創(chuàng)建過(guò)程語(yǔ)法:create[orreplace]procedureprocedure_name[(argment[{in|inout}]type,argment[{in|out|inout}]type{is|as}<類型.變量說(shuō)明>(注:不用declare語(yǔ)句)Begin<執(zhí)行部分>exception<可選異常處理說(shuō)明>end;第4頁(yè)14.1存放過(guò)程這里IN表示向存放過(guò)程傳遞參數(shù)，OUT表示從存放過(guò)程返回參數(shù)。而INOUT表示傳遞參數(shù)和返回參數(shù)；在存放過(guò)程內(nèi)變量類型只能指定變量類型；不能指定長(zhǎng)度；在AS或IS后申明要用到變量名稱和變量類型及長(zhǎng)度；在AS或IS后申明變量不要加declare語(yǔ)句。第5頁(yè)14.1存放過(guò)程CREATEORREPLACEPROCEDUREModeTest(p_InParameterINNUMBER,p_OutParameterOUTNUMBER,p_InOutParameterINOUTNUMBER)ISv_LocalVariableNUMBER;第6頁(yè)14.1存放過(guò)程BEGINv_LocalVariable:=p_InParameter;--Legalp_InParameter:=7;--Illegalp_OutParameter:=7;--Legalv_LocalVariable:=p_outParameter;--Illegalv_LocalVariable:=p_InOutParameter;--Legalp_InOutParameter:=7;--LegalENDModeTest;/第7頁(yè)14.1存放過(guò)程使用過(guò)程存放過(guò)程建立完成后，只要經(jīng)過(guò)授權(quán)，用戶就能夠在SQLPLUS、Oracle開(kāi)發(fā)工具或第三方開(kāi)發(fā)工具來(lái)調(diào)用運(yùn)行。Oracle使用EXECUTE語(yǔ)句來(lái)實(shí)現(xiàn)對(duì)存放過(guò)程調(diào)用。第8頁(yè)14.1存放過(guò)程開(kāi)發(fā)存放過(guò)程步驟1、編輯存放過(guò)程源碼使用文字編輯處理軟件編輯存放過(guò)程源碼，要用類似WORD文字處理軟件進(jìn)行編輯時(shí)，要將源碼存為文本格式。2、對(duì)存放過(guò)程程序進(jìn)行解釋在SQLPLUS或用調(diào)試工具將存放過(guò)程程序進(jìn)行解釋；如：SQL>startc:\stat1.sql3、調(diào)試源碼直到正確。在SQLPLUS下來(lái)調(diào)試主要用方法是：使用SHOWERROR命令來(lái)提醒源碼錯(cuò)誤位置；使用user_errors數(shù)據(jù)字典來(lái)查看各存放過(guò)程錯(cuò)誤位置。第9頁(yè)14.1存放過(guò)程例1創(chuàng)建一個(gè)存放過(guò)程，完成給定員工號(hào)以后，刪除該員工。

CREATEORREPLACEPROCEDUREDelEmp(p_empnoINemp.empno%TYPE)ASNo_resultEXCEPTION;BEGINDELETEFROMempWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;DBMS_OUTPUT.PUT_LINE('編碼為'||p_empno||'員工已被除名!');第10頁(yè)14.1存放過(guò)程EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你需要數(shù)據(jù)不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('發(fā)生其它錯(cuò)誤!');END;第11頁(yè)14.1存放過(guò)程例2給指定員工加薪。CREATEORREPLACEPROCEDUREmon_addsal(p_empnoinemp.empno%TYPE,p_addsalinm%TYPE)ASno_resultEXCEPTION;BEGINUPDATEempSETcomm=p_addsalWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;第12頁(yè)14.1存放過(guò)程DBMS_OUTPUT.PUT_LINE(p_empno||'本月加薪額度為'||p_addsal);EXCEPTIONWHENno_resultTHEN DBMS_OUTPUT.PUT_LINE('該員工不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('未知錯(cuò)誤!');END;第13頁(yè)14.2創(chuàng)建函數(shù)Oracle函數(shù)是一個(gè)獨(dú)有對(duì)象，它也是由PL/SQL語(yǔ)句編寫而成，但不一樣地方是：函數(shù)必須返回一些值，而存放過(guò)程能夠不返回任何值。第14頁(yè)14.2創(chuàng)建函數(shù)CREATEFUNCTION語(yǔ)法以下：create[orreplace]functionfunction_name[(argment[{in|inout}]TYPE,argment[{in|out|inout}]type]returnreturn_type{is|as}beginfunction_bodyexception......end;第15頁(yè)14.2創(chuàng)建函數(shù)例1：創(chuàng)建一個(gè)函數(shù)，完成給定部門號(hào)以后，求出該部門全部員工工資和。CREATEORREPLACEFUNCTIONf_sum_sal(deptidINemp.deptno%TYPE)RETURNNUMBERASv_sumsalnumber;BEGINSELECTSUM(sal)+SUM(nvl(comm,0))INTOv_sumsalFROMempWHEREdeptno=deptid;--DBMS_OUTPUT.PUT_LINE(deptid||'工資和為'||v_sumsal);RETURNV_sumsal;第16頁(yè)14.2創(chuàng)建函數(shù)EXCEPTIONWHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('你需要數(shù)據(jù)不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('發(fā)生其它錯(cuò)誤!');END;第17頁(yè)14.2創(chuàng)建函數(shù)調(diào)用過(guò)程：DECLAREv_sum_salnumber;BEGINV_sum_sal:=f_sum_sal(20);DBMS_OUTPUT.PUT_LINE('總工資是'||v_sum_sal);END;第18頁(yè)14.3過(guò)程和函數(shù)中例外處理使用例外處理必要性一旦出現(xiàn)例外情況，Oracle就自動(dòng)終止程序運(yùn)行。當(dāng)程序犯錯(cuò)時(shí)用戶無(wú)法得到提醒，調(diào)試者也無(wú)法進(jìn)行修改程序。普通不論多簡(jiǎn)單程序最好也要給出例外處理要求。第19頁(yè)14.3過(guò)程和函數(shù)中例外處理1、用戶定義例外必須申明且必須用RAISE語(yǔ)句來(lái)激活no_resultEXCEPTION;BEGINIFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;……EXCEPTIONWHENno_resultTHEN DBMS_OUTPUT.PUT_LINE('該員工不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('未知錯(cuò)誤!');END;第20頁(yè)14.3過(guò)程和函數(shù)中例外處理使用戶EXCEPTION_INIT處理.Pragma（也叫偽指令）是在編譯時(shí)內(nèi)處理，不是在運(yùn)行時(shí)被處理。DECLAREdeadlock_detectedEXCEPTION;PRAGMAEXCEPTION_INIT(deadlock_detected,-60);BEGIN...EXCEPTIONWHENdeadlock_detectedTHEN--handletheerrorEND;第21頁(yè)14.3過(guò)程和函數(shù)中例外處理使用戶raise_application_error處理BEGINSELECTsalINTOcurr_salFROMempWHEREempno=emp_id;IFcurr_salISNULLTHEN/*公布用戶定義錯(cuò)誤信息*/raise_application_error(-1,’Salaryismissing’);ELSEUPDATEempSETsal=curr_sal+amountWHEREempno=emp_id;ENDIF;ENDraise_salary;第22頁(yè)與存放過(guò)程、函數(shù)相關(guān)數(shù)據(jù)字典user_source用戶存放過(guò)程、函數(shù)源代碼字典all_source全部用戶存放過(guò)程、函數(shù)源代碼字典user_errors用戶存放過(guò)程、函數(shù)源代碼存在錯(cuò)誤信息字典相關(guān)權(quán)限:createanyproceduredropanyprocedure假如某個(gè)用戶沒(méi)有權(quán)