Oracle常用内置函数

单行函数分类:
1.数值函数:
2.字符函数
3.日期函数
4.转换函数
5.通用函数

一:数值函数

 1.ABS:绝对值 ABS(-1.2) = 1.2
 2.CEIL:删除小数点,向上取整 CEIL(4.3) = 5
 3.FLOOR:删除小数点,向下取整 FLOOR(4.3) = 4
 4.POWER:指数函数 POWER(4,3) = 64
 5.RAND:生产0~1的随机数 RAND() = ?
 6.ROUND:四舍五入,指定保留的小数位 ROUND(2.2345,2) = 2.23
 7.SQRT:平方根,不能是负数 SQRT(64) = 8 
 8.TRUNC:按指定小数位裁剪,没有四舍五入 TRUNC(12.2356,3) = 12.235
 9.MOD:求余数 MOD(25,8) = 1

二:字符串函数

1.大小写转换
1.1 UPPER 全部大写
1.2 LOWER 全部小写
1.3 INITCAP 首字母大写

2.处理函数
2.1 CONCAT 连接字符串

 2.2 SUBSTR 截取字符串

 2.3 LENGTH 字符串长度

 2.4 INSTR 判定字符串释放会出现在被查找的字符串中,并返回首次出现的位置  instr('abcd','c') = 3
      2.4.1 INSTR('原字符串' , '匹配字符串' , 从第几个位置开始匹配 , 第几个以及匹配上的)  INSTR('ABCABCABC','ABC',-2,2) = 4 从右边开始第二个字符开始匹配,匹配上的第二个的开始位置的4

 2.5 LPAD 从左边开始,返回指定长度的字符串,不够长度就使用字段字符串填充  LPAD('123',5,'*') = '**123'   /  LPAD('12345',2) = 12 

 2.6 RPAD 从右边开始返回指定长度字符串,不够就使用指定字符串进行填充

 2.7 TRIM 删除字符串或者数字的头部/尾部的给定字符串 TRIM('*' FROM '***123***') = '123' / 默认去掉两边空格
      2.7.1 可以指定去掉左边的字符串  TRIM(LEADING '*' RFOM '**123***') = '123***'
      2.7.2 可以指定去掉右边的字符串  TRIM(TRAILING '*' FROM '***123***') = '***123'
      2.7.3 可以指定去掉两边的字符串  TRIM(BOTH '*' FROM '***123***') = '123'
 
 2.8 LTRIM / RTRIM 类似TRIM,只是左边右边 LTRIM('***123***','*') = '123***'

 2.9REPLACE 替换 REPLACE('***123***','123','ABC') = '***ABC***'

 3.0 SUBSTR 截取指定长度的字符串,注意不同规则
      3.0.1 SUBSTR('***123***',3,5) = '123**' 从第三个位置开始截取5个字符
      3.0.2 SUBSTR('***123***,5') = '23***' 指从第(5-1) 个位置截取后边的所有
      3.0.3 SUBSTR('***123***',-5,3) = '23*' 从右边往左数,第5个开始截取3个字符
      3.0.4 SUBSTR('***123***,-4') = '3***' 从右边开始截取4个字符

三:日期时间操作函数

1.SYSDATE : 获取当前系统时间
2.MONTHS_BETWEEN : 得到两个日期之间的月份
3.ADD_MONTHS : 增加月份数
4.NEXT_DAY : 返回下个指定星期的准确日期
5.LAST_DAY : 当月最后一天
6.ROUND : 对日期进行四舍五入
7.TRUNC : 对日期进行截取

示例:
1.
SELECT to_char(SYSDATE , ‘CC WW W D DDD YYYY/MM/DD YEAR MONTH DAY’) FROM dual;
结果: 21 02 2 3 014 2020/01/14 TWENTY TWENTY 1月 星期二

