oracle 存储过程

•存储过程介绍•创建和管理存储过程•在存储过程中使用参数•处理错误信息补充：实现存储过程•定义存储过程•存储过程的优点存储过程介绍定义存储过程•存储过程–是存储在服务器上的Transact-SQL语句的命名集合–是封装重复性任务的方法–支持用户声明变量、条件执行以及其他强有力的编程特性•SQLServer中的存储过程与其他编程语言中的过程类似，它可以–包含执行数据库操作（包括调用其他过程）的编程语句–接受输入参数–向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）–以输出参数的形式将多个值返回至调用过程或批处理存储过程的优点•存储过程封装了商务逻辑，确保一致的数据访问和修改。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用•屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象•提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限•改善性能。预编译的Transact-SQL语句，可以根据条件决定执行哪一部分•减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包•存储过程介绍•创建和管理存储过程•在存储过程中使用参数•处理错误信息实现存储过程创建和管理存储过程•创建存储过程•执行存储过程•修改和删除存储过程创建存储过程•创建存储过程–只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在tempdb数据库中–存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表–若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失USENorthwindGOCREATEPROCdbo.OverdueOrdersASSELECT*FROMdbo.OrdersWHERERequiredDateGETDATE()ANDShippedDateISNullGO创建存储过程（续）•创建存储过程（续）–CREATEPROCEDURE定义可以包括任何数目和类型的Transact-SQL语句，但不包括下列对象创建语句：CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW–执行CREATEPROCEDURE语句的用户必须是sysadmin、db_owner或db_ddladmin角色的成员，或必须拥有CREATEPROCEDURE权限–依赖于可用内存，存储过程的最大大小为128MB–存储过程可以嵌套32层。当前的嵌套层数存储在系统函数@@nestlevel中。创建存储过程（续）•查看存储过程的信息–查看所有类型存储过程的额外信息•系统存储过程sp_help、sp_helptext、sp_depends–显示数据库中的存储过程以及拥有者名字的列表•系统存储过程sp_stored_procedures–得到存储过程的信息•查询系统表sysobjects、syscomments、sysdepends创建存储过程的指导原则•避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象•每个存储过程完成单个任务•命名本地存储过程的时候，避免使用“sp_”前缀•尽量少使用临时存储过程，以避免频繁连接tempdb里的系统表•不要直接从syscomments系统表里删除项执行存储过程•单独执行存储过程不带参数的情况：[[EXEC[UTE]存储过程名[WITHRECOMPILE]•在INSERT语句内执行存储过程语法：INSERTINTO表名EXEC[UTE]……–将本地或远程存储过程返回的结果集插入本地表中–在INSERT语句内执行的存储过程必须返回关系结果集USENorthwindGOEXECOverdueOrdersGO修改和删除存储过程•修改存储过程–用ALTERPROCEDURE中的定义取代现有存储过程原先的定义，但保留权限分配USENorthwindGOALTERPROCdbo.OverdueOrdersASSELECTCONVERT(char(8),OrderDate,1)OrderDate,OrderID,CustomerID,EmployeeIDFROMOrdersWHERERequiredDateGETDATE()ANDShippedDateISNullORDERBYRequiredDateGO修改和删除存储过程（续）•删除存储过程语法：DROPPROCEDURE{存储过程名}[,...n]–用DROPPROCEDURE语句从当前数据库中移除用户定义存储过程•删除存储过程的注意事项–在删除存储过程之前，执行系统存储过程sp_depends检查是否有对象依赖于此存储过程•存储过程介绍•创建和管理存储过程•在存储过程中使用参数•处理错误信息实现存储过程在存储过程中使用参数•使用输入参数•使用输入参数执行存储过程•使用输出参数返回值使用输入参数•输入参数允许传递信息到存储过程内–在CREATEPROCEDURE中指定@参数名数据类型[=默认值]USENorthwindGOCREATEPROCdbo.OverdueOrders2@Employee_IDint,@Order_datedatetimeASSELECTCONVERT(char(8),OrderDate,1)OrderDate,OrderID,CustomerID,EmployeeIDFROMOrdersWHEREEmployeeID=@Employee_IDandOrderDate=@Order_dateGO使用输入参数执行存储过程•通过参数名传递值(顺序无所谓)•通过位置传递参数（顺序保持一致）EXECOverdueOrders2@Employee_ID=1,@Order_date='1996-7-17'EXECOverdueOrders21,'1996-7-17'EXECOverdueOrders2@Order_date='1996-7-17',@Employee_ID=1使用输出参数返回值•输出参数：以OUTPUT关键字指定的变量CREATEPROCproc1@Aint,@Bint,@RESULTintOUTPUTASSET@RESULT=@A*@BGO•执行有输出参数的存储过程DECLARE@answerintEXECproc14,7,@answerOUTPUTSELECT@answerasANSWER必须定义一个变量，以接受返回值写上OUTPUT,才可以接收到返回值•存储过程介绍•创建和管理存储过程•在存储过程中使用参数•处理错误信息实现存储过程错误信息处理•为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败）–可以在错误处理逻辑中检查下列错误：返回码、SQLServer错误、用户定义的错误信息•RETURN语句–从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码）–返回码为0表示成功。返回非零表示失败。用户定义的返回值总是优先于系统的返回值。错误信息处理（续）•@@error全局变量–@@error包含了最近执行的Transact-SQL语句的错误号，随着每一条语句的执行而更新–如果语句成功执行，返回0演示错误信息处理例：创建一个存储过程，插入学生信息。createprocupStudInsert@studNamevarchar(20),@birthdatedatetime,@schooldatedatetimeasbegintransactioninsertintotblstudentvalues(@studName,@birthdate,@schooldate)if@@error0beginrollbacktranprint'fail'returnendelseprint'success'committransactionGO演示1错误信息处理（续）用下面的语句验证：EXECupStudInsert‘Tom’,‘2007-1-1’,‘1988-11-1’EXECupStudInsert‘Tom’,‘1981-1-1’,‘1988-11-1’结果：INSERT语句与COLUMNCHECK约束‘CK__tblstuden__birth__6383C8BA’冲突。该冲突发生于数据库‘student1’，表‘tblstudent’,column‘birthdate’。语句已终止。fail结果：successENDING•描述存储过程是如何处理的•创建、执行、修改和删除存储过程•创建接受参数的存储过程•创建用户定义错误信息