第11章 SQL Server的程序设计2-自定义函数

安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
Sysobjects系统表的主要属性有： Sysobjects系统表的主要属性有： 系统表的主要属性有 name：对象名称，xtype或type： name：对象名称，xtype或type：对象类型 各种常用对象的xtype（类型）简称： 各种常用对象的xtype（类型）简称： 自定义函数：FN(标量函数) IF( ) IF(表值函数) ) 自定义函数：FN( TF(多语句表值函数) TF( ) 存储过程：P 存储过程： 触发器： 触发器： TR 视图： V 视图： 表： U 主键： PK 主键： 外键： 外键： FK
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
1. 用CREATE FUNCTION 语句创建
标量函数返回RETURNS子句中定义的类型、RETURN 标量函数 子句指定数值的单个数据值，如同使用系统内置 的函数一样。语法格式： 语法格式： 语法格式 GO CREATE FUNCTION [ 所有者 .] 自定义函数名 ([参数[,…n]) RETURNS 返回参数的类型 AS BEGIN
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 《 SQL Server 2005》 11章 Server的程序设计 --返回指定编号的的学生的姓名 --返回指定编号的的学生的姓名 GO CREATE FUNCTION F_s_name(@bh char(10)) RETURNS char(10) RETURNS char(10) BEGIN DECLARE @name char(10) SELECT @ name =姓名 =姓名 FROM 学生信息表 WHERE 学号=@bh 学号=@bh RETURN @name END
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
内嵌表值函数是返回一个Select语句查询结果 内嵌表值函数 的表。 CREATE FUNCTION [所有者.] 自定义 函数名([参数[,…n]] RETURNS TABLE AS RETURN (SELECT查询语句 ) 【例】创建一个自定 义函数：[某班学生 信息表]输入参数班 级名称，返回：表。
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计 --返回指定编号的的学生的姓名 --返回指定编号的的学生的姓名
《 SQL Server 2005》
Use 简单学生信息管理库 go IF EXISTS ( SELECT * FROM sysobjects WHERE name='f_s_name' name='f_s_name' and type= 'FN' ) drop function f_s_name f_s_name --此代码功能：如果函数f_s_name --此代码功能：如果函数f_s_name存在就删除 f_s_name存在就删除 此代码功能
第11章 SQL Server的程序设计 11章 Server的程序设计 自定义函数
《 SQL Server 2005》
第11章 11章
SQL Server的程序设计 Server的程序设计
教学提示：SQL Server程序设计是读者学习SQL Server数据库的一个重要环节，它对以后程序开发有 着直接的决定因素。 教学目标：通过本章的学习，读者应该掌握编程的基 础知识、基本语句；
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
什么是函数？ 什么是函数？
函数的组成部分? 函数的组成部分?
1.函数名称 1.函数名称 2.函数体 2.函数体 3.参数（可以为：无参数的、单个参数 3.参数 可以为：无参数的、 参数（ 多个参数的） 的、多个参数的） 4.返回值(注意类型问题) 4.返回值 注意类型问题) 返回值(
安徽财贸职业学院 电子信息系
《 SQL Server 2005》
第11章 11章
SQL Server的程序设计 Server的程序设计
为了扩展T-SQL的编程能力，除了提供的内部函 数，SQL-Server 2005还允许用户自定义函数。用户可 以使用CREATE FUNCTION语句编写自己的函数， 以满足特殊需要。 可用用户自定义函数来传递0个或多个参数，并返 回一个简单的数值。 用户自定义函数一般来说返回的都是数值或字符 型的数据，如int、char、decimal等，但SQL Server2005也支持返回Fra Baidu bibliotekable数据类型的数据。
函数体
RETURN 函数返回的标量值 END
安徽财贸职业学院 电子信息系
此类函数在系 统对象表中的类 型是： 型是：FN
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
回顾：
如何将表中某列的值， 取出来，赋值给变量？ 如何看函数的结果（返回值）？ 提示： 提示：函数的输入参数可以有多个，但 返回值只有一个！
第11章 SQL Server的程序设计 11章 Server的程序设计 创建直接表值用户定义函数
《 SQL Server 2005》
表值函数返回一个Table型数据，对直接表值用户 定义函数而言，返回的结果只是一系列表值，没有明 确的函数体。 其语法格式为： P233 CREATE FUNCTION [owner_name.] function_name ([{@parameter_name [AS] scalar_parameter_data_type [=default]}[，...n]]) RETURNS TABLE [WITH <function_option>[[，]...n]] [AS] RETURN [(select-statement)]
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
用户自定义函数： 除了系统提供的内置函数以外，用户可以根据需要 自己定义函数，即通过编程实现特定的功能。 SQLServer2005支持三种用户自定义函数： SQLServer2005支持三种用户自定义函数： ★返回数值的用户自定义函数(标量函数) ； 返回数值的用户自定义函数( ) ★返回表的用户自定义函数(表值函数) ； 返回表的用户自定义函数( ) ★多语句返回表的用户自定义函数(多语句表值函数) 。 多语句返回表的用户自定义函数( )
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计 删除自定义函数的命令格式： 删除自定义函数的命令格式：
《 SQL Server 2005》
drop function 函数名称 补充：每个数据库有系统表sysobjects ，可以通 补充：每个数据库有系统表sysobjects 过命令： 过命令： SELECT * FROM sysobjects 查看系统创建的、我们创建的各种数据库对象。 查看系统创建的、我们创建的各种数据库对象。
安徽财贸职业学院 电子信息系
《 SQL Server 2005》
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
【例11.7】 创建一个自定义函数，返回特定出版社所出书的平 均定价。
使用下面语句对刚创建的函数进行调用操作：
SELECT dbo. Avgdingji_book1('中国长安') AS '平均成绩'
select dbo. F_s_name('110004')
owner_name.function_name owner_name.function_name
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计 【例11.7】 创建一个自定义函数，返回特定出版社所出 书的平均定价。 CREATE FUNCTION Avgdingji_book1(@出版社 nchar(20)) RETURNS FLOAT AS 方法一 BEGIN DECLARE @平均定价 FLOAT SET @平均定价=(SELECT AVG(定价) FROM book1 WHERE 出版社=@出版社) RETURN @平均定价 END 安徽财贸职业学院 电子信息系
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
思考设计： 1.查询某学号的学生的平均成绩的函数，并调 用此函数？ 2.查询某学号、某课程的成绩？ 提示：参数几个？ 返回值是什么？ 涉及几个表？
安徽财贸职业学院 电子信息系
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
标量用户自定义函数返回一个简单的数值，如int、 char、decimal等 其语法格式如下： P231
CREATE FUNCTION[owner_name.] function_name ([{@parameter_name [AS] scalar_parameter_data_type[=default]}[，…n]]) RETURNS scalar_return_data_type [WITH<function_option>[[，]…n]] [AS] EEGIN Function_body RETURN scalar_expression END 安徽财贸职业学院 电子信息系
用户自定义函数可以接受零个或多个输入参数，返回值 用户自定义函数可以接受零个或多个输入参数， 可以是数值或表，不支持输出参数，只有一个返回值。 可以是数值或表，不支持输出参数，只有一个返回值。 创建用户自定义函数用CREATE FUNCTION语句实现 语句实现。 创建用户自定义函数用CREATE FUNCTION语句实现。
《 SQL Server 2005》
第11章 SQL Server的程序设计 11章 Server的程序设计 【例11.7】 创建一个自定义函数，返回特定出版社所出 书的平均定价。 CREATE FUNCTION Avgdingji_book1(@出版社 nchar(20)) RETURNS FLOAT AS 方法三 BEGIN RETURN (SELECT AVG(定价) FROM book1 WHERE 出版社=@出版社) END
安徽财贸职业学院 电子信息系
第11章 SQL Server的程序设计 11章 Server的程序设计
《 SQL Server 2005》
用户自定义函数的调用: 用户自定义函数的调用: 要调用用户自定义函数， 要调用用户自定义函数，要在调用的时候指明函数 的拥有者和函数的名称。 的拥有者和函数的名称。 用户自定义函数的名称： 用户自定义函数的名称： database_name.owner_name.function_name database_name.owner_name.function_name 例如： 例如： --调用自定义函数 表名前一定要有所有者名称
《 SQL Server 2005》
第11章 SQL Server的程序设计 11章 Server的程序设计 【例11.7】 创建一个自定义函数，返回特定出版社所出 书的平均定价。 CREATE FUNCTION Avgdingji_book1(@出版社 nchar(20)) RETURNS FLOAT AS 方法二 BEGIN DECLARE @平均定价 FLOAT SELECT @平均定价= AVG(定价) FROM book1 WHERE 出版社=@出版社 RETURN @平均定价 END 安徽财贸职业学院 电子信息系