解释:
CC : 世纪
WW : 一年中的周
W : 一个月的周
D : 星期几
DDD : 一年中的第几天
YYYY/MM/DD : 年月日
YEAR : 年
MONTH : 月
DAY : 日

2.返回当天星期几
SELECT to_char(SYSDATE,‘day’) FROM dual;
结果: 星期二

3.返回月份差
SELECT months_between(‘20-1月 -20’,‘15-3月 -19’) FROM dual;
结果:10.1612903225806

4.返回指定下个星期几的日期
SELECT next_day(SYSDATE,‘星期二’) FROM dual;
结果:2020/1/21 10:03:59

5.当月最后一天
SELECT last_day(SYSDATE) FROM dual;
结果:2020/1/31 10:04:53

四:转换操作函数

主要有三:
TO_CHAR
TO_NUMBER
TO_DATE

1.TO_CHAR
转成指定格式日期
SELECT to_char(SYSDATE,‘YYYY-MM-DD HH12:MI:SS’) FROM DUAL;
结果: 2020-01-14 10:11:25

加上FM可以去掉01月前面的0
SELECT to_char(SYSDATE,‘FMYYYY-MM-DD HH12:MI:SS’) FROM DUAL;
结果:2020-1-14 10:11:44

数值转换字符串
SELECT to_char(12345678.900 , ‘L99,999,999.9999’) FROM dual;
结果:￥12,345,678.9000
解释:(‘L99,999,999.9999’ )
L:代表强制使用当地货币符号
$:显示美元符号
9:代表一个数字
0:强迫显示0
.:显示一个小数点
,:显示一个千分位分隔符

2.TO_NUMBER
把字符串转数值型
SELECT to_number(’$123,122.34’,’$000,000.0000’) FROM dual;
结果:123122.34

将十进制数值转成十六进制数值
SELECT to_number(12345,‘xxxxxx’) FROM dual;
结果:74565

3.TO_DATE
SELECT to_date(‘20190101’,‘YYYY-MM-DD’) FROM dual;
结果:2019/1/1

五:辅助函数

1.DECODE (value,if1,then1,if2,then2,if3,then3,…,else)
SELECT DECODE(5,‘5’,’[0-5]’,‘10’,’[6-10]’,’[~]’) FROM dual;
结果:[0-5]

2.GREATEST(n1,n2…)返回序列中最大值
SELECT GREATEST(1,2,3,6,8,1,2,9) FROM dual;
结果:9

3.LEAST(n1,n2…)返回序列中最小值
SELECT LEAST(1,2,3,4,5,6,2,1,2) FROM dual;
结果;1

4.NULLIF(c1,c2) 如果c1=c2 返回null,否则返回c1
SELECT NULLIF(‘4’,‘4’) FROM dual;
结果: ‘4’

5.nvl(c1,c2) 如果c1=null则返回c2,否则返回c1
SELECT nvl(’’,‘3’) FROM dual;
结果:‘3’

6.nvl2(c1,c2,c3) 如果c1不为null则返回c2,否则返回c3
SELECT NVL2(’’,‘a’,‘b’) FROM dual;
结果:b

7.CHR()返回字符集对应的字符
SELECT CHR(97) FROM dual;
结果:a

8.CONCAT(c1,c2)连接字符串 , 等同于 ||
SELECT CONCAT(1,‘3’) FROM dual;
SELECT 1 || ‘3’ FROM dual;
结果: 都是 ‘13’

9.INITCAP() 将第一个字母大写,其余小写
SELECT INITCAP(‘aBC’) FROM dual;
结果: Abc

10.NLS_INITCAP() 等同于 INITCAP()
SELECT nls_initcap(‘aBC’) FROM dual;
结果:Abc

11.ASCII() 把字符转换数字 , 和CHR()相反
SELECT ASCII(‘a’) FROM dual;
结果:a

12.DBTIMEZONE 返回数据库当前时区
SELECT DBTIMEZONE FROM dual;
结果: +00:00