Oracle 数据库 常用函数及其使用示例

常用函数

数值型函数

 

round(x[,y])

【功能】返回四舍五入后的值 【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则四舍五入为y位小数，如果y小于0则四舍五入到小数点向左第y位。 【返回】数字 【示例】 select round(5555.6666,2.1),round(5555.6666,-2.6),round(5555.6666) from dual; 返回：   5555.67     ,    5600    ,    5556

 

trunc(x[,y])

【功能】返回x按精度y截取后的值 【参数】x,y，数字型表达式,如果y不为整数则截取y整数部分，如果y>0则截取到y位小数，如果y小于0则截取到小数点向左第y位，小数前其它数据用0表示。 【返回】数字 【示例】 select trunc(5555.66666,2.1), trunc(5555.66666,-2.6),trunc(5555.033333)  from dual; 返回：5555.66                    5500               5555

 

字符型函数

LENGTH(c1)

【功能】返回字符串的长度; 【说明】多字节符(汉字、全角符等)，按1个字符计算 【参数】C1 字符串 【返回】数值型 【示例】 select length('it'),length('zxit') from dual;   LENGTH('it') LENGTH('zxit') ------------------ ------------------------                  4                       10

 

 

LPAD(c1,n[,c2])、RPAD(c1,n[,c2])

【功能】在字符串c1的左（右）边用字符串c2填充，直到长度为n时为止 【说明】如果c1长度大于n，则返回c1左边n个字符 【参数】C1 字符串         n 追加后字符总长度         c2 追加字符串,默认为空格 【返回】字符型   【示例】 select lpad('zx',10,'*'),rpad('zx',10,'*') from dual;

 

 

REPLACE(c1,c2[,c3])

【功能】将字符表达式值中，部分相同字符串，替换成新的字符串 【参数】         c1   希望被替换的字符或变量         c2   被替换的字符串         c3   要替换的字符串，默认为空(即删除之意，不是空格) 【返回】字符型   【示例】 select replace('he love you','he','i') from dual;

 

 

SUBSTR(c1,n1[,n2])

【功能】取子字符串 【说明】多字节符(汉字、全角符等)，按1个字符计算 【参数】在字符表达式c1里，从n1开始取n2个字符;若不指定n2,则从第n1个字符直到结束的字串. 【返回】字符型   【示例】 select substr('123456789',4,4),substr('123456789',3) from dual;

 

日期函数

 

sysdate

【功能】：返回当前日期。 【参数】：没有参数，没有括号 【返回】：日期   【示例】select sysdate from dual;

 

add_months(d1,n1)

【功能】：返回在日期d1基础上再加n1个月后新的日期。 【参数】：d1，日期型，n1数字型 【返回】：日期   【示例】select sysdate,add_months(sysdate,3) from dual;

 

months_between(d1,d2)

【功能】：返回日期d1到日期d2之间的月数。 【参数】：d1，d2 日期型 【返回】：数字 如果d1>d2，则返回正数 如果d1<d2，则返回负数   【示例】 select sysdate, months_between(sysdate,to_date('2015-01-01','YYYY-MM-DD')) 距2015元旦, months_between(sysdate,to_date('2016-01-01','YYYY-MM-DD')) 距2016元旦 from dual;

 

extract(c1 from d1)

【功能】：日期/时间d1中，参数(c1)的值 【参数】：d1日期型(date)/日期时间型(timestamp),c1为字符型(参数) 【参数表】：c1对应的参数表详见示例 【返回】：字符   【示例】 select  extract(YEAR from timestamp '2015-5-1 12:26:18 ' ) 年, extract(MONTH from timestamp '2015-5-1 12:26:18 ' ) 月, extract(DAY from timestamp '2015-1-5 12:26:18 ' ) 日, extract(hour from timestamp '2015-5-1 12:26:18 ' ) 小时, extract(minute from timestamp '2015-5-1 12:26:18' ) 分钟, extract(second from timestamp '2015-5-1 12:26:18 ' ) 秒  from dual;   select extract (YEAR from date '2015-5-1' ) from dual;    select sysdate 当前日期, extract(YEAR from sysdate ) 年, extract(MONTH from sysdate ) 月, extract(DAY from sysdate ) 日 from dual;   --如下语句也可获取年份、月份等 select to_number(to_char(sysdate,'yyyy')) from dual;

 

