第9章 Transact-SQL程序设计2

入的日期数据转换为该日期对应的季度值。如输入 '2006-8-5'，返回'3Q2006'，表示2006年3季度。
CREATE FUNCTION DatetoQuarter(@dqdate datetime) RETURNS char(6) AS BEGIN RETURN(datename(q,@dqdate)+'Q'+datename(yyyy,@dqdate)) END
数学函数
abs(x)：求绝对值； select abs(-3) 值为3 sqrt(x)：求平方根； select sqrt(4) 值为2.0 rand( )：返回0~1之间的随机float值； floor(X)：返回小于或等于X值的最大整数； select floor(78.5), floor(123), floor(-78.5) ceiling(X)：返回大于或等于X值的最小整数； select ceiling(78.5), ceiling(123), ceiling(-78.5)
1. 创建用户定义函数
(1) 使用CREATE FUNCTION创建用户定义函数 • 标量函数的语法格式：
CREATE FUNCTION 函数名称
（形式参数名称 [AS] 数据类型） RETURNS 返回数据类型 [AS] BEGIN 函数内容 RETURN 表达式 END
例9-18 创建一个用户定义函数DatetoQuarter，将输
(1) 内置函数:是一组预定义的函数，是Transact-SQL语言 的一部分，按Transact-SQL参考中定义的方式运行且不 能修改。 (2) 用户定义函数:由用户定义的Transact-SQL函数。它将 频繁执行的功能语句块封装到一个命名实体中，该实 体可以由Transact-SQL语句调用。
select ‘MICROSOFT’+ rtrim(‘ sql_server ‘) + ’2000’ select ‘MICROSOFT’+ Ltrim(‘ sql_server ‘)+’2000’
• space(个数)：返回指定个数的空格。 例：select ‘ab’+space(2)+’cd’
DATENAME函数
1).格式： DATENAME(datepart,date) 2).功能：返回日期date中由datepart指定的日期部分的 字符串。 3).返回值数据类型：nvarchar 例如： datename(yy,'1993-3-4') --结果为'1993' datename(mm,'1993-3-4') --结果为'03' datename(dd,'1993-3-4') --结果为'4'
DATEPART函数
1).格式：DATEPART(datepart,date) 2).功能：与DATENAME相似，只是返回值为整数。 3).返回值数据类型：int 例如：datepart(yy,'1993-3-4') --结果为1993 datepart(m,'1993-3-4') --结果为3 datepart(d,'1993-3-4') --结果为4
Sign(X)：求符号函数，X>0则sign(x)=1; X=0 则sign(X)=0;X<0 则sign(X)=-1 例：sign(-34) Power(x,y)：求表达式的y次方， 例：power(3,2) 例：计算3的4次方及16的平方根
例9-12 用ceiling和floor函数返回大于或 等于指定值的最小整数值和小于或等于 指定值的最大整数值。
• replicate(串,次数)：将串重复指定次数， 例: select replicate(‘中国’,2) • Left(串, 个数)：返回已知串从左边开始指定个 数的字符。 例：select left('我的祖国abc', 4)
select replicate(left(‘abc’,2),4)
• Replace(‘串1’,’串2’,’串3’)：用串3替换串1中出 现的所有串2字符，例：
SELECT 院’) replace(‘理工光电’,’光电’,’光电信息学
• Stuff(串1, 开始位置, 长度, 串2)：删除串1 指定位置开始指定长度的字符串，并在指定位 置插入串2 select stuff('我的祖国abc',5,2,'小花') 结果是：我的祖国小花c
例：SELECT ‘ABC’+STR(123.5,5,1)
ABC123.5
Lower(串)：把字符串全部转换为小写， 例：select lower(‘SmallFish’),lower(‘SMALLFISH’) Upper(串) ：把字符串全部转换为大写， 例：select upper(‘SmallFish’),upper(‘SMALLFISH’) LTrim(串), RTrim(串)：去掉引导空格或者尾空格 例：
SELECT ceiling(123),floor(321), ceiling(12.3), ceiling(-32.1),floor(- 321 13 -32 -33
例9-13 round函数的使用。
SELECT round(12.34512,3), round(12.34567,3), round(12.345,-2), round(54.321,-2)
round(x，length)：四舍五入函数，length为正，则对X 小数位数四舍五入，length为负，则对X从小数点左边 length位起四舍五入，若length既为负数且其绝对值 大于X整数部分数字个数，则函数值为0。 ROUND(63.567, 1) ROUND(63.567, 0) ROUND(63.567, -1) ROUND(63.567, -2) ROUND(63.567, -3) ROUND(63.567, -4)
• SubString(串,开始位置,长度)：返回从字符 串左边’开始位置’起数量为’长度’的字符 串。其中表达式可以是字符串或二进制串或含 字段名或字符型变量的表达式。SUBSTRING() 函数不能用于TEXT 和IMAGE 数据类型
select substring('我的祖国abc', 5, 2)
(2) 内嵌表值函数
内嵌表值函数返回的函数值为一个表。内嵌表值函数的函数体不 使用BEGIN„END语句，其返回的表是RETURN子句中的SELECT命令 查询的结果集，其功能相当于一个参数化的视图。
(3) 多语句表值函数
多语句表值函数可以看作标量函数和内嵌表值函数的结合体。其 函数值也是一个表，但函数体也用BEGIN„END语句定义，返回值 的表中的数据由函数体中的语句插入。
字符串函数
ASCII(串)：返回字符表达式最左端字符的ASCII 码值，例：ASCII(‘abc’)=97 CHAR(ASCII码)：用于将ASCII 码转换为字符，如 果没有输入0 ~ 255 之间的ASCII 码值，返回 值为NULL 。例：char(97)=’a’
STR(X[,length[,decimal]])：把数值型数据转换为 字符型数据 说明： length 和decimal 必须是非负值， length 指定返回的字符串的长度，decimal 指定 返回的小数位数。
DAY函数
1)格式：DAY(date) 2)功能：返回指定日期date中天的整数。 3)返回值数据类型：int 例如：select day(‘2004-3-5’) --结果为5
9.2.2 用户定义函数
根据函数返回值形式的不同将用户定义函数分为3种类型。 (1) 标量函数
标量函数返回一个确定类型的标量值，其函数值类型为SQL Server的系统数据类型（除text、ntext、image、cursor、 timestamp、table类型外）。函数体语句定义在BEGIN„END语句 内。
YEAR函数
1).格式：YEAR(date) 2).功能：返回指定日期date中年的整数。 3).返回值数据类型：int 例如：select year('2004-3-5') --结果为2004
MONTH函数
1)格式：MONTH(date) 2)功能：返回指定日期date中月份的整数。 3)返回值数据类型：int 例如：select month('2004-3-5') --结果为3
ss ms
1-59 0-999
GETDATE函数
1).格式：GETDATE() 2).功能：按SQL Server 2000内部标准格式返回系统日期和 时间。 3).返回值数据类型：datetime 例如： select getdate() --结果为2007-11-21 21:51:32.390
执行语句： SELECT dbo.DatetoQuarter('2006-8-5')
内嵌表值函数的语法格式：
CREATE FUNCTION 函数名称 (形式参数名称 [AS] 数据类型 [ ,...n ]) RETURNS TABLE [ AS ] RETURN ( select_stmt )
日期元素及其取值范围
元素名称 Year Quarter Month Dayofyear Day Week Hour Minute 缩写 yy qq mm dy dd wk hh mi 取值范围 1753--9999 1-4 1-12 1-366 1-31 0-51 0-23 1-59
Second Millisecond
DATEDIFF函数
1).格式：DATEDIFF(datepart,date1,date2) 2).功能：返回两个日期间的差值并转换为指定日期元素 的形式。 3).返回值数据类型：整型 例： Select datediff(yy,’1989.03.03’,’2003.04.05’) --值为14 Select datediff(mm,’1989.03.03’,’2003.04.05’) --值为169