oracle学习

在后端开发中，必不可少的是，使用oracle数据库，现在就分享几个在工作中用到的oracle函数查询，还是很强大的。

1.利用内置的dual表以及rownum查询一个时间段
select 
  rownum rn,to_date('2016-07-01', 'yyyy-MM-dd') + (rownum - 1) as year_month
from dual
connect by 
        rownum <= (to_date('2016-07-31', 'yyyy-MM-dd') + 1) - to_date('2016-07-01', 'yyyy-MM-dd')

2.decode函数
    decode(字段/运算,值1,值2，值3)  当字段/运算等于值1时,返回值2，否则返回值3,相当于MapUtils的getString功能，可以设定默认值
select decode(max(alys.testtime), null, to_char(sysdate, 'yyyy-MM-dd'), max((alys.testtime), 'yyyy-MM-dd')) as maxTime
  from batteryanalysis24h alys
 where 1 = 1
   and alys.avg = <> 0
   and alys.supplierid = 60779844
   and alys.barcode = 02220827
   and factoryid == 60779845
   and alys.testtype = '在线测试'
   and alys.testprocess = '尺寸测试'
   and testcondition = '长'
   and alys.productline = 56
 group by alys.barcode,
          alys.supplierid,
          alys.factoryid,
          alys.testtype,
          alys.processalys.testtime,
          alys.productline,
          alys.testcondition
 order by max(testtime) desc;
3. LPAD函数
    LPAD(String,padded_length,[pad_String]) padded_length  padded_length 填充后的字符串长度,pad_String  待填充的字符串 
    作用：从左边对字符串使用指定的字符进行填充
    select lpad('11',6,0) as id from dual ; 

lpad 结合 decode 可以对下一个id进行加0操作,例如 emp表的最后一个uuid是13，使用语句可以使下一id前面加上多个0
select lpad(decode(count(uuid), 0, 1, max(to_number(uuid) + 1)), 14, '0') as id  from emp

4. sign(x)函数  x 大于0 返回1 等于0 返回0 小于0 返回-1
利用这可以查询两个数中的较小值
select  decode(sign(2 - 1 ),-1,'b','a' ) maxValue from dual

5.with as select 语句
 with 
    e as (select * from scott_emp),
    d as (select * from scott_dept)
    select * from e,d where e.deptno = d.deptno
相等于建立了e d 两个临时表，用于存放sql语句，需要用的时候可以使用查询。       
with t1 as
 (select sysdate as dtime from dual),
t2 as
 (select * from emp),
t3 as
 (select a.dtime, b.username
    from t1 a
    left join t2 b
      on to_char(a.dtime, 'yyyy-MM-dd') = to_char(b.birthday, 'yyyy-MM-dd'))
select * from t3;

6. trunc 函数  


--关于时间方面
select trunc(sysdate,'yy') from dual  --返回当年第一天
select trunc(sysdate,'iw') from dual  --返回当前星期的第一天
select trunc(sysdate,'d') from dual   --返回当期星期的周一
select trunc(sysdate,'d')+7 from dual   --返回当期星期的周日
select trunc(sysdate,'hh') from dual   --返回当前时间的含整点的小时 20:00:00
select trunc(sysdate,'mi') from dual   --返回当前时间，没有秒的精确


--关于数字方面
--RUNC（number,num_digits）  Number 需要截尾取整的数字。 
--Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
--TRUNC()函数截取时不进行四舍五入
select trunc(123.48) from dual      --123
select trunc(123.48,0) from dual    --123
select trunc(123.48,1) from dual    --123.4
select trunc(123.48,-1)from dual    --120