实现用户定义函数

1、1第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作2用户定义函数用户定义函数SQL Server 2000 允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能用户定义函数采用零或多个输入参数输入参数并返回标量值或表SQL Server 2000 支持三种用户定义函数： 标量函数、多语句表值函数、内嵌表值函数标量函数标量函数返回一个标量（单值）结果返回值可为：Timestamp、Text、Ntext、Image、Table和Cursor之外的所有数据类型，但不能为用户自定义数据类型。16.1 用户定义函数用户定义函数3用户定

2、义函数（续）用户定义函数（续）多语句表值函数返回一个由一条或多条 Transact-SQL 语句建立的表，类似于存储过程与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用，仿佛视图一样内嵌表值函数返回一个表，该表是由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来的，类似于视图相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能16.1 用户定义函数用户定义函数4第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作5创建标量用户定义函数创建标量用户定义函数标量函数

3、返回 子句中定义的数据类型的单个数据值在 BEGIN END 块之间定义了函数体，包含返回值的一系列 Transact-SQL 语句返回值可以是除了 text、ntext、image、cursor 或 timestamp 之外的任何数据类型额外的限制标量函数内的 SQL 语句不能包括任何非确定性系统函数16.2.1 使用标量用户定义函数使用标量用户定义函数6标量用户函数定义标量用户函数定义CREATE FUNCTION own_name. function_name ( 参数名 AS 数据类型 = 默认值 ,.n )RETURNS 返回数据类型WITH ASBEGIN函数体 RETURN 返回

