SQL Server 创建函数、存储过程和触发器

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