实现存储过程和函数.ppt

《实现存储过程和函数.ppt》由会员分享，可在线阅读，更多相关《实现存储过程和函数.ppt（35页珍藏版）》请在三一办公上搜索。

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章：灾难恢复第12章：管理安全性第13章：监视 SQL Server第14章：维持高可用性第15章：自动执行管理任务第16章：使用 Service Broker第17章：传输数据第18章：复制功能简介,第9章：实现存储过

2、程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验习题,存储过程概述,包含在数据库中执行操作的语句接受输入参数状态值返回给指示成功或失败以输出参数的形式将多个值返回到发起调用的存储过程或客户端应用程序,9.1.1 存储过程概述,创建存储过程的语法,使用CREATE PROCEDURE语句在当前数据库中创建存储过程使用 EXECUTE 运行存储过程,CREATE PROCEDURE Production.LongLeadProductsAS SELECTName,ProductNumber FROMProduction.Product WHEREDaysToMa

3、nufacture=1GO,EXECUTE Production.LongLeadProducts,9.1.2 创建存储过程的语法,限定存储过程所引用的对象名称,创建存储过程的准则,每个任务创建一个存储过程,创建，测试存储过程，并对其进行故障诊断,存储过程名称避免使用 sp_ 前缀,对所有存储过程使用相同的连接设置,尽可能减少临时存储过程的使用,9.1.3 创建存储过程的准则,修改和删除存储过程,修改存储过程删除存储过程,ALTER PROC Production.LongLeadProductsASSELECTName,ProductNumber,DaysToManufactureFROMP

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

5、mLength=MinimumLengthORDER BY DaysToManufacture DESC,Name,EXEC Production.LongLeadProducts MinimumLength=4,9.2.1 输入参数,CREATE PROC HumanResources.AddDepartment Name nvarchar(50),GroupName nvarchar(50),DeptID smallint OUTPUTASINSERT INTO HumanResources.Department(Name,GroupName)VALUES(Name,GroupName)S

6、ET DeptID=SCOPE_IDENTITY(),DECLARE dept intEXEC AddDepartment 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(N

7、ame,GroupName)SET DeptID=SCOPE_IDENTITY()RETURN 0,DECLARE dept int,result intEXEC result=AddDepartment Refunds,dept OUTPUTIF(result=0)SELECT deptELSESELECT Error during insert,9.2.2 输出参数和返回值,课堂练习：创建参数化的存储过程,本次练习的目标是创建一个接受输入参数并将输出参数与成功或失败标志一起返回的存储过程。创建存储过程的语法及准则创建简单存储过程创建接受输入参数的存储过程创建接受输出参数和返回值的存储过程修

8、改和删除存储过程,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验习题,函数类型,标量函数与内置函数相似返回一个值内联表值函数与带有参数的视图相似返回一个表，该表是单个SELECT 语句的结果多语句表值函数与存储过程相似 返回一个新表，该表是INSERT语句的结果,9.3.1 函数类型,标量函数,RETURNS 子句指定数据类型函数在BEGIN END 块中定义允许相同数据类型的标量表达式在任何地方进行调用,CREATE FUNCTION Sales.SumSold(ProductID int)RETURNS int AS BEGINDEC

9、LARE ret intSELECT ret=SUM(OrderQty)FROM Sales.SalesOrderDetail WHERE ProductID=ProductID IF(ret IS NULL)SET ret=0RETURN retEND,SELECT ProductID,Name,Sales.SumSold(ProductID)AS SumSoldFROM Production.Product,9.3.2 标量函数,内联表值函数,RETURNS 指定表为返回的数据类型结果集定义了返回变量的格式 函数的内容是一个SELECT 语句,CREATE FUNCTION HumanRe

