用户自定义函数课件.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第16章,用户自定义函数,.,1,16.1 用户自定义函数的基本概念,SQL,Server允许创建用户定义函数,用户定义函数是可返回值的例程,.,2,用户定义函数种类,返回可更新数据表的函数,返回不可更新数据表的函数,返回标量值的函数,若函数含,单,个,SELECT,语句且可更新，则返回的数据表可更新,若函数含,多,个,SELECT,语句,或,一个不可更新的,SELECT,语句，则返回的数据表不可更新,.,3,16.2 创建用户自定义函数,16.2.1创建用户自定义函数,CREATE,FUNCTION,owner,-,name,function-name,(,parameter,-name,scalar_parameter_datetype,=default,n),参数名，局部变量,参数数据类型,.,4,RETURNS,scalar_return_datatype,WITH,n,AS,BEGIN,sql-statement,RETURN,scalar-expression,END,用户自定义函数的返回值,函数中返回值的表达式,.,5,SCHEMABINING,选项,可将函数绑定到它所引用的数据库对象,function-option,ENCRYPTION,选项,可以实现,SQL,Server加密包含,CREATE,FUNCTION,语句文本的系统表列,.,6,例：,在,Northwind,库上创建自定义函数,my_function1,，通过向该函数输入代表商品年销售额的,money,类型参数的大小返回,字符串,，若年销售额大于,10000,，返回“,热销商品,”，否则返回“,非热销商品,”,.,7,USE Northwind,go,CREATE,FUNCTION,my,_,function,1,(moneyinput money),RETURNS nvarchar(5),.,8,BEGIN,DECLARE,returnstring nvarchar(5),IF moneyinput10000,SET returnstring=非热销商品,ELSE,SET returnstring=热销商品,RETURN returnstring,END,.,9,在,Northwind,库上有一个记录各种,产品在,1997,年销售额的视图，通,过它来引用新建的,my_function1,，,查看哪些商品属于热销商品,USE Northwind,go,SELECT,ProductName,ProductSales,dbo,.,my,_function1(ProductSales),AS,Discription,FROM,Product sales for 1997,WHERE,CategoryName=Dairy Products,.,10,.,11,16.2.2查看用户自定义函数,自定义函数的,名称,保存在,sysobjects,系统表中,创建自定义函数的,源代码,保存在,syscomments,系统表中,.,12,1.使用系统存储过程查看,EXEC,sp_help(sp_helptext),例：,用系统存储过程sp_helptext 查看用户自定义函数my_funciton1的定义文本信息,USE Northwind,go,EXEC sp_helptext my_function1,go,.,13,.,14,2.使用系统表或系统信息架构视图,在,SQL,Server,中存在三个,信息架构视图,报告有关用户自定义函数的信息,ROUTINES,PARAMETERS,ROUTINE,_,COLUMNS,这些,信息架构视图,也是基于系统表,sysobjects,和,syscomments,实现的,.,15,例：,使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。

