【文档说明】数据库 第14章 实现用户定义函数课件.ppt，共(27)页，365.000 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92413.html

以下为本文档部分文字说明：

Copyright@20081第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作Copyright@20082用户定义函数SQLServer2000允许用户设计自己的函数，以补充和扩展系统提供（内

置）函数的功能用户定义函数采用零或多个输入参数并返回标量值或表SQLServer2000支持三种用户定义函数：标量函数、多语句表值函数、内嵌表值函数标量函数标量函数返回一个标量（单值）结果返回值可为：Timestamp、Text、Ntext、Image、Table和Cur

sor之外的所有数据类型，但不能为用户自定义数据类型。16.1用户定义函数Copyright@20083用户定义函数（续）多语句表值函数返回一个由一条或多条Transact-SQL语句建立的表，类似于存储过程与存储过程不同的是，多语句表值函数可以

在SELECT语句的FROM子句中被引用，仿佛视图一样内嵌表值函数返回一个表，该表是由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来的，类似于视图相对于视图，内嵌表值函数可使用参数，提

供了更强的适应性，扩展了索引视图的功能16.1用户定义函数Copyright@20084第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作Copyright@20085创建标量用户定义函数标量函

数返回RETURNS子句中定义的数据类型的单个数据值在BEGIN…END块之间定义了函数体，包含返回值的一系列Transact-SQL语句返回值可以是除了text、ntext、image、cursor或timestamp之外的任何数

据类型额外的限制标量函数内的SQL语句不能包括任何非确定性系统函数16.2.1使用标量用户定义函数Copyright@20086标量用户函数定义CREATEFUNCTION[own_name.]function_name([{@参数名[AS]数据类型[=默认值]}[,.

..n]])RETURNS返回数据类型[WITH<函数选项>][AS]BEGIN函数体RETURN返回表达式ENDSELECTown_name.function_name[(参数值[,...n])]16.2.2标量用户定义函数示例必须指出所

有者名称创建函数调用函数Copyright@20087标量用户定义函数示例USEpubsGOCREATEFUNCTIONMyFun(@typechar(12))RETURNSmoneyASBEGINDECLARE@varmoneySELECT@var=sum(price*ytd_sales)FR

OMtitlesWHEREtype=@typeRETURN@varENDSELECTdbo.MyFun('business')16.2.2标量用户定义函数示例创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额。调用函数Copyright@20088标量用户定义函数示例（续）USE

pubsGOCREATEFUNCTIONMyFun(@typechar(12)=‘business’)RETURNSmoneyASBEGINDECLARE@varmoneySELECT@var=sum(price*ytd_sales)FROMtitlesWHERE

type=@typeRETURN@varENDSELECTdbo.MyFun(DEFAULT)16.2.2标量用户定义函数示例不能省略参数带默认值创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额（带默认值）。调用函数Copyright@20089使用多语句表值函数多语句表

值函数BEGIN...END限定了函数体RETURNS子句指定table作为返回的数据类型RETURNS子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部16.2.3使用多语句表值函数Copyright@200810多语句表值函数定义

CREATEFUNCTION[own_name.]function_name([{@参数名[AS]数据类型[=默认值]}[,...n]])RETURNS@返回变量TABLE<表定义>[WITH<函数选项>][AS]BEGIN函数体RETURNENDSELECT*fromow

n_name.function_name[(参数值[,...n])][where<条件>]16.2.3使用多语句表值函数可以省略创建函数调用函数Copyright@200811多语句表值函数示例创建函数，根据要求返回ID及Lastname或Lastname、first

name组合CREATEFUNCTIONfn_Employees(@lengthnvarchar(10))RETURNS@fn_Employeestable(EmployeeIDintPRIMARYKEYNOTNULL,EmployeeNamenvarchar(20)N

OTNULL)ASBEGINIF@length='ShortName'INSERT@fn_EmployeesSELECTEmployeeID,LastNameFROMEmployeesELSEIF@length='LongName'INSERT@fn_EmployeesSELE

