数据库应用基础用户自定义函数市公开课一等奖百校联赛特等奖课件.pptx

复习复习 1 什么是存放过程？什么是存放过程？2 什么是触发器？什么是触发器？1第1页1 什么是存放过程？什么是存放过程？系统存放过程系统存放过程当地存放过程当地存放过程例：例：my_procdure22第2页程序运行程序运行3第3页按位置传递参数按位置传递参数4第4页使用参数名传送参数值使用参数名传送参数值5第5页USE pubsgoCREATE TRIGGER my_trigger1ON authorsFOR INSERTASraiserror(你已添加了数据你已添加了数据,10,1)2 什么是触发器？什么是触发器？instead of INSERTASraiserror(你不能添加数据你不能添加数据,10,1)6第6页触发器作用？触发器作用？约束约束触发器触发器强制数据完整性强制数据完整性数据类型数据类型7第7页第第九九章章用户自定义函数用户自定义函数 8第8页什么是函数？什么是函数？是一组编译好是一组编译好Transact-SQLTransact-SQL语句，语句，他们能够带一个或一组数值做参数，他们能够带一个或一组数值做参数，也能够不带参数，它返回一个数值、也能够不带参数，它返回一个数值、数值集合，或执行一些操作。数值集合，或执行一些操作。函数能够重复执行一些操作，从而函数能够重复执行一些操作，从而防止不停重写代码。防止不停重写代码。9第9页函数类型函数类型 SQL Server SQL Server 支持用户定义函数和支持用户定义函数和内置函数。内置函数。1 内置函数内置函数 是一组预定义函数，是是一组预定义函数，是Transact-SQL语言一部分，按语言一部分，按Transact-SQL参参考中定义方式运行且不能修改。考中定义方式运行且不能修改。10第10页聚合函数聚合函数函数函数函数函数n n功效功效sum(expression)sum(expression)n n计算一组数据和计算一组数据和avg(expression)avg(expression)n n计算一组数据平均值计算一组数据平均值max(expression)max(expression)n n计算一组数据最大值计算一组数据最大值min(expression)min(expression)n n计算一组数据最小值计算一组数据最小值count(expression|*)count(expression|*)计算总行数计算总行数计算总行数计算总行数11第11页系统函数系统函数函数函数函数函数n n功效功效n nCASECASE表示式表示式n n计算条件列表，并返回计算条件列表，并返回多个表示式可能结果之多个表示式可能结果之一一CASTCAST(expression as(expression as data_type)data_type)n n将表示式显式转化为另将表示式显式转化为另一个数据类型一个数据类型CONVERTCONVERT(data_type(length),(data_type(length),expression,style)expression,style)n n将表示式显式转化为另将表示式显式转化为另一个数据类型一个数据类型12第12页日期函数日期函数函数函数函数函数n n功效功效GETDATE()GETDATE()n n返回服务器当前系统日期返回服务器当前系统日期和时间和时间DATAPART(DATAPART(日期元素，日期元素，日期元素，日期元素，日期日期日期日期)n n返回指定日期一部分，用返回指定日期一部分，用整数返回整数返回YEAR(YEAR(日期日期日期日期)返回年份返回年份返回年份返回年份(整数整数整数整数)MONTH(MONTH(日期日期日期日期)返回月份返回月份返回月份返回月份(整数整数整数整数)DAY(DAY(日期日期日期日期)n n返回某月几号整数值返回某月几号整数值13第13页14getdate()得到当前系统日期时间得到当前系统日期时间year()得到日期参数年份得到日期参数年份14第14页1515第15页169.1 用户自定义函数概述用户自定义函数概述 2 2 它能够拥有零个、一个或多个参它能够拥有零个、一个或多个参数，必须有返回值数，必须有返回值1 1 用户定义函数是用户定义用户定义函数是用户定义Transact-SQLTransact-SQL函数函数3 3 返回值能够是单个数值（标量值）返回值能够是单个数值（标量值），也能够是一个表，也能够是一个表概念概念 16第16页171 创建步骤与方法创建步骤与方法 9.2 创建用户自定义函数创建用户自定义函数17第17页18例：创建一个自定义函数，计算某例：创建一个自定义函数，计算某人到现在为止工龄工资人到现在为止工龄工资思绪：思绪：工龄工资工龄工资=已工作年数已工作年数*工作一年工龄工资工作一年工龄工资已工作年数已工作年数=当前时间当前时间 开始参加工作时间开始参加工作时间工龄工资工龄工资=(当前时间当前时间-开始参加工作时间开始参加工作时间)*年工龄工资年工龄工资18第18页1919第19页例：例：在在NorthwindNorthwind库上创建自库上创建自定义函数定义函数my_function1my_function1，该，该函数实现输入代表商品年销函数实现输入代表商品年销售额售额moneymoney类型参数值后返回类型参数值后返回字符串字符串，假如年销售额大于，假如年销售额大于1000010000，返回，返回“热销商品热销商品”，不然返回不然返回“非热销商品非热销商品”20第20页USE NorthwindgoCREATECREATE FUNCTIONFUNCTION mymy_functionfunction1 (moneyinput money)RETURNS nvarchar(5)返回值数据返回值数据类型类型21第21页IF moneyinput10000SET returnstring=非热销商品非热销商品BEGINENDDECLAREDECLARE returnstring nvarchar(5)ELSESET returnstring=热销商品热销商品RETURN returnstring返回变量返回变量22第22页执行命令执行命令23第23页在在NorthwindNorthwind库上有一个统计各种产品在库上有一个统计各种产品在19971997年年销售额视图销售额视图，经过它来，经过它来引用引用新建新建my_function1my_function1，查看哪些商品属于热销商，查看哪些商品属于热销商品品视图视图函数调用函数调用24第24页25商品名称商品名称销售额销售额销售情况销售情况25第25页怎样调用用户自定义函数？怎样调用用户自定义函数？Use 数据库数据库goSelect 函数名函数名(详细参数值详细参数值|字段名字段名)from 表名表名where 条件条件26第26页271.用户自定义函数名称应是惟一用户自定义函数名称应是惟一2.一个函数最多能够定义一个函数最多能够定义1024 个参数，每个参数，每个参数前用个参数前用“”符号标明符号标明3.参数作用范围是整个函数参数作用范围是整个函数4参数只能替换常量，不能替换表名、列参数只能替换常量，不能替换表名、列名或其它数据库对象名称名或其它数据库对象名称5.用户自定义函数不支持输出参数用户自定义函数不支持输出参数注意：注意：27第27页2 查看用户自定义函数查看用户自定义函数 自定义函数自定义函数名称名称保留在保留在sysobjects系统表中系统表中创建自定义函数创建自定义函数源代码源代码保留保留在在syscomments系统表中系统表中 28第28页（1）使用系统存放过程查看）使用系统存放过程查看EXECEXEC sp_help(sp_helptext)例：例：用系统存放过程用系统存放过程sp_helptext 查看用户自定义函数查看用户自定义函数my_funciton1定义文本信息定义文本信息USE NorthwindUSE NorthwindgogoEXEC sp_helptext my_function1EXEC sp_helptext my_function1go go 29第29页30第30页（2）使用系统表或系统信息架构视图）使用系统表或系统信息架构视图 在在SQLSQL ServerServer中中存在三个存在三个信息信息架构视图架构视图汇报汇报相关用户自定相关用户自定义函数信息义函数信息ROUTINESROUTINESPARAMETERSPARAMETERSROUTINEROUTINE_COLUMNSCOLUMNS这些这些信息架构视图信息架构视图也是基于系统也是基于系统表表sysobjectssysobjects和和syscommentssyscomments实现实现 31第31页例：例：使用系统表使用系统表sysobjects查看数据库查看数据库Northwind上存在全部用户自定义函数相关上存在全部用户自定义函数相关信息。信息。32第32页9.3 用户自定义函数类型用户自定义函数类型 标量函数标量函数自定自定义函义函数有数有三种三种类型类型内嵌表值函数内嵌表值函数多语句表值函数多语句表值函数 33第33页1 标量函数标量函数 标量函数标量函数返回返回在在 RETURNSRETURNS子子句中定义数据句中定义数据类型单个数据类型单个数据值值标量函标量函数可重数可重复调用复调用34第34页例：例：my_function135第35页36例：例：创建标量函数，要求将当前创建标量函数，要求将当前系系统日期统日期转化为转化为年月日年月日格式字符串并格式字符串并返回，且默认分隔符为返回，且默认分隔符为 :，并允许用户自行定义并允许用户自行定义分隔符分隔符convert(数据类型，字段名，日期样式编号数据类型，字段名，日期样式编号)36第36页3737第37页BEGINDECLAREDECLARE returnstring nvarchar(20)CREATECREATE FUNCTIONFUNCTION my_function2(date date datetime,separator nvarchar(2)=:)RETURNS nvarchar(20)38第38页SET returnstring=今天是今天是+CONVERTCONVERT(nvarchar(5),datepart(year,date)+年年+separator+CONVERTCONVERT(nvarchar(5),datepart(month,date)+月月+separator+CONVERTCONVERT(nvarchar(5),datepart(day,date)+日日RETURN returnstringEND39第39页执行程序执行程序40第40页41第41页2 内嵌表值函数内嵌表值函数 在内嵌表值函数中，在内嵌表值函数中，RETURNRETURN子句子句中包含有一条单独中包含有一条单独SELECTSELECT语句，语句，该语句结果组成了内嵌表值函数该语句结果组成了内嵌表值函数所返回表所返回表 可替换视图，可用在可替换视图，可用在T-SQL查询中允许表或视查询中允许表或视图表示式地方图表示式地方 42第42页视图：视图：受限于单个受限于单个 SELECTSELECT 语句，语句，不允许包含用户自己提供参数不允许包含用户自己提供参数内嵌表值函数内嵌表值函数：可包含附加语句，可包含附加语句，使函数所包含逻辑比视图逻辑更使函数所包含逻辑比视图逻辑更强强返回表内嵌表值函数还可替换返返回表内嵌表值函数还可替换返回单个结果集回单个结果集存放过程存放过程43第43页例例:在在NorthwindNorthwind数据库上建立一个数据库上建立一个能够依据能够依据输入城市名输入城市名返回全部该城返回全部该城市客户和供给商信息内嵌表值函市客户和供给商信息内嵌表值函my_function3my_function344第44页45假如已经有一个假如已经有一个视图视图（城市中客户与供给商列（城市中客户与供给商列表）表）“Customer and Suppliers by City”45第45页USE NorthwindGoCREATECREATE FUNCTIONFUNCTION my_function3(RegionParameterRegionParameter nvarchar(25)RETURNS tableas46第46页RETURNSELECT City,CompanyName,ContactName,SuppliersFROM SuppliersWHERE City=RegionParameter)(SELECT City,CompanyName,ContactNameContactName,CustomersCustomers ASAS Relationship FROM CustomersWHERE City=RegionParameterUNION 47第47页执行命令执行命令48第48页49下面示例使用新建立内嵌表值函数下面示例使用新建立内嵌表值函数my_function3my_function3来获取来获取“巴黎巴黎”市全部市全部客户和供给商信息客户和供给商信息 49第49页3 多语句表值函数多语句表值函数 多语句表值多语句表值函数主体中函数主体中允许使用语允许使用语句句 赋值赋值控制流控制流DECLAREDECLARESELECTSELECT游标操作游标操作INSERTINSERTUPDATEUPDATEDELETEDELETEEXECUTEEXECUTE50第50页51例例:在在Northwind库上利用视图库上利用视图CustomerCustomer andand Suppliers by City（某一城市客户与（某一城市客户与供给商），建立一个能够依据供给商），建立一个能够依据输入城市输入城市名和用户，名和用户，要求要求返回返回全部该城市客户或全部该城市客户或供给商信息多语句表值函数供给商信息多语句表值函数my_function4，并将其与上例建立内嵌，并将其与上例建立内嵌表值函数表值函数my_function3对比对比 51第51页下面举例说明使用多语句表值函数下面举例说明使用多语句表值函数my_function4my_function4分别获取来自分别获取来自Paris全部全部客户和供给商信息。客户和供给商信息。5252第52页9.4 修改和修改和删除删除用户自定义函数用户自定义函数 1 修改用户自定义函数修改用户自定义函数 ALTERALTER FUNCTIONFUNCTION ower-name,function-name(parameter-name scalar-parameter-datetype=default,n)RETURNS scalar-return-datatypeWITH ,n ASBEGINsql-statementRETURN scalar-expressionEND53第53页例：例修改在修改在Northwind库上创建库上创建自定义函数自定义函数my_function1，将区，将区分商品是否热销年销售额由当初分商品是否热销年销售额由当初10000提升到提升到0ALTERALTER FUNCTIONFUNCTION my_function1(moneyinput money)RETURNS nvarchar(5)54第54页BEGINDECLAREDECLARE returnstring nvarchar(5)IF moneyinput0SET returnstring=非热销商品非热销商品ELSESET returnstring=热销商品热销商品RETURN returnstringEND55第55页练习：练习：在学生成绩表上创建用户自定义函数在学生成绩表上创建用户自定义函数my_function1,该函数实现经过输入某该函数实现经过输入某一门成绩来返回字符串。如输入大于一门成绩来返回字符串。如输入大于或等于或等于90成绩，就返回成绩，就返回“优异优异”字符字符串；如输入成绩大于等于串；如输入成绩大于等于60小于小于90，就返回就返回“经过经过”字符串，不然返回字符串，不然返回“不经过不经过”字符串。字符串。56第56页57第57页