java开发中常用Oracle函数实例总结比较，当真不少

昨天分享了一篇基础SQL语句，结果大家反应太简单，太基础，太low了，好吧，今天分享常见一点、复杂一点的Oracle函数，其中的例句中有函数套函数，还算中等难度，共同学习，欢迎大家吐槽！

Oracle的函数有多少，心里话，我真不知道，我在银行的开发工作中常用的也就30个左右，听说有一百多个，这个大家去求证！今天要分享的是Java开发中常用的Oracle函数，包括字符串处理函数、求和函数、数学函数、拼接函数、转换函数、替代函数等，每一个函数给几个例子，方便理解学习。

1、nvl和nvl2函数

nvl函数如果查询的为空值，就显示后面的值，trim为去空格，分化为ltrim()和rtrim()；

select user_name,nvl(trim(user_mail),'为空') user_mail from user_table;

select user_name,nvl(ltrim(user_mail),'为空') user_mail from user_table;

2、nvl2函数,如果为空显示第二个参数值，不为空显示第三个参数值，这个函数是nvl()函数的升级，更灵活一些。

select user_name,nvl2(Trim(user_mail),'不为空','为空') user_mail from user_table;

3、case...when的使用，case。。when算是语法，不是函数，因为下面要用到，这里说明一下。这里使用两种方法来写：

a、select user_id,user_name,(case when user_sex='1' then '男' when user_sex= '2' then '女' else user_sex end ) user_sex,user_mail from user_table;

b、select user_id,user_name,(case user_sex when'1' then '男' when '2' then '女' else user_sex end ) user_sex, user_mail from user_table;

4、sum()求和函数和count()求和函数,统计类函数在开发中经常使用，一般和round()配合

现在求男占比和女占比，这种在统计项目中经常使用到，如我现在做到的报表系统，要比这个还要复杂，数据量还要大，而且有时候是三四张表管理、、、

这里有两种写法，一种是使用别名，一种是不使用别名

a、select

count(*) tot_num,sum(case when user_name='bakehe' then 1 else 0 end) bakehe_num,sum(case when user_name='what' then 1 else 0 end) what_num,sum(case when user_sex='男' or user_sex='1' then 1 else 0 end) 男_num,sum(case when user_sex='男' or user_sex='1' then 1 else 0 end)/count(*)*100||'%' 男占比 ,sum(case when user_sex in ('女','2') then 1 else 0 end) 女_num,round(sum(case when user_sex in ('女','2') then 1 else 0 end)/count(*)*100,3)||'%' 女占比 ,sum(case when user_sex not in('男','女','1','2') then 1 else 0 end ) 中间_num,nvl(round(sum(case when user_sex not in('男','女','1','2') then 1 else 0 end )/count(*)*100,2),0)||'%' 中间占比,sum(case when user_age='高中一班'then 1 else 0 end ) 一班_num from user_table;

b、第二种写法，使用别名,同时使用临时表

select

D.tot_num,D.bakehe_num,D.what_num,D.男_num,round(D.男_num/D.tot_num,4)*100||'%' 男占比,D.女_num,round(D.女_num/D.tot_num,4)*100||'%' 女占比,D.中间_num,nvl(round(D.中间_num/D.tot_num,4)*100||'%',0) 中间占比,D.一班_num from

(select count(*) tot_num,sum(case when user_name='bakehe' then 1 else 0 end) bakehe_num,sum(case when user_name='what' then 1 else 0 end) what_num,sum(case when user_sex='男' or user_sex='1' then 1 else 0 end) 男_num,sum(case when user_sex in ('女','2') then 1else 0 end) 女_num,sum(case when user_sex not in('男','女','1','2') then 1 else 0 end ) 中间_num,sum(case when user_age='高中一班'then 1 else 0 end ) 一班_num from user_table ) D ;

5、拼接函数：拼接函数CONCAT()和||，个人比较喜欢||，但是效率好像concat()快一些！

select concat(CONCAT(user_name||'是'||user_age,'得')||(case user_sex when '1' then '男' when '2' then '女' else user_sex end),'生') 信息 from user_table;

6、截取函数：SUBSTR()，当作为查询条件的时候，截取函数和like有点相识。

select user_id,user_name,substr(user_age,1,2) 年级,substr(user_age,3,10) 班级,user_tel from user_table;

select * from user_table where substr(user_age,3,10)='二班'

select * from user_table where user_age like '%二班%'

7、时间函数，Oracle的时间函数非常强大，可以把字符串转换成时间格式，也可以把时间格式转换成字符串(to_char属于字符串处理函数)

select user_id,user_name,to_char(to_date(tm_smp,'yyyymmdd hh24miss'),'dd-mm-yyyy day year') tm_smp from user_table;

select user_id,user_name,to_char(SYSDATE,'yyyymmdd hh24miss') from user_table;

8、数学函数，Oracle提供了非常强大和繁多的数据计算函数，包括round(),ln(),abs(),acos(),cos(),ceil()等等。。

select user_id,user_name,tm_smp,

ceil( ( to_date(up_time,'yyyy-mm-dd hh24:mi:ss')-to_date(to_char(tm_smp),'yyyy-mm-dd hh24:mi:ss') )* 24 * 60 * 60) 交易用时,

floor( ( to_date(up_time,'yyyy-mm-dd hh24:mi:ss')-to_date(to_char(tm_smp),'yyyy-mm-dd hh24:mi:ss') ) ) 天,

mod(trunc(ceil( ( to_date(up_time,'yyyy-mm-dd hh24:mi:ss')-to_date(to_char(tm_smp),'yyyy-mm-dd hh24:mi:ss') )* 24 * 60 * 60)/3600),24) 小时,

floor(mod(ceil((to_date(up_time,'yyyy-mm-dd hh24:mi:ss')-to_date(to_char(tm_smp),'yyyy-mm-dd hh24:mi:ss'))* 24 * 60 * 60),3600)/60) 分,

mod(ceil((to_date(up_time,'yyyy-mm-dd hh24:mi:ss')-to_date(to_char(tm_smp),'yyyy-mm-dd hh24:mi:ss'))* 24 * 60 * 60),60) 秒

from user_table

9、数学函数中的三角求值，这里只简单写几个，大家想测试，可以自己写一些SQL。

select user_id,user_name,substr(tm_smp,8,10), cos(substr(tm_smp,8,10)) 余弦 ,

tan(substr(tm_smp,8,10)) 正切 from user_table;

注意：Oracle的函数非常之多，但是常用的基本就是十几个，一些简单的如：max(),min(),count(),upper(),instr(),replace()等等，大家可以私下写一写SQL验证一下。

非常欢迎大家关注我的个人微信号：thiscode

同样欢迎大家吐槽，监督！