数据库_第13章__实现存储过程

null第13章 实现存储过程第13章 实现存储过程存储过程介绍 创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息存储过程介绍存储过程介绍定义存储过程 存储过程的优点 存储过程的初始化和后续处理定义存储过程定义存储过程存储过程 是存储在服务器上的 Transact-SQL 语句的命名集合 是封装重复性任务的方法 支持用户声明变量、条件执行以及其他强有力的编程特性 SQL Server 中的存储过程与其他编程语言中的过程类似，它可以 包含执行数据库操作（包括调用其他过程）的编程语句 接受输入参数 向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因） 以输出参数的形式将多个值返回至调用过程或批处理存储过程的优点存储过程的优点存储过程封装了商务逻辑，确保一致的数据访问和修改。若规则或策略有变化，则只需要修改服务器上的存储过程，所有的客户端就可以直接使用 屏蔽数据库模式的详细资料。用户不需要访问底层的数据库和数据库内的对象 提供了安全性机制。用户可以被赋予执行存储过程的权限，而不必在存储过程引用的所有对象上都有权限 改善性能。预编译的 Transact-SQL 语句，可以根据条件决定执行哪一部分 减少网络通信量。客户端用一条语句调用存储过程，就可以完成可能需要大量语句才能完成的任务，这样减少了客户端和服务器之间的请求/回答包存储过程的初始处理存储过程的初始处理项存入 sysobjects 和 syscomments 表 编译过的执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 放入过程缓存编译优化创建执行 （初次或 重新编译）解析存储过程的后续处理存储过程的后续处理若符合下列条件，则 SQL Server 使用在内存中的计划来执行随后的查询 当前的环境和计划编译时的环境相同。服务器、数据库和连接的设置决定了环境 存储过程引用的对象不需要名称解析。若被不同用户拥有的对象具有相同的名字，则需要名称解析。 一个执行计划产生后，驻留在过程缓存中。仅当需要空间时，SQL Server 将老的、没用的计划移出缓存存储过程的后续处理（续）存储过程的后续处理（续）第13章 实现存储过程第13章 实现存储过程存储过程介绍 创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息创建和管理存储过程创建和管理存储过程创建存储过程 执行存储过程 修改和删除存储过程创建存储过程创建存储过程创建存储过程 只能在当前数据库内创建存储过程，除了临时存储过程。临时存储过程总是创建在 tempdb 数据库中 存储过程可以引用表、视图、用户定义函数、其他存储过程以及临时表 若存储过程创建了局部临时表，则当存储过程执行结束后临时表消失USE Northwind GO CREATE PROC dbo.OverdueOrders AS SELECT * FROM dbo.Orders WHERE RequiredDate < GETDATE() AND ShippedDate IS Null GO创建存储过程（续）创建存储过程（续）创建存储过程（续） CREATE PROCEDURE 定义可以包括任何数目和类型的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 中。创建存储过程（续）创建存储过程（续）查看存储过程的信息 查看所有类型存储过程的额外信息 系统存储过程 sp_help、sp_helptext、sp_depends 显示数据库中的存储过程以及拥有者名字的列表 系统存储过程 sp_stored_procedures 得到存储过程的信息 查询系统表 sysobjects、syscomments、sysdepends创建存储过程的指导原则 创建存储过程的指导原则 避免出现存储过程的拥有者和底层对象的拥有者不同的情况，建议由dbo用户拥有数据库中所有对象 每个存储过程完成单个任务 命名本地存储过程的时候，避免使用“sp_”前缀 尽量少使用临时存储过程，以避免频繁连接 tempdb 里的系统表 不要直接从 syscomments 系统表里删除项执行存储过程执行存储过程单独执行存储过程 不带参数的情况： [[EXEC[UTE] 存储过程名 [ WITH RECOMPILE]在 INSERT 语句内执行存储过程 语法：INSERT INTO 表名 EXEC[UTE] …… 将本地或远程存储过程返回的结果集插入本地表中 在 INSERT 语句内执行的存储过程必须返回关系结果集USE Northwind GO EXEC OverdueOrders GO 修改和删除存储过程修改和删除存储过程修改存储过程 用 ALTER PROCEDURE 中的定义取代现有存储过程原先的定义，但保留权限分配USE Northwind GO ALTER PROC dbo.OverdueOrders AS 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 检查是否有对象依赖于此存储过程第13章 实现存储过程第13章 实现存储过程存储过程介绍 创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息在存储过程中使用参数在存储过程中使用参数使用输入参数 使用输入参数执行存储过程 使用输出参数返回值 显式地重新编译存储过程使用输入参数使用输入参数输入参数允许传递信息到存储过程内 在 CREATE PROCEDURE 中指定 @参数名 数据类型 [=默认值] USE Northwind GO CREATE PROC dbo.OverdueOrders2 @Employee_ID int , @Order_date datetimeAS SELECT CONVERT(char(8), OrderDate, 1) OrderDate, OrderID, 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 = 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,才 可以接收到返回值显式地重新编译存储过程显式地重新编译存储过程存储过程可以显式地重新编译，但应尽量少做，仅当 存储过程所引用的表中的数据发生巨大的变化时 存储过程所引用的对象的架构发生变更时，如增加删除列、规则、约束，或者为底层表增加了存储过程可能从中受益的索引时显式地重新编译存储过程（续）显式地重新编译存储过程（续）三种显式重新编译存储过程的方法 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第13章 实现存储过程第13章 实现存储过程存储过程介绍 创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息存储过程类型存储过程类型SQL Server 支持五种类型的存储过程 系统存储过程（sp_）：存储在 master 数据库内，以“sp_”前缀标识 本地存储过程：本地存储过程在单独的用户数据库内创建 临时存储过程：临时存储过程可能是局部的，名称以“#”开头；也可能是全局的，名称以“##”开头 远程存储过程：远程存储过程是 SQL Server 的一个传统功能，分布式查询支持这项功能 扩展存储过程（xp_）：扩展存储过程以动态链接库（DLL）的形式实现，在 SQL Server 环境外执行执行扩展存储过程执行扩展存储过程扩展存储过程是以动态链接库（DLL）的形式存放C++代码，用于扩展 SQL Server 2000 的功能 大多数扩展存储过程和其他系统存储过程一起执行，很少单独使用 允许用户使用编程语言例如微软的 Visual C++ 等创建自己的外部例程，并创建自己的扩展存储过程 只能被添加到 master 数据库执行扩展存储过程（续）执行扩展存储过程（续）常用的扩展存储过程 例1：取得C盘inetpub的目录清单 EXEC xp_cmdshell 'dir c:\inetpub' 例2：显示autoexec.bat 文件是否在C盘中 DECLARE @ret int EXEC xp_fileexist 'c:\autoexec.bat', @ret output SELECT @ret第13章 实现存储过程第13章 实现存储过程存储过程介绍 创建和管理存储过程 在存储过程中使用参数 系统和扩展存储过程 处理错误信息错误信息处理错误信息处理为了增强存储过程的效率，应使用错误信息向用户传达事务状态（成功或失败） 可以在错误处理逻辑中检查下列错误：返回码、SQL Server 错误、用户定义的错误信息 RETURN 语句 从查询或存储过程无条件返回，同时可以返回一个整数状态值（返回码） 返回码为0表示成功。返回非零表示失败。用户定义的返回值总是优先于系统的返回值。错误信息处理（续）错误信息处理（续）sp_addmessage 系统存储过程 允许开发者创建用户定义的错误信息，指定消息号、严重级别和消息文本，可设定为把错误信息自动 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 到 Windows 2000 应用程序日志中 RAISERROR 语句 返回一个用户定义的错误信息，并设置系统标志记录发生了一个错误 @@error 全局变量 @@error 包含了最近执行的 Transact-SQL 语句的错误号，随着每一条语句的执行而更新 如果语句成功执行，返回0演示1 错误信息处理演示1 错误信息处理例：创建一个存储过程，插入学生信息。 create proc upStudInsert @studName varchar(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 错误信息处理（续）演示1 错误信息处理（续）用下面的语句验证：EXEC upStudInsert ‘Tom’, ‘2007-1-1’, ‘1988-11-1’EXEC upStudInsert ‘Tom’, ‘1981-1-1’, ‘1988-11-1’结果： INSERT 语句与 COLUMN CHECK 约束 ‘CK__tblstuden__birth__6383C8BA’ 冲突。该冲突发生于数据库 ‘student1’，表 ‘tblstudent’, column ‘birthdate’。语句已终止。 fail结果：success演示2 错误信息处理（续）演示2 错误信息处理（续）创建一个自定义的错误信息EXEC SP_ADDMESSAGE 50011, 16, 'Please input again', EnglishCREATE PROC proc1 @a int, @b int, @result int output AS if @a < 0 or @b < 0 begin RAISERROR(50011,16,1) RETURN end set @result = @a + @b GO创建一个返回错误信息的存储过程演示2 错误信息处理（续）演示2 错误信息处理（续）执行这个存储过程DECLARE @result int, @a int , @b int set @a = 1 set @b = -3 EXEC proc1 @a,@b,@result output select 'The result ' = @result演示 3 错误信息处理（续）演示 3 错误信息处理（续）创建一个自定义的错误信息EXEC SP_ADDMESSAGE 50005, 16, 'The current database ID is:%d, the database name is: %s.', englishDECLARE @DBID INT SET @DBID = DB_ID() DECLARE @DBNAME NVARCHAR(128) SET @DBNAME = DB_NAME() RAISERROR (50005, 16, 1, @DBID, @DBNAME)使用该错误信息回顾回顾学习完本章后，将能够： 描述存储过程是如何处理的 创建、执行、修改和删除存储过程 创建接受参数的存储过程 执行扩展存储过程 创建用户定义错误信息