【文档说明】数据库应用基础第九章用户自定义函数58张课件.ppt，共(58)页，560.500 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92499.html

以下为本文档部分文字说明：

1复习1什么是存储过程？2什么是触发器？21什么是存储过程？系统存储过程本地存储过程例：my_procdure23程序运行4按位置传递参数5使用参数名传送参数值6USEpubsgoCREATETRIGGERmy_trigger1ONauthorsFORINSERTASraiserror(‘你

已添加了数据’,10,1)2什么是触发器？insteadofINSERTASraiserror(‘你不能添加数据’,10,1)7触发器的作用？约束触发器强制数据完整性数据类型8第九章用户自定义函数9什么是函数？是一组编译好的Transact-SQL语句，他们可以带一个

或一组数值做参数，也可以不带参数，它返回一个数值、数值集合，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。10函数类型SQLServer2019支持用户定义函数和内置函数。1内置函数是一组预定义的函数，是Transact-SQL语言的一部分，按Tra

nsact-SQL参考中定义的方式运行且不能修改。11聚合函数12系统函数13日期函数1414getdate()得到当前系统日期时间year()得到日期参数的年份151516169.1用户自定义函数概述2它可以拥有零个、一个或多个参数，必须有返回值1用户定义函

数是用户定义的Transact-SQL函数3返回值可以是单个数值（标量值），也可以是一个表概念17171创建步骤与方法9.2创建用户自定义函数1818例：创建一个自定义函数，计算某人到现在为止的工龄工资思

路：工龄工资=已工作年数*工作一年的工龄工资已工作年数=当前时间–开始参加工作时间工龄工资=(当前时间-开始参加工作时间)*年工龄工资191920例：在Northwind库上创建自定义函数my_function1，该函数实现输入代表商品年

销售额的money类型参数的值后返回字符串，如果年销售额大于10000，返回“热销商品”，否则返回“非热销商品”21USENorthwindgoCREATEFUNCTIONmy_function1(moneyinputmoney)RETUR

NSnvarchar(5)返回值的数据类型22IFmoneyinput<10000SETreturnstring='非热销商品'BEGINENDDECLAREreturnstringnvarchar(5)ELSESETreturnstring='热销商

品'RETURNreturnstring返回的变量23执行命令24在Northwind库上有一个记录各种产品在2019年销售额的视图，通过它来引用新建的my_function1，查看哪些商品属于热销商品视图函数调用2525商品名称销售额销售情况26如何调用用户自定义函数？

Use数据库goSelect函数名(具体参数值|字段名)[from表名][where条件]27271.用户自定义函数的名称应是惟一的2.一个函数最多可以定义1024个参数，每个参数前用“”符号标明3.参数的作用范围是整个函数4.参数只能替

代常量，不能替代表名、列名或其它数据库对象的名称5.用户自定义函数不支持输出参数注意：282查看用户自定义函数自定义函数的名称保存在sysobjects系统表中创建自定义函数的源代码保存在syscomments系统表中29（1）使用系统存储过程查看EXECsp_he

lp(sp_helptext)<function-name>例：用系统存储过程sp_helptext查看用户自定义函数my_funciton1的定义文本信息USENorthwindgoEXECsp_helptextmy_function1go3031（2）使用系统表或系统信息

架构视图在SQLServer中存在三个信息架构视图报告有关用户自定义函数的信息ROUTINESPARAMETERSROUTINE_COLUMNS这些信息架构视图也是基于系统表sysobjects和syscomments实现的32例：使用

系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。339.3用户自定义函数类型标量函数自定义函数有三种类型内嵌表值函数多语句表值函数341标量函数标量函数返回在RETURNS子句中定义的数据类型的

单个数据值标量函数可重复调用35例：my_function13636例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为‘::’，并允许用户自行定义分隔符convert(数据类型，字段名，日期样式编号)3737

38BEGINDECLAREreturnstringnvarchar(20)CREATEFUNCTIONmy_function2(datedatetime,separatornvarchar(2)='::')RETURNSnvarchar(

20)39SETreturnstring='今天是'+CONVERT(nvarchar(5),datepart(year,date))+‘年’+separator+CONVERT(nvarchar(5),datepart(mont

h,date))+‘月’+separator+CONVERT(nvarchar(5),datepart(day,date))+'日‘RETURNreturnstringEND40执行程序41422内嵌表值函数在内嵌表值函数中，RETURN子句中包含有一条单独的

SELECT语句，该语句的结果构成了内嵌表值函数所返回的表可替代视图，可用在T-SQL查询中允许表或视图表达式的地方43视图：受限于单个SELECT语句，不允许包含用户自己提供的参数内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强返回表

的内嵌表值函数还可替换返回单个结果集的存储过程44例:在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函my_function34545如果已有一个视图（

城市中客户与供应商列表）“CustomerandSuppliersbyCity”46USENorthwindGoCREATEFUNCTIONmy_function3(RegionParameternvarchar(25))RETURNStableas47RETURNSELEC

TCity,CompanyName,ContactName,'Suppliers‘FROMSuppliersWHERECity=RegionParameter)(SELECTCity,CompanyName,ContactName,'Customers'ASR

elationshipFROMCustomersWHERECity=RegionParameterUNION48执行命令4949下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息503多语句表值函数多语句表值函数的主体中允许使用

的语句赋值控制流DECLARESELECT游标操作INSERTUPDATEDELETEEXECUTE5151例:在Northwind库上利用视图CustomerandSuppliersbyCity（某一城市的客户与供应商）

，建立一个可以根据输入的城市名和用户，要求返回所有该城市的客户或供应商的信息的多语句表值函数my_function4，并将其与上例建立的内嵌表值函数my_function3对比52下面举例说明使用多语句表值函数my_function4分别获取来自Paris的所有客户和供应商的信息

。52539.4修改和删除用户自定义函数1修改用户自定义函数ALTERFUNCTION[ower-name,]function-name([{parameter-namescalar-parameter-datetype[=default]}[,…n]])RETURNSsc

alar-return-datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND54例：修改在Northwind库上创建的自定义函数my_function1，将区分商

品是否热销的年销售额由当初的10000提高到20000ALTERFUNCTIONmy_function1(moneyinputmoney)RETURNSnvarchar(5)55BEGINDECLAREreturnstringnvarchar(5)IFmoneyinput<20000SETre

turnstring='非热销商品'ELSESETreturnstring='热销商品'RETURNreturnstringEND56练习：在学生成绩表上创建用户自定义函数my_function1,该

函数实现通过输入某一门成绩来返回字符串。如输入大于或等于90的成绩，就返回“优秀”字符串；如输入的成绩大于等于60小于90，就返回“通过”字符串，否则返回“不通过”字符串。57