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、ca衡痕贤伎se...when的使用，因为下面要用到，这里说明一下。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;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()配合现在求男占比和女占比，这里有两种写法，一种是使用别名，一种是不使用别名sele艘早祓胂ctcount(*) 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;第二种写法，使用别名，同时使用临时表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(selectcount(*) 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 1 else 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()select user_id,user_name,substr(user_age,1,2) 年级,user_tel from user_table;select user_id,user_name,substr(user_age,1,2) 年级,substr(user_age,3,10) 班级,user_tel from user_table;截取函数和like的使用：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;
