oracle存储过程与函数省公开课一等奖全国示范课微课金奖课件

第十四章存放过程与函数第1页第十三章存放过程与函数定义：过程（函数）是在服务器端运行完成一定功效一段pl/sql程序。优点：存放过程因为SQL语句已经预编绎过了，所以运行速度比较快。存放过程能够接收参数、输出参数、返回单个或多个结果集以及返回值。能够向程序返回错误原因。存放过程运行比较稳定，不会有太多错误。只要一次成功，以后都会按这个程序运行。

第2页第十三章存放过程与函数存放过程主要是在服务器上运行，降低对客户机压力。存放过程能够包含程序流、逻辑以及对数据库查询。同时能够实体封装和隐藏了数据逻辑。存放过程能够在单个存放过程中执行一系列SQL语句。存放过程能够从自己存放过程内引用其它存放过程，这能够简化一系列复杂语句。第3页14.1存放过程创建过程语法:create[orreplace]procedureprocedure_name[(argment[{in|inout}]type,argment[{in|out|inout}]type{is|as}<类型.变量说明>(注:不用declare语句)Begin<执行部分>exception<可选异常处理说明>end;第4页14.1存放过程这里IN表示向存放过程传递参数，OUT表示从存放过程返回参数。而INOUT表示传递参数和返回参数；在存放过程内变量类型只能指定变量类型；不能指定长度；在AS或IS后申明要用到变量名称和变量类型及长度；在AS或IS后申明变量不要加declare语句。第5页14.1存放过程CREATEORREPLACEPROCEDUREModeTest(p_InParameterINNUMBER,p_OutParameterOUTNUMBER,p_InOutParameterINOUTNUMBER)ISv_LocalVariableNUMBER;第6页14.1存放过程BEGINv_LocalVariable:=p_InParameter;--Legalp_InParameter:=7;--Illegalp_OutParameter:=7;--Legalv_LocalVariable:=p_outParameter;--Illegalv_LocalVariable:=p_InOutParameter;--Legalp_InOutParameter:=7;--LegalENDModeTest;/第7页14.1存放过程使用过程存放过程建立完成后，只要经过授权，用户就能够在SQLPLUS、Oracle开发工具或第三方开发工具来调用运行。Oracle使用EXECUTE语句来实现对存放过程调用。第8页14.1存放过程开发存放过程步骤1、编辑存放过程源码使用文字编辑处理软件编辑存放过程源码，要用类似WORD文字处理软件进行编辑时，要将源码存为文本格式。2、对存放过程程序进行解释在SQLPLUS或用调试工具将存放过程程序进行解释；如：SQL>startc:\stat1.sql3、调试源码直到正确。在SQLPLUS下来调试主要用方法是：使用SHOWERROR命令来提醒源码错误位置；使用user_errors数据字典来查看各存放过程错误位置。第9页14.1存放过程例1创建一个存放过程，完成给定员工号以后，删除该员工。

CREATEORREPLACEPROCEDUREDelEmp(p_empnoINemp.empno%TYPE)ASNo_resultEXCEPTION;BEGINDELETEFROMempWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;DBMS_OUTPUT.PUT_LINE('编码为'||p_empno||'员工已被除名!');第10页14.1存放过程EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你需要数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END;第11页14.1存放过程例2给指定员工加薪。CREATEORREPLACEPROCEDUREmon_addsal(p_empnoinemp.empno%TYPE,p_addsalinm%TYPE)ASno_resultEXCEPTION;BEGINUPDATEempSETcomm=p_addsalWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;第12页14.1存放过程DBMS_OUTPUT.PUT_LINE(p_empno||'本月加薪额度为'||p_addsal);EXCEPTIONWHENno_resultTHEN DBMS_OUTPUT.PUT_LINE('该员工不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('未知错误!');END;第13页14.2创建函数Oracle函数是一个独有对象，它也是由PL/SQL语句编写而成，但不一样地方是：函数必须返回一些值，而存放过程能够不返回任何值。第14页14.2创建函数CREATEFUNCTION语法以下：create[orreplace]functionfunction_name[(argment[{in|inout}]TYPE,argment[{in|out|inout}]type]returnreturn_type{is|as}beginfunction_bodyexception......end;第15页14.2创建函数例1：创建一个函数，完成给定部门号以后，求出该部门全部员工工资和。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页14.2创建函数EXCEPTIONWHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('你需要数据不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('发生其它错误!');END;第17页14.2创建函数调用过程：DECLAREv_sum_salnumber;BEGINV_sum_sal:=f_sum_sal(20);DBMS_OUTPUT.PUT_LINE('总工资是'||v_sum_sal);END;第18页14.3过程和函数中例外处理使用例外处理必要性一旦出现例外情况，Oracle就自动终止程序运行。当程序犯错时用户无法得到提醒，调试者也无法进行修改程序。普通不论多简单程序最好也要给出例外处理要求。第19页14.3过程和函数中例外处理1、用户定义例外必须申明且必须用RAISE语句来激活no_resultEXCEPTION;BEGINIFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;……EXCEPTIONWHENno_resultTHEN DBMS_OUTPUT.PUT_LINE('该员工不存在!');WHENOTHERSTHEN DBMS_OUTPUT.PUT_LINE('未知错误!');END;第20页14.3过程和函数中例外处理使用户EXCEPTION_INIT处理.Pragma（也叫伪指令）是在编译时内处理，不是在运行时被处理。DECLAREdeadlock_detectedEXCEPTION;PRAGMAEXCEPTION_INIT(deadlock_detected,-60);BEGIN...EXCEPTIONWHENdeadlock_detectedTHEN--handletheerrorEND;第21页14.3过程和函数中例外处理使用户raise_application_error处理BEGINSELECTsalINTOcurr_salFROMempWHEREempno=emp_id;IFcurr_salISNULLTHEN/*公布用户定义错误信息*/raise_application_error(-1,’Salaryismissing’);ELSEUPDATEempSETsal=curr_sal+amountWHEREempno=emp_id;ENDIF;ENDraise_salary;第22页与存放过程、函数相关数据字典user_source用户存放过程、函数源代码字典all_source全部用户存放过程、函数源代码字典user_errors用户存放过程、函数源代码存在错误信息字典相关权限:createanyproceduredropanyprocedure假如某个用户没有权