16--用户自定义函数.pptVIP

SQL Server--chap11--第十一章 用户自定义函数 * 第16章 用户自定义函数 16.1 用户自定义函数的基本概念 SQL Server允许创建用户定义函数 用户定义函数是可返回值的例程 用户定义函数种类 返回可更新数据表的函数 返回不可更新数据表的函数 返回标量值的函数 若函数含单个SELECT语句且可更新，则返回的数据表可更新 若函数含多个SELECT语句或一个不可更新的SELECT语句，则返回的数据表不可更新 16.2 创建用户自定义函数 16.2.1 创建用户自定义函数 CREATE FUNCTION [owner-name,] function-name ([{@parameter-name scalar_parameter_datetype [=default]}[,…n]]) 参数名，局部变量 参数数据类型 RETURNS scalar_return_datatype [WITH function-option [,…n] ] [AS] BEGIN sql-statement RETURN scalar-expression END 用户自定义函数的返回值 函数中返回值的表达式 SCHEMABINING选项可将函数绑定到它所引用的数据库对象 function-option ENCRYPTION选项可以实现 SQL Server加密包含CREATE FUNCTION语句文本的系统表列 例：在Northwind库上创建自定义函数my_function1，通过向该函数输入代表商品年销售额的money类型参数的大小返回字符串，若年销售额大于10000，返回“热销商品”，否则返回“非热销商品” USE Northwind go CREATE FUNCTION my_function1 (@moneyinput money) RETURNS nvarchar(5) BEGIN DECLARE @returnstring nvarchar(5) IF @moneyinput10000 SET @returnstring=非热销商品 ELSE SET @returnstring=热销商品 RETURN @returnstring END 在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’ 16.2.2 查看用户自定义函数 自定义函数的名称保存在sysobjects系统表中 创建自定义函数的源代码保存在syscomments系统表中 1. 使用系统存储过程查看 EXEC sp_help(sp_helptext) function-name 例：用系统存储过程sp_helptext 查看用户自定义函数my_funciton1的定义文本信息 USE Northwind go EXEC sp_helptext my_function1 go 2. 使用系统表或系统信息架构视图 在SQL Server中存在三个信息架构视图报告有关用户自定义函数的信息 ROUTINES PARAMETERS ROUTINE_COLUMNS 这些信息架构视图也是基于系统表sysobjects和syscomments实现的 例：使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。 ? USE Northwind SELECT * from sysobjects WHERE type=FN go 16.3 用户自定义函数类型 自定义函数有三种类型 标量函数 内嵌表值函数 多语句表值函数 16.3.1 标量函数 标量函数返回在 RETURNS子句中定义的数据类型的单个数据值 标量函数可重复调用 例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为 ‘ :: ’ ，并允许用户自行定义分隔符 CREATE FUNCTION my_function2 (@DATE datetime, @separator nvarchar(2)=::) RETURNS nvarchar(20) BEGIN DECLARE @returnstring nvarchar(20) SET @returnstring=