oracle 存储过程.ppt

1、存储过程介绍 创建和管理存储过程 在存储过程中使用参数 处理错误信息,补充：实现存储过程,定义存储过程 存储过程的优点,存储过程介绍,定义存储过程,存储过程 是存储在服务器上的 Transact-SQL 语句的命名集合 是封装重复性任务的方法 支持用户声明变量、条件执行以及其他强有力的编程特性 SQL Server 中的存储过程与其他编程语言中的过程类似，它可以 包含执行数据库操作（包括调用其他过程）的编程语句 接受输入参数 向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因） 以输出参数的形式将多个值返回至调用过程或批处理,存储过程的优点,存储过程封装了商务逻辑，确保一致的数据访问

2、和修改。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用 屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象 提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限 改善性能。预编译的 Transact-SQL 语句，可以根据条件决定执行哪一部分 减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包,存储过程介绍 创建和管理存储过程 在存储过程中使用参数 处理错误信息,实现存储过程,创建和管理存储过程,创建存储过程 执行存储过程 修

3、改和删除存储过程,创建存储过程,创建存储过程 只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在 tempdb 数据库中 存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表 若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失,USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM dbo.Orders WHERE RequiredDate GETDATE() AND ShippedDate IS Null GO,创建存储过程（续）,创建存储过程（续） CREATE PROCEDU

4、RE 定义可以包括任何数目和类型的Transact-SQL语句，但不包括下列对象创建语句：CREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 执行 CREATE PROCEDURE 语句的用户必须是 sysadmin、db_owner 或 db_ddladmin角色的成员，或必须拥有 CREATE PROCEDURE 权限 依赖于可用内存，存储过程的最大大小为128 MB 存储过程可以嵌套32层。当前的嵌套层数存储在系统函数 nestlevel 中。,创建存储过程（续）,查看存储过程的信息 查看所有类

5、型存储过程的额外信息 系统存储过程 sp_help、sp_helptext、sp_depends 显示数据库中的存储过程以及拥有者名字的列表 系统存储过程 sp_stored_procedures 得到存储过程的信息 查询系统表 sysobjects、syscomments、sysdepends,创建存储过程的指导原则,避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象 每个存储过程完成单个任务 命名本地存储过程的时候，避免使用“sp_”前缀 尽量少使用临时存储过程，以避免频繁连接 tempdb 里的系统表 不要直接从 syscomments 系统表里删

6、除项,执行存储过程,单独执行存储过程 不带参数的情况：EXECUTE 存储过程名 WITH RECOMPILE,在 INSERT 语句内执行存储过程 语法：INSERT INTO 表名 EXECUTE 将本地或远程存储过程返回的结果集插入本地表中 在 INSERT 语句内执行的存储过程必须返回关系结果集,USE Northwind GO EXEC OverdueOrders GO,修改和删除存储过程,修改存储过程 用 ALTER PROCEDURE 中的定义取代现有存储过程原先的定义，但保留权限分配,USE Northwind GO ALTER PROC dbo.OverdueOrders A

7、S SELECT CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerID, EmployeeID FROM Orders WHERE RequiredDate GETDATE() AND ShippedDate IS Null ORDER BY RequiredDate GO,修改和删除存储过程（续）,删除存储过程 语法：DROP PROCEDURE 存储过程名 ,.n 用 DROP PROCEDURE 语句从当前数据库中移除用户定义存储过程 删除存储过程的注意事项 在删除存储过程之前，执行系统存储过程 sp_depends

8、检查是否有对象依赖于此存储过程,存储过程介绍 创建和管理存储过程 在存储过程中使用参数 处理错误信息,实现存储过程,在存储过程中使用参数,使用输入参数 使用输入参数执行存储过程 使用输出参数返回值,使用输入参数,输入参数允许传递信息到存储过程内 在 CREATE PROCEDURE 中指定 参数名 数据类型 =默认值,USE Northwind GO CREATE PROC dbo.OverdueOrders2 Employee_ID int , Order_date datetime,AS SELECT CONVERT(char(8), OrderDate, 1) OrderDate, Or

9、derID, CustomerID, EmployeeID FROM Orders WHERE EmployeeID = Employee_ID and OrderDate = Order_date GO,使用输入参数执行存储过程,通过参数名传递值(顺序无所谓),通过位置传递参数（顺序保持一致）,EXEC OverdueOrders2 Employee_ID = 1 , Order_date = 1996-7-17,EXEC OverdueOrders2 1 , 1996-7-17,EXEC OverdueOrders2 Order_date = 1996-7-17, Employee_ID

10、= 1,使用输出参数返回值,输出参数：以 OUTPUT 关键字指定的变量,CREATE PROC proc1 A int , B int , RESULT int OUTPUT AS SET RESULT = A * B GO,执行有输出参数的存储过程,DECLARE answer int EXEC proc1 4, 7, answer OUTPUT SELECT answer as ANSWER,必须定义一个变量， 以接受返回值,写上OUTPUT,才 可以接收到返回值,存储过程介绍 创建和管理存储过程 在存储过程中使用参数 处理错误信息,实现存储过程,错误信息处理,为了增强存储过程的效率，应

11、使用错误信息向用户传达事务状态（成功或失败） 可以在错误处理逻辑中检查下列错误：返回码、SQL Server 错误、用户定义的错误信息 RETURN 语句 从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码） 返回码为0表示成功。返回非零表示失败。用户定义的返回值总是优先于系统的返回值。,错误信息处理（续）,error 全局变量 error 包含了最近执行的 Transact-SQL 语句的错误号，随着每一条语句的执行而更新 如果语句成功执行，返回0,演示 错误信息处理,例：创建一个存储过程，插入学生信息。,create proc upStudInsert studName var

12、char(20),birthdate datetime,schooldate datetime as begin transaction insert into tblstudent values(studName,birthdate,schooldate) if error 0 begin rollback tran print fail return end else print success commit transaction GO,演示1 错误信息处理（续）,用下面的语句验证：,EXEC upStudInsert Tom, 2007-1-1, 1988-11-1,EXEC upStudInsert Tom, 1981-1-1, 1988-11-1,结果：