10、sources.EmployeesForManager(ManagerId 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 HumanResour

11、ces.EmployeesForManager(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_Employees SELECT Employee

12、ID,LastName FROM HumanResources.vEmployeeELSE IF(format=LONGNAME)INSERT tbl_Employees SELECT EmployeeID,(FirstName+LastName)FROM HumanResources.vEmployeeRETURNEND,SELECT*FROM HumanResources.EmployeeNames(LONGNAME),9.3.4 多语句表值函数,课堂练习：创建函数,本次练习的目标是创建标量函数、内联表值函数以及多语句表值函数。用户定义函数的类型创建标量函数创建内联表值函数创建多语句表值函

13、数,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验习题,结构化异常处理的语法,TRYE CATCH 块提供结构TRY 块包含受保护的事务CATCH 块处理事务,CREATE PROCEDURE dbo.AddData a int,b int ASBEGIN TRYEND TRYBEGIN CATCH END CATCH,CREATE PROCEDURE dbo.AddData a int,b int ASBEGIN TRYINSERT INTO TableWithKey VALUES(a,b)END TRYBEGIN CATCH END

14、CATCH,CREATE PROCEDURE dbo.AddData a int,b int ASBEGIN TRYINSERT INTO TableWithKey VALUES(a,b)END TRYBEGIN CATCH SELECT ERROR_NUMBER()ErrorNumber,ERROR_MESSAGE()MessageEND CATCH,9.4.1 结构化异常处理的语法,BEGIN TRY-INSERT INTO.END TRYSELECT*FROM TableWithKey-NOT ALLOWEDBEGIN CATCH-SELECT ERROR_NUMBER()END CAT

15、CH,BEGIN TRYBEGIN TRANINSERT.INSERT.COMMIT TRANEND TRYBEGIN CATCH ROLLBACK TRANSELECT MESSAGE_NUMBER.END CATCH,SET XACT_ABORT ONBEGIN TRYBEGIN TRAN.COMMIT TRANEND TRYBEGIN CATCH IF(XACT_STATE()=-1-uncommitableROLLBACK TRANELSE IF(XACT_STATE()=1-commitableCOMMIT TRANEND CATCH,BEGIN TRY.END TRYBEGIN C

16、ATCH INSERT INTO ErrorLog VALUES(ERROR_NUMBER(),ERROR_MESSAGE(),GETDATE()END CATCH,处理错误的准则,9.4.2 处理错误的准则,课堂练习：处理错误,本次练习的目标是向存储过程添加错误处理。结构化错误处理的语法在存储过程中处理错误的准则,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验习题,执行上下文,Sales.Order(所有者:John),Ted(无权限),存储过程(所有者:Pat),GetOrders,Ted(EXECUTE 权限),Pat,Pat(SE

17、LECT 权限),CREATE PROC GetOrdersAS SELECT*FROM Sales.Order,CREATE PROC GetOrders WITH EXECUTE AS PatAS SELECT*FROM Sales.Order,9.5.1 执行上下文,EXECUTE AS 选项：,EXECUTE AS 子句,调用者模式,创建或者修改模式,所有者模式,指定用户,CREATE PROCEDURE GetOrdersWITH EXECUTE AS CALLERASSELECT*FROM Sales.Order,CREATE PROCEDURE GetOrdersWITH EXE

18、CUTE AS SELFASSELECT*FROM Sales.Order,CREATE PROCEDURE GetOrdersWITH EXECUTE AS OWNERASSELECT*FROM Sales.Order,CREATE PROCEDURE GetOrdersWITH EXECUTE AS PatASSELECT*FROM Sales.Order,CREATE PROCEDURE GetOrdersWITH EXECUTE AS CALLER|SELF|OWNER|user_name ASSELECT*FROM Sales.Order,9.5.2 EXECUTE AS 子句,扩展

19、模拟上下文的选项,EXECUTE AS 默认限制于当前数据库建立信任关系以扩展到其他数据库,dbo,Mapped dbo,签名验证模式,证书使用者,GRANT AUTHENTICATE,SET TRUSTWORTHY ON,9.5.3 扩展模拟上下文的选项,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验习题,实验,在本实验中，你将按照要求创建多个存储过程和函数，正确设定它们的参数及实现逻辑。创建存储过程和参数化存储过程的语法及使用场合函数的类型创建函数的准则及使用函数的场合结构化异常处理,9.6实验,第9章：实现存储过程和函数,实现存储过

20、程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验习题,习题,1.关于创建存储过程，下列说法中哪个是错误的？A设计每个存储过程以完成单项任务B用相应的架构名称限定存储过程所引用的对象名称C尽可能减少临时存储过程的使用D对所有存储过程使用不同的连接设置,9.7习题,习题（续）,2.关于函数，下列说法中哪个是错误的？A函数是由一条或多条Transact-SQL语句组成的例程B标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的C内联表值函数不能够返回表D多语句表值函数返回由一条或多条Transact-SQL语句构建的表,9.7习题,习题（续）,3.A有一台SQL Serv

21、er 2005计算机。小王的任务是收集当前登录的用户的信息。他要创建一个函数，该函数返回一个特定用户活动时间的数量信息。哪两种可能的方法可以达到这个目的？A创建一个返回一列值的函数，这些值描述给定用户的登录次数B创建一个返回一列值的函数，这些值描述比当前用户登录时间长的用户C创建一个返回数量值的函数，这个值描述用户当天登录的小时数D创建一个返回数量值的函数，这个值描述用户当月登录的小时数,9.7习题,习题（续）,4.关于执行上下文，下列说法中哪个是错误的？A 执行上下文是代码在执行时所使用的标识B 调用模块（如存储过程或函数）的用户或登录名通常决定了执行上下文C 可在存储过程或函数中使用EXE

22、CUTE AS子句来设置在其执行上下文中使用的标识D 可在存储过程或函数中使用CREATE AS子句来设置在其执行上下文中使用的标识,9.7习题,习题（续）,5.小王管理一台SQL Server 2005数据库，这个数据库为客户提供银行信息。小王想让客户看到一个银行报告，这个报告包含由第三方提供的Web服务以及关于现在投资信息的数据库的数据。当报告执行的时候，投资信息必须是最新的。小王需要创建合适的对象来支持报告，他应当怎么做？A通过使用FOR XML AUTO子句把数据库中的数据发布成一个XML Web 服务B为每个客户创建一个存储银行信息的表格，创建一个触发器使得当数据被插入到连接来源We

23、b服务的数据的表格时触发C创建一个Transact-SQL存储过程，使用临时表格为每个客户存储银行信息，用来自Web 服务的值更新表格D让A的一开发人员创建一个调用远程Web 服务的程序集，通过使用程序集创建一个CLR函数，调用CLR函数并联合数据库中的银行信息的结果,9.7习题,习题（续）,6.小王正在为一家抵押公司创建一个SQL Server 2005 数据库，这个数据库将支持一个供1 000个用户同时操作的基于Web的应用程序，这个应用程序必须快速地显示精确计算的结果，例如计算抵押付款和分期付款的时间。小王需要确保数据库计算进程尽可能地快速和有效，他应当怎么做？A在应用程序中执行参数化的Transact-SQL查询B在数据库中执行Transact-SQL存储过程C在数据库中执行CLR存储过程D执行分布式的Web 服务,9.7习题,习题（续）,7.什么时候使用内嵌表值函数来代替视图比较好？,9.7习题,