数据库应用基础用户自定义函数市公开课金奖市赛课一等奖课件

复习

1什么是存储过程？2什么是触发器？1第1页第1页1什么是存储过程？系统存储过程当地存储过程例：my_procdure22第2页第2页程序运营3第3页第3页按位置传递参数4第4页第4页使用参数名传送参数值5第5页第5页USEpubsgoCREATETRIGGERmy_trigger1ONauthorsFORINSERTASraiserror(‘你已添加了数据’,10,1)2什么是触发器？insteadofINSERTASraiserror(‘你不能添加数据’,10,1)6第6页第6页触发器作用？约束触发器强制数据完整性数据类型7第7页第7页第九章用户自定义函数

8第8页第8页什么是函数？

是一组编译好Transact-SQL语句，他们能够带一个或一组数值做参数，也能够不带参数，它返回一个数值、数值集合，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。9第9页第9页函数类型

SQLServer支持用户定义函数和内置函数。1内置函数

是一组预定义函数，是Transact-SQL语言一部分，按Transact-SQL参考中定义方式运营且不能修改。10第10页第10页聚合函数函数功效sum(expression)计算一组数据和avg(expression)计算一组数据平均值max(expression)计算一组数据最大值min(expression)计算一组数据最小值count(expression|*)计算总行数11第11页第11页系统函数函数功效CASE表示式计算条件列表，并返回多个表示式也许结果之一CAST(expressionasdata_type)将表示式显式转化为另一个数据类型CONVERT(data_type[(length)],expression[,style])将表示式显式转化为另一个数据类型12第12页第12页日期函数函数功效GETDATE()返回服务器当前系统日期和时间DATAPART(日期元素，日期)返回指定日期一部分，用整数返回YEAR(日期)返回年份(整数)MONTH(日期)返回月份(整数)DAY(日期)返回某月几号整数值13第13页第13页14getdate()得到当前系统日期时间year()得到日期参数年份14第14页第14页1515第15页第15页169.1用户自定义函数概述2它能够拥有零个、一个或多个参数，必须有返回值1用户定义函数是用户定义Transact-SQL函数3返回值能够是单个数值（标量值），也能够是一个表概念

16第16页第16页171创建环节与办法9.2创建用户自定义函数17第17页第17页18例：创建一个自定义函数，计算某人到现在为止工龄工资思绪：工龄工资=已工作年数*工作一年工龄工资已工作年数=当前时间–开始参与工作时间工龄工资=(当前时间-开始参与工作时间)*年工龄工资18第18页第18页1919第19页第19页例：在Northwind库上创建自定义函数my_function1，该函数实现输入代表商品年销售额money类型参数值后返回字符串，假如年销售额不小于10000，返回“热销商品”，不然返回“非热销商品”20第20页第20页USENorthwindgoCREATE

FUNCTION

my_function1

(@moneyinputmoney)RETURNS

nvarchar(5)返回值数据类型21第21页第21页IF@moneyinput<10000SET@returnstring='非热销商品'BEGINENDDECLARE@returnstringnvarchar(5)ELSESET@returnstring='热销商品'RETURN@returnstring返回变量22第22页第22页执行命令23第23页第23页在Northwind库上有一个统计各种产品在1997年销售额视图，通过它来引用新建my_function1，查看哪些商品属于热销商品视图函数调用24第24页第24页25商品名称销售额销售情况25第25页第25页如何调用用户自定义函数？Use数据库goSelect函数名(详细参数值|字段名)[from表名][where条件]26第26页第26页27用户自定义函数名称应是惟一一个函数最多能够定义1024个参数，每个参数前用“@”符号标明参数作用范围是整个函数参数只能替换常量，不能替换表名、列名或其它数据库对象名称用户自定义函数不支持输出参数注意：27第27页第27页2查看用户自定义函数

自定义函数名称保留在sysobjects系统表中创建自定义函数源代码保留在syscomments系统表中28第28页第28页（1）使用系统存储过程查看EXECsp_help(sp_helptext)<function-name>例：用系统存储过程sp_helptext查看用户自定义函数my_funciton1定义文本信息USENorthwindgoEXECsp_helptextmy_function1go29第29页第29页30第30页第30页（2）使用系统表或系统信息架构视图在SQL

