1.SQL函数简介
函数,和Java中的方法类似,是完成一定功能的代码集合。SQL函数是SQL语言中的一个强有力的特征,被广泛使用。SQL函数可以完成如下功能:
- 执行数据计算。
- 修改单个数据项。
- 格式化显示的日期和数字。
- 进行数据类型转换。
- 根据行分组操纵输出。
与Java中的方法不同的是,SQL函数有输入函数,并且总有一个返回值。由于篇幅的关系,本章只介绍程序员常用的函数。
根据函数所操纵的数据行数的不同,可以将SQL函数分为两类,单行函数和多行函数。其中单行函数仅对单个函数进行计算,并且每行返回一个结果。在单行函数中,又可根据操作对象的不同分为字符函数、数字函数、日期函数、转换函数和通用函数。而多行函数可以操作成组的多个行,每个行组返回一个结果,这些函数又称为组函数。单行函数可操作的输入参数,可以是常量、变量、字段名和表达式。单行函数可以在SELECT、WHERE和ORDER BY子句中出现,也可以进行嵌套。
SQL函数分类:
单行函数分类:
2.字符函数
单行字符函数接受字符数据作为输入,既可以返回字符值也可以返回数字值。作为输入字符数据,既可以是字段名也可以是表达式。
表:单行字符函数
函数 | 说明 |
LOWER(input) | 将输入字符值全部转换为小写 |
UPPER(input) | 将输入字符值全部转换为大写 |
INITCAP(input) | 将每个单词的首字母转换成大写,其他值为小写 |
CONCAT(input1,input2) | 连接第一个字符值到第二个字符值,等价于连接符"||" |
SUBSTR(input,m[,n]) | 获取字符值中指定的字符,从m位置开始,取n个字符长度,如果n被忽略,则取到字符值结尾处 |
LENGTH(input) | 返回字符值的字符数 |
INSTR(input,char [,m][,n]) | 返回在字符值中查找字符串char的数字位置。参数m作为查找的开始,参数n代表第n次发现。m和n的默认值是1,即默认从开始位置查找,并且报告第一个发现的位置 |
REPLACE(input,char 1,char 2) | 从字符值中查找字符串char1,找到则替换成char2 |
LPAD(input,n,char) RPAD(input,n,char) | 从左边(或右边)对字符使用指定的字符插char进行填充,直到满足参数n的长度要求 |
表:单行字符函数示例
函数 | 结果 |
concat('Oracle','DB') | OracleDB |
substr('OracleDB',1,6) | Oracle |
length(;OracleDB') | 8 |
instr('OracleDB','DB') | 7 |
replace('OracleDB','DB','Database') | OracleDatabase |
lpad('OracleDB',12,'*') | ****OreacleDB |
3.数字函数
数字函数,接受数字输入并返回数字值
表:单行数字函数
函数名 | 说明 |
ROUND(input[,n]) | 将数字值四舍五入,参数n表示小数位,如果n被忽略,则无小数位 |
TRUNC(input[,n]) | 将数字值截断,参数n表示截断到的小数位,如果n被忽略,则默认为0 |
MOD(m,n) | 返回m除以n的余数 |
表:单行数字函数示
函数 | 结果 | 函数 | 结果 |
trunc(12.45678) | 123 | trunc(123.45678,-3) | 0 |
trunc(123.456,3) | 123.456 | trunc(123.45678,-2) | 100 |
mod(21,5) | 1 | mod(-21,5) | -1 |
4.日期函数
在Oracle数据库中,以数字格式存储日期值,分别表示世纪、年、月、日、小时、分和秒。对于日期,Oracle数据库默认显示和输入格式是DD-MON-RR,但是在其他客户端工具中,可能会以不同的格式进行显示,比如在PL/SQL Dev中,显示格式YYYY/MM/DD,例如2013/6/17
虽然默认的显示和输入格式是DD-MON-RR,但是存储在数据库的日期不是这种格式,所有日期和时间的组成部分都会被存储。也就是说,17-JUN-13 显示的是日、月、和年,但日期和时间的信息也包含在日期类型里。一个完整的日期类型如下:
2013年6月17日11:22:33。
日期函数SYSDATE,它返回当前数据库服务器的日期和时间(在SQL Server中使用GETDATE()方法)。例如:获取数据库服务器当前时间;
select sysdate from dual;
其中dual是一个空表,所以可以把数据放在dual中显示。
既然Oracle数据库以数字方式存储日期,也就可以用算术运算符对日期类型进行计算。在日期上加或减一个数,得到的是增加或减少该天数后的日期类型,而用两个日期类型相减,得到的是这两个日期相差的天数。
例如得到,员工编号7369为的员工,截止今天工作了多少天:
select ename,job,sal,round(sysdate-hiredate)"天数" from emp where empno = 7369;
得到结果:
表:常用日期函数
函数 | 说明 |
MONTHS_BETWEEN(date1,date2) | 两个日期之间的月数 |
ADD_MONTHS(date,n) | 添加n个日历月后的日期 |
NEXT_DAY(date,char) | 计算指定日期之后的下一周char指定天的日期,char的值可以是一个表示星期的数或者字符串 |
LAST_DAY(date) | 计算包含指定日期所在月的最后一天日期 |
ROUND(date[,'fmt']) | 按格式化模式fmt四舍五入到指定单位的日期,如果格式化模式fmt被忽略,则四舍五入到天 |
TRUNC(date[,'fmt']) | 按格式化模式fmt截断到指定单位的日期,如果格式化模式fmt被忽略,则截断到天 |
表:日期函数示例
函数 | 结果 |
months_between('17-jun-13','21-jan-11') | 28.879677419355 |
add_months('17-jun-13',10) | '17-apr-14' |
next_day('17-jun-13','sunday') | '24-jun-13' |
last_day('17-jun-13') | '30-jun-13' |
在英文系统中,月用三个字母作为缩写,round和trunc函数用于日期时,这些函数按指定的格式化模板四舍五入或截断,默认到日,也可以四舍五入或截断到月或年。
表:日期函数示例
函数(假定sysdate值为'17-jun-13') | 结果 |
round(sysdate,'month') | '01-jul-13' |
round(sysdate,'year') | '01-jan-13' |
trunc(sysdate,'month') | '01-jun-13' |
trunc(sysdate,'year') | '01-jan-13' |
注意:在对月份进行四舍五入的时,日期1日到15日结果在当前月的第一天,日期16日到31日结果在下月第一天;如果对年进行四舍五入,1月到6月结果在当前年的1月1日,7月到12月的结果在下年的1月1日。