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

1、12程序运行程序运行345USE pubsgoCREATE TRIGGER my_trigger1ON authorsFOR INSERTASraiserror(你已添加了数据你已添加了数据,10,1)instead of INSERTASraiserror(你不能添加数据你不能添加数据,10,1)67第第九九章章用户自定义函数用户自定义函数 89Transact-SQL语言的一部分，按语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。参考中定义的方式运行且不能修改。1011121314getdate()得到当前系统日期时间得到当前系统日期时间year()得到日期参数的年

2、份得到日期参数的年份141515169.1 用户自定义函数概述用户自定义函数概述 2 2 它可以拥有零个、一个或多个参它可以拥有零个、一个或多个参数，必须有返回值数，必须有返回值1 1 用户定义函数是用户定义的用户定义函数是用户定义的Transact-SQLTransact-SQL函数函数3 3 返回值可以是单个数值（标量返回值可以是单个数值（标量值），也可以是一个表值），也可以是一个表概念概念 16171 创建步骤与方法创建步骤与方法 9.2 创建用户自定义函数创建用户自定义函数1718例：创建一个自定义函数，计算某例：创建一个自定义函数，计算某人到现在为止的工龄工资人到现在为止的工龄工资思

3、路：思路：工龄工资工龄工资=已工作年数已工作年数*工作一年的工龄工资工作一年的工龄工资已工作年数已工作年数=当前时间当前时间 开始参加工作时间开始参加工作时间工龄工资工龄工资=(当前时间当前时间-开始参加工作时间开始参加工作时间)*年工龄工资年工龄工资181919例：例：在在NorthwindNorthwind库上创建自库上创建自定义函数定义函数my_function1my_function1，该，该函数实现输入代表商品年销函数实现输入代表商品年销售额的售额的moneymoney类型参数的值后类型参数的值后返回返回字符串字符串，如果年销售额，如果年销售额大于大于1000010000，返回，返回

4、“热销商品热销商品”，否则返回否则返回“非热销商品非热销商品”20USE NorthwindgoCREATECREATE FUNCTIONFUNCTION mymy_functionfunction1 (moneyinput money)RETURNS nvarchar(5)返回值的数返回值的数据类型据类型21IF moneyinput10000SET returnstring=非热销商品非热销商品BEGINENDDECLAREDECLARE returnstring nvarchar(5)ELSESET returnstring=热销商品热销商品RETURN returnstring返回的变

5、量返回的变量2223在在NorthwindNorthwind库上有一个记录各种产品在库上有一个记录各种产品在20192019年年销售额的视图销售额的视图，通过它来，通过它来引用引用新建新建的的my_function1my_function1，查看哪些商品属于热销，查看哪些商品属于热销商品商品视图视图函数调用函数调用2425商品名称商品名称销售额销售额销售情况销售情况25Use 数据库数据库goSelect 函数名函数名(具体参数值具体参数值|字段名字段名)from 表名表名where 条件条件26271.用户自定义函数的名称应是惟一的用户自定义函数的名称应是惟一的2.一个函数最多可以定义一个函

6、数最多可以定义1024 个参数，每个参数，每个参数前用个参数前用“”符号标明符号标明3.参数的作用范围是整个函数参数的作用范围是整个函数4.参数只能替代常量，不能替代表名、列参数只能替代常量，不能替代表名、列名或其它数据库对象的名称名或其它数据库对象的名称5.用户自定义函数不支持输出参数用户自定义函数不支持输出参数注意：注意：272 查看用户自定义函数查看用户自定义函数 自定义函数的自定义函数的名称名称保存在保存在sysobjects系统表中系统表中创建自定义函数的创建自定义函数的源代码源代码保保存在存在syscomments系统表中系统表中 28（1）使用系统存储过程查看）使用系统存储过程查

7、看EXECEXEC sp_help(sp_helptext)例：例：用系统存储过程用系统存储过程sp_helptext 查看用户自定义函数查看用户自定义函数my_funciton1的定义文本信息的定义文本信息USE NorthwindUSE NorthwindgogoEXEC sp_helptext my_function1EXEC sp_helptext my_function1go go 2930（2）使用系统表或系统信息架构视图）使用系统表或系统信息架构视图 在在SQLSQL ServerServer中中存在三个存在三个信息信息架构视图架构视图报告报告有关用户自定有关用户自定义函数的信息

8、义函数的信息ROUTINESROUTINESPARAMETERSPARAMETERSROUTINEROUTINE_COLUMNSCOLUMNS这些这些信息架构视图信息架构视图也是基于系统也是基于系统表表sysobjectssysobjects和和syscommentssyscomments实现的实现的 31例：例：使用系统表使用系统表sysobjects查看数据库查看数据库Northwind上存在的所有用户自定义函数的上存在的所有用户自定义函数的相关信息。相关信息。329.3 用户自定义函数类型用户自定义函数类型 标量函数标量函数自定自定义函义函数有数有三种三种类型类型内嵌表值函数内嵌表值函数