Server中存在三个信息架构视图汇报相关用户自定义函数信息ROUTINESPARAMETERSROUTINE_COLUMNS这些信息架构视图也是基于系统表sysobjects和syscomments实现31第31页第31页例：使用系统表sysobjects查看数据库Northwind上存在所有用户自定义函数相关信息。32第32页第32页9.3用户自定义函数类型标量函数自定义函数有三种类型内嵌表值函数多语句表值函数33第33页第33页1标量函数标量函数返回在RETURNS子句中定义数据类型单个数据值标量函数可重复调用34第34页第34页例：my_function135第35页第35页36例：创建标量函数，要求将当前系统日期转化为年月日格式字符串并返回，且默认分隔符为‘::’，并允许用户自行定义分隔符convert(数据类型，字段名，日期样式编号)36第36页第36页3737第37页第37页BEGINDECLARE

@returnstringnvarchar(20)CREATE

FUNCTIONmy_function2(@datedatetime,

@separatornvarchar(2)='::')RETURNSnvarchar(20)38第38页第38页SET@returnstring='今天是'+CONVERT(nvarchar(5),datepart(year,@date))+‘年’+@separator+CONVERT(nvarchar(5),datepart(month,@date))+‘月’+@separator+CONVERT(nvarchar(5),datepart(day,@date))+'日‘RETURN@returnstringEND39第39页第39页执行程序40第40页第40页41第41页第41页2内嵌表值函数

在内嵌表值函数中，RETURN子句中包括有一条单独SELECT语句，该语句结果构成了内嵌表值函数所返回表可替换视图，可用在T-SQL查询中允许表或视图表示式地方42第42页第42页视图：受限于单个SELECT语句，不允许包括用户自己提供参数内嵌表值函数：可包括附加语句，使函数所包括逻辑比视图逻辑更强返回表内嵌表值函数还可替换返回单个结果集存储过程43第43页第43页例:在Northwind数据库上建立一个能够依据输入都市名返回所有该都市客户和供应商信息内嵌表值函my_function344第44页第44页45假如已有一个视图（都市中客户与供应商列表）“CustomerandSuppliersbyCity”45第45页第45页USENorthwindGoCREATE

FUNCTIONmy_function3(@RegionParameternvarchar(25))RETURNStableas46第46页第46页RETURNSELECTCity,CompanyName,ContactName,'Suppliers‘FROMSuppliersWHERECity=@RegionParameter)(SELECTCity,CompanyName,ContactName,'Customers'AS

Relationship

FROMCustomersWHERECity=@RegionParameterUNION47第47页第47页执行命令48第48页第48页49下面示例使用新建立内嵌表值函数my_function3来获取“巴黎”市所有客户和供应商信息49第49页第49页3多语句表值函数多语句表值函数主体中允许使用语句赋值控制流DECLARESELECT游标操作INSERTUPDATEDELETEEXECUTE50第50页第50页51例:在Northwind库上利用视图Customer

andSuppliersbyCity（某一都市客户与供应商），建立一个能够依据输入都市名和用户，要求返回所有该都市客户或供应商信息多语句表值函数my_function4，并将其与上例建立内嵌表值函数my_function3对比51第51页第51页下面举例阐明使用多语句表值函数my_function4分别获取来自Paris所有客户和供应商信息。5252第52页第52页9.4修改和删除用户自定义函数1修改用户自定义函数

ALTER

FUNCTION[ower-name,]function-name([{@parameter-namescalar-parameter-datetype[=default]}[,…n]])RETURNSscalar-return-datatype[WITH<function-option>[,…n]][AS]BEGINsql-statementRETURNscalar-expressionEND53第53页第53页例：修改在Northwind库上创建自定义函数my_function1，将区别商品是否热销年销售额由当初10000提升到0ALTER

FUNCTIONmy_function1(@moneyinputmoney)RETURNSnvarchar(5)54第54页第54页BEGINDECLARE@returnstringnva