实现存储过程和函数.ppt

第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章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验 习题,存储过程概述,包含在数据库中执行操作的语句 接受输入参数 状态值返回给指示成功或失败 以输出参数的形式将多个值返回到发起调用的存储过程或客户端应用程序,9.1.1 存储过程概述,创建存储过程的语法,使用CREATE PROCEDURE语句在当前数据库中创建存储过程 使用 EXECUTE 运行存储过程,CREATE PROCEDURE Production.LongLeadProducts AS SELECT Name, ProductNumber FROM Production.Product WHERE DaysToManufacture = 1 GO,EXECUTE Production.LongLeadProducts,9.1.2 创建存储过程的语法,限定存储过程所引用的对象名称,创建存储过程的准则,每个任务创建一个存储过程,创建，测试存储过程，并对其进行故障诊断,存储过程名称避免使用 sp_ 前缀,对所有存储过程使用相同的连接设置,尽可能减少临时存储过程的使用,9.1.3 创建存储过程的准则,修改和删除存储过程,修改存储过程 删除存储过程,ALTER PROC Production.LongLeadProducts AS SELECT Name, ProductNumber, DaysToManufacture FROM Production.Product WHERE DaysToManufacture = 1 ORDER BY DaysToManufacture DESC, Name GO,DROP PROC Production.LongLeadProducts,9.1.4 修改和删除存储过程,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验 习题,输入参数,提供合适的默认值 验证输入的参数值，包括空值检查,ALTER PROC Production.LongLeadProducts MinimumLength int = 1 - default value AS IF (MinimumLength = MinimumLength ORDER BY DaysToManufacture DESC, Name,EXEC Production.LongLeadProducts MinimumLength=4,9.2.1 输入参数,CREATE PROC HumanResources.AddDepartment Name nvarchar(50), GroupName nvarchar(50), DeptID smallint OUTPUT AS INSERT INTO HumanResources.Department (Name, GroupName) VALUES (Name, GroupName) SET DeptID = SCOPE_IDENTITY(),DECLARE dept int EXEC AddDepartment Refunds, , dept OUTPUT SELECT dept,输出参数和返回值,CREATE PROC HumanResources.AddDepartment Name nvarchar(50), GroupName nvarchar(50), DeptID smallint OUTPUT AS IF (Name = ) OR (GroupName = ) RETURN -1 INSERT INTO HumanResources.Department (Name, GroupName) VALUES (Name, GroupName) SET DeptID = SCOPE_IDENTITY() RETURN 0,DECLARE dept int, result int EXEC result = AddDepartment Refunds, , dept OUTPUT IF (result = 0) SELECT dept ELSE SELECT Error during insert,9.2.2 输出参数和返回值,课堂练习：创建参数化的存储过程,本次练习的目标是创建一个接受输入参数并将输出参数与成功或失败标志一起返回的存储过程。 创建存储过程的语法及准则 创建简单存储过程 创建接受输入参数的存储过程 创建接受输出参数和返回值的存储过程 修改和删除存储过程,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验 习题,函数类型,标量函数 与内置函数相似 返回一个值 内联表值函数 与带有参数的视图相似 返回一个表，该表是单个SELECT 语句的结果 多语句表值函数 与存储过程相似 返回一个新表，该表是INSERT语句的结果,9.3.1 函数类型,标量函数,RETURNS 子句指定数据类型 函数在BEGIN END 块中定义 允许相同数据类型的标量表达式在任何地方进行调用,CREATE FUNCTION Sales.SumSold(ProductID int) RETURNS int AS BEGIN DECLARE ret int SELECT ret = SUM(OrderQty) FROM Sales.SalesOrderDetail WHERE ProductID = ProductID IF (ret IS NULL) SET ret = 0 RETURN ret END,SELECT ProductID, Name, Sales.SumSold(ProductID) AS SumSold FROM Production.Product,9.3.2 标量函数,内联表值函数,RETURNS 指定表为返回的数据类型 结果集定义了返回变量的格式 函数的内容是一个SELECT 语句,CREATE FUNCTION HumanResources.EmployeesForManager (ManagerId int) RETURNS TABLE AS RETURN ( SELECT FirstName, LastName FROM HumanResources.Employee Employee INNER JOIN Person.Contact Contact ON Employee.ContactID = Contact.ContactID WHERE ManagerID = ManagerId ),SELECT * FROM HumanResources.EmployeesForManager(3) - OR SELECT * FROM HumanResources.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) AS BEGIN IF (format = SHORTNAME) INSERT tbl_Employees SELECT EmployeeID, LastName FROM HumanResources.vEmployee ELSE IF (format = LONGNAME) INSERT tbl_Employees SELECT EmployeeID, (FirstName + + LastName) FROM HumanResources.vEmployee RETURN END,SELECT * FROM HumanResources.EmployeeNames(LONGNAME),9.3.4 多语句表值函数,课堂练习：创建函数,本次练习的目标是创建标量函数、内联表值函数以及多语句表值函数。 用户定义函数的类型 创建标量函数 创建内联表值函数 创建多语句表值函数,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验 习题,结构化异常处理的语法,TRYE CATCH 块提供结构 TRY 块包含受保护的事务 CATCH 块处理事务,CREATE PROCEDURE dbo.AddData a int, b int AS BEGIN TRY END TRY BEGIN CATCH END CATCH,CREATE PROCEDURE dbo.AddData a int, b int AS BEGIN TRY INSERT INTO TableWithKey VALUES (a, b) END TRY BEGIN CATCH END CATCH,CREATE PROCEDURE dbo.AddData a int, b int AS BEGIN TRY INSERT INTO TableWithKey VALUES (a, b) END TRY BEGIN CATCH SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() Message END CATCH,9.4.1 结构化异常处理的语法,BEGIN TRY - INSERT INTO . END TRY SELECT * FROM TableWithKey - NOT ALLOWED BEGIN CATCH - SELECT ERROR_NUMBER() END CATCH,BEGIN TRY BEGIN TRAN INSERT . INSERT . COMMIT TRAN END TRY BEGIN CATCH ROLLBACK TRAN SELECT MESSAGE_NUMBER. END CATCH,SET XACT_ABORT ON BEGIN TRY BEGIN TRAN . COMMIT TRAN END TRY BEGIN CATCH IF (XACT_STATE() = -1 - uncommitable ROLLBACK TRAN ELSE IF (XACT_STATE() = 1 - commitable COMMIT TRAN END CATCH,BEGIN TRY . END TRY BEGIN CATCH 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 (SELECT 权限),CREATE PROC GetOrders AS SELECT * FROM Sales.Order,CREATE PROC GetOrders WITH EXECUTE AS Pat AS SELECT * FROM Sales.Order,9.5.1 执行上下文,EXECUTE AS 选项：,EXECUTE AS 子句,调用者模式,创建或者修改模式,所有者模式,指定用户,CREATE PROCEDURE GetOrders WITH EXECUTE AS CALLER AS SELECT * FROM Sales.Order,CREATE PROCEDURE GetOrders WITH EXECUTE AS SELF AS SELECT * FROM Sales.Order,CREATE PROCEDURE GetOrders WITH EXECUTE AS OWNER AS SELECT * FROM Sales.Order,CREATE PROCEDURE GetOrders WITH EXECUTE AS Pat AS SELECT * FROM Sales.Order,CREATE PROCEDURE GetOrders WITH EXECUTE AS CALLER | SELF | OWNER | user_name AS SELECT * FROM Sales.Order,9.5.2 EXECUTE AS 子句,扩展模拟上下文的选项,EXECUTE AS 默认限制于当前数据库 建立信任关系以扩展到其他数据库,dbo,Mapped dbo,签名验证模式,证书使用者,GRANT AUTHENTICATE ,SET TRUSTWORTHY ON,9.5.3 扩展模拟上下文的选项,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验 习题,实验,在本实验中，你将按照要求创建多个存储过程和函数，正确设定它们的参数及实现逻辑。 创建存储过程和参数化存储过程的语法及使用场合 函数的类型 创建函数的准则及使用函数的场合 结构化异常处理,9.6 实验,第9章：实现存储过程和函数,实现存储过程 创建参数化存储过程 创建函数 处理错误 控制执行上下文 实验 习题,习题,1. 关于创建存储过程，下列说法中哪个是错误的？ A设计每个存储过程以完成单项任务 B用相应的架构名称限定存储过程所引用的对象名称 C尽可能减少临时存储过程的使用 D对所有存储过程使用不同的连接设置,9.7 习题,习题（续）,2. 关于函数，下列说法中哪个是错误的？ A函数是由一条或多条Transact-SQL语句组成的例程 B标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的 C内联表值函数不能够返回表 D多语句表值函数返回由一条或多条Transact-SQL语 句构建的表,9.7 习题,习题（续）,3. A有一台SQL Server 2005计算机。小王的任务是收集当前登录的用户的信息。他要创建一个函数，该函数返回一个特定用户活动时间的数量信息。哪两种可能的方法可以达到这个目的？ A创建一个返回一列值的函数，这些值描述给定用户的登录次数 B创建一个返回一列值的函数，这些值描述比当前用户登录时间长的用户 C创建一个返回数量值的函数，这个值描述用户当天登录的小时数 D创建一个返回数量值的函数，这个值描述用户当月登录的小时数,9.7 习题,习题（续）,4. 关于执行上下文，下列说法中哪个是错误的？ A 执行上下文是代码在执行时所使用的标识 B 调用模块（如存储过程或函数）的用户或登录名通常决定了执行上下文 C 可在存储过程或函数中使用EXEC