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''))';  --按照分区