常用oracle语句整理

一.锁定某一行记录进行查询更新
select t.*,t.rowid from T_KB01 t where t.FLX_MED_ORG_ID = 'S5113001901010000002'

select * from T_KB01 for update (不建议，统筹容易锁表）

二、查询行数小于多少条

select * from t_kb01 where rownum <=600

三、日期范围内查询

（1）select sum(t.zfy) from jksh.ck10_jsmx t left join ck10_ghdj t1 on t.ghdjid=t1.id
where t.jsrq>=to_date('01-02-2018 00:00:00', 'dd-mm-yyyy hh24:mi:ss')
and t.jsrq<to_date('01-03-2018 00:00:00', 'dd-mm-yyyy hh24:mi:ss')

（2）select sum(t.zfy) from jksh.ck10_jsmx t left join ck10_ghdj t1 on t.ghdjid=t1.id

where t.jsrq BETWEEN TO_NUMBER('20200500000001') AND TO_NUMBER('20200501235959')

四、刷新物化视图

BEGIN
DBMS_MVIEW.REFRESH (
list => 'ST_MMAP_43.F_RPT_BASY', --普通用户+试图名称
Method =>'COMPLETE',
refresh_after_errors => True);
end;

五、删除物化视图

DROP MATERIALIZED VIEW 表名；

六、更新列，将ab表相关的列更新到b表中

update t_data_detection_tbl a set a.overall_cd=(select b.OVERALL_CD_ORG from t_kc21 b where b.med_clinic_id =a.med_clinic_id);

update t_data_detection_tbl a set a.hospital_id=(select b.FLX_MED_ORG_ID from t_kc21 b where b.med_clinic_id =a.med_clinic_id);

七、查询的数据与字典表进行关联

select b.item,b.itemType,a.totalCost,b.ovePay from summer a ,(select * from pay b where b.item='pay_type') b

where b.item=a.item_value and   …… group by b.item,b.itemType

八、case  when 的用法

select

CASE
when crowd_type = '1' then
'青年男性'
when crowd_type = '2' then
'青年女性'
when crowd_type = '3' then
'中年男性'
when crowd_type = '4' then
'中年女性'
when crowd_type = '5' then
'老年男性'
when crowd_type = '6' then
'老年女性'
else
'7'
end, sum(med_amout)
from T_CROWD_TYPE_SUMMARY_TBL
group by crowd_type