第十章 SQL函数
?函数综述
?系统函数
?用户自定义函数的基本操作
?创建用户自定义函数
? 查看用户自定义函数
?修改用户自定义函数
?删除用户自定义函数
第十章 SQL函数
?函数综述
为了使用户对数据库进行查询和修改时更加方便, SQL server在
T-SQL语言中提供了许多内部函数以供调用 。 用户也可以根据自己的
需要创建函数 。
函数可以由系统提供, 也可以由用户创建 。 系统提供的函数称为
内置函数, 也叫做系统函数, 它为用户方便快捷地执行某些操作提供
帮助；用户创建的函数称为用户自定义函数, 它是用户根据自己的特
殊需求而创建的, 用来补充和扩展内置函数 。
第十章 SQL函数
?系统函数 —标量函数
系统函数
标量函数
聚合函数
行集函数 。
标量函数
标量函数对单一值操作，返回单一值。只要在能够使用表达式的
地方，就可以使用标量函数。
数学函数
日期和时间函数
字符串函数
数据类型转换函数 。
第十章 SQL函数
?系统函数 —标量函数
数学函数
1,abs(数值型表达式 )
功能：返回表达式 的绝对值，其值的数据类型与参数一致。
例,SELECT ABS(-1),ABS(0),ABS(1)
2,ceiling(数值型表达式 )
功能：返回最小的大于或等于给定数值型表达式的整数值，值的
类型和给定的值相同。
floor(数值型表达式 )
功能：返回最大的小于或等于给定数值型表达式的整数值。
例,SELECT FLOOR(123.45),CEILING(123.45)
SELECT FLOOR(-123.45),CEILING(-123.45)
第十章 SQL函数
?系统函数 —标量函数
数学函数
3,SQUARE(float表达式 )函数
功能：此函数用于返回给定表达式的平方值
例如,SQUARE(3)的结果为 9.0。
POWER（数值型表达式 1，数值型表达式 2）函数
功能：此函数用于返回给定表达式乘指定次方的值。成方运算函
数返回值的数据类型与第一个参数的数据类型相同。
例如,POWER(2,3)表示 2的 3次方。
SELECT POWER(2,-3),POWER(2.0,-3),POWER(2.000,-3)
4,sign(数值型表达式 )
功能：判断数值的正负属性,+1表示正数； -1表示负数。
第十章 SQL函数
?系统函数 —标量函数
数学函数
5,rand(整型表达式 )
功能：返回一个位于 0和 1之间的随机数，在单个查询中反复调用
rand( )将产生相同的值。
例,DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND(@counter) Random_Number
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO
第十章 SQL函数
?系统函数 —标量函数
数学函数
6,round(数值表达式，整数 )
功能：将数值四舍五入成整数指定的精度形式。整数为正表示要
进行的运算位置在小数点后，为负表示在小数点前。
例,SELECT ROUND(789.34,1),ROUND(789.34,0)
SELCET ROUND(789.34,-1),ROUND(789.34,-2)
7、三角函数
ACOS(float表达式 )反余弦函数：返回以弧度表示的角度值。
ASIN(float表达式 )反正弦函数：返回以弧度表示的角度值。
ATAN(float表达式 )反正切函数：返回以弧度表示的角度值。
SIN(float表达式 )正弦函数：返回输入表达式的三角正弦值。
COS(float表达式 )余弦函数：返回输入表达式的三角余弦值。
TAN(float表达式 )正切函数：返回输入表达式的三角正切值。
COT(float表达式 )余切函数：返回输入表达式的三角余切值。
第十章 SQL函数
?系统函数 —标量函数
日期和时间函数
GETDATE（）：返回当前系统日期和时间。
DATEADD（ datepart,number,date）：在 date值上加上 datepart和
number参数指定的时间间隔，返回新的 datetime值。
DATEDIFF（ datepart,startdate,enddate）：返回跨两个指定日期
的日期和时间边界数。
DATENAME（ datepart,date）：返回代表指定日期的指定日期部
分的字符串。
DATEPART（ datepart,date）：返回代表指定日期的指定日期部分
的整数。
YEAR（ date）：返回表示指定日期中的年份的整数。
MONTH（ date）：返回代表指定日期月份的整数。
DAY（ date）：返回代表指定日期的天的日期部分的整数。
第十章 SQL函数
?系统函数 —标量函数
一个 date型数据日期部分的取值如下：
Datepart 缩写 Datepart 缩写
year yy,yyyy quarter qq,q
month mm,m dayof year dy,y
day dd,d week wk,ww
weekday dw hour hh
minute mi,n second ss,s
millisecond ms
第十章 SQL函数
?系统函数 —标量函数
例：查看今天的年月日，并以格式化的形式显示。在查询分析器中输
入以下代码：
SELECT '今天是 ' + DATENAME (YY,GETDATE()) + '年 ' +
DATENAME(MM,GETDATE())+?月 ?
+DATENAME(DD,GETDATE())+'日 ?
例：用日期函数计算教师表中教师的年龄。其程序代码如下：
USE STUDENT
GO
SELECT 姓名,DATEDIFF(YY,出生日期,GETDATE()) AS 年龄
FROM 教师
GO
第十章 SQL函数
?系统函数 —标量函数
字符串函数
1,ASCLL(字符 )
功能：返回字符的 ASCLL码值，值的类型为整型。
2,CHAR (整型表达式 )
功能：将整型的 ASCII代码转换为字符。
3,CHARINDEX (字符型表达式 1,字符型表达式 2 [,开始位置 ] )
功能：返回字符串中指定表达式的起始位置。
4,LOWER(字符型表达式 )
功能：将大写字符数据转换为小写字符。
UPPER (字符型表达式 )：
功能：将小写字符数据转换为大写字符。
第十章 SQL函数
?系统函数 —标量函数
字符串函数
5,STR ( float型表达式 [,长度 [,小数点后长度 ]])
功能：将数字数据转换为字符数据。
6,LEFT (字符型表达式,整型表达式 )
功能：返回字符串中从左边开始指定个数的字符。
RIGHT (字符型表达式,整型表达式 )
功能：返回字符串中从右边开始指定个数的字符。
7,LTRIM (字符型表达式 )
功能：删除起始空格后返回字符表达式。
RTRIM (字符型表达式 )
功能：截断所有尾随空格后返回一个字符串。
8,LEN (字符串表达式 )
功能：返回给定字符串表达式的字符（而不是字节）个数，其中
不包含尾随空格。
第十章 SQL函数
?系统函数 —标量函数
字符串函数
9,SUBSTRING ( expression,start,length )
功能：从 expression的第 start个字符处返回 length个字符。
例：将学生表中的出生日期的月份转化为字符串，并测试其长度：
SELECT LEN(STR(MONTH (出生日期 ))) FROM 学生 WHERE
学号 ='010101001001'
例：使用字符串函数查找姓刘的同学，并格式化显示其出生年月。
USE STUDENT
GO
SELECT 姓名,STR(YEAR(出生日期 ))+'年 '+
LTRIM(STR(MONTH(出生日期 )))+'月 ' AS 出生年月
FROM 学生
WHERE LEFT(姓名,1)='刘 '
GO
第十章 SQL函数
?系统函数 —标量函数
数据类型转换函数
1,CAST ( expression AS data_type )
功能：将某种数据类型的表达式显式转换为另一种数据类型。
2,CONVERT(data_type[(length)],expression [,style])
功能：将表达式的值从一种数据类型转换为另一种数据类型。
例,USE STUDENT
GO
SELECT 学号 +' 同学平均成绩为 '+CAST(AVG(成绩 )
AS CHAR(2))+'分 '
FROM 课程注册
GROUP BY 学号
GO
第十章 SQL函数
?系统函数 —标量函数
其它函数
1,isdate(表达式 )
功能：确定输入表达式的值是否为有效日期，如果是返回 1，否
则返回 0。
例,select isdate(“Feb 7 1995 11:00pm”),
isdate(null),
isdate(“1998.12.02”),
isdate(“1997.2.29”),
isdate(“1/23/95”)
go
2,isnull(表达式 1，表达式 2)
功能：判断表达式 1的值是否为空，如果是，则返回表达式 2的值；如果不是则返回表达式 1的值。使用此函数时，表达式 1和表达式 2
的类型必须相同。
3,print(字符串表达式 )
功能：将字符串输出给用户。
第十章 SQL函数
?系统函数 —标量函数
聚合函数
聚合函数对一组值进行计算后，向调用者返回单一的值。一般情
况下，它经常与 SELECT语句的 GROUP BY 子句一同使用。
1,COUNT(*)
功能：用于计算所有行数
2,MIN(数值表达式 )
功能：用于计算表达式的最小值
3,MAX(数值表达式 )
功能：用于计算表达式的最大值
4,SUM(数值表达式 )
功能：用于计算表达式的和
5,AVG(数值表达式 )
功能：用于计算表达式的平均值
第十章 SQL函数
?系统函数 —标量函数
聚合函数
例：使用聚合函数统计 STUDENT数据库中学生的成绩情况。 USE
STUDENT
GO
SELECT COUNT(*) AS 课程门数,MAX(成绩 ) AS 最高分数
,MIN(成绩 ) AS 最低分数,SUM(成绩 ) AS 总成绩
,AVG(成绩 ) AS 平均成绩
FROM 课程注册
GROUP BY 学号
GO
第十章 SQL函数
?用户自定义函数
在 SQL Server中, 用户不仅可以使用标准的内置函数, 也可以使用
自己定义的函数来实现一些特殊的功能 。 用户自定义函数可以在企业
管理器中创建, 也可以使用 CREATE FUNCTION 语句创建 。 在创建
时需要注意：函数名在数据库中必须唯一, 其可以有参数, 也可以没
有参数, 其参数只能是输入参数, 最多可以有 1024参数 。
标量函数：返回单个数据值。
表值函数：返回值是一个记录集合 ——表。在此函数中,return语
句包含一条单独的 select语句。
多语句表值函数：返回值是由选择的结果构成的记录集。
第十章 SQL函数
?用户自定义函数
1、使用 CREATE FUNCTION语句创建用户自定义函数
在查询分析器中，可以使用 CREATE FUNCTION创建用户自定义
函数，其语法格式如下：
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ =
default ] } [,...n ] ] )
RETURNS scalar_return_data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
第十章 SQL函数
?用户自定义函数
function_name：指用户自定义函数的名称。其名称必须符合标识符
的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。
@parameter_name：用户自定义函数的参数，其可以是一个或多个
。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它
函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据
库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除
非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函
数时必须指定 "default"关键字才能获得默认值。
scalar_parameter_data_type：参数的数据类型。
scalar_return_data_type：是用户定义函数的返回值。可以是 SQL
Server 支持的任何标量数据类型（ text,ntext,image 和 timestamp
除外）。
function_body：位于 begin和 end之间的一系列 Transact-SQL 语句，
其只用于标量函数和多语句表值函数。
scalar_expression：用户自定义函数中返回值的表达式。
第十章 SQL函数
?用户自定义函数
例：在 STUDENT库中创建一个用户自定义函数 XUEFEN，该函数通过输入
成
绩来判断是否取得学分，当成绩大于等于 50时，返回取得学分，否则，
返回未取得学分。其代码如下：
CREATE FUNCTION xuefen(@inputxf int) RETURNS nvarchar(10)
BEGIN
declare @retrunstr nvarchar(10)
If @inputxf >=50
set @retrunstr='取得学分 '
else
set @retrunstr='未取得学分 '
return @retrunstr
END
如果使用用户自定义函数，要在使用的时候指明函数的所有者和函数的名
称。在查询分析器中输入如下代码：
SELECT 学号,成绩,dbo.xuefen(成绩 ) AS 学分情况
FROM 课程注册
WHERE 课程号 ='0003'
GO
第十章 SQL函数
?用户自定义函数
2、使用企业管理器创建用户定义函数
在企业管理器中创建用户自定义函数的步骤为：
1）在企业管理器中，选择需要建立函数的数据库，从中选择用户定
义函数图标。
2）选择用户定义函数图标后，击右健，从弹出的快捷菜单中选择“
新建用户定义函数”命令，打开“用户定义函数属性”对话框，该属
性窗口在“文本”框中列出了建立函数的框架，如图 10.2所示。
3）在“文本”框中输入函数的所有者、函数名称、参数列表、返回
类型和函数体等函数的各个组成部分。
4）单击“检查语法”按钮，检查输入的建立函数语句是否有语法错
误。如果没有语法错误，单击“确定”按钮，将用户定义的函数保存
到数据库中供用户以后使用。
第十章 SQL函数
?用户自定义函数的分类 --标量函数
例：在 STUDENT库中创建一个用户自定义函数 XUEFENJI，该函数通过输
入成绩来计算学生的学分绩。其代码如下：
CREATE FUNCTION xuefenji(@inputzz int) RETURNS nvarchar(10)
BEGIN
declare @retrunstr nvarchar(10)
if @inputzz >=50 AND @inputzz <60
set @retrunstr='学分绩为 0.8'
else if @inputzz >=60 AND @inputzz <70
set @retrunstr='学分绩为 1.0'
else if @inputzz >=70 AND @inputzz <80
set @retrunstr='学分绩为 1.2'
else if @inputzz >=80 AND @inputzz <=100
set @retrunstr='学分绩为 1.5'
else
set @retrunstr='无学分绩 '
return @retrunstr
END
第十章 SQL函数
?用户自定义函数的分类 —标量函数
例：
USE STUDENT
GO
SELECT 课程号,成绩,dbo.xuefenji(成绩 ) AS 学分绩
FROM 课程注册
WHERE 学号 ='010101001001'
GO
需要查看,010101001001”号学生的课程学分绩
第十章 SQL函数
?用户自定义函数的分类 —表值函数
表值函数遵循的原则：
1,RETURNS子句仅包含关键字 table。不必定义返回变量的格式，因为它由
RETURN 子句中的 SELECT 语句的结果集的格式设置。
2,function_body 不由 BEGIN和 END分隔。
3,RETURN子句在括号中包含单个 SELECT语句。 SELECT语句的结果集构成
函数所返回的表。内嵌表值函数中使用的 SELECT语句受到与视图中使用的
SELECT语句相同的限制。
例：在 STUDENT库中创建一个内嵌表值函数 XUESHENG，该函数可以根据输入
的系部代码返回该系学生的基本信息。其代码如下：
CREATE FUNCTION XUESHENG(@inputxbdm nvarchar(4)) RETURNS table
AS
RETURN
( SELECT 学号,姓名,入学时间 FROM 学生 WHERE 系部代码 =@inputxbdm)
GO
建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：
SELECT * FROM DBO.XUESHENG('01')
GO
第十章 SQL函数
?用户自定义函数的分类 —多表值函数
例：在 STUDENT库中创建一个多语句表值函数 CHENGJI，该函数可以根据输入
的课程名称返回选修该课程的学生姓名和成绩。其代码如下：
CREATE FUNCTION CHENGJI( @inputkc as char(20) )
RETURNS @chji TABLE
(
课程名 char(20),
姓名 char(8),
成绩 tinyint
)
AS
BEGIN
INSERT @chji
SELECT c.课程名,s.姓名,k.成绩
FROM 学生 as s INNER JOIN 课程注册 as k
ON s.学号 =k.学号 inner join 课程 as c
on c.课程号 =k.课程号
WHERE c.课程名 =@inputkc
RETURN
END
GO
在查询分析器中输入以下查询命令：
SELECT * FROM DBO.CHENGJIi('大学语文 ')
第十章 SQL函数
?用户自定义函数的分类 —多表值函数
总结：
多语句函数的主体中允许使用以下语句。
1、赋值语句。
2、控制流语句。
3,DECLARE 语句，该语句定义函数局部的数据变量和游标。
4,SELECT 语句，该语句包含带有表达式的选择列表，其中的表达
式将值赋予函数的局部变量。
5、游标操作，该操作引用在函数中声明、打开、关闭和释放的局部
游标。只允许使用以 INTO子句向局部变量赋值的 FETCH语句；不允
许使用将数据返回到客户端的 FETCH语句。
6,INSERT,UPDATE和 DELETE语句，这些语句修改函数的局部
table变量。
7,EXECUTE语句调用扩展存储过程。
第十章 SQL函数
?查看用户自定义函数的属性 —查询分析器
在 SQL Server中，根据不同需要，可以使用 sp_helptext,sp_help等系
统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的
具体作用和语法如下：
使用 sp_helptext查看用户定义函数的文本信息，其语法格式为：
sp_helptext 用户自定义函数名
使用 sp_help查看用户自定义函数的一般信息，其语法格式为：
sp_help 用户自定义函数名
例：使用有关系统过程查看 STUDENT数据库中名为 XUEFEN的用户
自定义函数的文本信息。其程序代码如下：
USE STUDENT
GO
SP_HELPTEXT XUEFEN
GO
第十章 SQL函数
?查看用户自定义函数的属性 —企业管理器
在企业管理器中，用户可以很方便地查看用户定义函数的信息，其
步骤为：
1）在企业管理器中，选择需要查看的函数所在的数据库。
2）在详细信息窗格中双击户定义函数图标，在详细窗格中显示出
用户建立的函数名称。
3）右击需要查看的用户定义函数，从弹出的快捷菜单中选择“属
性”命令。
4）当出现“用户定义函数数性”对话框时，在“文本”框中可以
浏览用户定义函数的文本信息。单击“权限”按钮查看用户定义函
数的权限。
第十章 SQL函数
?修改用户自定义函数的属性 —企业管理器
1、使用企业管理器修改用户定义函数，在企业管理器中修改用户自
定义函数的步骤为：
1）在企业管理器中，选择需要修改的函数所在的数据库。
2）在详细信息窗格中双击户定义函数图标，显示出用户建立的函
数名称。
3）右击需要修改的用户定义函数，从弹出的快捷菜单中选择“属
性”命令。
4）当出现“用户定义函数数性”对话框时，在“文本”框中修改
用户定义函数的文本。
5）修改完后，单击“检查语法”按钮，语法检查通过后，单击“
确定”按钮，完成函数修改。
第十章 SQL函数
?修改用户自定义函数的属性 —查询分析器
2、使用 SQL命令修改用户自定义函数，使用 ALTER FUNCTION 命
令可以修改用户自定义函数。修改由 CREATE FUNCTION 语句创建
的现有用户定义函数，不会更改权限，也不影响相关的函数、存储
过程或触发器。其语法格式如下：
ALTER FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default
] } [,...n ] ] )
RETURNS scalar_return_data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
其中的参数与建立用户自定义函数中的参数意义相同。
第十章 SQL函数
?删除用户自定义函数的属性
1、使用企业管理器删除用户定义函数，在企业管理器中删除用户自
定义函数的步骤为：
1）在企业管理器中，选择要删除函数所在的数据库。
2）在详细信息窗格中双击户定义函数图标，显示出用户建立的函
数名称。
3）选择需要删除的函数，击右键，从弹出的快捷菜单中选择“删
除”命令，出现“移除对象”对话框。
4）在“移除对象”对话框中选择“全部删除”按钮，删除用户自
定义函数。
2、使用 DROP命令删除用户自定义函数
使用 DROP命令可以一次删除多个用户自定义函数，其语法格式为：
DROP FUNCTION [所有者名称,]函数名称 [,N…]
?函数综述
?系统函数
?用户自定义函数的基本操作
?创建用户自定义函数
? 查看用户自定义函数
?修改用户自定义函数
?删除用户自定义函数
第十章 SQL函数
?函数综述
为了使用户对数据库进行查询和修改时更加方便, SQL server在
T-SQL语言中提供了许多内部函数以供调用 。 用户也可以根据自己的
需要创建函数 。
函数可以由系统提供, 也可以由用户创建 。 系统提供的函数称为
内置函数, 也叫做系统函数, 它为用户方便快捷地执行某些操作提供
帮助；用户创建的函数称为用户自定义函数, 它是用户根据自己的特
殊需求而创建的, 用来补充和扩展内置函数 。
第十章 SQL函数
?系统函数 —标量函数
系统函数
标量函数
聚合函数
行集函数 。
标量函数
标量函数对单一值操作，返回单一值。只要在能够使用表达式的
地方，就可以使用标量函数。
数学函数
日期和时间函数
字符串函数
数据类型转换函数 。
第十章 SQL函数
?系统函数 —标量函数
数学函数
1,abs(数值型表达式 )
功能：返回表达式 的绝对值，其值的数据类型与参数一致。
例,SELECT ABS(-1),ABS(0),ABS(1)
2,ceiling(数值型表达式 )
功能：返回最小的大于或等于给定数值型表达式的整数值，值的
类型和给定的值相同。
floor(数值型表达式 )
功能：返回最大的小于或等于给定数值型表达式的整数值。
例,SELECT FLOOR(123.45),CEILING(123.45)
SELECT FLOOR(-123.45),CEILING(-123.45)
第十章 SQL函数
?系统函数 —标量函数
数学函数
3,SQUARE(float表达式 )函数
功能：此函数用于返回给定表达式的平方值
例如,SQUARE(3)的结果为 9.0。
POWER（数值型表达式 1，数值型表达式 2）函数
功能：此函数用于返回给定表达式乘指定次方的值。成方运算函
数返回值的数据类型与第一个参数的数据类型相同。
例如,POWER(2,3)表示 2的 3次方。
SELECT POWER(2,-3),POWER(2.0,-3),POWER(2.000,-3)
4,sign(数值型表达式 )
功能：判断数值的正负属性,+1表示正数； -1表示负数。
第十章 SQL函数
?系统函数 —标量函数
数学函数
5,rand(整型表达式 )
功能：返回一个位于 0和 1之间的随机数，在单个查询中反复调用
rand( )将产生相同的值。
例,DECLARE @counter smallint
SET @counter = 1
WHILE @counter < 5
BEGIN
SELECT RAND(@counter) Random_Number
SET NOCOUNT ON
SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO
第十章 SQL函数
?系统函数 —标量函数
数学函数
6,round(数值表达式，整数 )
功能：将数值四舍五入成整数指定的精度形式。整数为正表示要
进行的运算位置在小数点后，为负表示在小数点前。
例,SELECT ROUND(789.34,1),ROUND(789.34,0)
SELCET ROUND(789.34,-1),ROUND(789.34,-2)
7、三角函数
ACOS(float表达式 )反余弦函数：返回以弧度表示的角度值。
ASIN(float表达式 )反正弦函数：返回以弧度表示的角度值。
ATAN(float表达式 )反正切函数：返回以弧度表示的角度值。
SIN(float表达式 )正弦函数：返回输入表达式的三角正弦值。
COS(float表达式 )余弦函数：返回输入表达式的三角余弦值。
TAN(float表达式 )正切函数：返回输入表达式的三角正切值。
COT(float表达式 )余切函数：返回输入表达式的三角余切值。
第十章 SQL函数
?系统函数 —标量函数
日期和时间函数
GETDATE（）：返回当前系统日期和时间。
DATEADD（ datepart,number,date）：在 date值上加上 datepart和
number参数指定的时间间隔，返回新的 datetime值。
DATEDIFF（ datepart,startdate,enddate）：返回跨两个指定日期
的日期和时间边界数。
DATENAME（ datepart,date）：返回代表指定日期的指定日期部
分的字符串。
DATEPART（ datepart,date）：返回代表指定日期的指定日期部分
的整数。
YEAR（ date）：返回表示指定日期中的年份的整数。
MONTH（ date）：返回代表指定日期月份的整数。
DAY（ date）：返回代表指定日期的天的日期部分的整数。
第十章 SQL函数
?系统函数 —标量函数
一个 date型数据日期部分的取值如下：
Datepart 缩写 Datepart 缩写
year yy,yyyy quarter qq,q
month mm,m dayof year dy,y
day dd,d week wk,ww
weekday dw hour hh
minute mi,n second ss,s
millisecond ms
第十章 SQL函数
?系统函数 —标量函数
例：查看今天的年月日，并以格式化的形式显示。在查询分析器中输
入以下代码：
SELECT '今天是 ' + DATENAME (YY,GETDATE()) + '年 ' +
DATENAME(MM,GETDATE())+?月 ?
+DATENAME(DD,GETDATE())+'日 ?
例：用日期函数计算教师表中教师的年龄。其程序代码如下：
USE STUDENT
GO
SELECT 姓名,DATEDIFF(YY,出生日期,GETDATE()) AS 年龄
FROM 教师
GO
第十章 SQL函数
?系统函数 —标量函数
字符串函数
1,ASCLL(字符 )
功能：返回字符的 ASCLL码值，值的类型为整型。
2,CHAR (整型表达式 )
功能：将整型的 ASCII代码转换为字符。
3,CHARINDEX (字符型表达式 1,字符型表达式 2 [,开始位置 ] )
功能：返回字符串中指定表达式的起始位置。
4,LOWER(字符型表达式 )
功能：将大写字符数据转换为小写字符。
UPPER (字符型表达式 )：
功能：将小写字符数据转换为大写字符。
第十章 SQL函数
?系统函数 —标量函数
字符串函数
5,STR ( float型表达式 [,长度 [,小数点后长度 ]])
功能：将数字数据转换为字符数据。
6,LEFT (字符型表达式,整型表达式 )
功能：返回字符串中从左边开始指定个数的字符。
RIGHT (字符型表达式,整型表达式 )
功能：返回字符串中从右边开始指定个数的字符。
7,LTRIM (字符型表达式 )
功能：删除起始空格后返回字符表达式。
RTRIM (字符型表达式 )
功能：截断所有尾随空格后返回一个字符串。
8,LEN (字符串表达式 )
功能：返回给定字符串表达式的字符（而不是字节）个数，其中
不包含尾随空格。
第十章 SQL函数
?系统函数 —标量函数
字符串函数
9,SUBSTRING ( expression,start,length )
功能：从 expression的第 start个字符处返回 length个字符。
例：将学生表中的出生日期的月份转化为字符串，并测试其长度：
SELECT LEN(STR(MONTH (出生日期 ))) FROM 学生 WHERE
学号 ='010101001001'
例：使用字符串函数查找姓刘的同学，并格式化显示其出生年月。
USE STUDENT
GO
SELECT 姓名,STR(YEAR(出生日期 ))+'年 '+
LTRIM(STR(MONTH(出生日期 )))+'月 ' AS 出生年月
FROM 学生
WHERE LEFT(姓名,1)='刘 '
GO
第十章 SQL函数
?系统函数 —标量函数
数据类型转换函数
1,CAST ( expression AS data_type )
功能：将某种数据类型的表达式显式转换为另一种数据类型。
2,CONVERT(data_type[(length)],expression [,style])
功能：将表达式的值从一种数据类型转换为另一种数据类型。
例,USE STUDENT
GO
SELECT 学号 +' 同学平均成绩为 '+CAST(AVG(成绩 )
AS CHAR(2))+'分 '
FROM 课程注册
GROUP BY 学号
GO
第十章 SQL函数
?系统函数 —标量函数
其它函数
1,isdate(表达式 )
功能：确定输入表达式的值是否为有效日期，如果是返回 1，否
则返回 0。
例,select isdate(“Feb 7 1995 11:00pm”),
isdate(null),
isdate(“1998.12.02”),
isdate(“1997.2.29”),
isdate(“1/23/95”)
go
2,isnull(表达式 1，表达式 2)
功能：判断表达式 1的值是否为空，如果是，则返回表达式 2的值；如果不是则返回表达式 1的值。使用此函数时，表达式 1和表达式 2
的类型必须相同。
3,print(字符串表达式 )
功能：将字符串输出给用户。
第十章 SQL函数
?系统函数 —标量函数
聚合函数
聚合函数对一组值进行计算后，向调用者返回单一的值。一般情
况下，它经常与 SELECT语句的 GROUP BY 子句一同使用。
1,COUNT(*)
功能：用于计算所有行数
2,MIN(数值表达式 )
功能：用于计算表达式的最小值
3,MAX(数值表达式 )
功能：用于计算表达式的最大值
4,SUM(数值表达式 )
功能：用于计算表达式的和
5,AVG(数值表达式 )
功能：用于计算表达式的平均值
第十章 SQL函数
?系统函数 —标量函数
聚合函数
例：使用聚合函数统计 STUDENT数据库中学生的成绩情况。 USE
STUDENT
GO
SELECT COUNT(*) AS 课程门数,MAX(成绩 ) AS 最高分数
,MIN(成绩 ) AS 最低分数,SUM(成绩 ) AS 总成绩
,AVG(成绩 ) AS 平均成绩
FROM 课程注册
GROUP BY 学号
GO
第十章 SQL函数
?用户自定义函数
在 SQL Server中, 用户不仅可以使用标准的内置函数, 也可以使用
自己定义的函数来实现一些特殊的功能 。 用户自定义函数可以在企业
管理器中创建, 也可以使用 CREATE FUNCTION 语句创建 。 在创建
时需要注意：函数名在数据库中必须唯一, 其可以有参数, 也可以没
有参数, 其参数只能是输入参数, 最多可以有 1024参数 。
标量函数：返回单个数据值。
表值函数：返回值是一个记录集合 ——表。在此函数中,return语
句包含一条单独的 select语句。
多语句表值函数：返回值是由选择的结果构成的记录集。
第十章 SQL函数
?用户自定义函数
1、使用 CREATE FUNCTION语句创建用户自定义函数
在查询分析器中，可以使用 CREATE FUNCTION创建用户自定义
函数，其语法格式如下：
CREATE FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ =
default ] } [,...n ] ] )
RETURNS scalar_return_data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
第十章 SQL函数
?用户自定义函数
function_name：指用户自定义函数的名称。其名称必须符合标识符
的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。
@parameter_name：用户自定义函数的参数，其可以是一个或多个
。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它
函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据
库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除
非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函
数时必须指定 "default"关键字才能获得默认值。
scalar_parameter_data_type：参数的数据类型。
scalar_return_data_type：是用户定义函数的返回值。可以是 SQL
Server 支持的任何标量数据类型（ text,ntext,image 和 timestamp
除外）。
function_body：位于 begin和 end之间的一系列 Transact-SQL 语句，
其只用于标量函数和多语句表值函数。
scalar_expression：用户自定义函数中返回值的表达式。
第十章 SQL函数
?用户自定义函数
例：在 STUDENT库中创建一个用户自定义函数 XUEFEN，该函数通过输入
成
绩来判断是否取得学分，当成绩大于等于 50时，返回取得学分，否则，
返回未取得学分。其代码如下：
CREATE FUNCTION xuefen(@inputxf int) RETURNS nvarchar(10)
BEGIN
declare @retrunstr nvarchar(10)
If @inputxf >=50
set @retrunstr='取得学分 '
else
set @retrunstr='未取得学分 '
return @retrunstr
END
如果使用用户自定义函数，要在使用的时候指明函数的所有者和函数的名
称。在查询分析器中输入如下代码：
SELECT 学号,成绩,dbo.xuefen(成绩 ) AS 学分情况
FROM 课程注册
WHERE 课程号 ='0003'
GO
第十章 SQL函数
?用户自定义函数
2、使用企业管理器创建用户定义函数
在企业管理器中创建用户自定义函数的步骤为：
1）在企业管理器中，选择需要建立函数的数据库，从中选择用户定
义函数图标。
2）选择用户定义函数图标后，击右健，从弹出的快捷菜单中选择“
新建用户定义函数”命令，打开“用户定义函数属性”对话框，该属
性窗口在“文本”框中列出了建立函数的框架，如图 10.2所示。
3）在“文本”框中输入函数的所有者、函数名称、参数列表、返回
类型和函数体等函数的各个组成部分。
4）单击“检查语法”按钮，检查输入的建立函数语句是否有语法错
误。如果没有语法错误，单击“确定”按钮，将用户定义的函数保存
到数据库中供用户以后使用。
第十章 SQL函数
?用户自定义函数的分类 --标量函数
例：在 STUDENT库中创建一个用户自定义函数 XUEFENJI，该函数通过输
入成绩来计算学生的学分绩。其代码如下：
CREATE FUNCTION xuefenji(@inputzz int) RETURNS nvarchar(10)
BEGIN
declare @retrunstr nvarchar(10)
if @inputzz >=50 AND @inputzz <60
set @retrunstr='学分绩为 0.8'
else if @inputzz >=60 AND @inputzz <70
set @retrunstr='学分绩为 1.0'
else if @inputzz >=70 AND @inputzz <80
set @retrunstr='学分绩为 1.2'
else if @inputzz >=80 AND @inputzz <=100
set @retrunstr='学分绩为 1.5'
else
set @retrunstr='无学分绩 '
return @retrunstr
END
第十章 SQL函数
?用户自定义函数的分类 —标量函数
例：
USE STUDENT
GO
SELECT 课程号,成绩,dbo.xuefenji(成绩 ) AS 学分绩
FROM 课程注册
WHERE 学号 ='010101001001'
GO
需要查看,010101001001”号学生的课程学分绩
第十章 SQL函数
?用户自定义函数的分类 —表值函数
表值函数遵循的原则：
1,RETURNS子句仅包含关键字 table。不必定义返回变量的格式，因为它由
RETURN 子句中的 SELECT 语句的结果集的格式设置。
2,function_body 不由 BEGIN和 END分隔。
3,RETURN子句在括号中包含单个 SELECT语句。 SELECT语句的结果集构成
函数所返回的表。内嵌表值函数中使用的 SELECT语句受到与视图中使用的
SELECT语句相同的限制。
例：在 STUDENT库中创建一个内嵌表值函数 XUESHENG，该函数可以根据输入
的系部代码返回该系学生的基本信息。其代码如下：
CREATE FUNCTION XUESHENG(@inputxbdm nvarchar(4)) RETURNS table
AS
RETURN
( SELECT 学号,姓名,入学时间 FROM 学生 WHERE 系部代码 =@inputxbdm)
GO
建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它：
SELECT * FROM DBO.XUESHENG('01')
GO
第十章 SQL函数
?用户自定义函数的分类 —多表值函数
例：在 STUDENT库中创建一个多语句表值函数 CHENGJI，该函数可以根据输入
的课程名称返回选修该课程的学生姓名和成绩。其代码如下：
CREATE FUNCTION CHENGJI( @inputkc as char(20) )
RETURNS @chji TABLE
(
课程名 char(20),
姓名 char(8),
成绩 tinyint
)
AS
BEGIN
INSERT @chji
SELECT c.课程名,s.姓名,k.成绩
FROM 学生 as s INNER JOIN 课程注册 as k
ON s.学号 =k.学号 inner join 课程 as c
on c.课程号 =k.课程号
WHERE c.课程名 =@inputkc
RETURN
END
GO
在查询分析器中输入以下查询命令：
SELECT * FROM DBO.CHENGJIi('大学语文 ')
第十章 SQL函数
?用户自定义函数的分类 —多表值函数
总结：
多语句函数的主体中允许使用以下语句。
1、赋值语句。
2、控制流语句。
3,DECLARE 语句，该语句定义函数局部的数据变量和游标。
4,SELECT 语句，该语句包含带有表达式的选择列表，其中的表达
式将值赋予函数的局部变量。
5、游标操作，该操作引用在函数中声明、打开、关闭和释放的局部
游标。只允许使用以 INTO子句向局部变量赋值的 FETCH语句；不允
许使用将数据返回到客户端的 FETCH语句。
6,INSERT,UPDATE和 DELETE语句，这些语句修改函数的局部
table变量。
7,EXECUTE语句调用扩展存储过程。
第十章 SQL函数
?查看用户自定义函数的属性 —查询分析器
在 SQL Server中，根据不同需要，可以使用 sp_helptext,sp_help等系
统存储过程来查看用户自定义函数的不同信息。每个系统存储过程的
具体作用和语法如下：
使用 sp_helptext查看用户定义函数的文本信息，其语法格式为：
sp_helptext 用户自定义函数名
使用 sp_help查看用户自定义函数的一般信息，其语法格式为：
sp_help 用户自定义函数名
例：使用有关系统过程查看 STUDENT数据库中名为 XUEFEN的用户
自定义函数的文本信息。其程序代码如下：
USE STUDENT
GO
SP_HELPTEXT XUEFEN
GO
第十章 SQL函数
?查看用户自定义函数的属性 —企业管理器
在企业管理器中，用户可以很方便地查看用户定义函数的信息，其
步骤为：
1）在企业管理器中，选择需要查看的函数所在的数据库。
2）在详细信息窗格中双击户定义函数图标，在详细窗格中显示出
用户建立的函数名称。
3）右击需要查看的用户定义函数，从弹出的快捷菜单中选择“属
性”命令。
4）当出现“用户定义函数数性”对话框时，在“文本”框中可以
浏览用户定义函数的文本信息。单击“权限”按钮查看用户定义函
数的权限。
第十章 SQL函数
?修改用户自定义函数的属性 —企业管理器
1、使用企业管理器修改用户定义函数，在企业管理器中修改用户自
定义函数的步骤为：
1）在企业管理器中，选择需要修改的函数所在的数据库。
2）在详细信息窗格中双击户定义函数图标，显示出用户建立的函
数名称。
3）右击需要修改的用户定义函数，从弹出的快捷菜单中选择“属
性”命令。
4）当出现“用户定义函数数性”对话框时，在“文本”框中修改
用户定义函数的文本。
5）修改完后，单击“检查语法”按钮，语法检查通过后，单击“
确定”按钮，完成函数修改。
第十章 SQL函数
?修改用户自定义函数的属性 —查询分析器
2、使用 SQL命令修改用户自定义函数，使用 ALTER FUNCTION 命
令可以修改用户自定义函数。修改由 CREATE FUNCTION 语句创建
的现有用户定义函数，不会更改权限，也不影响相关的函数、存储
过程或触发器。其语法格式如下：
ALTER FUNCTION [ owner_name.] function_name
( [ { @parameter_name [AS] scalar_parameter_data_type [ = default
] } [,...n ] ] )
RETURNS scalar_return_data_type
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
其中的参数与建立用户自定义函数中的参数意义相同。
第十章 SQL函数
?删除用户自定义函数的属性
1、使用企业管理器删除用户定义函数，在企业管理器中删除用户自
定义函数的步骤为：
1）在企业管理器中，选择要删除函数所在的数据库。
2）在详细信息窗格中双击户定义函数图标，显示出用户建立的函
数名称。
3）选择需要删除的函数，击右键，从弹出的快捷菜单中选择“删
除”命令，出现“移除对象”对话框。
4）在“移除对象”对话框中选择“全部删除”按钮，删除用户自
定义函数。
2、使用 DROP命令删除用户自定义函数
使用 DROP命令可以一次删除多个用户自定义函数，其语法格式为：
DROP FUNCTION [所有者名称,]函数名称 [,N…]
