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:由自查询关联查询的记录，必须只有一条，否则会报错.