Oracle函数汇总

SQL 函数

日期函数：可以进行算术运算

SYSDATE	返回系统日期	select sysdate from dual;
ADD_MONTHS(<d>,<i>)	返回日期d 加上i个月后的新日期(i正可负)。	Add_Months(sysdate,2) Add_Months(sysdate,-2)
LAST_DAY(<d>)	返回日期d所在的月的最后一天。	Select Last_Day(sysdate) from dual;
MONTHS_BETWEEN(<d1>,<d2>)	返回日期d1比d2大多少月数。	Months_Between(’19-1月-2008’, ’19-1月-2009’)
NEW_TIME(<d>,<tz1>,<tz2>)	将时区tz1的时间d，转换为时区tz2里的时间。	SELECT SYSDATE,NEW_TIME(SYSDATE,'CDT','PDT') FROM DUAL;
NEXT_DAY(<d>,<dow>)	返回日期d后的第一个dow。(dow：day of week)	Select Next_Day(sysdate,’星期一’) from dual;
EXTRACT	用于提取日期时间类型的特定部分	Select extract(month from sysdate) from dual;
ROUND	四舍五入	Round(sysdate,’Year’)精确到年
TRUNC	截断日期	Trunc(sysdate,’Month’)截断月份

字符函数

LPAD(<c1>,<i>[,<c2>])	在字符串c1的左边添加字符串c2直到c1字符串的长度等于i。	SELECT LPAD('Hello!',8,'*') leftpad,RPAD('Hello!',8,'*') rightpad FROM DUAL;
RPAD(<c1>,<i>[,<c2>])	在字符串c1的右边添加字符串c2直到c1字符串的长度等于i。
LOWER(<c1>)	把字符串c1转换为小写。	SELECT LOWER(ename) 小写,UPPER(ename) 大写, INITCAP(ename) 首字符 FROM EMP;
UPPER(<c1>)	把字符串c1转换为大写。
INITCAP(<c1>)	把c1字符串的每一个单词的第一个字母转换成大写字母。
LENGTH(<c1>)	返回字符串c1的长度。	SELECT LENGTH('How are you') FROM DUAL;
SUBSTR(<c1>,<i>[,<j>])	返回字符串c1中从第i个位置开始的j个字符（向右）。如果省略j，则返回c1中从第i个位置开始的所有字符。如果j为负，则返回字符串c1中从第i个位置开始的j个字符（向左）。	SELECT SUBSTR('Hello,World',1,5) FROM DUAL; 1不是下标
INSTR(<c1>,<c2>[,<i>[,<j>]])	在c1中从位置i开始查找c2在c1中出第j次的位置，i可以为负(此时，从c1的尾部开始)。	SELECT INSTR('Mississippi','i',3,3) FROM DUAL; 返回结果11。 SELECT INSTR('Mississippi','i',-2,3) FROM DUAL; 返回结果2。
LTRIM(<c1>,<c2>)	从c1前面开始去掉出现在c2的中任何前导字符集。	SELECT LTRIM('Mississippi','Mis') FROM DUAL; 返回结果'ppi'。 SELECT RTRIM('Mississippi','ip') FROM DUAL; 返回结果'Mississ'
RTRIM(<c1>,<c2>)	从c1后面开始去掉出现在c2的中任何前导字符集。 默认去掉空格 Trim()，去掉两端空格
Concat (<c1>, <c2>)	用于连接c1，c2两个表达式	Select concat ('Hello',' world') from dual;
Replace(<c1>,<c2>,[<c3>] )	用c3替换c1中所有c2字符，如果没有c3，表示去掉c2字符	Select replace('jack and jue' ,'j','bl') from dual;
CHR和ASCII	字符与ASCII值之间的转换
DECODE(<x>,<m1>,<r1>[,<m2>,<r2…>])	功能类似于一系列的if…then…else语句。 如果deptno为10，用学术部替代 如果为20，用市场部替代 如果为30，用就业部替代	select deptno,decode(deptno,10,'学术部',20,'市场部',30,'就业部') from emp;

数字函数

转换函数

TO_CHAR(<x>[,<fmt>[,<nlsparm>]])	将x转换成字符串。	Select to_char(sysdate,’yyyy-mm-dd’) from dual; Select to_char(123456,’$999,999’) from dual;
TO_NUMBER(<c>[,<fmt>[,<nlsparm>]])	将字符串c转换成数字。	Select to_number(‘123’) from dual;
TO_DATE(<c>[,<fmt>[,<nlsparm>]])	将字符串c转换成日期。	Select to_date(‘2009-7-8’,’yyyy-mm-dd’) from dual;

SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual;

其他函数

NVL(x1,x2)	如果x1为空返回x2，否则返回x1。	SELECT NVL(comm,0) FROM EMP;
NVL2(x1,x2,x3)	如果x1为空返回x3，否则返回x2	select nvl2(comm,2000,0) from emp;
NULLIF(x1,x2)	如果x1=x2返回空，否则返回x1	select nullif(comm,300) from emp;

分组函数

Group by子句，having筛选

分析函数

用于计算一个行在一组有序行中的排位排位从1开始。如计算聚集的累积排名、移动平均数和报表聚合值。

ROW_NUMBER	返回连续的排位，不论值是否相等
RANK	具有相等值的行排位相同，序号随后跳跃
DENSE_RANK	具有相等值的行排位相同，序号是连续的

select ename 姓名,sal 薪水,

row_number() over(order by sal) row_number,

rank() over(order by sal) rank,

DENSE_RANK() over(order by sal) dense_rank from emp;