受立信oss刘长海第8章函数

1、第8章 函数授课教师：赵友杰计算机与信息学院 数据库原理及应用第8章 函数8.1 系统内置函数8.2 自定义函数第8章 函数8.1 系统内置函数为了让用户更方便地对数据库进行操作，SQL Server 在T-SQL 中提供了许多内置函数。函数其实就是一段程序代码，用户可以通过调用内置函数并为其提供所需的参数来执行一些特殊的运算或完成复杂的操作。T-SQL 提供的函数有系统函数、字符串函数、日期和时间函数、数学函数、转换函数等。8.1.1 系统函数系统函数用于获取有关计算机系统、用户、数据库和数据库对象的信息。可以使用户在不直接访问系统表的情况下，获取SQL Server 系统表中的信息。系统函

2、数可以让用户在得到信息后使用条件语句，根据返回的信息进行不同的操作。与其他函数一样，可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用系统函数。第8章 函数【例 8.1】 返回学生信息表中姓名字段的长度和数据长度。SELECT COL_LENGTH(StudInfo,StudName) AS Name_Col_Length,DATALENGTH(StudName) AS Name_Data_LengthFROM StudInfo第8章 函数8.1.2 字符串函数字符串函数对二进制数据、字符串和表达式执行不同的运算。此类函数作用于CHAR、VARCHAR、BINARY 和

3、VARBINARY 数据类型以及可以隐式转换为CHAR 或VARCHAR的数据类型。可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用字符串函数。1. 字符转换函数（1）ASCII功能：返回字符表达式最左端字符的ASCII 码值。语法：ASCII 返回类型：INT示例：SELECT ascii(123),ascii(A)在ASCII 函数中，纯数字的字符串可不用括起来，但含其他字符的字符串必须用括起来使用，否则会出错。第8章 函数（2）CHAR功能：用于将 ASCII 码转换为字符。语法：CHAR 返回类型：CHAR示例：SELECT char(65),Char(123

4、)如果没有输入0 255 之间的ASCII 码值CHAR()函数会返回一个NULL 值（3）LOWER功能：把字符串全部转换为小写。语法：LOWER 返回类型：VARCHAR示例：SELECT lower(Abc),lower(A 李C)第8章 函数（4）UPPER功能：把字符串全部转换为大写。语法：LOWER 返回类型：VARCHAR示例：SELECT upper(Abc),upper(a 李c)（5）STR功能：把数值型数据转换为字符型数据。语法：STR length 返回类型：CHAR示例：SELECT str(123.5),str(123456,5),str(123.456,8,2)自

5、变量length 和decimal 必须是非负值，length 指定返回的字符串的长度。decimal 指定返回的小数位数，如果没有指定长度缺省的length 值为10，decimal 缺省值为0。小数位数大于decimal 值时STR 函数将其下一位四舍五入。指定长度应大于或等于数字的符号位数+小数点前的位数+小数点位数+小数点后的位数。如果小数点前的位数超过了指定的长度，则返回指定长度的*。第8章 函数2. 去空格函数（1）LTRIM功能：把字符串头部的空格去掉。语法：LTRIM 返回类型：VARCHAR示例：SELECT Ltrim( A),rtrim( 李 )（2）RTRIM功能：把字