9、多语句表值函数多语句表值函数 331 标量函数标量函数 标量函数标量函数返回返回在在 RETURNSRETURNS子子句中定义的数句中定义的数据类型的单个据类型的单个数据值数据值标量函标量函数可重数可重复调用复调用343536例：例：创建标量函数，要求将当前创建标量函数，要求将当前系系统日期统日期转化为转化为年月日年月日格式的字符串格式的字符串并返回，且默认的分隔符为并返回，且默认的分隔符为 :，并允许用户自行定义并允许用户自行定义分隔符分隔符convert(数据类型，字段名，日期样式编号数据类型，字段名，日期样式编号)363737BEGINDECLAREDECLARE returnstrin

10、g nvarchar(20)CREATECREATE FUNCTIONFUNCTION my_function2(date date datetime,separator nvarchar(2)=:)RETURNS nvarchar(20)38SET returnstring=今天是今天是+CONVERTCONVERT(nvarchar(5),datepart(year,date)+年年+separator+CONVERTCONVERT(nvarchar(5),datepart(month,date)+月月+separator+CONVERTCONVERT(nvarchar(5),datepa

11、rt(day,date)+日日RETURN returnstringEND3940412 内嵌表值函数内嵌表值函数 在内嵌表值函数中，在内嵌表值函数中，RETURNRETURN子句子句中包含有一条单独的中包含有一条单独的SELECTSELECT语句，语句，该语句的结果构成了内嵌表值函该语句的结果构成了内嵌表值函数所返回的表数所返回的表 可替代视图，可用在可替代视图，可用在T-SQL查询中允许表或视查询中允许表或视图表达式的地方图表达式的地方 42视图：视图：受限于单个受限于单个 SELECTSELECT 语句，语句，不允许包含用户自己提供的参数不允许包含用户自己提供的参数内嵌表值函数内嵌表值函

12、数：可包含附加的语可包含附加的语句，使函数所包含的逻辑比视图句，使函数所包含的逻辑比视图的逻辑更强的逻辑更强返回表的内嵌表值函数还可替换返回表的内嵌表值函数还可替换返回单个结果集的返回单个结果集的存储过程存储过程43例例:在在NorthwindNorthwind数据库上建立一个数据库上建立一个可以根据可以根据输入的城市名输入的城市名返回所有该返回所有该城市的客户和供应商的信息的内嵌城市的客户和供应商的信息的内嵌表值函表值函my_function3my_function34445如果已有一个如果已有一个视图视图（城市中客户与供应商列表）（城市中客户与供应商列表）“Customer and Sup

13、pliers by City”45USE NorthwindGoCREATECREATE FUNCTIONFUNCTION my_function3(RegionParameterRegionParameter nvarchar(25)RETURNS tableas46RETURNSELECT City,CompanyName,ContactName,SuppliersFROM SuppliersWHERE City=RegionParameter)(SELECT City,CompanyName,ContactNameContactName,CustomersCustomers ASAS R

14、elationship FROM CustomersWHERE City=RegionParameterUNION 474849下面示例使用新建立的内嵌表值函数下面示例使用新建立的内嵌表值函数my_function3my_function3来获取来获取“巴黎巴黎”市的所有市的所有客户和供应商的信息客户和供应商的信息 493 多语句表值函数多语句表值函数 多语句表值多语句表值函数的主体函数的主体中允许使用中允许使用的语句的语句 赋值赋值控制流控制流DECLAREDECLARESELECTSELECT游标操作游标操作INSERTINSERTUPDATEUPDATEDELETEDELETEEXECU

15、TEEXECUTE5051例例:在在Northwind库上利用视图库上利用视图CustomerCustomer andand Suppliers by City（某一城市的客户（某一城市的客户与供应商），建立一个可以根据与供应商），建立一个可以根据输入的输入的城市名和用户，城市名和用户，要求要求返回返回所有该城市的所有该城市的客户或供应商的信息的多语句表值函数客户或供应商的信息的多语句表值函数my_function4，并将其与上例建立的内，并将其与上例建立的内嵌表值函数嵌表值函数my_function3对比对比 51下面举例说明使用多语句表值函数下面举例说明使用多语句表值函数my_functi

16、on4my_function4分别获取来自分别获取来自Paris的所的所有客户和供应商的信息。有客户和供应商的信息。52529.4 修改和修改和删除删除用户自定义函数用户自定义函数 1 修改用户自定义函数修改用户自定义函数 ALTERALTER FUNCTIONFUNCTION ower-name,function-name(parameter-name scalar-parameter-datetype=default,n)RETURNS scalar-return-datatypeWITH ,n ASBEGINsql-statementRETURN scalar-expressionEND

17、53例：例：修改在修改在Northwind库上创建库上创建的自定义函数的自定义函数my_function1，将，将区分商品是否热销的年销售额由区分商品是否热销的年销售额由当初的当初的10000提高到提高到20000ALTERALTER FUNCTIONFUNCTION my_function1(moneyinput money)RETURNS nvarchar(5)54BEGINDECLAREDECLARE returnstring nvarchar(5)IF moneyinput20000SET returnstring=非热销商品非热销商品ELSESET returnstring=热销商品热销商品RETURN returnstringEND555657