oracle常用语法和语句收藏


		oracle常用语法和语句收藏
 
1.case when 示例       
select task_id taskId, 
          start_time startTime, 
          end_time endTime, 
          status status, 
          plan_type planType, 
          is_manual isManual, 
          is_pilotCal isPilotCal, 
          scheduling_code schedulingCode 
          from log_schedule_plan 
          where plan_type = 'RSV_SCHEDULING_PLAN' 
          and sysdate > (case when is_manual=0 then (start_time + interval '10' minute) else (start_time + interval '2' minute) end) 
 
2.merge 示例 
 
merge 
into WAREH_LOCKED_LST wll using (select 
wareh_id,prod_id,locked_type,sum(locked_qty) locked_qty from 
TEMP_UR_WAREH_LOCKED_LST where id=#batchId# group by 
wareh_id,prod_id,locked_type)uwllt 
on (wll.wareh_id = uwllt.wareh_id 
and wll.prod_id = uwllt.prod_id 
and wll.locked_type = uwllt.locked_type 
) 
when matched then update 
set wll.locked_qty = nvl(wll.locked_qty,0) + 
nvl(uwllt.locked_qty,0) 
,stk_change_date =systimestamp 
when not matched 
then 
insert 
(PROD_ID, WAREH_ID, locked_type, locked_qty,STK_CHANGE_DATE) 
values( 
uwllt.prod_id, 
uwllt.wareh_id, 
uwllt.locked_type, 
uwllt.locked_qty, 
systimestamp 
) 
ps:由自查询关联查询的记录，必须只有一条，否则会报错. 
 
3.upper(lsp.task_id) = lower(lower(lsp.task_id))来判断task_id字段是否为纯数字 
eg:select * from log_schedule_plan lsp where (upper(lsp.task_id) = lower(lower(lsp.task_id)))