Oracle SQL 提供了用于执行特定操作的专用函数,这些函数大大增强了SQL语言的功能。函数可以接受零个或多个输入的参数,并返回一个输出结果,Oracle数据库主要使用两种类型的函数:
1、单行函数:对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果,比如:mod(x,y)返回x除以y的余数,常用的单行函数有:
字符函数:对字符串操作
数字函数:对数字进行运算,返回一个数字
日期函数:对日期和时间进行处理
转换函数:可以将一种数据类型
2、聚合函数:聚合函数同时可以对多行数据进行操作,并返回一个结果。比如sum(x)返回表中x列的总和。
字符函数:接受字符参数,这些参数可以是表中的列,也可以是一个字符表达式。常用字符函数:
函数 | 说明 |
ASCII(x) | 返回字符集的第一个字符的十进制表示 |
INSTR(x,str[,start][,n]) | 在x中查找str,可以指定从start开始,也可以指定从第n次开始 |
CONCAT(X,Y) | 连接两个串(与“||”相同) |
length(x) | 返回x的长度 |
lower(x) | x转换为小写 |
upper(x) | x转换为大写 |
ltrim(x[,_str]) | 把x的左边截去_str长度字符串,缺省_str截去空格 |
rtrim(x[,_str]) | 把x的右边截去_str长度字符串,缺省_str截去空格 |
trim(x) | 把x的两边截去_str长度字符串,缺省_str截去空格 |
replace(x,old,new) | 在x中查找old,并替换为new |
substr(x,start[,length]) | 返回x的字符串,从start出开始,截取length个字符,缺省length,默认到结尾 |
lpad(str,length[,set]) | 左填充,在串的左边添加一组字符,使串达到指定长度 |
rpad(str,length[,set]) | 右填充,在串的左边添加一组字符,使串达到指定长度 |
initcap(str) | 使一个单词的首字母大写或一串单词的第一个字母大写 |
[ ]内容为可选
示例:SELECT ASCII('abc') FROM dual 返回 97
SELECT INSTR('hello world','or') FROM dual 返回 8
SELECT SUBSTR ('abcde',2,2) FROM dual 返回 bc
SELECT LPAD('asc',10,'-') FROM dual 返回 Asd Sda
数字函数:接受数字参数,参数可以来自表中的一列,也可以是一个数字表达式
函数 | 说明 | 示例 |
abs(x) | x的绝对值 | abs(-2)=2 |
ceil(x) | 大于或等于x的最小整数 | ceil(5.6)=6 |
floor(x) | 小于或等于x的最大整数 | floor(5.8)=5 |
mod(x) | x除以y的余数 | mod(8,3)=2 |
power(x) | x的y次幂 | power(2,3)=8 |
sqrt(x) | x的平方根 | sqrt(4)=2 |
round(x[,y]) | x在第y位四舍五入 | round(3.456,2)=3.46 |
trunc(x[,y]) | x在第y位截断 | reunc(3.456,2)=3.45 |
1、round(x[,y]),四舍五入
在缺省y时,默认y=0;如:round(3.56) = 4 。
y是正整数,就是四舍五入到小数点后y位。round(3.654,2) = 3.65 。
y是负整数,四舍五入到小数点左边|y|位。round(351.654,-2)=400 。
trunc(x[,y])也是类似。
日期函数:
日期函数对日期进行计算。常用的有:
1、ADD_MONYHS(d,n),在某一个日期d上,加上指定的月数n,返回计算后的新日期,d表示日期,n表示要加的月数
SELECT add_months(Sysdate,2) FROM dual 返回 2017/10/10 9:45:25
2、last_day(d) ,返回指定日期的当月的最后一天。
SELECT last_day(sysdate)FROM dual 返回 2017/8/31 9:47:34
3、extract(fmt from d),提取日期中的特定部分。
fmt 为:YEAR,MONTH,DAY , HOUR , MINUTE , SECOND .其中YEAR , MONTH , DAY 可以为DATE类型匹配,也可以与TIMESTAMP 类型匹配;但是HOUR , MINUTE , SECOND 必须与TIMESTAMP类型匹配。
转换函数
转换函数将值从一种数据类型转换为另一种数据类型。常用的有:
1、 TO_CHAR(d,fmt)
把日期或数字转换为指定格式的字符串。fmt是格式化字符串
SELECT to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:MI:ss ') FROM dual 返回
2017年08月10日 10:41:19
2、 TO_DATE(x,fmt)
将一个字符串以fmt的格式转换为一个数字
其它单行函数
1、NVL(x,value)
如果x为空,就返回value,否则返回x 。
2、NVL2(x,value1,value2)
如果x非空,返回value1,否则返回value2 。
聚合函数
聚合函数同时对一组数据进行操作,返回一行结果,比如计算一组数据的总和,平均值等
函数 | 作用 |
AVG | 平均值 |
SUM | 求和 |
MIN | 最小值 |
MAX | 最大值 |
COUNT | 数据统计 |