实现存储过程和函数

1、第第9章章实现存储过程和函数实现存储过程和函数SQL Server 2005数据库开发与实现数据库开发与实现第第1章：章：SQL Server 2005启航启航第第2章：安装和配置章：安装和配置SQL Server 2005第第3章：创建数据库和数据章：创建数据库和数据库文件库文件第第4章：创建数据类型和表章：创建数据类型和表第第5章：使用章：使用Transact-SQL查查询数据库询数据库第第6章：创建和优化索引章：创建和优化索引第第7章：实现数据完整性章：实现数据完整性第第8章：实现视图章：实现视图第第9章：实现存储过程和函章：实现存储过程和函数数第第10章：使用章：使用 XML第第11章

2、：灾难恢复章：灾难恢复第第12章：管理安全性章：管理安全性第第13章：监视章：监视 SQL Server第第14章：维持高可用性章：维持高可用性第第15章：自动执行管理任务章：自动执行管理任务第第16章：使用章：使用 Service Broker第第17章：传输数据章：传输数据第第18章：复制功能简介章：复制功能简介第第9章：实现存储过程和函数章：实现存储过程和函数实现存储过程实现存储过程 创建参数化存储过程创建参数化存储过程 创建函数创建函数 处理错误处理错误 控制执行上下文控制执行上下文 实验实验习题习题存储过程存储过程概述概述 包含在数据库中执行操作的语句包含在数据库中执行操作的语句接受

3、输入参数接受输入参数状态值返回给指示成功或失败状态值返回给指示成功或失败以输出参数的形式将多个值返回到发起调用的存以输出参数的形式将多个值返回到发起调用的存储过程或客户端应用程序储过程或客户端应用程序 9.1.1 存储过程存储过程概述概述创建存储过程的语法创建存储过程的语法使用使用CREATE PROCEDURE语句在当前数据库中创建语句在当前数据库中创建存储过程存储过程使用使用 EXECUTE 运行存储过程运行存储过程CREATE PROCEDURE Production.LongLeadProductsAS SELECTName, ProductNumber FROMProduction.

4、Product WHEREDaysToManufacture = 1GOEXECUTE Production.LongLeadProducts9.1.2 创建存储过程的语法创建存储过程的语法限定存储过程所引用的对象名称限定存储过程所引用的对象名称 创建存储过程的准则创建存储过程的准则 每个任务创建一个存储过程每个任务创建一个存储过程 创建，测试存储过程，并对其进行故障诊断创建，测试存储过程，并对其进行故障诊断 存储过程名称避免使用存储过程名称避免使用 sp_ 前缀前缀 对所有存储过程使用相同的连接设置对所有存储过程使用相同的连接设置 尽可能减少临时存储过程的使用尽可能减少临时存储过程的使用 9

5、.1.3 创建存储过程的准则创建存储过程的准则修改和删除存储过程修改和删除存储过程 修改存储过程修改存储过程删除存储过程删除存储过程ALTER PROC Production.LongLeadProductsASSELECT Name, ProductNumber, DaysToManufactureFROMProduction.ProductWHEREDaysToManufacture = 1ORDER BY DaysToManufacture DESC, NameGODROP PROC Production.LongLeadProducts9.1.4 修改和删除存储过程修改和删除存储过程

