oracle常见用法

1.insert into 和with 结合使用

insert into HW_RATE
(ID,
SUB_TYPE,
DISTRICT_CODE,
REGION_CODE,
SUB_AREA_CODE,
CREATE_DATE,
CREATE_USER)
with areaInfo as
(select o.district_code bu, o.region_code re
from eis_user.org_office o
where o.sub_area_code = ‘60487’
and o.status = 1
and o.year = extract(year from sysdate)
and o.sub_type = 2)
select sys_guid(),
2,
areaInfo.bu,//这里直接with 临时表别名.子句查询结果别名，即可
areaInfo.re,
‘60487’,
sysdate,
‘ww00068’
from areaInfo；
参考：oracle with和insert结合使用

2.timestamp 和 date 的转换

cast(字段名A as date) 字段名A是时间戳，转换为日期
cast(字段名B as timestamp) 字段B是日期，转换为时间戳

参考：Oracle学习笔记：date和timestamp相互转换