《数据库应用基础第九章用户自定义函数.ppt》由会员分享，可在线阅读，更多相关《数据库应用基础第九章用户自定义函数.ppt（57页珍藏版）》请在三一办公上搜索。
1、1,复习,1 什么是存储过程？2 什么是触发器？,2,1 什么是存储过程？,系统存储过程,本地存储过程,例：my_procdure2,3,程序运行,4,按位置传递参数,5,使用参数名传送参数值,6,USE pubsgo,CREATE TRIGGER my_trigger1,ON authors,FOR INSERTAS,raiserror(你已添加了数据,10,1),2 什么是触发器？,instead of INSERTAS,raiserror(你不能添加数据,10,1),7,触发器的作用？,约束,触发器,强制数据完整性,数据类型,8,第九章用户自定义函数,9,什么是函数？,是一组编译好的Tr
2、ansact-SQL语句，他们可以带一个或一组数值做参数，也可以不带参数，它返回一个数值、数值集合，或执行一些操作。函数能够重复执行一些操作，从而避免不断重写代码。,10,函数类型,SQL Server 2005 支持用户定义函数和内置函数。,1 内置函数 是一组预定义的函数，是Transact-SQL语言的一部分，按Transact-SQL参考中定义的方式运行且不能修改。,11,聚合函数,12,系统函数,13,日期函数,14,14,getdate()得到当前系统日期时间year()得到日期参数的年份,15,15,16,16,9.1 用户自定义函数概述,2 它可以拥有零个、一个或多个参数，必须
3、有返回值,1 用户定义函数是用户定义的Transact-SQL函数,3 返回值可以是单个数值（标量值），也可以是一个表,概念,17,17,1 创建步骤与方法,9.2 创建用户自定义函数,18,18,例：创建一个自定义函数，计算某人到现在为止的工龄工资,思路：工龄工资=已工作年数*工作一年的工龄工资已工作年数=当前时间 开始参加工作时间工龄工资=(当前时间-开始参加工作时间)*年工龄工资,19,19,20,例：在Northwind库上创建自定义函数my_function1，该函数实现输入代表商品年销售额的money类型参数的值后返回字符串，如果年销售额大于10000，返回“热销商品”，否则返回“
4、非热销商品”,21,USE Northwindgo,CREATE FUNCTION my_function1,(moneyinput money),RETURNS nvarchar(5),返回值的数据类型,22,IF moneyinput10000SET returnstring=非热销商品,BEGINEND,DECLARE returnstring nvarchar(5),ELSESET returnstring=热销商品,RETURN returnstring,返回的变量,23,执行命令,24,在Northwind库上有一个记录各种产品在1997年销售额的视图，通过它来引用新建的my_fu
5、nction1，查看哪些商品属于热销商品,视图,函数调用,25,25,商品名称,销售额,销售情况,26,如何调用用户自定义函数？,Use 数据库goSelect 函数名(具体参数值|字段名)from 表名where 条件,27,27,用户自定义函数的名称应是惟一的一个函数最多可以定义1024 个参数，每个参数前用“”符号标明参数的作用范围是整个函数参数只能替代常量，不能替代表名、列名或其它数据库对象的名称用户自定义函数不支持输出参数,注意：,28,2 查看用户自定义函数,自定义函数的名称保存在sysobjects系统表中,创建自定义函数的源代码保存在syscomments系统表中,29,（1）
6、使用系统存储过程查看,EXEC sp_help(sp_helptext),例：用系统存储过程sp_helptext 查看用户自定义函数my_funciton1的定义文本信息USE NorthwindgoEXEC sp_helptext my_function1go,30,31,（2）使用系统表或系统信息架构视图,在SQL Server中存在三个信息架构视图报告有关用户自定义函数的信息,ROUTINESPARAMETERSROUTINE_COLUMNS,这些信息架构视图也是基于系统表sysobjects和syscomments实现的,32,例：使用系统表sysobjects查看数据库Northw
7、ind上存在的所有用户自定义函数的相关信息。,33,9.3 用户自定义函数类型,34,1 标量函数,标量函数返回在 RETURNS子句中定义的数据类型的单个数据值,标量函数可重复调用,35,例：my_function1,36,36,例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为:，并允许用户自行定义分隔符,convert(数据类型，字段名，日期样式编号),37,37,38,BEGINDECLARE returnstring nvarchar(20),CREATE FUNCTION my_function2,(date datetime,separator
8、nvarchar(2)=:),RETURNS nvarchar(20),39,SET returnstring=今天是+CONVERT(nvarchar(5),datepart(year,date)+年+separator+CONVERT(nvarchar(5),datepart(month,date)+月+separator+CONVERT(nvarchar(5),datepart(day,date)+日,RETURN returnstringEND,40,执行程序,41,42,2 内嵌表值函数,在内嵌表值函数中，RETURN子句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌表值
9、函数所返回的表,可替代视图，可用在T-SQL查询中允许表或视图表达式的地方,43,视图：受限于单个 SELECT 语句，不允许包含用户自己提供的参数,内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强,返回表的内嵌表值函数还可替换返回单个结果集的存储过程,44,例:在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函my_function3,45,45,如果已有一个视图（城市中客户与供应商列表）“Customer and Suppliers by City”,46,USE NorthwindGo,CREATE FUNCTION
10、my_function3,(RegionParameter nvarchar(25),RETURNS tableas,47,RETURN,SELECT City,CompanyName,ContactName,SuppliersFROM SuppliersWHERE City=RegionParameter),(SELECT City,CompanyName,ContactName,Customers AS Relationship FROM CustomersWHERE City=RegionParameter,UNION,48,执行命令,49,49,下面示例使用新建立的内嵌表值函数my_f
11、unction3来获取“巴黎”市的所有客户和供应商的信息,50,3 多语句表值函数,多语句表值函数的主体中允许使用的语句,赋值控制流DECLARESELECT游标操作INSERTUPDATEDELETEEXECUTE,51,51,例:在Northwind库上利用视图Customer and Suppliers by City（某一城市的客户与供应商），建立一个可以根据输入的城市名和用户，要求返回所有该城市的客户或供应商的信息的多语句表值函数my_function4，并将其与上例建立的内嵌表值函数my_function3对比,52,下面举例说明使用多语句表值函数my_function4分别获取来
12、自Paris的所有客户和供应商的信息。,52,53,9.4 修改和删除用户自定义函数,1 修改用户自定义函数,ALTER FUNCTION ower-name,function-name(parameter-name scalar-parameter-datetype=default,n)RETURNS scalar-return-datatypeWITH,n ASBEGINsql-statementRETURN scalar-expressionEND,54,例：修改在Northwind库上创建的自定义函数my_function1，将区分商品是否热销的年销售额由当初的10000提高到2000
13、0,ALTER FUNCTION my_function1(moneyinput money)RETURNS nvarchar(5),55,BEGINDECLARE returnstring nvarchar(5)IF moneyinput20000SET returnstring=非热销商品ELSESET returnstring=热销商品RETURN returnstringEND,56,练习：在学生成绩表上创建用户自定义函数my_function1,该函数实现通过输入某一门成绩来返回字符串。如输入大于或等于90的成绩，就返回“优秀”字符串；如输入的成绩大于等于60小于90，就返回“通过”字符串，否则返回“不通过”字符串。,57,