6、第第9章：实现存储过程和函数章：实现存储过程和函数实现存储过程实现存储过程 创建参数化存储过程创建参数化存储过程 创建函数创建函数 处理错误处理错误 控制执行上下文控制执行上下文 实验实验习题习题输入参数输入参数提供合适的默认值提供合适的默认值验证输入的参数值，包括空值检查验证输入的参数值，包括空值检查ALTER PROC Production.LongLeadProducts MinimumLength int = 1 - default valueASIF (MinimumLength = MinimumLengthORDER BY DaysToManufacture DESC, Name

7、EXEC Production.LongLeadProducts MinimumLength=49.2.1 输入参数输入参数CREATE PROC HumanResources.AddDepartment Name nvarchar(50), GroupName nvarchar(50), DeptID smallint OUTPUTASINSERT INTO HumanResources.Department (Name, GroupName)VALUES(Name, GroupName)SET DeptID = SCOPE_IDENTITY()DECLARE dept intEXEC Ad

8、dDepartment Refunds, , dept OUTPUTSELECT dept输出参数和返回值输出参数和返回值CREATE PROC HumanResources.AddDepartment Name nvarchar(50), GroupName nvarchar(50), DeptID smallint OUTPUTASIF (Name = ) OR (GroupName = ) RETURN -1INSERT INTO HumanResources.Department (Name, GroupName)VALUES(Name, GroupName)SET DeptID =

9、SCOPE_IDENTITY()RETURN 0DECLARE dept int, result intEXEC result = AddDepartment Refunds, , dept OUTPUTIF (result = 0)SELECT deptELSESELECT Error during insert9.2.2 输出参数和返回值输出参数和返回值课堂练习：创建参数化的存储过程课堂练习：创建参数化的存储过程 本次练习的目标是创建一个接受输入参本次练习的目标是创建一个接受输入参数并将输出参数与成功或失败标志一起数并将输出参数与成功或失败标志一起返回的存储过程。返回的存储过程。 创建存储

10、过程的语法及准则创建存储过程的语法及准则创建简单存储过程创建简单存储过程创建接受输入参数的存储过程创建接受输入参数的存储过程创建接受输出参数和返回值的存储过程创建接受输出参数和返回值的存储过程修改和删除存储过程修改和删除存储过程 第第9章：实现存储过程和函数章：实现存储过程和函数实现存储过程实现存储过程 创建参数化存储过程创建参数化存储过程 创建函数创建函数 处理错误处理错误 控制执行上下文控制执行上下文 实验实验习题习题函数类型函数类型标量函数标量函数 与内置函数相似 返回一个值内联表值函数内联表值函数 与带有参数的视图相似 返回一个表，该表是单个SELECT 语句的结果多语句表值函数多语句

11、表值函数 与存储过程相似 返回一个新表，该表是INSERT语句的结果9.3.1 函数类型函数类型标量函数标量函数RETURNS 子句指定数据类型子句指定数据类型函数在函数在BEGIN END 块中定义块中定义允许相同数据类型的标量表达式在任何地方进行调用允许相同数据类型的标量表达式在任何地方进行调用 CREATE FUNCTION Sales.SumSold(ProductID int) RETURNS int AS BEGINDECLARE ret intSELECT ret = SUM(OrderQty) FROM Sales.SalesOrderDetail WHERE ProductI

12、D = ProductID IF (ret IS NULL) SET ret = 0RETURN retENDSELECT ProductID, Name, Sales.SumSold(ProductID) AS SumSoldFROM Production.Product 9.3.2 标量函数标量函数内联表值函数内联表值函数RETURNS 指定表为返回的数据类型指定表为返回的数据类型结果集定义了返回变量的格式结果集定义了返回变量的格式 函数的内容是一个函数的内容是一个SELECT 语句语句CREATE FUNCTION HumanResources.EmployeesForManager(M

13、anagerId int) RETURNS TABLEASRETURN (SELECTFirstName, LastNameFROM HumanResources.Employee Employee INNER JOINPerson.Contact Contact ON Employee.ContactID = Contact.ContactIDWHERE ManagerID = ManagerId )SELECT * FROM HumanResources.EmployeesForManager(3)- ORSELECT * FROM HumanResources.EmployeesForM

14、anager(6)9.3.3 内联表值函数内联表值函数多语句表值函数多语句表值函数RETURNS 指定了表为返回值类型并定义了结构指定了表为返回值类型并定义了结构BEGIN END 块界定了函数的主体块界定了函数的主体 CREATE FUNCTION HumanResources.EmployeeNames (format nvarchar(9)RETURNS tbl_Employees TABLE(EmployeeID int PRIMARY KEY, Employee Name nvarchar(100)ASBEGINIF (format = SHORTNAME)INSERT tbl_Em

15、ployees SELECT EmployeeID, LastName FROM HumanResources.vEmployeeELSE IF (format = LONGNAME)INSERT tbl_Employees SELECT EmployeeID, (FirstName + + LastName) FROM HumanResources.vEmployeeRETURNENDSELECT * FROM HumanResources.EmployeeNames(LONGNAME)9.3.4 多语句表值函数多语句表值函数课堂练习：创建函数课堂练习：创建函数 本次练习的目标是创建标量函数

16、、内联本次练习的目标是创建标量函数、内联表值函数以及多语句表值函数。表值函数以及多语句表值函数。用户定义函数的类型用户定义函数的类型创建标量函数创建标量函数创建内联表值函数创建内联表值函数创建多语句表值函数创建多语句表值函数第第9章：实现存储过程和函数章：实现存储过程和函数实现存储过程实现存储过程 创建参数化存储过程创建参数化存储过程 创建函数创建函数 处理错误处理错误 控制执行上下文控制执行上下文 实验实验习题习题结构化异常处理的语法结构化异常处理的语法 TRY CATCH 块提供结构块提供结构 TRY 块包含受保护的事务 CATCH 块处理事务CREATE PROCEDURE dbo.Ad

17、dData a int, b int ASBEGIN TRYEND TRYBEGIN CATCH END CATCHCREATE PROCEDURE dbo.AddData a int, b int ASBEGIN TRYINSERT INTO TableWithKey VALUES (a, b)END TRYBEGIN CATCH END CATCHCREATE PROCEDURE dbo.AddData a int, b int ASBEGIN TRYINSERT INTO TableWithKey VALUES (a, b)END TRYBEGIN CATCH SELECT ERROR_

18、NUMBER() ErrorNumber, ERROR_MESSAGE() MessageEND CATCH9.4.1 结构化异常处理的语法结构化异常处理的语法 BEGIN TRY- INSERT INTO .END TRYSELECT * FROM TableWithKey - NOT ALLOWEDBEGIN CATCH - SELECT ERROR_NUMBER()END CATCHBEGIN TRYBEGIN TRANINSERT .INSERT .COMMIT TRANEND TRYBEGIN CATCH ROLLBACK TRANSELECT MESSAGE_NUMBER.END

19、CATCHSET XACT_ABORT ONBEGIN TRYBEGIN TRAN.COMMIT TRANEND TRYBEGIN CATCH IF (XACT_STATE() = -1 - uncommitableROLLBACK TRANELSE IF (XACT_STATE() = 1- commitableCOMMIT TRANEND CATCHBEGIN TRY.END TRYBEGIN CATCH INSERT INTO ErrorLog VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), GETDATE()END CATCHCreate CATCH

20、block immediately after TRY Roll back failed transactions in CATCH Consider using XACT_ABORT ON and XACT_STATE Capture ERROR_xxx information if required 处理错误的准则处理错误的准则 在在TRY后立即创建后立即创建CATCH块块 在在CATCH块中回滚错误事务块中回滚错误事务 使用使用XACT_ABORT ON 和和XACT_STATE 如果需要捕获错误信息如果需要捕获错误信息 9.4.2 处理错误的准则处理错误的准则课堂练习：处理错误课堂练习

21、：处理错误本次练习的目标是向存储过程添加错误本次练习的目标是向存储过程添加错误处理。处理。结构化错误处理的语法结构化错误处理的语法在存储过程中处理错误的准则在存储过程中处理错误的准则第第9章：实现存储过程和函数章：实现存储过程和函数实现存储过程实现存储过程 创建参数化存储过程创建参数化存储过程 创建函数创建函数 处理错误处理错误 控制执行上下文控制执行上下文 实验实验习题习题实验实验在本实验中，你将按照要求创建多个存在本实验中，你将按照要求创建多个存储过程和函数，正确设定它们的参数及储过程和函数，正确设定它们的参数及实现逻辑。实现逻辑。创建存储过程和参数化存储过程的语法及使用场创建存储过程和参

22、数化存储过程的语法及使用场合合函数的类型函数的类型创建函数的准则及使用函数的场合创建函数的准则及使用函数的场合结构化异常处理结构化异常处理9.6实验实验第第9章：实现存储过程和函数章：实现存储过程和函数实现存储过程实现存储过程 创建参数化存储过程创建参数化存储过程 创建函数创建函数 处理错误处理错误 控制执行上下文控制执行上下文 实验实验习题习题习题习题1. 关于创建存储过程，下列说法中哪个是错误的？关于创建存储过程，下列说法中哪个是错误的？A设计每个存储过程以完成单项任务设计每个存储过程以完成单项任务B用相应的架构名称限定存储过程所引用的对象名用相应的架构名称限定存储过程所引用的对象名称称C

23、尽可能减少临时存储过程的使用尽可能减少临时存储过程的使用D对所有存储过程使用不同的连接设置对所有存储过程使用不同的连接设置9.7习题习题习题（续）习题（续）2. 关于函数，下列说法中哪个是错误的？关于函数，下列说法中哪个是错误的？A函数是由一条或多条函数是由一条或多条Transact-SQL语句组成的例程语句组成的例程B标量函数返回单个数据值，并且其类型是在标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的子句中定义的C内联表值函数不能够返回表内联表值函数不能够返回表D多语句表值函数返回由一条或多条多语句表值函数返回由一条或多条Transact-SQL语语句构建的表句构建的表 9

24、.7习题习题习题（续）习题（续）3. A有一台有一台SQL Server 2005计算机。计算机。小王的任务是收集当前登录的用户的信息。他要创小王的任务是收集当前登录的用户的信息。他要创建一个函数，该函数返回一个特定用户活动时间的建一个函数，该函数返回一个特定用户活动时间的数量信息。哪两种可能的方法可以达到这个目的？数量信息。哪两种可能的方法可以达到这个目的？A创建一个返回一列值的函数，这些值描述给定用创建一个返回一列值的函数，这些值描述给定用户的登录次数户的登录次数B创建一个返回一列值的函数，这些值描述比当前创建一个返回一列值的函数，这些值描述比当前用户登录时间长的用户用户登录时间长的用户C

25、创建一个返回数量值的函数，这个值描述用户当创建一个返回数量值的函数，这个值描述用户当天登录的小时数天登录的小时数D创建一个返回数量值的函数，这个值描述用户当创建一个返回数量值的函数，这个值描述用户当月登录的小时数月登录的小时数9.7习题习题习题（续）习题（续）4. 关于执行上下文，下列说法中哪个是错误的？关于执行上下文，下列说法中哪个是错误的？A 执行上下文是代码在执行时所使用的标识执行上下文是代码在执行时所使用的标识B 调用模块（如存储过程或函数）的用户或登录名调用模块（如存储过程或函数）的用户或登录名通常决定了执行上下文通常决定了执行上下文C 可在存储过程或函数中使用可在存储过程或函数中使

26、用EXECUTE AS子句来子句来设置在其执行上下文中使用的标识设置在其执行上下文中使用的标识D 可在存储过程或函数中使用可在存储过程或函数中使用CREATE AS子句来设子句来设置在其执行上下文中使用的标识置在其执行上下文中使用的标识9.7习题习题习题（续）习题（续）5. 小王管理一台小王管理一台SQL Server 2005数据库，这个数据库为客户提供数据库，这个数据库为客户提供银行信息。小王想让客户看到一个银行报告，这个报告包含由银行信息。小王想让客户看到一个银行报告，这个报告包含由第三方提供的第三方提供的Web服务以及关于现在投资信息的数据库的数据服务以及关于现在投资信息的数据库的数据。当报告执行的时候，投资信息必须是最新的。小王需要创建。当报告执行的时候，投资信息必须是最新的。小王需要创建合适的对象来支持报