1.trunc
/**************日期********************/1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-182.select trunc(sysdate, 'mm') from dual --2011-3-1 返回当月第一天.3.select trunc(sysdate,'yy') from dual --2011-1-1 返回当年第一天4.select trunc(sysdate,'dd') from dual --2011-3-18 返回当前年月日5.select trunc(sysdate,'yyyy') from dual --2011-1-1 返回当年第一天6.select trunc(sysdate,'d') from dual --2011-3-13 (星期天)返回当前星期的第一天7.select trunc(sysdate, 'hh') from dual --2011-3-18 14:00:00 当前时间为14:41 8.select trunc(sysdate, 'mi') from dual --2011-3-18 14:41:00 TRUNC()函数没有秒的精确/***************数字********************//*TRUNC（number,num_digits） Number 需要截尾取整的数字。 Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。TRUNC()函数截取时不进行四舍五入*/9.select trunc(123.458) from dual --12310.select trunc(123.458,0) from dual --12311.select trunc(123.458,1) from dual --123.412.select trunc(123.458,-1) from dual --12013.select trunc(123.458,-4) from dual --014.select trunc(123.458,4) from dual --123.45815.select trunc(123) from dual --12316.select trunc(123,1) from dual --12317.select trunc(123,-1) from dual --120
2.numtoyminterval numtodsinterval
numtoyminterval(n,'char_expr') 日期描述，用于year和month
numtodsinterval(n,'char_expr') 用于day,hour,mintue,second,
select numtoyminterval(100,'month') a from dual; //+000000008-04 8年4个月
select numtoyminterval(100,'year') a from dual; //+000000100-00 100年
select numtodsinterval(100,'day') a from dual; //+000000100 00:00:00.000000000
select numtodsinterval(100,'hour') a from dual; //+000000004 04:00:00.000000000 4天4个小时select numtodsinterval(100,'minute') a from dual;//+000000000 01:40:00.000000000select numtodsinterval(100,'second') a from dual;//+000000000 00:01:40.000000000select (months_between(dt2,dt1)) years_months from (select to_date('2012-11-29 01:02:03','yyyy-MM-dd hh24:mi:ss') dt1,to_date('2014-03-15 11:22:33','yyyy-MM-dd hh24:mi:ss') dt2 from dual);
/* 15.5622871863799 */ months_between计算相差月份，不足的用小数 select trunc((months_between(dt2,dt1))) years_months from (select to_date('2012-11-29 01:02:03','yyyy-MM-dd hh24:mi:ss') dt1,to_date('2014-03-15 11:22:33','yyyy-MM-dd hh24:mi:ss') dt2 from dual); /* 15 */ select numtoyminterval(trunc((months_between(dt2,dt1))),'month') years_months from (select to_date('2012-11-29 01:02:03','yyyy-MM-dd hh24:mi:ss') dt1,to_date('2014-03-15 11:22:33','yyyy-MM-dd hh24:mi:ss') dt2 from dual);/*+000000001-03*/select numtoyminterval(trunc(months_between(dt2,dt1)),'month') years_months, numtodsinterval(dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ), 'day' ) days_hours
from (select to_date('2012-11-29 01:02:03','yyyy-MM-dd hh24:mi:ss') dt1, to_date('2014-03-15 11:22:33','yyyy-MM-dd hh24:mi:ss') dt2 from dual ); /* +000000001-03 +000000015 10:20:30.000000000*/select dt,add_months(dt,1) dt1,dt+NUMTOYMINTERVAL(1,'month') DT2 from (select to_date('29-02-2012','dd-MM-yyyy')dt from dual); /* 2012-02-29 2012-03-31 2012-03-29 */ 注意：ADD_MONTHS是添加自然月，而NUMTOYMINTERVAL是加到下个月的同一天，不管这一天是否有效，如果3月31日使用NUMTOYMINTERVAL加一个月，系统会报错. select dt,add_months(dt,1) dt1 from (select to_date('31-03-2012','dd-MM-yyyy')dt from dual); /* 2012-03-31 2012-04-30 */ select dt,dt+numtoyminterval(1,'month') dt1 from (select to_date('31-03-2012','dd-MM-yyyy')dt from dual); 报错 没有2012-04-31这一天 同理，向2012年2月29日增加1 年也会报错，因为不存在2013年2月29日。 3.months_between
select months_between(add_months(sysdate, 5), sysdate) as "months" from dual //5select months_between(sysdate, add_months(sysdate, 9)) as "months" from dual //-9select months_between(sysdate+20, sysdate) as "months" from dual //0.645161290322581 测试时间是2012-12-27号，这个月有31天select months_between(sysdate, sysdate+31) as "months" from dual //-1select months_between(sysdate+30, sysdate) as "months" from dual //0.967741935483871
4.round
select sysdate, ROUND(sysdate,'year') aa from dual; //2012/12/27 11:40:09 2013/1/1 select sysdate, ROUND(sysdate,'month') aa from dual; //2012/12/27 11:40:09 2013/1/1 select sysdate, ROUND(sysdate-200,'year') aa from dual; //2012/12/27 11:40:09 2012/1/1 select sysdate, ROUND(sysdate-20,'month') aa from dual; //2012/12/27 11:40:09 2012/12/1
select round(5555.555) from dual; //5556select round(5555.555,0) from dual; //5556select round(5555.555,2) from dual; //5555.56select round(5555.555,2.8) from dual; //5555.56select round(5555.555,-2) from dual; //5600select round(5555.555,-2.8) from dual; //5600select trunc(5555.555) from dual; //5555select trunc(5555.555,0) from dual; //5555select trunc(5555.555,2) from dual; //5555.55select trunc(5555.555,2.8) from dual; //5555.55select trunc(5555.555,-2) from dual; //5500select trunc(5555.555,-2.8) from dual; //5500
5.nvl nvl2
NVL( string1, replace_with)
作用：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
Oracle在NVL函数的功能上扩展，提供了NVL2函数。
NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，否则返回E2。select nvl(null,'hongda') from dual; //hongdaselect nvl('hongda',sysdate) from dual; //hongdaselect nvl(null,null) from dual; //nullselect nvl2(null,'hong','hongda') from dual; //hongdaselect nvl2('ff','hong','hongda') from dual; //hong
6.substr substrb instr instrb length lengthb
instr(string1,instring2[,start_position[,nth_appearance]])
string1:被搜索的字符串string2:在string1里面寻找的字符串start_position:从string1中开始搜索的位置，这是个可选参数，默认为1。sting1中第一个字符的位置为1。如果这个参数为一个负数，那么搜索将从string1的末尾开始，并向string1的开始位置进行搜索。nth_appearance:string2在string1中出现的次数，这是一个可选参数，默认值为1.注意：如果string2未在string1中出现，那么instr函数的返回值为0。select instr('hongdada','d') from dual; //5select instr('hongdada','d',1,2) from dual; //7select instr('hongdada','d',-1) from dual; //7select instr('hongdada','d',-1,2) from dual; //5select instr('hongdada','d',6) from dual; //7 select length('hongda') from dual; //6select lengthb('hongda') from dual; //6select length('洪大') from dual; //2select lengthb('洪大') from dual; //6 我还以为是4呢，不晓得为什么是6，我在plsql中测的 select substr(1234567890,5,3) from dual; //567select substrb(1234567890,5,3) from dual; //567select length('抢钱，抢粮，抢女人') from dual; //9select lengthb('抢钱，抢粮，抢女人') from dual; //27 这行的标点是中文标点select lengthb('抢钱,抢粮,抢女人') from dual; //23 这行的标点是英文标点 还是看的出来的，紧凑的是英文的，反之是中文的select substr('抢钱，抢粮，抢女人',5,3) from dual; //粮，抢select substrb('抢钱，抢粮，抢女人',5,18) from dual; //，抢粮，抢 select substrb('抢钱，抢粮，抢女人',6,18) from dual; //，抢粮，抢 select substrb('抢钱,抢粮,抢女人',5,18) from dual; // ,抢粮,抢女 select substrb('抢钱，抢粮，抢女人',0,18) from dual; //抢钱，抢粮，select substrb('抢钱，抢粮，抢女人',1,18) from dual; //抢钱，抢粮， 当从1个字符开始时，直接忽略select substrb('抢钱，抢粮，抢女人',2,18) from dual; // 钱，抢粮， 前面有2个空格，后面有1个空格 当从2个字符开始时，第一个汉字就没了，前面。。。select substrb('抢钱，抢粮，抢女人',3,18) from dual; // 钱，抢粮， 前面有1个空格，后面有2个空格 当从3个字符开始时，。。。select substrb('抢钱，抢粮，抢女人',4,18) from dual; //钱，抢粮，抢 推测oracle中这种带b的函数对老外没分别，对我们中国人的汉字，中文标点才有用，
例：length把汉字，英文都看成一个来计算，
lengthb 把汉字，中文标点当成3个来计算，英文的还是一个，我不晓得为啥是3个，反正plsql上是这么显示的
7.decode
主要作用：将查询结果翻译成其他值（即以其他形式表现出来，以下举例说明）；
使用方法：
Select decode（columnname，值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值）From talbenameWhere …
其中columnname为要选择的table中所定义的column，
·含义解释：
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
注：其中缺省值可以是你要选择的column name 本身，也可以是你想定义的其他值，比如Other等；
现定义一table名为output，其中定义两个column分别为monthid（var型）和sale（number型），若sale值=1000时翻译为D，=2000时翻译为C，=3000时翻译为B，=4000时翻译为A，如是其他值则翻译为Other；
SQL如下：
Select monthid , decode (sale,1000,'D',2000,'C',3000,'B',4000,'A',’Other’) sale from output
与nvl,sign合用
select monthid,decode(nvl(sale,6000),6000,'NG','OK') from output
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1，
如果取较小值就是
select monthid,decode(sign(sale-6000),-1,sale,6000) from output，即达到取较小值的目的。
select decode('hongda','hongda','other') from dual; //otherselect decode('HONGDA','hongda','other') from dual; //null 说明Oracle中具体值是区分大小写的，关键字，字段名，用户，密码，表名，序列名，触发器等是不区分大小写的select decode('hongda','hong','other') from dual; //nullselect decode('hongda',null,'other') from dual; //nullselect decode(null,null,'other') from dual; //otherselect decode('hongda','hongda',null) from dual; //nullselect decode('hongda','hong','answer','other') from dual; //otherselect decode('hongda','hongda','answer','other') from dual; //answerselect decode('hongda','hong',1000,'hongda',2000,'hongdada',3000,4000) from dual; //2000select decode(nvl('hongda','hong'),'hong',1000,'hongda',2000,'other') from dual; //2000select decode(nvl2('ff','hongda','hong'),'hong',1000,'hongda',2000,'other') from dual; //2000select decode(sign(100-200),1,1000,-1,2000,0,3000,'other') from dual; //2000
8.case when ..then..when..then.......else...end
跟上面的差不多，上面decode是Oracle独有的
(表)
select country,people,case when people>10 then '人口超级大国' when people>1 and people<10 then '人口大国' else '小国' end as a from tb;select sum(people),case countrywhen '中国' then '亚洲'when '印度' then '亚洲' when '德国' then '欧洲' when '法国' then '欧洲' when '俄国' then '欧洲' when '英国' then '欧洲' when '美国' then '美洲' when '墨西哥' then '美洲' when '加拿大' then '美洲' when '澳大利亚' then '澳洲' else '地狱' end as continent from tb group by case countrywhen '中国' then '亚洲'when '印度' then '亚洲' when '德国' then '欧洲' when '法国' then '欧洲' when '俄国' then '欧洲' when '英国' then '欧洲' when '美国' then '美洲' when '墨西哥' then '美洲' when '加拿大' then '美洲' when '澳大利亚' then '澳洲' else '地狱' end
同志们，我把日本放到地狱里了，哈哈！