《SQL Server 创建函数、存储过程和触发器课件》由会员分享，可在线阅读，更多相关《SQL Server 创建函数、存储过程和触发器课件（18页珍藏版）》请在金锄头文库上搜索。
1、第 9 章创建函数、存储过程和触发器,函数概述 标量函数和表值函数 确定性函数和非确定性函数 存储过程概述 创建存储过程 执行存储过程 查看、重命名和删除存储过程 触发器概述 DML触发器的创建和应用 DDL触发器的创建和应用 查看、修改和删除触发器,函数概述：,提问： 有没有这样一种方法，它既能像存储过程那样封装一些复杂的T-SQL 代码，并根据需要设置参数，同时又能返回程序所需的值呢？,在SQL SERVER 2005 中，函数是用于封装频繁执行的逻辑的例程。任何必须执行的代码都可调用函数，而无需重复所有的函数逻辑。,知识点： 学完本节后，你应能够了解： 不同类型的函数； 标量函数的工作方
2、式； 表值函数的工作方式（内联表值函数和多语句表值函数）； 确定性和非确定性函数。,函数： 函数是由一条或多条T-SQL 语句组成的例程，可用于封装代码以便进行重用。函数接受零个或多个输入参数，并返回标量值或表。函数不支持输出参数。,函数类型：,标量函数： 标量函数返回单个数据值，并且其类型是在RETURNS子句中定义的。,内联表值函数： 内联表值函数返回一个表，该表是单个SELECT 语句的结果。它类似与视图，但是比视图提供了更多的灵活性，因为可向函数提供参数。,多语句表值函数： 多语句表值函数返回由一条或多条T-SQL 语句构建的表并且类似于存储过程。,函数类型（续）：,与存储过程不同的是
3、，多语句表值函数可以在 SELECT 语句的FROM 子句中进行引用，就像它是视图或表一样。,标量函数和表值函数：,标量函数：,创建标量函数： RETURNS 子句指定数据类型 函数在BEGIN END 块中定义,举例： 用代码创建一个标量函数，它累加了AdventureWorks数据库中某件特定产品的所有销售量，并将总计作为int 型数据返回。,- 创建标量函数 Sales.SumSold ，并接受一个int 型的输入参数ProductID ，一个int型的返回值。,- 该标量函数返回指定产品的总销售量。,CREATE FUNCTION Sales.SumSold(ProductID int
4、) RETURNS int AS BEGIN - 声明变量ret ，以存储指定产品的总销售量 DECLARE ret int SELECT ret = SUM(OrderQty) FROM Sales.SalesOrderDetail WHERE ProductID = ProductID - 如果ret为空，则重新赋值0给ret - 如果ret不为空，则返回变量值 IF (ret IS NULL) SET ret = 0 RETURN ret END,注意： 在修改和删除函数时，可使用类似于修改和删除其他数据库对象的语法。在创建函数之后，用ALTER FUNCTION 来修改你的函数，使用D
5、ROP FUNCTION 从数据库中删除函数。,调用标量函数： 对于返回标量值的用户定义函数，允许相同数据类型的标量表达式在任何地方进行调用 。,该语句的基本语法中的选项： ENCRYPTION选项，SQL SERVER 在存储函数定义时，对其进行加密； SCHEMABINDING选项，防止该函数所依赖的任何对象被删除； EXECUTE AS 选项，指定函数的安全上下文。 当你需要依赖访问对象，但又不希望依赖与为断裂的所有权链时使用它实现安全性。,表定义中：CHECK约束。函数只能引用同一表中的列。 T-SQL 语句中：CASE 表达式中； PRINT 语句中（只适用于字符串函数）； 作为存储
6、过程的RETURN语句（只适用于返回整数的标量函数）。 函数和存储过程中：作为用户定义函数的RETURN 子句，前提是被调用的用户函数返回的值可隐式转换为进行调用的函数的返回数据类型。,可使用标量函数的位置： 区域： 查询中：作为SELECT 语句中的select_list的expression； 作为WHERE 或 HAVING子句中的expression； 作为 UPDATE 语句中SET子句中的expression。,SELECT ProductID, Name, Sales.SumSold(ProductID) AS SumSold FROM Production.Product,程序
7、清单如下：,举例： 用代码执行一个SELECT语句，该语句检索AdventureWorks数据库中的每条产品记录的ProductID, Name及SumSold标量函数的结果。,表值函数： 表值函数遵守与标量函数相同的规则，区别在于表值函数返回一个表作为输出。 因此，一般在SELECT 语句的FROM 子句中进行引用，并可能与其他表或视图进行联接。,内联表值函数： 可使用内联函数实现参数化视图的功能。 视图的一个局限性是在创建视图时，不允许在视图中包含用户提供的参数。通常可在调用视图时提供的WHERE 子句来解决此问题。 需要构建动态的执行字符串，由此增加应用程序的复杂性。使用内联表值函数可实
8、现参数化视图的功能。,内联表值的特点： RETURNS 指定表为返回的数据类型； SELECT语句的结果集定义了返回变量的格式 ； 函数的内容是一个单条的 SELECT 语句，内联函数使用的SELECT语句与视图中使用的SELECT 语句，受到相同的限制； 该函数的主体不需要包含在BEGINEND块中。,程序清单如下：,举例： 用代码创建一个内联表值函数，它返回AdventureWorks数据库中的某个特定经理的下属雇员的姓名。,- 创建一个内联表值函数HumanResources.EmployeesForManager，接受一个表示经理ID的输入参数； - 返回指定经理的下属雇员的姓名 CR
9、EATE FUNCTION HumanResources.EmployeesForManager (ManagerId int) RETURNS TABLE AS RETURN ( SELECTFirstName, LastName FROM HumanResources.Employee Employee INNER JOIN Person.Contact Contact ON Employee.ContactID = Contact.ContactID WHERE ManagerID = ManagerId ),程序清单如下：,调用内联表值函数： 可在通常使用视图的任何地方使用内联表值函数
10、。,举例： 下列的代码检索两位经理管理所有雇员的的姓名。,SELECT * FROM HumanResources.EmployeesForManager(3) - OR SELECT * FROM HumanResources.EmployeesForManager(6),多语句表值函数：,场景： 在某个企业的人事管理系统中，有个“每月回顾”模块以供企业各部门经理查看前一个月各部门全部的总体情况；总裁查看全部的总体情况。其中内容包括：每个员工的请假、加班、考勤、业绩和薪资情况。 模块要求根据访问身份判断在屏幕上显示不同的信息。若是员工访问，显示该员工的个人信息；部门经理查看，显示该部门全部员
11、工情况；企业总裁登录，则汇总各个部门的情况按部门显示出来。该模块需要信息可以从viewEmpInfo视图截取。 作为一个开发人员，你将如何来做？ 用表值函数实现是最佳的方法。,使用多语句表值函数的场合： 多语句表值函数是视图和存储过程的结合。可使用返回表的用户定义函数来替代存储过程或视图。,表值函数类似于存储过程，可使用复杂的逻辑和多条T-SQL语句来构建表。,多语句表值函数具有以下特点： RETURNS 指定了表为返回值类型，并定义了结构（名称和格式）； BEGIN END 块界定了函数的主体,- 创建多语句表值函数HumanResources.EmployeeNames，接受一个判断 表返
12、回数据的输入参数， - 从HumanResources.vEmployee 视图中截取相应数据并返回。 CREATE FUNCTION HumanResources.EmployeeNames (format nvarchar(9) - 指定返回类型的为表，并定义表的名称和格式 RETURNS tbl_Employees TABLE (EmployeeID int PRIMARY KEY, Employee Name nvarchar(100) AS,举例： 下列的代码创建含有两列、名为 tbl_Employees 的表变量。第二列根据所请求的 format 参数而变化。,BEGIN - 如果
13、接受的参数值为”SHORTNAME“，则将视图中的员工ID和姓名插 入表，并返回 IF (format = SHORTNAME) BEGIN INSERT tbl_Employees SELECT EmployeeID, LastName FROM HumanResources.vEmployee END - 如果接受的参数值为”LONGNAME“，则将视图中的员工ID和员工完 整姓名插入表，并返回 ELSE IF (format = LONGNAME) BEGIN INSERT tbl_Employees SELECT EmployeeID, (FirstName + + LastName)
14、 FROM HumanResources.vEmployee RETURN END,调用多语句表值函数： 可在FROM子句中调用该函数，而不是使用表或视图。,举例： 下列的代码同时以长格式和短格式检索雇员姓名。,SELECT * FROM HumanResources.EmployeeNames(LONGNAME) -OR SELECT * FROM HumanResources.EmployeeNames(SHORTNAME),确定性与非确定性函数：,使用函数时，重要的是我们要确定函数是确定的还是非确定的。对于相同的输入值集合，每次调用确定性函数则返回相同的值。 Eg :SQL SERVER
15、 中内置函数COS 就是确定性函数的一个实例，该函数返回三角余旋值。,每次调用非确定性函数，就会返回不同的值。 Eg: SQL SERVER内置函数GETDATE（）就是一个非确定性函数，它返回当前的时间和日期。,注意： 如果一个函数调用一个非确定性函数，或者调用一个扩展的存储过程，则SQL SERVER 也会认为该函数是非确定性的。 一个函数是非确定性的还是确定性的，决定了是否在该函数返回的结果集上建立索引，以及能否在引用该函数的视图上定义聚集索引。 如果一个函数是非确定的，就不能索引该函数的结果。,练习：,创建函数,本次练习的目标是创建标量函数、内联表值函数以及多语句表值函数。 用户定义函数的类型 创建标量函数 创建内联表值函数 创建多语句表值函数,本节讲的两种UDF是什么，如何使用？ 答：标量函数返回一个单独的值，通常在列表和WHERE子句中使用； 表值函数返回一个变量，并在FROM子句使用。,