4、表达式ENDSELECT own_name.function_name (参数值,.n)16.2.2 标量用户定义函数示例标量用户定义函数示例必须指出所必须指出所有者名称有者名称创建函数创建函数调用函数调用函数7标量用户定义函数示例标量用户定义函数示例USE pubsGOCREATE FUNCTION MyFun (type char(12)RETURNS moneyASBEGIN DECLARE var money SELECT var = sum(price * ytd_sales) FROM titles WHERE type = type RETURN varENDSELECT dbo

5、.MyFun(business)16.2.2 标量用户定义函数示例标量用户定义函数示例创建标量函数创建标量函数MyScarlarFun，计算用户指定类图，计算用户指定类图书的当年销售额。书的当年销售额。调用函数调用函数8标量用户定义函数示例（续）标量用户定义函数示例（续）USE pubsGOCREATE FUNCTION MyFun (type char(12) = business)RETURNS moneyASBEGIN DECLARE var money SELECT var = sum(price * ytd_sales) FROM titles WHERE type = type R

6、ETURN varENDSELECT dbo.MyFun(DEFAULT)16.2.2 标量用户定义函数示例标量用户定义函数示例不能省略不能省略参数带参数带默认值默认值创建标量函数创建标量函数MyScarlarFun，计算用户指定类图书，计算用户指定类图书的当年销售额（带默认值）的当年销售额（带默认值） 。调用函数调用函数9使用多语句表值函数使用多语句表值函数多语句表值函数BEGIN . END 限定了函数体RETURNS 子句指定 table 作为返回的数据类型RETURNS 子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部16.2.3 使用多语句表值函数使用多语句表值函数10

7、多语句表值函数定义多语句表值函数定义CREATE FUNCTION own_name. function_name ( 参数名 AS 数据类型 = 默认值 ,.n)RETURNS 返回变量 TABLE WITH ASBEGIN函数体 RETURN ENDSELECT * from own_name.function_name (参数值,.n) where 16.2.3 使用多语句表值函数使用多语句表值函数可以省略可以省略创建函数创建函数调用函数调用函数11多语句表值函数示例多语句表值函数示例创建函数，根据要求返回ID及Lastname或Lastname、firstname组合CREATE FU

8、NCTION fn_Employees (length nvarchar(10)RETURNS fn_Employees table (EmployeeID int PRIMARY KEY NOT NULL, EmployeeName nvarchar(20) NOT NULL)ASBEGIN IF length = ShortName INSERT fn_Employees SELECT EmployeeID, LastName FROM Employees ELSE IF length = LongName INSERT fn_Employees SELECT EmployeeID, (F

9、irstName + + LastName) FROM Employees RETURNEND SELECT * FROM dbo.fn_Employees(ShortName)16.2.3 多语句表值函数示例多语句表值函数示例SELECT * FROM dbo.fn_Employees(LongName)WHERE employeeid 5 调用函数表定义表定义12使用多语句表值函数（续）使用多语句表值函数（续）函数体内只允许如下语句赋值语句流控制语句用于定义函数局部数据变量和游标的 DECLARE 语句SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量游

10、标操作，如声明、打开、关闭和释放局部游标。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值，不允许使用 FETCH 语句将数据返回到客户端修改函数中定义的局部表变量的 INSERT、UPDATE 和 DELETE 语句16.2.3 使用多语句表值函数使用多语句表值函数13使用内嵌表值函数使用内嵌表值函数内嵌表值函数返回表，可在 FROM 子句中被引用，就像视图一样关于使用内嵌表值函数的依据和指导方针RETURNS 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表函数体不由 BEGIN 和 END 分隔RETURNS 指定 table 作为返回

11、的数据类型不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置16.2.4 使用内嵌表值函数使用内嵌表值函数14内嵌表值函数示例内嵌表值函数示例USE NorthwindGOCREATE FUNCTION fn_CustomerNamesInRegion ( RegionParameter nvarchar(30) )RETURNS tableASRETURN ( SELECT CustomerID, CompanyName FROM Northwind.dbo.Customers WHERE Region = RegionParameter )SEL

12、ECT * FROM fn_CustomerNamesInRegion(WA) 16.2.4 内嵌表值函数示例内嵌表值函数示例创建函数，根据地区查看其所在的客户姓名。创建函数，根据地区查看其所在的客户姓名。使用参数调用函数使用参数调用函数15更改和删除用户定义函数更改和删除用户定义函数更改函数语法：语法：ALTER FUNCTION 使用新的函数定义代替原来的函数定义保留所有已分配的权限删除函数语法：DROP FUNCTION 删除一个用户定义函数16.2.5 更改和删除用户定义函数更改和删除用户定义函数16第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数的定义与使用架构

13、绑定函数的创建推荐操作17创建架构绑定函数创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库对象若函数创建时使用 SCHEMABINDING 选项，则此函数引用的数据库对象不能被更改（使用 ALTER 语句）或删除（使用 DROP 语句）必须满足以下条件才能在 CREATE FUNCTION 中指定 SCHEMABINDING 选项该函数所引用的所有视图和用户定义函数必须是绑定到架构的函数引用的对象必须以两部分名称的格式引用，即“拥有者名.对象名”该函数所引用的所有对象必须与函数位于同一数据库中执行 CREATE FUNCTION 语句的用户必须具有对该函数中引用的所有对象的 REFE

14、RENCE 权限16.3.1 创建架构绑定函数创建架构绑定函数18创建架构绑定函数（续）创建架构绑定函数（续）创建架构绑定的函数16.3.1 创建架构绑定函数创建架构绑定函数CREATE FUNCTION fn_Employees (employeeid int) RETURNS varchar(100)with SCHEMABINDINGASBEGIN declare emLastName varchar(50),emFirstName varchar(50) select emLastName = lastName, emFirstName = firstname from dbo.viw

15、Employee where employeeID = employeeid RETURN (emLastName + + emfirstName)END创建架构绑定的视图CREATE VIEW dbo.ViwEmployeewith schemabindingASSELECT employeeid,lastname,firstname,cityfrom dbo.employees必须先构建必须先构建绑定视图绑定视图19为用户定义函数设置权限为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象的类似要创建、更改或删除用户定义函数，必须具有 CREATE FUNCTION 权限若函数拥有

16、者之外的用户要在 Transact-SQL 语句中使用函数，则必须先被授予 EXECUTE 权限若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的 REFERENCE 权限若在 CREATE TABLE 或 ALTER TABLE 语句的 CHECK 约束、DEFAULT 子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的 REFERENCE 权限16.3.2 为用户定义函数设置权限为用户定义函数设置权限20第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数的定义架构绑定函数的创建推荐操作21推荐操作推荐操作使用多语句表值函数代替返回表的存储过

17、程使用多语句表值函数代替返回表的存储过程使用内嵌表值函数过滤索引视图使用内嵌表值函数过滤索引视图在小的结果集上使用复杂的标量函数在小的结果集上使用复杂的标量函数使用内嵌表值函数创建参数化视图使用内嵌表值函数创建参数化视图16.4 推荐操作推荐操作22随堂练习随堂练习1表AppUsers中存放了用户的姓名与密码，要求密码经过加密后，以二进制的方式存放，请提供加密与解密函数来实现该功能。create function dbo.fn_EncryptPassword(Password varchar(20)returns binary(20)begindeclare EPassword binary(

18、20) set EPassword = cast(Password as binary(20)return EPasswordendcreate function dbo.fn_DecryptPassword(EPassword binary(20)returns varchar(20)begindeclare Password varchar(20)set Password = cast(EPassword as varchar(20)return Passwordend23随堂练习随堂练习1（续）（续）在向表AppUsers插入数据时，使用加密函数对密码进行加密后再插入，在从表中读取数据时，用解密函数对密码进行解密insert into AppUsers (UserName, Password) values(Tom,dbo.fn_EncryptPassword(00001)select username,dbo.fn_DecryptPassword(password) from AppUsers24随堂练习随堂练习2在设计某个销售公司的数据库系统时，客户要求设计一张季度销售情况的视图。这张视图连接多张表同时使用聚集函数计算