存储过程介绍创建和管理存储过程在存储过程中使用参数系统.ppt

《存储过程介绍创建和管理存储过程在存储过程中使用参数系统.ppt》由会员分享，可在线阅读，更多相关《存储过程介绍创建和管理存储过程在存储过程中使用参数系统.ppt（37页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)1Version No:1.0Version No:1.0存储过程介绍创建和管理存储过程在存储过程中使用参数系统 Still waters run deep.流静水深流静水深,人静心深人静心深 Where there is life,there is hope。有生命必有希望。有生命必有希望Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)2Versi

2、on No:1.0Version No:1.0定义存储过程存储过程的优点存储过程的初始化和后续处理存储过程介绍存储过程介绍Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)3Version No:1.0Version No:1.0定义存储过程定义存储过程存储过程存储过程是存储在服务器上的 Transact-SQL 语句的命名集合是封装重复性任务的方法支持用户声明变量、条件执行以及其他强有力的编程特性SQL Server 中的存储过程与其他编程语言中的过程类中的存储过程与其他编程语言中的过程类似，它

3、可以似，它可以包含执行数据库操作（包括调用其他过程）的编程语句接受输入参数向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）以输出参数的形式将多个值返回至调用过程或批处理Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)4Version No:1.0Version No:1.0存储过程的优点存储过程的优点存储过程封装了商务逻辑，确保一致的数据访问和修改存储过程封装了商务逻辑，确保一致的数据访问和修改。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用屏蔽数据

4、库模式的详细资料屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象提供了安全性机制提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限改善性能改善性能。预编译的 Transact-SQL 语句，可以根据条件决定执行哪一部分减少网络通信量减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)5Version No:1.

5、0Version No:1.0存储过程的初始处理存储过程的初始处理项存入项存入 sysobjects和和 syscomments 表表 编译过的执行计划编译过的执行计划放入过程缓存放入过程缓存编译编译优化优化创建创建创建创建执行执行执行执行（初次或（初次或（初次或（初次或重新编译）重新编译）重新编译）重新编译）解析解析Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)6Version No:1.0Version No:1.0存储过程的后续处理存储过程的后续处理若符合下列条件，则 SQL Serve

6、r 使用在内存中的计划来执行随后的查询当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境存储过程引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析。一个执行计划产生后，驻留在过程缓存中。仅当需要空间时，SQL Server 将老的、没用的计划移出缓存Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)7Version No:1.0Version No:1.0存储过程的后续处理（续）存储过程的后续处理（续）检索到的执行计划检索到的执行计划未用过的计划过时

7、被清除未用过的计划过时被清除执行计划执行计划执行上下文执行上下文SELECT*FROM dbo.memberWHEREmember_no=?连接连接18082连接连接2连接连接3241003Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)8Version No:1.0Version No:1.0存储过程介绍创建和管理存储过程在存储过程中使用参数系统和扩展存储过程处理错误信息第第13章章 实现存储过程实现存储过程Copyright2006Copyright2006College of ITSoft

8、(HZIEE)College of ITSoft(HZIEE)9Version No:1.0Version No:1.0创建和管理存储过程创建和管理存储过程创建存储过程执行存储过程修改和删除存储过程Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)10Version No:1.0Version No:1.0创建存储过程创建存储过程创建存储过程只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在 tempdb 数据库中存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表

9、若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失USE NorthwindGOCREATE PROC dbo.OverdueOrdersAS SELECT*FROM dbo.Orders WHERE RequiredDate GETDATE()AND ShippedDate IS NullGOCopyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)11Version No:1.0Version No:1.0创建存储过程（续）创建存储过程（续）创建存储过程（续）CREATE PROCEDURE

10、 定义可以包括任何数目和类型的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 中。Copyright2006Copyright2006Colle

11、ge of ITSoft(HZIEE)College of ITSoft(HZIEE)12Version No:1.0Version No:1.0创建存储过程（续）创建存储过程（续）查看存储过程的信息查看所有类型存储过程的额外信息系统存储过程 sp_help、sp_helptext、sp_depends显示数据库中的存储过程以及拥有者名字的列表系统存储过程 sp_stored_procedures得到存储过程的信息查询系统表 sysobjects、syscomments、sysdependsCopyright2006Copyright2006College of ITSoft(HZIEE)Co

12、llege of ITSoft(HZIEE)13Version No:1.0Version No:1.0创建存储过程的指导原则创建存储过程的指导原则 避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象每个存储过程完成单个任务命名本地存储过程的时候，避免使用“sp_”前缀尽量少使用临时存储过程，以避免频繁连接 tempdb 里的系统表不要直接从 syscomments 系统表里删除项Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)14Version No:1

13、.0Version No:1.0执行存储过程执行存储过程单独执行存储过程单独执行存储过程不带参数的情况：EXECUTE 存储过程名存储过程名 WITH RECOMPILE在在 INSERT 语句内执行存储过程语句内执行存储过程语法：INSERT INTO 表名表名 EXECUTE 将本地或远程存储过程返回的结果集插入本地表中在 INSERT 语句内执行的存储过程必须返回关系结果集USE NorthwindGOEXEC OverdueOrdersGO Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE

