oracle 工作中遇到的函数

 --decode语法 decode(条件，值1，翻译值1，值2，翻译值2)
                --decode(字段,比较1，值1,比较 2，值2)

   decode((select count(1)
                         from commnct_notification f
                        where f.parent_id = t.notification_id),
                       0,
                       'true',
                       'false')

  DECODE(T.VALUE_BOOL,'0','否','1','是'),

 select decode (u.gender ,0,'男',1,'女') sex  from sys_user u

 

.Round (数值的四舍五入)

SELECT Round(123.456,1)FROM Dual;

Trunc (截取日期和数字处理)

select substr(to_char(sysdate,'yyyyMMdd'),3,6) from dual;

 

Substr (字符串的截取)

 

Substr(字符串,截取开始位置,截取长度)
Substr('HelloWorld',1,1)//返回结果为'h'*0和1都是表示截取的开始位置为第一个字符
Substr('HelloWorld',2,4)//返回结果为'Ello'

to_char(日期转换成字符串); 

to_char(sysdate, 'yyyy/MM/dd'))  from dual;

select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss')    from dual//

 

Nvl (数据表字段的判空处理)

select nvl(max(t.sort_order), 0) + 1 CODE
      FROM PM_WBS_RELATION  t
   WHERE T.PARENT_TASK_ID = '6D170892332141A8B5FD41676C91D6EC' ;

start with (树递归)

SELECT FROM PM_TASK_OBJECT T, PM_WBS_RELATION T1
 WHERE T.TASK_ID = T1.CHILD_TASK_ID
 START WITH t1.parent_task_id = ''
        and t.task_status = 'planning'
CONNECT BY PRIOR t.task_id = t1.parent_task_idwg

逆向递归

connect by prior t.parent_id = t.notification_id\n"
                + " start with t.notification_title like '%"
                + vo.getNotificationTitle() + "%' ";

unill 合并 unillALL(union和union all的区别是,union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复。 )

select pm.PROGRESS_PERCENTAGE progress, t.activity_name name,'上游' type
  from ENGINE_ACTIVITY t, pm_task_object pm
 where t.activity_id in
       (select t.from_activity_id
          from engine_transition t
         where t.to_activity_id in
               (select t.activity_id
                  from ENGINE_ACTIVITY_ACTION t
                 where t.action_id = '63A52773930B48EB93A14B296DD77090' and pm.task_id='63A52773930B48EB93A14B296DD77090'))
union
select pm.PROGRESS_PERCENTAGE,t.activity_name name, '下游' type
  from ENGINE_ACTIVITY t, pm_task_object pm
 where t.activity_id in
       (select t.to_activity_id
          from engine_transition t
         where t.from_activity_id in
               (select t.activity_id
                  from ENGINE_ACTIVITY_ACTION t
                 where t.action_id = '63A52773930B48EB93A14B296DD77090' and pm.task_id='63A52773930B48EB93A14B296DD77090'))