oracle 存储过程与函数

辽宁工程技术大学软件工程系E-MAIL:[e-mail]:第十四章存储过程与函数辽宁工程技术大学软件工程系E-MAIL:[e-mail]:第十三章存储过程与函数定义：过程（函数）是在服务器端运行的完成一定功能的一段pl/sql程序。优点：1.存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。2.存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。3.存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。辽宁工程技术大学软件工程系E-MAIL:[e-mail]:第十三章存储过程与函数4.存储过程主要是在服务器上运行，减少对客户机的压力。5.存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。6.存储过程可以在单个存储过程中执行一系列SQL语句。7.存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程创建过程语法:create[orreplace]procedureprocedure_name[(argment[{in|inout}]type,argment[{in|out|inout}]type{is|as}类型.变量的说明(注:不用declare语句)Begin执行部分exception可选的异常处理说明end;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程这里的IN表示向存储过程传递参数，OUT表示从存储过程返回参数。而INOUT表示传递参数和返回参数；在存储过程内的变量类型只能指定变量类型；不能指定长度；在AS或IS后声明要用到的变量名称和变量类型及长度；在AS或IS后声明变量不要加declare语句。辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程CREATEORREPLACEPROCEDUREModeTest(p_InParameterINNUMBER,p_OutParameterOUTNUMBER,p_InOutParameterINOUTNUMBER)ISv_LocalVariableNUMBER;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程BEGINv_LocalVariable:=p_InParameter;--Legalp_InParameter:=7;--Illegalp_OutParameter:=7;--Legalv_LocalVariable:=p_outParameter;--Illegalv_LocalVariable:=p_InOutParameter;--Legalp_InOutParameter:=7;--LegalENDModeTest;/辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程使用过程存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS、Oracle开发工具或第三方开发工具来调用运行。Oracle使用EXECUTE语句来实现对存储过程的调用。辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程开发存储过程步骤1、编辑存储过程源码使用文字编辑处理软件编辑存储过程源码，要用类似WORD文字处理软件进行编辑时，要将源码存为文本格式。2、对存储过程程序进行解释在SQLPLUS或用调试工具将存储过程程序进行解释；如：SQLstartc:\stat1.sql3、调试源码直到正确。在SQLPLUS下来调试主要用的方法是：使用SHOWERROR命令来提示源码的错误位置；使用user_errors数据字典来查看各存储过程的错误位置。辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程例1创建一个存储过程，完成给定的员工号以后，删除该员工。CREATEORREPLACEPROCEDUREDelEmp(p_empnoINemp.empno%TYPE)ASNo_resultEXCEPTION;BEGINDELETEFROMempWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;DBMS_OUTPUT.PUT_LINE('编码为'||p_empno||'的员工已被除名!');辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程例2给指定的员工加薪。CREATEORREPLACEPROCEDUREmon_addsal(p_empnoinemp.empno%TYPE,p_addsalinemp.comm%TYPE)ASno_resultEXCEPTION;BEGINUPDATEempSETcomm=p_addsalWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:存储过程DBMS_OUTPUT.PUT_LINE(p_empno||'的本月加薪额度为'||p_addsal);EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('该员工不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('未知错误!');END;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:创建函数Oracle的函数是一个独有的对象，它也是由PL/SQL语句编写而成，但的不同的地方是：函数必须返回某些值，而存储过程可以不返回任何值。辽宁工程技术大学软件工程系E-MAIL:[e-mail]:创建函数CREATEFUNCTION语法如下：create[orreplace]functionfunction_name[(argment[{in|inout}]TYPE,argment[{in|out|inout}]type]returnreturn_type{is|as}beginfunction_bodyexception......end;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:创建函数例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;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:创建函数EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('发生其它错误!');END;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:创建函数调用过程：DECLAREv_sum_salnumber;BEGINV_sum_sal:=f_sum_sal(20);DBMS_OUTPUT.PUT_LINE('总工资是'||v_sum_sal);END;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:过程和函数中的例外处理使用例外处理的必要性一旦出现例外的情况，Oracle就自动终止程序的运行。当程序出错时用户无法得到提示，调试者也无法进行修改程序。一般无论多简单的程序最好也要给出例外处理的要求。辽宁工程技术大学软件工程系E-MAIL:[e-mail]:过程和函数中的例外处理1、用户定义的例外必须声明且必须用RAISE语句来激活no_resultEXCEPTION;BEGINIFSQL%NOTFOUNDTHENRAISEno_result;ENDIF;……EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE('该员工不存在!');WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('未知错误!');END;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:过程和函数中的例外处理使用户EXCEPTION_INIT处理.Pragma（也叫伪指令）是在编译时内处理，不是在运行时被处理。DECLAREdeadlock_detectedEXCEPTION;PRAGMAEXCEPTION_INIT(deadlock_detected,-60);BEGIN...EXCEPTIONWHENdeadlock_detectedTHEN--handletheerrorEND;辽宁工程技术大学软件工程系E-MAIL:[e-mail]:过程