14、)15Version No:1.0Version No:1.0修改和删除存储过程修改和删除存储过程修改存储过程用 ALTER PROCEDURE 中的定义取代现有存储过程原先的定义，但保留权限分配USE NorthwindGOALTER PROC dbo.OverdueOrdersASSELECT CONVERT(char(8),OrderDate,1)OrderDate,OrderID,CustomerID,EmployeeIDFROM OrdersWHERE RequiredDate GETDATE()AND ShippedDate IS NullORDER BY RequiredDate

15、GO Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)16Version No:1.0Version No:1.0修改和删除存储过程（续）修改和删除存储过程（续）删除存储过程语法：DROP PROCEDURE 存储过程名,.n用 DROP PROCEDURE 语句从当前数据库中移除用户定义存储过程删除存储过程的注意事项在删除存储过程之前，执行系统存储过程 sp_depends 检查是否有对象依赖于此存储过程Copyright2006Copyright2006College of ITSoft(H

16、ZIEE)College of ITSoft(HZIEE)17Version No:1.0Version No:1.0存储过程介绍创建和管理存储过程在存储过程中使用参数系统和扩展存储过程处理错误信息第第13章章 实现存储过程实现存储过程Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)18Version No:1.0Version No:1.0在存储过程中使用参数在存储过程中使用参数使用输入参数使用输入参数执行存储过程使用输出参数返回值显式地重新编译存储过程Copyright2006Copyrig

17、ht2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)19Version No:1.0Version No:1.0使用输入参数使用输入参数输入参数允许传递信息到存储过程内在 CREATE PROCEDURE 中指定 参数名参数名 数据类型数据类型=默认值默认值 USE NorthwindGOCREATE PROC dbo.OverdueOrders2Employee_ID int,Order_date datetimeASSELECT CONVERT(char(8),OrderDate,1)OrderDate,OrderID,Custome

18、rID,EmployeeIDFROM OrdersWHERE EmployeeID=Employee_ID and OrderDate=Order_dateGO Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)20Version No:1.0Version No:1.0使用输入参数执行存储过程使用输入参数执行存储过程通过参数名传递值(顺序无所谓)通过位置传递参数（顺序保持一致）EXEC OverdueOrders2 Employee_ID=1,Order_date=1996-7-17EXEC O

19、verdueOrders2 1,1996-7-17EXEC OverdueOrders2 Order_date=1996-7-17,Employee_ID=1 Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)21Version No:1.0Version No:1.0使用输出参数返回值使用输出参数返回值输出参数：以 OUTPUT 关键字指定的变量CREATE PROC proc1A int,B int,RESULT int OUTPUTASSET RESULT=A*BGO 执行有输出参数的存储过

20、程DECLARE answer intEXEC proc1 4,7,answer OUTPUTSELECT answer as ANSWER 必须定义一个变量，必须定义一个变量，以接受返回值以接受返回值写上写上OUTPUT,才才可以接收到返回值可以接收到返回值Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)22Version No:1.0Version No:1.0显式地重新编译存储过程显式地重新编译存储过程存储过程可以显式地重新编译，但应尽量少做，仅当存储过程所引用的表中的数据发生巨大的变化时

21、存储过程所引用的对象的架构发生变更时，如增加删除列、规则、约束，或者为底层表增加了存储过程可能从中受益的索引时Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)23Version No:1.0Version No:1.0显式地重新编译存储过程（续）显式地重新编译存储过程（续）三种显式重新编译存储过程的方法CREATE PROCEDURE WITH RECOMPILEEXECUTE WITH RECOMPILE CREATE PROC proc1 A int,B int,RESULT int OUT

22、PUT WITH RECOMPILE AS SET RESULT=A*B GO 不对该存储过程计划进行高速缓存EXEC proc1 4,7,answer OUTPUT WITH RECOMPILE Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)24Version No:1.0Version No:1.0显式地重新编译存储过程（续）显式地重新编译存储过程（续）三种显式重新编译存储过程的方法sp_recompileEXEC sp_recompile proc1说明：下次存储过程proc1执行时重新

23、编译。EXEC sp_recompile Orders说明：作用于表Orders上的存储过程在下次运行时重新编译。Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)25Version No:1.0Version No:1.0存储过程介绍创建和管理存储过程在存储过程中使用参数系统和扩展存储过程处理错误信息第第13章章 实现存储过程实现存储过程Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)26Vers

24、ion No:1.0Version No:1.0存储过程类型存储过程类型SQL Server 支持五种类型的存储过程系统存储过程（sp_）：存储在 master 数据库内，以“sp_”前缀标识本地存储过程：本地存储过程在单独的用户数据库内创建临时存储过程：临时存储过程可能是局部的，名称以“#”开头；也可能是全局的，名称以“#”开头远程存储过程：远程存储过程是 SQL Server 的一个传统功能，分布式查询支持这项功能扩展存储过程（xp_）：扩展存储过程以动态链接库（DLL）的形式实现，在 SQL Server 环境外执行Copyright2006Copyright2006College of