转换函数

TO_CHAR(x[[,c2],C3])

【功能】将日期或数据转换为char数据类型 【参数】        x是一个date或number数据类型。        c2为格式参数        c3为NLS设置参数 【返回】varchar2字符型 【示例】 select to_char（sysdate,'YYYY-MM-DD HH24:MI:SS'） FROM dual; select to_char（1210.7, '$9,999.00'） FROM dual;

 

 

TO_DATE(X[,c2[,c3]])

【功能】将字符串X转化为日期型 【参数】c2,c3,字符型，参照to_char() 【返回】字符串 如果x格式为日期型(date)格式时，则相同表达：date x 如果x格式为日期时间型(timestamp)格式时，则相同表达：timestamp x   【示例】 select to_date('201212','yyyymm'), to_date('2012.12.20','yyyy.mm.dd'), (date '2012-12-20') XXdate,  to_date('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'), to_timestamp('2012-12-20 12:31:30','yyyy-mm-dd hh24:mi:ss'), (timestamp '2012-12-20 12:31:30') XXtimestamp from dual;

 

 

TO_NUMBER(X[[,c2],c3])

【功能】将字符串X转化为数字型 【参数】c2,c3,字符型 【返回】数字串   【示例】 select TO_NUMBER('201212') + 3,TO_NUMBER('450.05') + 1 from dual; --等同上述结果 select '201212' + 3 from dual;

 

​​​​​​​​​​​​​​聚合函数

 

sum:求和

avg:求平均数

count：计数

max：求最大值

min：求最小值

​​​​​​​​​​​​​​分析函数

分析函数中了解rank()/dense_rank()/row_number()的使用：

--查询部门的员工工种情况，并在部门内重新进行排序；PARTITION BY类似group by,根据ORDER BY排序字段的值重新由1开始排序。   --RANK 使用相同排序排名一样，后继数据空出排名；即有2个排序为1的，那么接下来的排序号则为3 select deptno,ename,job,rank() over(partition by deptno order by job) as myRank from emp e;   --DENSE_RANK使用，使用相同排序排名一样，后继数据不空出排名；即有2个排序为1的，那么接下来的排序号则为2 select deptno,ename,job,dense_rank() over(partition by deptno order by job) as myDenseRank from emp e;   --ROW_NUMBER使用，不管排名是否一样，都按顺序排名；即有2个排序为1的，那么排序号不会重现重复 select deptno,ename,job,row_number() over(partition by deptno order by job) as myRowNumber from emp e;

 

​​​​​​​​​​​​​​其它函数

NVL()/NVL2()

【语法】NVL (expr1, expr2) 【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。注意两者的类型要一致 【示例】将员工的奖金如果是空的话则设置为0  select ename,sal,comm,nvl(comm,0) from emp;     【语法】NVL2 (expr1, expr2, expr3) 【功能】expr1不为NULL，返回expr2；expr2为NULL，返回expr3。 expr2和expr3类型不同的话，expr3会转换为expr2的类型 【示例】 select ename,job,nvl2(job,'job有值','job无值') from emp;

 

 

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)

【功能】根据条件返回相应值 【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null 注：值1……n 不能为条件表达式,这种情况只能用case when then end解决   含义解释：　　 　　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)　　 　　该函数的含义如下：　　 　　IF 条件=值1 THEN 　　RETURN(翻译值1) 　　ELSIF 条件=值2 THEN 　　RETURN(翻译值2) 　　...... 　　ELSIF 条件=值n THEN 　　RETURN(翻译值n)　　 　　ELSE 　　RETURN(缺省值) 　　END IF   【示例】根据员工的部门号，条件判断找到对应的部门名称 select ename,deptno,decode(deptno,10,'ACCOUNTING',20,'RESEARCH',30,'SALES','无部门') from emp;