SQLServer数据库应用第十章.ppt

1,第十章 用户自定义函数,2,什么是函数？,是一组编译好的Transact-SQL语句，他们可以带一个或一组数值做参数，也可以不带参数，它返回一个数值、数值集合，或执行一些操作。 函数能够重复执行一些操作，从而避免不断重写代码。,3,函数类型：,SQL Server 2005 支持用户定义函数和内置函数。 内置函数 是一组预定义的函数，是Transact- SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。,4,10.1 用户自定义函数概述,用户定义函数是用户定义的Transact-SQL函数，它可以拥有零个、一个或多个参数，必须返回单一的返回值。返回值可以是单个数值，也可以是一个表。,5,用户定义函数种类,返回可更新数据表的函数,返回不可更新数据表的函数,返回标量值的函数,若函数含单个SELECT语句且可更新，则返回的数据表可更新,若函数含多个SELECT语句或一个不可更新的SELECT语句，则返回的数据表不可更新,6,10.2 创建用户自定义函数,10.2.1 创建用户自定义函数,CREATE FUNCTION ower-name, function-name (parameter-name scalar_parameter_datetype =default,n),参数名，局部变量,参数数据类型,7,RETURNS scalar_return_datatype WITH ,n AS BEGIN sql-statement RETURN scalar-expression END,用户自定义函数的返回值,函数中返回值的表达式,8,SCHEMABINING选项可将函数绑定到它所引用的数据库对象,function-option,ENCRYPTION选项可以实现 SQL Server加密包含CREATE FUNCTION语句文本的系统表列,9,例：在Northwind库上创建自定义函数my_function1，该函数实现输入代表商品年销售额的money类型参数的值后返回字符串，如果年销售额大于10000，返回“热销商品”，否则返回“非热销商品”,10,USE Northwind go CREATE FUNCTION my_function1 (moneyinput money) RETURNS nvarchar(5),11,BEGIN DECLARE returnstring nvarchar(5) IF moneyinput10000 SET returnstring=非热销商品 ELSE SET returnstring=热销商品 RETURN returnstring END,12,在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,13,14,10.2.2 查看用户自定义函数,自定义函数的名称保存在sysobjects系统表中,创建自定义函数的源代码保存在syscomments系统表中,15,1. 使用系统存储过程查看,EXEC sp_help(sp_helptext) ,例：用系统存储过程sp_helptext 查看用户自定义函数my_funciton1的定义文本信息 USE Northwind go EXEC sp_helptext my_function1 go,16,17,2. 使用系统表或系统信息架构视图,在SQL Server中存在三个信息架构视图报告有关用户自定义函数的信息,ROUTINES PARAMETERS ROUTINE_COLUMNS,这些信息架构视图也是基于系统表sysobjects和syscomments实现的,18,例：使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。 USE Northwind SELECT * from sysobjects WHERE type=FN go,19,10.3 用户自定义函数类型,自定义函数有三种类型,标量函数,内嵌表值函数,多语句表值函数,20,10.3.1 标量函数,标量函数返回在 RETURNS子句中定义的数据类型的单个数据值,标量函数可重复调用,21,例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为 : ，并允许用户自行定义分隔符,22,CREATE FUNCTION my_function2 (DATE datetime, separator nvarchar(2)=:) RETURNS nvarchar(20) BEGIN DECLARE returnstring nvarchar(20),23,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,24,如何调用新建立的标量函数呢？ 可以运行如下命令： SELECT dbo.my_function2(GETDATE(),-),返回当天的年、月和日,25,10.3.2 内嵌表值函数,在内嵌表值函数中，RETURN子句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌表值函数所返回的表,可替代视图，可用在T-SQL查询中允许表或视图表达式的地方,26,视图：受限于单个 SELECT 语句，不允许包含用户自己提供的参数,内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强,返回表的内嵌表值函数还可替换返回单个结果集的存储过程,27,CREATE FUNCTION ower-name, function-name (parameter-name scalar_parameter_datetype =default,n),28,RETURNS TABLE WITH ,n AS RETURN SELECT_STATEMENT,定义内嵌表值函数返回值返回值的单个select语句,指定表值函数的返回值为表,29,例: 在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函数my_function3,30,USE Northwind Go CREATE FUNCTION my_function3 (RegionParameter nvarchar(25) RETURNS table AS,31,RETURN (SELECT City, CompanyName, ContactName, Customers AS Relationship FROM Customers WHERE City=RegionParameter UNION SELECT City, CompanyName, ContactName, Suppliers FROM Suppliers WHERE City=RegionParameter),32,下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息 SELECT * FROM dbo.my_function3(Paris) go,33,如果用户想通过视图“Customer and Suppliers by City”来实现同 样的目的，则使用如下查询命令： SELECT * FROM Customer and Suppliers by City WHERE City=Paris go,34,10.3.3 多语句表值函数,多语句表值函数的主体中允许使用的语句,赋值 控制流 DECLARE SELECT 游标操作 INSERT UPDATE DELETE EXECUTE,35,CREATE FUNCTION ower-name, function-name (parameter-name scalar_parameter_datetype =default,n),36,RETURNS return_variable TABLE WITH ,n AS BEGIN function_body RETURN END,函数体，由sql语句组成，返回表变量,是TABLE变量，用于存储作为函数值返回的行,37,TABLE:= (column_definition|table_constraint ,),表的列声明,表约束,38,例: 在Northwind库上利用视图Customer and Suppliers by City，建立一个可以根据输入的城市名和用户要求返回所有该城市的客户或供应商的信息的多语句表值函数my_function4，并将其与上例建立的内嵌表值函数my_function3对比,39,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),40,AS BEGIN INSERT my_table SELECT City, CompanyName, ContactName FROM Customer and Suppliers by City WHERE City=RegionParameter AND Relationship=SelectParameter RETURN END,41,下面举例说明使用多语句表值函数my_function4分别获取来自Paris的所有客户和供应商的信息。命令如下： SELECT * FROM dbo.my_function4(Paris,Customers) go SELECT * FROM dbo.my_function4(Paris,Suppliers) go,42,10.4 修改和删除用户自定义函数,10.4.1 修改用户自定义函数,ALTER 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,43,例：修改在Northwind库上创建的自定义函数my_function1，将区分商品是否热销的年销售额由当初的10000提高到20000 ALTER FUNCTION my_function1 (moneyinput money) RETURNS nvarchar(5),44,BEGIN DECLARE returnstring nvarchar(5) IF moneyinput20000 SET returnstring=非热销商品 ELSE SET returnstring=热销商品 RETURN returnstring END,45,10.4.2 删除用户自定义函数,DROP FUNCTION ower-name, function-name,例：删除在Northwind库上创建的自定义函数my_function1 DROP FUNCTION my_function1,46,10.4.3 设置用户自定义函数的权限,设置自定义函数的权限类似于设置表或其他数据库对象的权限,要为用户授予 CREATE FUNCTION 权限,才能进行创建、修改或删除自定义函数的操作,47,对于标量值函数，一般用户在使用此函数前，必须先被授予此函数上的 EXECUTE 权限,48,如果函数是表值函数，则用户在引用函数前必须对函数具有 SELECT权限,49,若CREATE TABLE或ALTER TABLE语句在CHECK约束、DEFAULT约束、计算列中引用了自定义函数，那么该表的所有者也要拥有该函数,