《数据库应用基础第九章用户自定义函数.ppt》由会员分享，可在线阅读，更多相关《数据库应用基础第九章用户自定义函数.ppt（57页珍藏版）》请在三一文库上搜索。
1、1,复习,1 什么是存储过程？ 2 什么是触发器？,2,1 什么是存储过程？,系统存储过程,本地存储过程,例：my_procdure2,3,程序运行,4,按位置传递参数,5,使用参数名传送参数值,6,USE pubs go,CREATE TRIGGER my_trigger1,ON authors,FOR INSERT AS,raiserror(你已添加了数据,10,1),2 什么是触发器？,instead of INSERT AS,raiserror(你不能添加数据,10,1),7,触发器的作用？,约束,触发器,强制数据完整性,数据类型,8,第九章 用户自定义函数,9,什么是函数？,是一组编
2、译好的Transact-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类型参数的值后返回字符串，如果年销售额大于1000
4、0，返回“热销商品”，否则返回“非热销商品”,21,USE Northwind go,CREATE FUNCTION my_function1,(moneyinput money),RETURNS nvarchar(5),返回值的数据类型,22,IF moneyinput10000 SET returnstring=非热销商品,BEGIN END,DECLARE returnstring nvarchar(5),ELSE SET returnstring=热销商品,RETURN returnstring,返回的变量,23,执行命令,24,在Northwind库上有一个记录各种产品在1997年销
5、售额的视图，通过它来引用新建的my_function1，查看哪些商品属于热销商品,视图,函数调用,25,25,商品名称,销售额,销售情况,26,如何调用用户自定义函数？,Use 数据库 go Select 函数名(具体参数值|字段名) from 表名 where 条件,27,27,用户自定义函数的名称应是惟一的 一个函数最多可以定义1024 个参数，每个参数前用“”符号标明 参数的作用范围是整个函数 参数只能替代常量，不能替代表名、列名或其它数据库对象的名称 用户自定义函数不支持输出参数,注意：,28,2 查看用户自定义函数,自定义函数的名称保存在sysobjects系统表中,创建自定义函数的
6、源代码保存在syscomments系统表中,29,（1）使用系统存储过程查看,EXEC sp_help(sp_helptext) ,例：用系统存储过程sp_helptext 查看用户自定义函数my_funciton1的定义文本信息 USE Northwind go EXEC sp_helptext my_function1 go,30,31,（2）使用系统表或系统信息架构视图,在SQL Server中存在三个信息架构视图报告有关用户自定义函数的信息,ROUTINES PARAMETERS ROUTINE_COLUMNS,这些信息架构视图也是基于系统表sysobjects和syscomments
7、实现的,32,例：使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。,33,9.3 用户自定义函数类型,34,1 标量函数,标量函数返回在 RETURNS子句中定义的数据类型的单个数据值,标量函数可重复调用,35,例：my_function1,36,36,例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为 : ，并允许用户自行定义分隔符,convert(数据类型，字段名，日期样式编号),37,37,38,BEGIN DECLARE returnstring nvarchar(20),CREATE FUNCTION
8、my_function2,(date datetime, separator 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 returnstring END,40,执行程序,41,42,2 内嵌表值函
9、数,在内嵌表值函数中，RETURN子句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌表值函数所返回的表,可替代视图，可用在T-SQL查询中允许表或视图表达式的地方,43,视图：受限于单个 SELECT 语句，不允许包含用户自己提供的参数,内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强,返回表的内嵌表值函数还可替换返回单个结果集的存储过程,44,例:在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函my_function3,45,45,如果已有一个视图（城市中客户与供应商列表） “Customer and Sup
10、pliers by City”,46,USE Northwind Go,CREATE FUNCTION my_function3,(RegionParameter nvarchar(25),RETURNS table as,47,RETURN,SELECT City, CompanyName, ContactName, Suppliers FROM Suppliers WHERE City=RegionParameter),(SELECT City, CompanyName, ContactName, Customers AS Relationship FROM Customers WHERE
11、 City=RegionParameter,UNION,48,执行命令,49,49,下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息,50,3 多语句表值函数,多语句表值函数的主体中允许使用的语句,赋值 控制流 DECLARE SELECT 游标操作 INSERT UPDATE DELETE EXECUTE,51,51,例: 在Northwind库上利用视图Customer and Suppliers by City（某一城市的客户与供应商），建立一个可以根据输入的城市名和用户，要求返回所有该城市的客户或供应商的信息的多语句表值函数my_func
12、tion4，并将其与上例建立的内嵌表值函数my_function3对比,52,下面举例说明使用多语句表值函数my_function4分别获取来自Paris的所有客户和供应商的信息。,52,53,9.4 修改和删除用户自定义函数,1 修改用户自定义函数,ALTER FUNCTION ower-name, function-name ( parameter-name scalar-parameter-datetype =default,n) RETURNS scalar-return-datatype WITH ,n AS BEGIN sql-statement RETURN scalar-exp
13、ression END,54,例：修改在Northwind库上创建的自定义函数my_function1，将区分商品是否热销的年销售额由当初的10000提高到20000,ALTER FUNCTION my_function1 (moneyinput money) RETURNS nvarchar(5),55,BEGIN DECLARE returnstring nvarchar(5) IF moneyinput20000 SET returnstring=非热销商品 ELSE SET returnstring=热销商品 RETURN returnstring END,56,练习： 在学生成绩表上创建用户自定义函数my_function1,该函数实现通过输入某一门成绩来返回字符串。如输入大于或等于90的成绩，就返回“优秀”字符串；如输入的成绩大于等于60小于90，就返回“通过”字符串，否则返回“不通过”字符串。,57,