CTEmployeeID,(FirstName+''+LastName)FROMEmployeesRETURNENDSELECT*FROMdbo.fn_Employees('ShortName')16.2.3多语句表值函数示例SELECT*FROMdbo.fn_Employees('LongNa

me')WHEREemployeeid<5调用函数表定义Copyright@200812使用多语句表值函数（续）函数体内只允许如下语句赋值语句流控制语句用于定义函数局部数据变量和游标的DECLARE语句S

ELECT语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量游标操作，如声明、打开、关闭和释放局部游标。仅允许使用FETCH语句通过INTO子句给局部变量赋值，不允许使用FETCH语句将数据返回到客户端修改函数中定义的局部表变量的INSERT、UPDATE和DELETE语句

16.2.3使用多语句表值函数Copyright@200813使用内嵌表值函数内嵌表值函数返回表，可在FROM子句中被引用，就像视图一样关于使用内嵌表值函数的依据和指导方针RETURNS子句在括号中包含单个SELECT语句。SELE

CT语句的结果集构成函数所返回的表函数体不由BEGIN和END分隔RETURNS指定table作为返回的数据类型不必定义返回变量的格式，因为它由RETURN子句中的SELECT语句的结果集的格式设置16.2.4使用内嵌表值函

数Copyright@200814内嵌表值函数示例USENorthwindGOCREATEFUNCTIONfn_CustomerNamesInRegion(@RegionParameternvarchar(30))RETURNStableASRETURN(SELECTCustome

rID,CompanyNameFROMNorthwind.dbo.CustomersWHERERegion=@RegionParameter)SELECT*FROMfn_CustomerNamesInRegion('WA')16.2.4内嵌表值函数示例创建函数，根据地区查看其所在的客户

姓名。使用参数调用函数Copyright@200815更改和删除用户定义函数更改函数语法：ALTERFUNCTION……使用新的函数定义代替原来的函数定义保留所有已分配的权限删除函数语法：DROPFUNCTI

ON……删除一个用户定义函数16.2.5更改和删除用户定义函数Copyright@200816第14章实现用户定义函数用户定义函数用户定义函数的定义与使用架构绑定函数的创建推荐操作Copyright@200817创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库

对象若函数创建时使用SCHEMABINDING选项，则此函数引用的数据库对象不能被更改（使用ALTER语句）或删除（使用DROP语句）必须满足以下条件才能在CREATEFUNCTION中指定SCHEMABINDING选项该

函数所引用的所有视图和用户定义函数必须是绑定到架构的函数引用的对象必须以两部分名称的格式引用，即“拥有者名.对象名”该函数所引用的所有对象必须与函数位于同一数据库中执行CREATEFUNCTION语句的用户必须具有对该函数中引用的

所有对象的REFERENCE权限16.3.1创建架构绑定函数Copyright@200818创建架构绑定函数（续）创建架构绑定的函数16.3.1创建架构绑定函数CREATEFUNCTIONfn_Employees(@employeeidint)RETURN

Svarchar(100)withSCHEMABINDINGASBEGINdeclare@emLastNamevarchar(50),@emFirstNamevarchar(50)select@emLastName=lastN

ame,@emFirstName=firstnamefromdbo.viwEmployeewhereemployeeID=@employeeidRETURN(@emLastName+''+@emfirstName)END创建架构绑定的视图CRE

ATEVIEWdbo.ViwEmployeewithschemabindingASSELECTemployeeid,lastname,firstname,cityfromdbo.employees必须先构建绑定视图Copyright@200819为用户定义函数

设置权限用户定义函数的权限要求和其他数据库对象的类似要创建、更改或删除用户定义函数，必须具有CREATEFUNCTION权限若函数拥有者之外的用户要在Transact-SQL语句中使用函数，则必须先被授予EXECUTE权限若函

数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的REFERENCE权限若在CREATETABLE或ALTERTABLE语句的CHECK约束、DEFAULT子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的REFERENCE权限16.3.2为用户定义函数设置权限Copy

right@200820第14章实现用户定义函数用户定义函数用户定义函数的定义架构绑定函数的创建推荐操作Copyright@200821推荐操作使用多语句表值函数代替返回表的存储过程使用内嵌表值函数过滤索引视图在小的结果集上使用复杂的标

量函数使用内嵌表值函数创建参数化视图16.4推荐操作Copyright@200822随堂练习1表AppUsers中存放了用户的姓名与密码，要求密码经过加密后，以二进制的方式存放，请提供加密与解密函数来实现该功能。createfunctiondbo.fn_EncryptPassword

(@Passwordvarchar(20))returnsbinary(20)begindeclare@EPasswordbinary(20)set@EPassword=cast(@Passwordasbinary(20))return@

EPasswordendcreatefunctiondbo.fn_DecryptPassword(@EPasswordbinary(20))returnsvarchar(20)begindeclare@Passwordvarchar(20)set@Password=cast(@EP

asswordasvarchar(20))return@PasswordendCopyright@200823随堂练习1（续）在向表AppUsers插入数据时，使用加密函数对密码进行加密后再插入，在从表中读取数据时，用解密函

数对密码进行解密insertintoAppUsers(UserName,[Password])values('Tom',dbo.fn_EncryptPassword('00001'))selectusername,dbo.fn

_DecryptPassword([password])fromAppUsersCopyright@200824随堂练习2在设计某个销售公司的数据库系统时，客户要求设计一张季度销售情况的视图。这张视图连接多张表同时使用聚集函数计算所需的统计信息。客

户要求提供一个带参数的查询方法，输出的结果要作为其他的SELECT语句的列值使用。请问应当如何设计该视图？A、使用ALTERVIEW语句在视图定义中添加参数B、创建带参数的存储过程，将结果集作为一个行集合返回C、创建

一个接收参数输入的标量用户自定义函数D、创建一个接收参数输入的内联用户自定义函数Copyright@200825在设计某个公司的数据库系统时，要求设计一张数据表。这张数据表使用聚集函数计算所需的统计信息并使用索引提高查询速度。要求将该数据表查询输出的结果要放在其他SELE

CT语句的FROM子句使用，下列做法哪个是正确的？A、创建一个接收参数输入标量用户自定义函数B、创建一个接收参数输入并将结果集作为一个行集合返回的标量用户自定义函数C、创建一个接收参数输入内联用户自定义函数D、创建一个接收参数输入的存储过程随堂练习3Copyright@20082630

分钟实验创建用户定义函数目标创建标量用户定义函数创建多语句表值用户定义函数创建内嵌表值用户定义函数练习1创建标量用户定义函数练习2创建多语句表值用户定义函数练习3创建内嵌表值用户定义函数Copyright@200827回顾学习完本章

后，将能够：描述用户定义函数的三种类型创建和更改用户定义函数创建每种类型的用户定义函数