6、符串尾部的空格去掉。语法：RTRIM 返回类型：VARCHAR示例：SELECT Ltrim(A ),rtrim( 李 )在许多情况下,往往需要得到头部和尾部都没有空格字符的字符串这时可将上两个函数嵌套使用。【例 8.2】 去除字符串头部和尾部空格。SELECT Rtrim(ltrim( 李 )第8章 函数3. 取子串函数（1）LEFT功能：返回从字符串左边开始指定个数的字符。语法：LEFT(character_expression,integer_expression)返回类型：VARCHAR示例：SELECT LEFT(ABC,2),LEFT(A 西林C,2)（2）RIGHT功能：返回从字

7、符串右边开始指定个数的字符。语法：RIGHT(character_expression,integer_expression)返回类型：VARCHAR示例：SELECT RIGHT(ABC,2),RIGHT(A 西林C,2)（3）SUBSTRING功能：返回字符串、binary、text 表达式的一部分。语法：SUBSTRING (expression,start,length)返回类型：VARCHAR，NVARCHAR，VARBINARY示例：SELECT SubString(ABC,2,1), SubString(ABC,2,4), SubString(A 西F,1,2)第8章 函数4.

8、字符串比较函数（1）CHARINDEX功能：返回字符串中某个指定的子串出现的起始位置。语法：CHARINDEX(substring_expression,expression,start_location)返回类型：INT示例：SELECT Charindex(B,ABC),CharIndex(AD,ABCD), CharIndex(B,ABCBDE,3)其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式，start_location 表示要查询的开始位置，省略该参数默认为1。如果没有发现子串则返回0 值。此函数不能用于TE

9、XT 和IMAGE 数据类型。（2）REPLACE功能：用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。语法：REPLACE(string_expression1,string_expression2,string_expression3)返回类型：与表达式类型一致示例：SELECT Replace(ABC,B,123),Replace(ABCD,BD,ERR)第8章 函数8.1.3 日期函数日期函数用来显示关于日期和时间的信息，日期函数的数据类型为datetime 和smalldatetime 值，可以对这些值执行算术运算，最后将返回一个字符串、数字值或日期和时间值。

10、可以在SELECT 语句的SELECT 和WHERE 子句以及表达式中使用日期函数。1. DAY功能：返回date_expression 中的日期值。语法：DAY 返回类型：INT示例：SELECT Day(2008-05-12),Day(05/12/2008)2. MONTH功能：返回date_expression 中的月份值。语法：MONTH 返回类型：INT示例：SELECT Month(2008-05-12),Month(05/12/2008)第8章 函数3. YEAR功能：返回date_expression 中的年份值。语法：YEAR 返回类型：INT示例：SELECT YEAR(2

11、008-05-12),YEAR(05/12/2008)在使用日期函数时，其日期值应在1753 年到9999 年之间，这是 SQL Server 系统所能识别的日期范围，否则会出现错误。4. GETDATE功能：按datetime 数据类型格式返回当前系统日期和时间。语法：GETDATE()返回类型：datetime示例：SELECT getdate()第8章 函数5. DATEPART功能：返回代表指定日期的指定日期部分的整数。语法：DATEPART(datepart,date)返回类型：INT示例：SELECT Datepart(year,getdate(),Datepart(Month,2

12、008-05-12)Datepart 是返回的日期部分可选参数:第8章 函数6. DATEADD功能：在向指定日期加上一段时间的基础上，返回新的datetime 值。语法：DATEADD（Datepart，number，date）返回类型：Datetime示例：SELECT DATEADD（day，20，2008-05-12），DATEADD（Month，10，2008-05-12），7. DATEDIFF功能：返回跨两个指定日期的日期和时间边界数。语法：DATEDIFF(datepart,startdate,enddate）返回类型：Int示例：SELECT DATEDIFF(Day,200

13、8-05-12,2008-08-08)第8章 函数8.1.4 数学函数1. POWER功能：返回给定表达式乘指定次方的值。语法：POWER(numeric_expression,y)返回类型：与numeric_expression 相同示例：SELECT POWER(2,3),POWER(2,0)2. ROUND功能：返回数字表达式并四舍五入为指定的长度或精度。语法：ROUND(numeric_expression,length,function)返回类型：与numeric_expression 相同。示例：SELECT ROUND(24.567,2),ROUND(24.25,0)3. SQR

14、T功能：返回给定表达式的平方根。语法：SQRT(float_expression)返回类型：float示例：SELECT SQRT(4)ROUND(748.58, -1) 750.00 ROUND(748.58, -2) 700.00 ROUND(748.58, -3) 1000.00 第8章 函数8.1.5 CASE 函数1. 简单 CASE 函数【例 8.3】 查询学生信息，将性别以英文显示。SELECT StudNo,StudName,学生性别=Case StudSexWhen 男 then MaleWhen 女 then FemaleElse 性别不详endFROM StudInfo第

15、8章 函数2. CASE 搜索函数【例 8.4】 统计各学生平均分，并按等级显示。SELECT StudNo,AVG(StudScore) AvgScore,Case When AVG(StudScore)=90 then 优秀When AVG(StudScore)=80 AND AVG(StudScore)=70 then 中等When AVG(StudScore)=60 then 及格Else 不及格End AS ScoreLevelFROM StudScoreInfoGROUP BY StudNo第8章 函数8.1.6 系统内置函数应用系统内置函数不仅可以在T-SQL 程序设计中使用，也

16、可以在标准SQL 语句使用，下面举例说明系统内置函数在SQL 语句中的应用。【例 8.5】 查询姓“李”的学生信息。SELECT * FROM StudInfoWHERE Left(StudName,1)=李【例 8.6】 查询姓名中包含“文”的学生信息。SELECT * FROM StudInfoWHERE Charindex(文,StudName)0【例 8.7】 查询姓名中第二个字为“文”的学生信息。SELECT * FROM StudInfoWHERE substring(StudName,2,1)=文第8章 函数【例 8.8】 将姓名中的“云”字改为“芸”。UPDATE StudIn

17、foSet StudName=Replace(StudName,云,芸)【例 8.9】 统计学生信息表中同姓学生人数为3 人以上的信息。SELECT Left(StudName,1),COUNT(*)FROM StudInfoGROUP BY Left(StudName,1)HAVING COUNT(*)=3【例 8.11】 使用NEWID()函数实现将学生信息随机排序，每次运行排序结果不同。SELECT * FROM StudInfo ORDER BY newid()第8章 函数【例 8.10】 统计学号为20050319001 的各分数段课程门数。SELECT Case When Stud

18、Score=90 then 优秀When StudScore=80 then 良好When StudScore=70 then 中等When StudScore=60 then 及格Else 不及格End AS ScoreLevel,COUNT(*) CourseCountFROM StudScoreInfoWHERE StudNo=20050319001GROUP BY Case When StudScore=90 then 优秀When StudScore=80 then 良好When StudScore=70 then 中等When StudScore=60 then 及格Else 不及

19、格End第8章 函数8.2 自定义函数8.2.1 自定义函数简介除了使用系统提供的函数外，用户还可以根据需要自定义函数。用户自定义函数不能用于执行一系列改变数据库状态的操作，但它可以像系统函数一样在查询或存储过程等的程序段中使用，也可以像存储过程一样通过EXECUTE 命令来执行。用户自定义函数中存储了一个Transact-SQL 例程，可以返回一定的值。在 SQL Server 中根据函数返回值形式的不同将用户自定义函数分为以下类型： 标量函数（Scalar function）：返回单一的数据值。 返回数据集（RowSet）的用户定义函数：返回一个table 类型的数据集，依定义语法不同分为

20、以下两类： 行内数据集函数（或称为“内嵌数据表值函数”） 多语句数据集函数（或称为 “多语句式数据表值函数”）第8章 函数8.2.2 创建自定义函数标量函数（Scalar function）：这类函数会返回单一的数据值，而数据值的类型可以是除了text、ntext、image、cursor 及rowversion(timestamp)之外的所有类型。创建标量函数语法如下：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_type = default ,.n )RETURNS sc

21、alar_return_data_type WITH , .n AS BEGINfunction_bodyRETURN scalar_expressionEND:=ENCRYPTION | SCHEMABINDING函数的参数，可有0 个或多个（最多可有1024 个参数），而参数的名称前要加上“”。参数行必须用小括号括起来，即使没有参数，小括号也不可省略。可以用来为参数指定默认值。例如：CREATE FUNCTION myFunct(achar(10),b int=500)。声明返回值的类型设置函数的选项指定ENCRYPTION 时表示函数的内容加密，函数建立之后即无法查看其程序内容。若指定SCHEMABINDING（结构绑定）选项，则可限制在函数中所使用到的各数