数据库应用基础第九章用户自定义函数ppt课件

1、程序运转程序运转USE pubsgoCREATE TRIGGER my_trigger1ON authorsFOR INSERTASraiserror(他已添加了数据他已添加了数据,10,1)instead of INSERTASraiserror(他不能添加数据他不能添加数据,10,1)第九章第九章用户自定义函数用户自定义函数 14getdate() 得到当前系统日期时间得到当前系统日期时间year() 得到日期参数的年份得到日期参数的年份15169.1 用户自定义函数概述用户自定义函数概述 2 2 它可以拥有零个、一个或多个参它可以拥有零个、一个或多个参数，必需有前往值数，必需有前往值1

2、1 用户定义函数是用户定义的用户定义函数是用户定义的Transact-SQLTransact-SQL函数函数3 3 前往值可以是单个数值标量前往值可以是单个数值标量值，也可以是一个表值，也可以是一个表概念概念 171 创建步骤与方法创建步骤与方法 9.2 创建用户自定义函数创建用户自定义函数18例：创建一个自定义函数，计算某例：创建一个自定义函数，计算某人到如今为止的工龄工资人到如今为止的工龄工资思绪：思绪：工龄工资工龄工资=已任务年数已任务年数 * 任务一年的工龄工资任务一年的工龄工资已任务年数已任务年数=当前时间当前时间 开场参与任务时间开场参与任务时间工龄工资工龄工资=(当前时间当前时间

3、-开场参与任务时间开场参与任务时间) *年工龄工资年工龄工资19例：在例：在Northwind库上创建库上创建自定义函数自定义函数my_function1，该函数实现输入代表商品年该函数实现输入代表商品年销售额的销售额的money类型参数的类型参数的值后前往字符串，假设年销值后前往字符串，假设年销售额大于售额大于10000，前往，前往“热销热销商品，否那么前往商品，否那么前往“非热销非热销商品商品USE NorthwindgoCREATE FUNCTION CREATE FUNCTION my_function1 my_function1 (moneyinput money)RETURNS n

4、varchar(5)前往值的数前往值的数据类型据类型IF moneyinput10000SET returnstring=非热销商品非热销商品BEGINENDDECLARE returnstring DECLARE returnstring nvarchar(5)nvarchar(5)ELSESET returnstring=热销商品热销商品RETURN returnstring前往的变量前往的变量在在Northwind库上有一个记录各种产品在库上有一个记录各种产品在1997年销售额的视图，经过它来援用新建年销售额的视图，经过它来援用新建的的my_function1，查看哪些商品属于热销，查看

5、哪些商品属于热销商品商品视图视图函数调用函数调用25商品称号商品称号销售额销售额销售情况销售情况Use 数据库数据库goSelect 函数名函数名(详细参数值详细参数值|字段名字段名)from 表名表名where 条件条件27 用户自定义函数的称号应是独一的用户自定义函数的称号应是独一的 一个函数最多可以定义一个函数最多可以定义1024 个参数，个参数，每个参数前用每个参数前用“符号标明符号标明 参数的作用范围是整个函数参数的作用范围是整个函数 参数只能替代常量，不能替代表名、参数只能替代常量，不能替代表名、列名或其它数据库对象的称号列名或其它数据库对象的称号 用户自定义函数不支持输出参数用户

6、自定义函数不支持输出参数留意：留意：2 查看用户自定义函数查看用户自定义函数 自定义函数的称号保管在自定义函数的称号保管在sysobjects系统表中系统表中创建自定义函数的源代码保创建自定义函数的源代码保管在管在syscomments系统表中系统表中 1运用系统存储过程查看运用系统存储过程查看EXEC sp_help(sp_helptext) EXEC sp_help(sp_helptext) 例：用系统存储过程例：用系统存储过程sp_helptext 查看用户自定义函数查看用户自定义函数my_funciton1的定义文本信息的定义文本信息USE NorthwindgoEXEC sp_hel

7、ptext my_function1go 2运用系统表或系统信息架构视图运用系统表或系统信息架构视图 在在SQL Server中存在三个信中存在三个信息架构视图报息架构视图报告有关用户自告有关用户自定义函数的信定义函数的信息息ROUTINESROUTINESPARAMETERSPARAMETERSROUTINE_COLUMNSROUTINE_COLUMNS这些信息架构视图也是基于系统这些信息架构视图也是基于系统表表sysobjects和和syscomments实现实现的的 例：运用系统表例：运用系统表sysobjects查看数据库查看数据库Northwind上存在的一切用户自定义函数的上存在的

