--数据归档的方法
create or replace produced FW_JBPM_BACKUP_P is
--保存每次归档的条数
zc_date_number number;
v_number number;

begin
     select count(1) into v_number from user_tables where table_name = 'A';
     if (v_number) then--如果不存在临时表，则创建
     execute immediate 'create global temporay table A (id number(19) ,bizId number(19) ,processName varchar2(255 char)) on commit delete rows';
     end if;
    
     --归档流程开始，核心思想是把复制的查询SQL转换为中间表，大大提升性能
     insert into A select * from (
            with S1 as (select * from B) ,S2 as (select * from C ,S1) ,S3 as (select * from D ,S2))
            select * from S1 ,S2 ,S3 ,A ,B ,C
     );
    
     --查询归档的条数
     select count(1) into zc_date_number from A;
     output_line('归档条数' || zc_date_number);
    
     commit;
end FW_JBPM_BACKUP_P;