oracle常用语法和语句收藏

rge5kgrsg4

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

merge into jimmy_student js using
jimmy_student1 js1
on (js1.id = js.id)
when matched then update
set js.score = js.score + 100 where js.score + 100 <=200
when not matched
then insert values(js1.id,js1.name,js1.score,js1.subject)
更新语句可以加where过滤条件，insert,update可以去掉一个


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)))

4.联合分区（其它分区略） range_hash联合分区
create table JIMMY_STUDENT2
(
  ID      NUMBER,
  NAME    VARCHAR2(20),
  SCORE   NUMBER,
  SUBJECT VARCHAR2(20),
  CDATE   DATE
)PARTITION BY RANGE(SCORE)

SUBPARTITION BY HASH(SUBJECT)SUBPARTITIONS 2
(
PARTITION part_1 VALUES LESS THAN (80)
(
   SUBPARTITION part_1_sub_1,
   SUBPARTITION part_1_sub_2,
   SUBPARTITION part_1_sub_3
),

PARTITION part_2 VALUES LESS THAN (150)
(
   SUBPARTITION part_2_sub_1,
   SUBPARTITION part_2_sub_2
)
);


5.查看分区信息
select ut.partitioned from user_tables ut where table_name ='UR_RESERVED_SOURCE'//查看是否为分区表
SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='JIMMY_STUDENT2'//查看分区表的详细信息

6.查询当前锁表信息
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;