8、一切用户自定义函数的相关信息。相关信息。9.3 用户自定义函数类型用户自定义函数类型 标量函数标量函数自定自定义函义函数有数有三种三种类型类型内嵌表值函数内嵌表值函数多语句表值函数多语句表值函数 1 标量函数标量函数 标量函数前往标量函数前往在在 RETURNS子子句中定义的数句中定义的数据类型的单个据类型的单个数据值数据值标量函标量函数可反数可反复调用复调用36例：创建标量函数，要求将当前系例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串统日期转化为年月日格式的字符串并前往，且默许的分隔符为并前往，且默许的分隔符为 : ，并允许用户自行定义分隔符并允许用户自行定义分隔符conve

9、rt(数据类型，字段名，日期款式编号数据类型，字段名，日期款式编号)37BEGINDECLARE returnstring nvarchar(20)CREATE FUNCTION CREATE FUNCTION my_function2my_function2(date datetime, separator nvarchar(2)=:)RETURNS nvarchar(20)SET returnstring=今天是今天是+CONVERT(nvarchar(5), datepart(year,date)+年年+separator+CONVERT(nvarchar(5), datepart(mo

10、nth,date)+月月 +separator+CONVERT(nvarchar(5), datepart(day,date)+日日RETURN returnstringEND2 内嵌表值函数内嵌表值函数 在内嵌表值函数中，在内嵌表值函数中，RETURN子子句中包含有一条单独的句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌语句，该语句的结果构成了内嵌表值函数所前往的表表值函数所前往的表 可替代视图，可用在可替代视图，可用在T-SQL查询中允许表或视查询中允许表或视图表达式的地方图表达式的地方 视图：受限于单个视图：受限于单个 SELECT 语语句，不允许包含用户本人提供的句，不允

11、许包含用户本人提供的参数参数内嵌表值函数：可包含附加的语内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图句，使函数所包含的逻辑比视图的逻辑更强的逻辑更强前往表的内嵌表值函数还可交换前往表的内嵌表值函数还可交换前往单个结果集的存储过程前往单个结果集的存储过程例例: :在在NorthwindNorthwind数据库上建立一个数据库上建立一个可以根据输入的城市名前往一切该可以根据输入的城市名前往一切该城市的客户和供应商的信息的内嵌城市的客户和供应商的信息的内嵌表值函表值函my_function3my_function345假设已有一个视图城市中客户与供应商列表假设已有一个视图城市中客户与供应

12、商列表“Customer and Suppliers by CityUSE NorthwindGoCREATE FUNCTION CREATE FUNCTION my_function3my_function3(RegionParameter nvarchar(25)RETURNS tableasRETURNSELECT City, CompanyName, ContactName, SuppliersFROM SuppliersWHERE City=RegionParameter)(SELECT City, CompanyName, ContactName, Customers AS Rel

13、ationship FROM CustomersWHERE City=RegionParameterUNION 49下面例如运用新建立的内嵌表值函数下面例如运用新建立的内嵌表值函数my_function3来获取来获取“巴黎市的一切巴黎市的一切客户和供应商的信息客户和供应商的信息 3 多语句表值函数多语句表值函数 多语句表值多语句表值函数的主体函数的主体中允许运用中允许运用的语句的语句 赋值赋值控制流控制流DECLARESELECT游标操作游标操作INSERTUPDATEDELETEEXECUTE51例例: 在在Northwind库上利用视图库上利用视图Customer and Supplier

14、s by City某一某一城市的客户与供应商，建立一个可以城市的客户与供应商，建立一个可以根据输入的城市名和用户，要求前往一根据输入的城市名和用户，要求前往一切该城市的客户或供应商的信息的多语切该城市的客户或供应商的信息的多语句表值函数句表值函数my_function4，并将其与上，并将其与上例建立的内嵌表值函数例建立的内嵌表值函数my_function3对对比比 下面举例阐明运用多语句表值函数下面举例阐明运用多语句表值函数my_function4分别获取来自分别获取来自Paris的的一切客户和供应商的信息。一切客户和供应商的信息。529.4 修正和删除用户自定义函数修正和删除用户自定义函数

15、1 修正用户自定义函数修正用户自定义函数 ALTER FUNCTION ower-name, function-nameALTER FUNCTION ower-name, function-name( parameter-name ( parameter-name scalar-parameter-datetype scalar-parameter-datetype =default,n)=default,n)RETURNS scalar-return-datatypeRETURNS scalar-return-datatypeWITH ,n WITH ,n ASASBEGINBEGINsql-statementsql-statementRETURN scalar-expressionRETURN scalar-expressionENDEND例：修正在例：修正在Northwind库上创建库上创建的自定义函数的自定义函数my_function1，将，将区分商品能否热销的年销售额由区分商品能否热销的年销售额由当初的当初的10000提高到提高到20000ALTER FUNCTION A