with用法
oracle有时候查询一个逻辑需要多层查询，中间数据放一个表有点累赘临时表也有点麻烦，代码套起来又显得冗余
insert into table_a
with a as (
select * from table where ...
)
select * from a;
commit;
也可以多个如a as ... b as ...用逗号分隔
select * from b;之前创建的b括号后面没有逗号，也就是说最后一次使用with的结果的时候不能有逗号了
insert into table_b
with a as (
select * from table
),
b as (
select * from a where ...;
)
select * from b;
commit;
创建存储过程
procedure p_prod(id_date IN DATE) AS
iv_name varchar2; --声明变量，i代表入参o为出参，第二个字母代表类型
in_age number;
BEGIN
select age into in_age from user; --给声明的变量里面赋值
IF in_age=20 THEN
....
ELSE
...
END if
END p_prod;
存储过程里truncate
EXECUTE IMMEDIATE 'truncate table .....';
EXECUTE IMMEDIATE 'alter table tablename drop partition for (to_date(''' || to_char(id_date, 'yyyymmdd') || ''',''yyyymmdd''))'; --按照分区