USE Northwind,SELECT*from sysobjects,WHERE type=FN,go,.,16,16.3 用户自定义函数类型,自定义函数有三种类型,标量函数,内嵌表值函数,多语句表值函数,.,17,16.3.1 标量函数,标量函数,返回在,RETURNS,子句中定义的数据类型的单个数据值,标量函数可重复调用,.,18,例：,创建标量函数，要求将当前,系统日期,转化为,年月日,格式的字符串并返回，且默认的分隔符为,:,，并允许用户自行定义,分隔符,.,19,CREATE,FUNCTION,my_function2,(,DATE,datetime,separator nvarchar(2)=:),RETURNS nvarchar(20),BEGIN,DECLARE,returnstring nvarchar(20),.,20,SET returnstring=今天是,+,CONVERT,(nvarchar(5),datepart(year,date)+年,+separator,+,CONVERT,(nvarchar(5),datepart(month,date)+月,+separator,+,CONVERT,(nvarchar(5),datepart(day,date)+日,RETURN returnstring,END,.,21,如何调用新建立的标量函数呢？,可以运行如下命令：,SELECT,dbo.my_function2(,GETDATE(),-),返回当天的年、月和日,.,22,16.3.2 内嵌表值函数,在内嵌表值函数中，,RETURN,子句中包含有一条单独的,SELECT,语句，该语句的结果构成了,内嵌表值函数,所返回的表,可替代视图，可用在T-SQL查询中允许表或视图表达式的地方,.,23,视图：,受限于单个,SELECT,语句，不允许包含用户自己提供的参数,内嵌表值函数：,可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强,返回表的内嵌表值函数还可替换返回单个结果集的存储过程,.,24,例:,在,Northwind,数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函数my_function3,.,25,USE Northwind,Go,CREATE,FUNCTION,my_function3,(,RegionParameter,nvarchar(25),RETURNS table,AS,.,26,RETURN,(SELECT City,CompanyName,ContactName,Customers,AS,Relationship,FROM Customers,WHERE City=RegionParameter,UNION SELECT City,CompanyName,ContactName,Suppliers,FROM Suppliers,WHERE City=RegionParameter),.,27,下面示例使用新建立的内嵌表值函数,my_function3,来获取“巴黎”市的所有客户和供应商的信息,SELECT*FROM,dbo.my_function3(Paris),go,.,28,如果用户想通过,视图,“Customer,and Suppliers by City”来实现同,样的目的，则使用如下查询命令：,SELECT*FROM,Customer and Suppliers by City,WHERE City=Paris,go,.,29,16.3.3 多语句表值函数,多语句表值函数的主体中允许使用的语句,赋值,控制流,DECLARE,SELECT,游标操作,INSERT,UPDATE,DELETE,EXECUTE,.,30,例:,在,Northwind,库上利用视图,Customer,and,Suppliers by City,，建立一个可以根据输入的城市名和用户要求返回所有该城市的客户或供应商的信息的多语句表值函数,my_function4,，并将其与上例建立的内嵌表值函数,my_function3,对比,.,31,USE Northwind,GO,CREATE,FUNCTION my_function4,(RegionParameter nvarchar(25),SelectParameter char(9),RETURNS my_table TABLE,(City nvarchar(15)NULL,CompanyName,nvarchar(40),NOT,NULL,ContactName nvarchar(30)NULL),.,32,AS,BEGIN,INSERT my_table SELECT City,CompanyName,ContactName,FROM,Customer,and,Suppliers by City,WHERE,City=,RegionParameter,AND,Relationship=SelectParameter,RETURN,END,.,33,下面举例说明使用多语句表值函数,my_function4,分别获取来自Paris的所有客户和供应商的信息。

命令如下：,SELECT*FROM,dbo.my_function4(Paris,Customers),go,SELECT*FROM,dbo.my_function4(Paris,Suppliers),go,.,34,16.4 修改和删除用户自定义函数,16.4.1 修改用户自定义函数,CREATE,FUNCTION,ower-name,function-name,(parameter-name,scalar-parameter-datetype=default,n),RETURNS scalar-return-datatype,WITH ,n,AS,BEGIN,sql-statement,RETURN scalar-expression,END,.,35,例：,修改在Northwind库上创建的自定义函数my_function1，将区分商品是否热销的年销售额由当初的10000提高到20000,ALTER,FUNCTION,my_function1,(moneyinput money),RETURNS nvarchar(5),.,36,BEGIN,DECLARE,returnstring nvarchar(5),IF moneyinput20000,SET returnstring=非热销商品,ELSE,SET returnstring=热销商品,RETURN returnstring,END,.,37,16.4.2 删除用户自定义函数,DROP,FUNCTION,ower-name,function-name,例：,删除在,Northwind,库上创建的自定义函数,my_function1,DROP FUNCTION my_function1,.,38,16.4.3 设置用户自定义函数的权限,设置自定义函数的权限类似于设置表或其他数据库对象的权限,要为用户授予,CREATE,FUNCTION,权限,才能进行创建、修改或删除自定义函数的操作,.,39,对于,标量值函数,，一般用户在使用此函数前，必须先被授予此函数上的,EXECUTE,权限,.,40,如果函数是,表值函数,，则用户在引用函数前必须对函数具有,SELECT,权限,.,41,若,CREATE,TABLE,或,ALTER,TABLE,语句在,CHECK,约束、,DEFAULT,约束、计算列中引用了自定义函数，那么该表的所有者也要拥有该函数,.,42,。