数据库实现用户定义函数省公共课一等奖全国赛课获奖课件.pptx

Copyright2008Copyright20081第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数定义与使用架构绑定函数创建推荐操作第1页Copyright2008Copyright20082用户定义函数用户定义函数SQL Server 允许用户设计自己函数，以补充和扩展系统提供（内置）函数功效用户定义函数采取零或多个输入参数输入参数并返回标量值或表SQL Server 支持三种用户定义函数：标量函数、多语句表值函数、内嵌表值函数标量函数标量函数返回一个标量（单值）结果返回值可为：Timestamp、Text、Ntext、Image、Table和Cursor之外全部数据类型，但不能为用户自定义数据类型。16.1 用户定义函数用户定义函数第2页Copyright2008Copyright20083用户定义函数（续）用户定义函数（续）多语句表值函数返回一个由一条或多条 Transact-SQL 语句建立表，类似于存放过程与存放过程不一样是，多语句表值函数能够在 SELECT 语句 FROM 子句中被引用，好像视图一样内嵌表值函数返回一个表，该表是由一个位于RETURN子句中SELECT命令段从数据库中筛选出来，类似于视图相对于视图，内嵌表值函数可使用参数，提供了更强适应性，扩展了索引视图功效16.1 用户定义函数用户定义函数第3页Copyright2008Copyright20084第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数定义与使用架构绑定函数创建推荐操作第4页Copyright2008Copyright20085创建标量用户定义函数创建标量用户定义函数标量函数返回 RETURNSRETURNS 子句中定义数据类型单个数据值在 BEGIN END 块之间定义了函数体，包含返回值一系列 Transact-SQL 语句返回值能够是除了 text、ntext、image、cursor 或 timestamp 之外任何数据类型额外限制标量函数内 SQL 语句不能包含任何非确定性系统函数16.2.1 使用标量用户定义函数使用标量用户定义函数第5页Copyright2008Copyright20086标量用户函数定义标量用户函数定义CREATE FUNCTION own_name.function_name (参数名 AS 数据类型=默认值,.n)RETURNS 返回数据类型WITH ASBEGIN函数体 RETURN 返回表示式ENDSELECT own_name.function_name(参数值,.n)16.2.2 标量用户定义函数示例标量用户定义函数示例必须指出全必须指出全部者名称部者名称创建函数创建函数调用函数调用函数第6页Copyright2008Copyright20087标量用户定义函数示例标量用户定义函数示例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.MyFun(business)16.2.2 标量用户定义函数示例标量用户定义函数示例创建标量函数创建标量函数MyScarlarFun，计算用户指定类图书，计算用户指定类图书当年销售额。当年销售额。调用函数调用函数第7页Copyright2008Copyright20088标量用户定义函数示例（续）标量用户定义函数示例（续）USE pubsGOCREATE FUNCTION MyFun (type char(12)=business)RETURNS moneyASBEGIN DECLARE var money SELECT var=sum(price*ytd_sales)FROM titles WHERE type=type RETURN varENDSELECT dbo.MyFun(DEFAULT)16.2.2 标量用户定义函数示例标量用户定义函数示例不能省略不能省略参数带参数带默认值默认值创建标量函数创建标量函数MyScarlarFun，计算用户指定类图书，计算用户指定类图书当年销售额（带默认值）当年销售额（带默认值）。调用函数调用函数第8页Copyright2008Copyright20089使用多语句表值函数使用多语句表值函数多语句表值函数BEGIN.END 限定了函数体RETURNS 子句指定 table 作为返回数据类型RETURNS 子句定义了返回表名字和格式。返回变量名使用域限定于函数局部16.2.3 使用多语句表值函数使用多语句表值函数第9页Copyright2008Copyright200810多语句表值函数定义多语句表值函数定义CREATE FUNCTION own_name.function_name (参数名 AS 数据类型=默认值,.n)RETURNS 返回变量 TABLE WITH ASBEGIN函数体 RETURN ENDSELECT *from own_name.function_name(参数值,.n)where 16.2.3 使用多语句表值函数使用多语句表值函数能够省略能够省略创建函数创建函数调用函数调用函数第10页Copyright2008Copyright200811多语句表值函数示例多语句表值函数示例创建函数，依据要求返回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)ASBEGIN IF length=ShortName INSERT fn_Employees SELECT EmployeeID,LastName FROM Employees ELSE IF length=LongName INSERT fn_Employees SELECT EmployeeID,(FirstName+LastName)FROM Employees RETURNEND SELECT*FROM dbo.fn_Employees(ShortName)16.2.3 多语句表值函数示例多语句表值函数示例SELECT*FROM dbo.fn_Employees(LongName)WHERE employeeid 5 调用函数表定义表定义第11页Copyright2008Copyright200812使用多语句表值函数（续）使用多语句表值函数（续）函数体内只允许以下语句赋值语句流控制语句用于定义函数局部数据变量和游标 DECLARE 语句SELECT 语句，该语句包含带有表示式选择列表，其中表示式将值赋予函数局部变量游标操作，如申明、打开、关闭和释放局部游标。仅允许使用 FETCH 语句经过 INTO 子句给局部变量赋值，不允许使用 FETCH 语句将数据返回到客户端修改函数中定义局部表变量 INSERT、UPDATE 和 DELETE 语句16.2.3 使用多语句表值函数使用多语句表值函数第12页Copyright2008Copyright200813使用内嵌表值函数使用内嵌表值函数内嵌表值函数返回表，可在 FROM 子句中被引用，就像视图一样关于使用内嵌表值函数依据和指导方针RETURNS 子句在括号中包含单个 SELECT 语句。SELECT 语句结果集组成函数所返回表函数体不由 BEGIN 和 END 分隔RETURNS 指定 table 作为返回数据类型无须定义返回变量格式，因为它由 RETURN 子句中 SELECT 语句结果集格式设置16.2.4 使用内嵌表值函数使用内嵌表值函数第13页Copyright2008Copyright200814内嵌表值函数示例内嵌表值函数示例USE NorthwindGOCREATE FUNCTION fn_CustomerNamesInRegion (RegionParameter nvarchar(30)RETURNS tableASRETURN(SELECT CustomerID,CompanyName FROM Northwind.dbo.Customers WHERE Region=RegionParameter )SELECT*FROM fn_CustomerNamesInRegion(WA)16.2.4 内嵌表值函数示例内嵌表值函数示例创建函数，依据地域查看其所在客户姓名。创建函数，依据地域查看其所在客户姓名。使用参数调用函数使用参数调用函数第14页Copyright2008Copyright200815更改和删除用户定义函数更改和删除用户定义函数更改函数语法：语法：ALTER FUNCTION 使用新函数定义代替原来函数定义保留全部已分配权限删除函数语法：DROP FUNCTION 删除一个用户定义函数16.2.5 更改和删除用户定义函数更改和删除用户定义函数第15页Copyright2008Copyright200816第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数定义与使用架构绑定函数创建推荐操作第16页Copyright2008Copyright200817创建架构绑定函数创建架构绑定函数能够使用架构绑定将函数绑定到其引用数据库对象若函数创建时使用 SCHEMABINDING 选项，则此函数引用数据库对象不能被更改（使用 ALTER 语句）或删除（使用 DROP 语句）必须满足以下条件才能在 CREATE FUNCTION 中指定 SCHEMABINDING 选项该函数所引用全部视图和用户定义函数必须是绑定到架构函数引用对象必须以两部分名称格式引用，即“拥有者名.对象名”该函数所引用全部对象必须与函数位于同一数据库中执行 CREATE FUNCTION 语句用户必须含有对该函数中引用全部对象 REFERENCE 权限16.3.1 创建架构绑定函数创建架构绑定函数第17页Copyright2008Copyright200818创建架构绑定函数（续）创建架构绑定函数（续）创建架构绑定函数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.viwEmployee where employeeID=employeeid RETURN (emLastName+emfirstName)END创建架构绑定视图CREATE VIEW dbo.ViwEmployeewith schemabindingASSELECT employeeid,lastname,firstname,cityfrom dbo.employees必须先构建必须先构建绑定视图绑定视图第18页Copyright2008Copyright200819为用户定义函数设置权限为用户定义函数设置权限用户定义函数权限要求和其它数据库对象类似要创建、更改或删除用户定义函数，必须含有 CREATE FUNCTION 权限若函数拥有者之外用户要在 Transact-SQL 语句中使用函数，则必须先被授予 EXECUTE 权限若函数是架构绑定，则创建者必须含有函数所引用表、视图和函数上 REFERENCE 权限若在 CREATE TABLE 或 ALTER TABLE 语句 CHECK 约束、DEFAULT 子句或计算列中引用了用户定义函数，则表拥有者必须含有那个函数 REFERENCE 权限16.3.2 为用户定义函数设置权限为用户定义函数设置权限第19页Copyright2008Copyright200820第第14章章 实现用户定义函数实现用户定义函数用户定义函数用户定义函数定义架构绑定函数创建推荐操作第20页Copyright2008Copyright200821推荐操作推荐操作使用多语句表值函数代替返回表存放过程使用多语句表值函数代替返回表存放过程使用内嵌表值函数过滤索引视图使用内嵌表值函数过滤索引视图在小结果集上使用复杂标量函数在小结果集上使用复杂标量函数使用内嵌表值函数创建参数化视图使用内嵌表值函数创建参数化视图16.4 推荐操作推荐操作第21页Copyright2008Copyright200822随堂练习随堂练习1表AppUsers中存放了用户姓名与密码，要求密码经过加密后，以二进制方式存放，请提供加密与解密函数来实现该功效。create function dbo.fn_EncryptPassword(Password varchar(20)returns binary(20)begindeclare EPassword binary(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 Passwordend第22页Copyright2008Copyright200823随堂练习随堂练习1（续）（续）在向表AppUsers插入数据时，使用加密函数对密码进行加密后再插入，在从表中读取数据时，用解密函数对密码进行解密insert into AppUsers(UserName,Password)values(Tom,dbo.fn_EncryptPassword(00001)select username,dbo.fn_DecryptPassword(password)from AppUsers第23页Copyright2008Copyright200824随堂练习随堂练习2在设计某个销售企业数据库系统时，客户要求设计一张季度销售情况视图。这张视图连接多张表同时使用聚集函数计算所需统计信息。客户要求提供一个带参数查询方法，输出结果要作为其它SELECT语句列值使用。请问应该怎样设计该视图？A、使用ALTER VIEW语句在视图定义中添加参数B、创建带参数存放过程，将结果集作为一个行集合返回C、创建一个接收参数输入标量用户自定义函数D、创建一个接收参数输入内联用户自定义函数第24页Copyright2008Copyright200825在设计某个企业数据库系统时，要求设计一张数据表。这张数据表使用聚集函数计算所需统计信息并使用索引提升查询速度。要求将该数据表查询输出结果要放在其它SELECT语句FROM子句使用，下列做法哪个是正确？A、创建一个接收参数输入标量用户自定义函数 B、创建一个接收参数输入并将结果集作为一个行集合返回标 量用户自定义函数 C、创建一个接收参数输入内联用户自定义函数 D、创建一个接收参数输入存放过程随堂练习随堂练习3第25页Copyright2008Copyright20082630分钟分钟 试验试验 创建用户定义函数创建用户定义函数目标目标创建标量用户定义函数创建标量用户定义函数创建多语句表值用户定义函数创建多语句表值用户定义函数创建内嵌表值用户定义函数创建内嵌表值用户定义函数练习练习1 创建标量用户定义函数创建标量用户定义函数练习练习 2 创建多语句表值用户定义函数创建多语句表值用户定义函数练习练习 3 创建内嵌表值用户定义函数创建内嵌表值用户定义函数 第26页Copyright2008Copyright200827回顾回顾学习完本章后，将能够：描述用户定义函数三种类型创建和更改用户定义函数创建每种类型用户定义函数第27页