Copyright200Copyright2008 81第14章 实现用户定义函数 用户定义函数 用户定义函数的定义与使用 架构绑定函数的创建 推荐操作Copyright200Copyright2008 82用户定义函数 SQL Server 2000 允许用户设计自己的函数，以补充和扩 展系统提供（内置）函数的功能 用户定义函数采用零或多个输入参数并返回标量值或 表 SQL Server 2000 支持三种用户定义函数：标量函数、多语句表值函数、内嵌表值函数 标量函数 标量函数返回一个标量（单值）结果 返回值可为：Timestamp、Text、Ntext、Image、 Table和Cursor之外的所有数据类型，但不能为用户自 定义数据类型。16.1 用户定义函数Copyright200Copyright2008 83用户定义函数（续） 多语句表值函数 返回一个由一条或多条 Transact-SQL 语句建立的表 ，类似于存储过程 与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用，仿佛视图一 样 内嵌表值函数 返回一个表，该表是由一个位于RETURN子句中的 SELECT命令段从数据库中筛选出来的，类似于视图 相对于视图，内嵌表值函数可使用参数，提供了更强 的适应性，扩展了索引视图的功能16.1 用户定义函数Copyright200Copyright2008 84第14章 实现用户定义函数 用户定义函数 用户定义函数的定义与使用 架构绑定函数的创建 推荐操作Copyright200Copyright2008 85创建标量用户定义函数 标量函数返回 RETURNSRETURNS 子句中定义的数据类型的单个数据值 在 BEGIN END 块之间定义了函数体，包含 返回值的一系列 Transact-SQL 语句 返回值可以是除了 text、ntext、image、cursor 或 timestamp 之外的任何数据类型 额外的限制 标量函数内的 SQL 语句不能包括任何非确定性系统函数16.2.1 使用标量用户定义函数Copyright200Copyright2008 86标量用户函数定义CREATE FUNCTION own_name. function_name ( 参数名 AS 数据类型 = 默认值 ,.n ) RETURNS 返回数据类型 WITH AS BEGIN 函数体RETURN 返回表达式 ENDSELECT own_name .function_name (参数值,.n)16.2.2 标量用户定义函数示例必须指出所 有者名称 创建函数 调用函数Copyright200Copyright2008 87标量用户定义函数示例USE pubs GO CREATE FUNCTION MyFun (type char(12) RETURNS money AS BEGINDECLARE var moneySELECT var = sum(price * ytd_sales) FROM titlesWHERE type = typeRETURN var ENDSELECT dbo.MyFun(business)16.2.2 标量用户定义函数示例 创建标量函数MyScarlarFun，计算用户指定类图 书的当年销售额。 调用函数Copyright200Copyright2008 88标量用户定义函数示例（续）USE pubs GO CREATE FUNCTION MyFun (type char(12) = business) RETURNS money AS BEGINDECLARE var moneySELECT var = sum(price * ytd_sales) FROM titlesWHERE type = typeRETURN var ENDSELECT dbo.MyFun(DEFAULT)16.2.2 标量用户定义函数示例不能省略参数带 默认值 创建标量函数MyScarlarFun，计算用户指定类图书 的当年销售额（带默认值） 。 调用函数Copyright200Copyright2008 89使用多语句表值函数 多语句表值函数 BEGIN . END 限定了函数体 RETURNS 子句指定 table 作为返回的数据类型 RETURNS 子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部16.2.3 使用多语句表值函数Copyright200Copyright2008 810多语句表值函数定义CREATE FUNCTION own_name. function_name ( 参数名 AS 数据类型 = 默认值 ,.n) RETURNS 返回变量 TABLE WITH AS BEGIN 函数体RETURN ENDSELECT * from own_name .function_name (参数值,.n) where 16.2.3 使用多语句表值函数可以省略 创建函数 调用函数Copyright200Copyright2008 811多语句表值函数示例 创建函数，根据要求返回ID及Lastname或 Lastname、firstname组合 CREATE FUNCTION fn_Employees (length nvarchar(10) RETURNS fn_Employees table(EmployeeID int PRIMARY KEY NOT NULL,EmployeeName nvarchar(20) NOT NULL) AS BEGINIF length = ShortNameINSERT fn_Employees SELECT EmployeeID, LastName FROM EmployeesELSE IF length = LongNameINSERT fn_EmployeesSELECT EmployeeID, (FirstName + + LastName) FROM EmployeesRETURN END SELECT * FROM dbo.fn_Employees(ShortName)16.2.3 多语句表值函数示例SELECT * FROM dbo.fn_Employees(LongName) WHERE employeeid 5 调用函数表定义Copyright200Copyright2008 812使用多语句表值函数（续） 函数体内只允许如下语句 赋值语句 流控制语句 用于定义函数局部数据变量和游标的 DECLARE 语句 SELECT 语句，该语句包含带有表达式的选择列表， 其中的表达式将值赋予函数的局部变量 游标操作，如声明、打开、关闭和释放局部游标。仅允 许使用 FETCH 语句通过 INTO 子句给局部变量赋值， 不允许使用 FETCH 语句将数据返回到客户端 修改函数中定义的局部表变量的 INSERT、UPDATE 和 DELETE 语句16.2.3 使用多语句表值函数Copyright200Copyright2008 813使用内嵌表值函数 内嵌表值函数返回表，可在 FROM 子句中被引用 ，就像视图一样 关于使用内嵌表值函数的依据和指导方针 RETURNS 子句在括号中包含单个 SELECT 语 句。SELECT 语句的结果集构成函数所返回的 表 函数体不由 BEGIN 和 END 分隔 RETURNS 指定 table 作为返回的数据类型 不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置16.2.4 使用内嵌表值函数Copyright200Copyright2008 814内嵌表值函数示例USE Northwind GO CREATE FUNCTION fn_CustomerNamesInRegion( RegionParameter nvarchar(30) ) RETURNS table AS RETURN (SELECT CustomerID, CompanyNameFROM Northwind.dbo.CustomersWHERE Region = RegionParameter)SELECT * FROM fn_CustomerNamesInRegion(WA) 16.2.4 内嵌表值函数示例 创建函数，根据地区查看其所在的客户姓名。 使用参数调用函数Copyright200Copyright2008 815更改和删除用户定义函数 更改函数语法：ALTER FUNCTION 使用新的函数定义代替原来的函数定义 保留所有已分配的权限 删除函数 语法：DROP FUNCTION 删除一个用户定义函数16.2.5 更改和删除用户定义函数Copyright200Copyright2008 816第14章 实现用户定义函数 用户定义函数 用户定义函数的定义与使用 架构绑定函数的创建 推荐操作Copyright200Copyright2008 817创建架构绑定函数 可以使用架构绑定将函数绑定到其引用的数据库对象 若函数创建时使用 SCHEMABINDING 选项，则此 函数引用的数据库对象不能被更改（使用 ALTER 语句）或删除（使用 DROP 语句） 必须满足以下条件才能在 CREATE FUNCTION 中指 定 SCHEMABINDING 选项 该函数所引用的所有视图和用户定义函数必须是绑 定到架构的 函数引用的对象必须以两部分名称的格式引用，即“ 拥有者名.对象名” 该函数所引用的所有对象必须与函数位于同一数据 库中 执行 CREATE FUNCTION 语句的用户必须具有对 该函数中引用的所有对象的 REFERENCE 权限16.3.1 创建架构绑定函数Copyright200Copyright2008 818创建架构绑定函数（续） 创建架构绑定的函数16.3.1 创建架构绑定函数CREATE FUNCTION fn_Employees (employeeid int)RETURNS varchar(100) with SCHEMABINDING AS BEGINdeclare emLastName varchar(50),emFirstName varchar(50)select emLastName = lastName, emFirstName = firstname from dbo.viwEmployeewhere employeeID = employeeidRETURN (emLastName + + emfirstName) END 创建架构绑定的视图CREATE VIEW dbo.ViwEmployee with schemabinding AS SELECT employeeid,lastname,firstname,city from dbo.employees必须先构建 绑定视图Copyright200Copyright2008 819为用户定义函数设置权限 用户定义函数的权限要求和其他数据库对象的类似 要创建、更改或删除用户定义函数，必须具有 CREATE FUNCTION 权限 若函数拥有者之外的用户要在 Tr