第7_2章++存储过程.ppt

1、存储过程概述 创建和管理存储过程 在存储过程中使用参数,第7章 存储过程,概 述,可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点： 可以在单个存储过程中执行一系列SQL语句。 可以从自己的存储过程内引用其他存储过程，这可以简化一系列复杂语句。 存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，且能减少网络通信的负担。,存储过程的初始处理,项存入 sysobjects和 syscomments 表,编译过的执行计划放入过程缓存,编译,优化,创建,执行（初次或重新编译）,解析,存储过程的后续处理（续）,存储过程概述 创建和管理存储过程 在存储过程中使用参数,第7

2、章 存储过程,创建存储过程,创建存储过程 只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在 tempdb 数据库中 存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表 若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失,USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM dbo.Orders WHERE RequiredDate GETDATE() AND ShippedDate IS Null GO,执行存储过程,执行存储过程 不带参数的情况： EXECUTE 存储过程名

3、 WITH RECOMPILE,存储过程在执行后都会返回一个整型值。如果执行成功，则返回0；否则返回-1-99之间的数值。也可以使用RETURN语句来指定一个返回值。,USE Northwind GO EXEC OverdueOrders GO,修改和删除存储过程,修改存储过程 用 ALTER PROCEDURE 中的定义取代现有存储过程原先的定义，但保留权限分配,USE Northwind GO ALTER PROC dbo.OverdueOrders AS SELECT CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, CustomerI

4、D, EmployeeID FROM Orders WHERE RequiredDate GETDATE() AND ShippedDate IS Null ORDER BY RequiredDate GO,修改和删除存储过程（续）,删除存储过程 语法：DROP PROCEDURE 存储过程名 ,.n 用 DROP PROCEDURE 语句从当前数据库中移除用户定义存储过程 删除存储过程的注意事项 在删除存储过程之前，执行系统存储过程 sp_depends 检查是否有对象依赖于此存储过程,存储过程概述 创建和管理存储过程 在存储过程中使用参数,第7章 存储过程,在存储过程中使用参数,使用输入参

5、数 使用输入参数执行存储过程 使用输出参数返回值 显式地重新编译存储过程,使用输入参数,输入参数允许传递信息到存储过程内 在 CREATE PROCEDURE 中指定 参数名 数据类型 =默认值,USE Northwind GO CREATE PROC dbo.OverdueOrders2 Employee_ID int , Order_date datetime,AS SELECT OrderDate, OrderID, CustomerID, EmployeeID FROM Orders WHERE EmployeeID = Employee_ID and OrderDate Order_

6、date GO,使用输入参数执行存储过程,通过参数名传递值(顺序无所谓),通过位置传递参数（顺序保持一致）,EXEC OverdueOrders2 Employee_ID = 1 , Order_date = 1996-11-8,EXEC OverdueOrders2 1 , 1996-11-8,EXEC OverdueOrders2 Order_date = 1996-11-8, Employee_ID = 1,使用输出参数返回值,输出参数：以 OUTPUT 关键字指定的变量,CREATE PROC proc1 A int , B int , RESULT int OUTPUT AS SET

7、 RESULT = A * B GO,执行有输出参数的存储过程,DECLARE answer int EXEC proc1 4, 7, answer OUTPUT SELECT answer as ANSWER,必须定义一个变量， 以接受返回值,写上OUTPUT,才 可以接收到返回值,显式地重新编译存储过程,存储过程可以显式地重新编译，但应尽量少做，仅当 存储过程所引用的表中的数据发生巨大的变化时 存储过程所引用的对象的架构发生变更时，如增加删除列、规则、约束,显式地重新编译存储过程（续）,三种显式重新编译存储过程的方法 CREATE PROCEDURE WITH RECOMPILE EXECUTE WITH RECOMPILE,CREATE PROC proc1 A int , B int , RESULT int OUTPUT WITH RECOMPILE AS SET RESULT = A * B GO,不对该存储过程 计划进行高速缓存,EXEC proc1 4, 7, answer OUTPUT WITH RECOMPILE,显式地重新编译存储过程（续）,三种显式重新编译存储过程的方法 sp_recompile,随堂练习,学生表Student（sid,sname,sage,sdept） 课程表Course