数据库应用基础课件 第九章 用户自定义函数.pptVIP

SQL Server--chap11--第十一章 用户自定义函数 复习 1 什么是存储过程？ 2 什么是触发器？ 1 什么是存储过程？ 按位置传递参数 使用参数名传送参数值 触发器的作用？ 什么是函数？ 是一组编译好的Transact-SQL语句，他们可以带一个或一组数值做参数，也可以不带参数，它返回一个数值、数值集合，或执行一些操作。 函数能够重复执行一些操作，从而避免不断重写代码。 函数类型 聚合函数 系统函数 日期函数 执行命令 例：my_function1 执行程序 执行命令 练习：在学生成绩表上创建用户自定义函数my_function1,该函数实现通过输入某一门成绩来返回字符串。如输入大于或等于90的成绩，就返回“优秀”字符串；如输入的成绩大于等于60小于90，就返回“通过”字符串，否则返回“不通过”字符串。 （2）使用系统表或系统信息架构视图 在SQL Server中存在三个信息架构视图报告有关用户自定义函数的信息 ROUTINES PARAMETERS ROUTINE_COLUMNS 这些信息架构视图也是基于系统表sysobjects和syscomments实现的 例：使用系统表sysobjects查看数据库Northwind上存在的所有用户自定义函数的相关信息。 9.3 用户自定义函数类型 标量函数 自定义函数有三种类型 内嵌表值函数 多语句表值函数 1 标量函数 标量函数返回在 RETURNS子句中定义的数据类型的单个数据值 标量函数可重复调用 * 例：创建标量函数，要求将当前系统日期转化为年月日格式的字符串并返回，且默认的分隔符为 ‘ :: ’ ，并允许用户自行定义分隔符 convert(数据类型，字段名，日期样式编号) * BEGIN DECLARE @returnstring nvarchar(20) CREATE FUNCTION my_function2 (@date datetime, @separator nvarchar(2)=::) RETURNS nvarchar(20) 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 2 内嵌表值函数 在内嵌表值函数中，RETURN子句中包含有一条单独的SELECT语句，该语句的结果构成了内嵌表值函数所返回的表 可替代视图，可用在T-SQL查询中允许表或视图表达式的地方 视图：受限于单个 SELECT 语句，不允许包含用户自己提供的参数 内嵌表值函数：可包含附加的语句，使函数所包含的逻辑比视图的逻辑更强 返回表的内嵌表值函数还可替换返回单个结果集的存储过程 例:在Northwind数据库上建立一个可以根据输入的城市名返回所有该城市的客户和供应商的信息的内嵌表值函my_function3 * 如果已有一个视图（城市中客户与供应商列表） “Customer and Suppliers by City” USE Northwind Go CREATE FUNCTION my_function3 (@RegionParameter nvarchar(25)) RETURNS table as RETURN SELECT City, CompanyName, ContactName, Suppliers‘ FROM Suppliers WHERE City=@RegionParameter) (SELECT City, CompanyName, ContactName, Customers AS Relationship FROM Customers WHERE City=@RegionParameter UNION * 下面示例使用新建立的内嵌表值函数my_function3来获取“巴黎”市的所有客户和供应商的信息 3 多语句表值函数 多语句表值函数的主体中允许使用的语句 赋值 控制流 DECLARE SELECT 游标操作 INSERT UPDATE DELETE EXECUTE * 例: 在Northwind库上利用视图Customer and Suppliers by City（某一城市的客户与供应商），建立一个可以根据输入的城市名和用户，要求返回所有该城市的