Oracle中的内置函数

Oracle中的内置函数非常多，这里只介绍比较常用的。

1，字符型函数

LOWER/UPPER – 大小写转换

select upper('this is a lower string.') from dual;

CONCAT – 连接两个字符串

select concat('abc','def') from dual;

SUBSTR – 从字符串中截取子字符串

select substr('abcdef',0,4) from dual; --0表示从第一位开始截取，4表示截取4个字符。省略4表示截取到字符串末尾。

LENGTH – 返回字符串长度

select length('abcdef') from dual; -- 返回6

TRIM – 去掉字符串前后的空格

select trim(' dhc ') from dual; -- 返回‘dhc’

REPLACE – 替换部分字符串
replace(string, search string, replace string)
select replace(‘This is a Oracle DB.’,‘Oracle’,‘SQL Server’) from dual; – 返回’This is a SQL Server DB.’ 注意这里的search string是要区分大小写的。

2，数字型函数
ROUND

round(3.16,1) -- 四舍五入3.14到1位小数位，返回3.2

TRUNC

trunc(3.14,1) -- 裁断3.14到1位小数位，返回3.1

MOD

mod(3,6) -- 返回3除以6的余数

3，日期函数

Oracle提供三种与日期时间相关的数据类型：
DATE：包含世纪，年，月，日，小时，分钟和秒。通常使用date就可以满足需要。
TIMESTAMP：除包含DATE相同的部分外，还包含秒的小数位，可精确到微秒。
INTERVAL：用于存储两个时间戳之间的时间间隔。
Oracle在内部用数字格式存储日期。默认显示DATE时只显示日期，例如“2020-10-09”，但很容易更改：

alter session set nls_date_format='yyyy-mm-dd hh24:mm:dd'

a, date类型加减整数，相当于加减天数。
b, 两个date相加减，结果是两个date之间的天数差异(整数)。
c, 用小时数除以24，可以加减小时到date。
可以使用sysdate和systimestampe返回当前系统date和timestamp。

select sysdate from dual;
select date'2019-01-31' from dual; --构建一个日期值。
select to_date('2019-11-03 09:35:30','yyyy-mm-dd hh24:mi:ss') from dual; --构建一个日期值。

EXTRACT

select extract(year/month/day from sysdate) from dual; -- 注意只能用于返回年月日的正整数，不能返回星期，小时，分，秒。这里返回2020。

TO_CHAR

用to_char函数可以方便地取出date中的所有部分，包括世纪和星期这样extract函数做不到的部分。而且形式灵活，及其方便。to_char函数返回的是字符串。

select to_char(sysdate,'HH24') from dual; -- 返回小时，以24小时方式显示
select to_char(sysdate,'ww') from dual; -- 返回星期
select to_char(sysdate,'mi') from dual; -- 返回分钟
select to_char(sysdate,'yyyy/mm/dd ww hh24:mi:ss') from dual；

ROUND&TRUNC
round和trunc函数除可以用于数字外，还可以用于日期，且返回值依然是日期类型。
参考手册：round and trunc date functions

select round(to_date('2019-11-03 09:35:30','yyyy-mm-dd hh24:mi:ss'),'year') from dual
--这里可用的参数表示精确到的位置。如上例则表示精确到年的日期值，因此返回值是2020年1月1日零点。具体可选参数见手册，其中常用的是：year/month/day/dd/hh/mi
select round(to_date('2019-11-03 09:35:25','yyyy-mm-dd hh24:mi:ss'),'mi') from dual
--同理，返回的是'2019-11-03 09:35:00'的日期值，且精确到了分钟。
select round(to_date('2019-11-03 09:35:25','yyyy-mm-dd hh24:mi:ss'),'day') from dual
--注意这里返回的是当前星期的第一天。
select round(to_date('2019-11-03 09:35:25','yyyy-mm-dd hh24:mi:ss'),'dd') from dual
--这里才是当前天的零点。

select trunc(sysdate, 'year') from dual; -- trunc和round类似，只是round四舍五入而trunc是裁掉后面的部分。

4，其他函数

NVL
nvl(exp1,exp2) – 如果exp1为空，则返回exp2.

select nvl('','dfas') from dual

DECODE
decode是类似于if-then-else逻辑的函数。

decode(job,'职工',salary*1.5) -- 如果job为‘职工’，则工资涨薪50%。

RANK&DENSE_RANK
Rank既是汇总函数，也可以是分析函数，二者语法不同。这里按汇总函数来使用。例如显示工资1500在公司所有雇员中的排名是多少。注意rank的参数只能是常量，不能是变量。

select rank(1500) within group (order by sal desc)
from emp
--注意即使没有人工资是1500，rank依然会返回1500在所有人工资中的排名。

dense_rank和rank的区别是，如果并列排名的情况，如何处理。

select dense_rank(1500) within group (order by sal desc)
from emp
--如果部门工资倒序是：2000，1800，1800，1500。那么工资是1500的员工，排名应该是第4名还是第3名呢？此时rank(1500)返回第4名，而dense_rank()则返回第3名。