25、 ITSoft(HZIEE)College of ITSoft(HZIEE)27Version No:1.0Version No:1.0执行扩展存储过程执行扩展存储过程扩展存储过程是以动态链接库（DLL）的形式存放C+代码，用于扩展 SQL Server 2000 的功能大多数扩展存储过程和其他系统存储过程一起执行，很少单独使用允许用户使用编程语言例如微软的 Visual C+等创建自己的外部例程，并创建自己的扩展存储过程只能被添加到 master 数据库Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZ

26、IEE)28Version No:1.0Version No:1.0执行扩展存储过程（续）执行扩展存储过程（续）常用的扩展存储过程扩展存储过程描述xp_cmdshell以操作系统外壳命令的形式运行给定命令，并将结以操作系统外壳命令的形式运行给定命令，并将结果作为文本行的形式返回果作为文本行的形式返回xp_Fileexist测试文件是否存在，如果存在，可以进行一些操作测试文件是否存在，如果存在，可以进行一些操作 例1：取得C盘inetpub的目录清单 EXEC xp_cmdshell dir c:inetpub 例2：显示autoexec.bat 文件是否在C盘中 DECLARE ret int

27、 EXEC xp_fileexist c:autoexec.bat,ret output SELECT retCopyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)29Version No:1.0Version No:1.0存储过程介绍创建和管理存储过程在存储过程中使用参数系统和扩展存储过程处理错误信息第第13章章 实现存储过程实现存储过程Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)30Versio

28、n No:1.0Version No:1.0错误信息处理错误信息处理为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败）可以在错误处理逻辑中检查下列错误：返回码、SQL Server 错误、用户定义的错误信息RETURN 语句从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码）返回码为0表示成功。返回非零表示失败。用户定义的返回值总是优先于系统的返回值。Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)31Version No:1.0Version No:1.0错误信

29、息处理（续）错误信息处理（续）sp_addmessage 系统存储过程允许开发者创建用户定义的错误信息，指定消息号、严重级别和消息文本，可设定为把错误信息自动记录到 Windows 2000 应用程序日志中RAISERROR 语句返回一个用户定义的错误信息，并设置系统标志记录发生了一个错误error 全局变量error 包含了最近执行的 Transact-SQL 语句的错误号，随着每一条语句的执行而更新如果语句成功执行，返回0Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)32Version N

30、o:1.0Version No:1.0演示演示1 错误信息处理错误信息处理例：创建一个存储过程，插入学生信息。create create procproc upStudInsert studName varchar(20),birthdate datetime,schooldate datetime as as begin transaction begin transaction insert into tblstudent values(studName,birthdate,schooldate)if errorerror 0 begin rollback tranrollback tra

31、n print failfail return end else print successsuccess commit transaction commit transaction GOCopyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)33Version No:1.0Version No:1.0演示演示1 错误信息处理（续）错误信息处理（续）用下面的语句验证：EXEC upStudInsert Tom,2007-1-1,1988-11-1EXEC upStudInsert Tom,1981-1-

32、1,1988-11-1结果：INSERT 语句与 COLUMN CHECK 约束 CK_tblstuden_birth_6383C8BA 冲突。该冲突发生于数据库 student1，表 tblstudent,column birthdate。语句已终止。fail结果：successCopyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)34Version No:1.0Version No:1.0演示演示2 错误信息处理（续）错误信息处理（续）创建一个自定义的错误信息EXEC SP_ADDMESSAGE

33、50011,16,Please input again,EnglishCREATE PROC proc1a int,b int,result int outputASif a 0 or b 0 begin RAISERROR(50011,16,1)RETURN end set result=a+bGO创建一个返回错误信息的存储过程Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)35Version No:1.0Version No:1.0演示演示2 错误信息处理（续）错误信息处理（续）执行这个存储

34、过程DECLARE result int,a int,b intset a=1set b=-3EXEC proc1 a,b,result outputselect The result =resultCopyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)36Version No:1.0Version No:1.0演示演示 3 错误信息处理（续）错误信息处理（续）创建一个自定义的错误信息EXEC SP_ADDMESSAGE 50005,16,The current database ID is:%d,t

35、he database name is:%s.,englishDECLARE DBID INTSET DBID=DB_ID()DECLARE DBNAME NVARCHAR(128)SET DBNAME=DB_NAME()RAISERROR(50005,16,1,DBID,DBNAME)使用该错误信息Copyright2006Copyright2006College of ITSoft(HZIEE)College of ITSoft(HZIEE)37Version No:1.0Version No:1.0回顾回顾学习完本章后，将能够：描述存储过程是如何处理的创建、执行、修改和删除存储过程创建接受参数的存储过程执行扩展存储过程创建用户定义错误信息