金蝶云星空运行分拆中断后流程实例数据清理sql

下面是sql server 版本：

--收集可能的流程实例数据
if exists (select 1 from sys.tables where name like 'chl_instance_self')
drop table chl_instance_self
create table chl_instance_self(ftablename varchar(100),fentryid int,fseq int)
if exists (select 1 from sys.tables where name like 'chl_executesqllist')
drop table chl_executesqllist
create table chl_executesqllist(fid int identity(1,1),fisexec int default 0,fsql varchar(max),ftype varchar(50))
--更新chlT_BF_TABLEDEFINE
update a set a.FSEQ=isnull(b.FSEQ,0) from chlT_BF_TABLEDEFINE a join T_BF_TABLEDEFINE b on a.FTABLENUMBER=b.FTABLENUMBER 
insert into chl_executesqllist(fsql,ftype)
select    'insert into chl_instance_self(ftablename,fentryid,fseq) select '''+a.FTABLENUMBER+''',fbillid,'+convert(varchar(100),a.FSEQ)+' from  '+b.FTABLENAME+'' ,'instance' 
from  chlT_BF_TABLEDEFINE a join T_BAS_SPLITENTRYREGISTER b on a.ftablename=b.FBILLTABLE 
where b.FTABLENAME in (select name from sys.tables)
--执行chl_executesqllist中的sql,更新执行状态
select * from chl_executesqllist where fisexec=0 and ftype='instance'

--------------------------------流程实例删除------------------------------------------   
if  exists (select 1 from sysobjects where name='t_transfer_instanceid_self' and xtype='u')
drop table  t_transfer_instanceid_self
create table t_transfer_instanceid_self(FROUTEID varchar(50) )
insert t_transfer_instanceid_self(FROUTEID)
select distinct a.FROUTEID  
from T_BF_InstanceEntry a join chl_instance_self b on a.FTTABLENAME=b.ftablename
and a.FTID=b.fentryid
union
select distinct a.FROUTEID
from T_BF_InstanceEntry a join chl_instance_self b on a.FSTABLENAME=b.ftablename
and a.fsID=b.fentryid
--可能超时
delete  from T_BF_INSTANCEAMOUNT where FROUTEID in (select FROUTEID from t_transfer_instanceid_self)
delete  from T_BF_InstanceEntry where FROUTEID in (select  FROUTEID from t_transfer_instanceid_self) and FFIRSTNODE<>'1'
delete from T_BF_Instance where FINSTANCEID not in  (select  FINSTANCEID from T_BF_InstanceEntry)


---------------------------------流程实例历史数据------------------------------------------------------
if exists (select 1 from sysobjects where name='t_transfer_instanceid_self' and xtype='u')
drop table t_transfer_instanceid_self
create table t_transfer_instanceid_self(FROUTEID varchar(50) )
insert into t_transfer_instanceid_self(FROUTEID)
select  FROUTEID 
 from T_BF_InstanceEntryhis a join t_transfer_instance b on a.FSTABLEID=b.fseq and a.FSID=b.fid  
union
select  FROUTEID 
from T_BF_InstanceEntryhis a join t_transfer_instance b on a.FTTABLEID=b.fseq and a.FTID=b.fid  

delete  from T_BF_INSTANCEAMOUNTHIS where FROUTEID in (select FROUTEID from t_transfer_instanceid_self)
delete  from T_BF_INSTANCEENTRYHIS where FROUTEID in (select  FROUTEID from t_transfer_instanceid_self)  and FFIRSTNODE<>'1'
delete from T_BF_INSTANCEHIS where FINSTANCEID not in  (select  FINSTANCEID from T_BF_INSTANCEENTRYHIS ) 


-----------------------------------------------备份实例数据删-----------------------------------------------
if exists (select 1 from sysobjects where name='t_transfer_instanceid_self' and xtype='u')
drop table t_transfer_instanceid_self
create table t_transfer_instanceid_self(FROUTEID varchar(50) )
insert into t_transfer_instanceid_self(FROUTEID)
select  FROUTEID 
 from T_BF_INSTENTRYBACKUP a join t_transfer_instance b on a.FSTABLEID=b.fseq and a.FSID=b.fid  
union
select  FROUTEID 
from T_BF_INSTENTRYBACKUP a join t_transfer_instance b on a.FTTABLEID=b.fseq and a.FTID=b.fid  

delete  from T_BF_INSTAMOUNTBACKUP where FROUTEID in (select FROUTEID from t_transfer_instanceid_self)
delete  from T_BF_INSTENTRYBACKUP where FROUTEID in (select  FROUTEID from t_transfer_instanceid_self)  and FFIRSTNODE<>'1'
delete from T_BF_INSTBACKUP where FINSTANCEID not in  (select  FINSTANCEID from T_BF_INSTENTRYBACKUP ) 

--创建表收集反写规则的FCID和单据类型，单据内码
if  exists (select 1 from sys.tables where name like 'chl_INSTANCESNAP_fcid' )
drop  table  chl_INSTANCESNAP_fcid 
create table chl_INSTANCESNAP_fcid(fcid varchar(200) primary key,fformid varchar(100),fid int,fisdelete char(1))

insert into chl_INSTANCESNAP_fcid(fcid,fformid,fid,fisdelete)
select fcid,substring(fcid,1,CHARINDEX(',',FCID)-1),substring(fcid, CHARINDEX(',',FCID,CHARINDEX(',',FCID)+1)+1,len(fcid)),0  from T_BF_INSTANCESNAP 

insert into chl_INSTANCESNAP_fcid(fcid,fformid,fid,fisdelete)
select fcid,substring(fcid,1,CHARINDEX(',',FCID)-1),substring(fcid, CHARINDEX(',',FCID,CHARINDEX(',',FCID)+1)+1,len(fcid)),0  from T_BF_INSTANCESNAPHIS 

insert into chl_INSTANCESNAP_fcid(fcid,fformid,fid,fisdelete)
select fcid,substring(fcid,1,CHARINDEX(',',FCID)-1),substring(fcid, CHARINDEX(',',FCID,CHARINDEX(',',FCID)+1)+1,len(fcid)),0  from t_bf_snapbackup 

--单据不存在的反写数据标识为1
if exists (select 1 from sys.tables where name like 'chl_INSTANCESNAP_fformid')
drop table  chl_INSTANCESNAP_fformid
create table  chl_INSTANCESNAP_fformid(fformid nvarchar(40),ftablename nvarchar(100),ffield nvarchar(40))
insert into chl_INSTANCESNAP_fformid(fformid) select distinct  fformid from  chl_INSTANCESNAP_fcid

--插入需要执行的脚本
insert into chl_executesqllist(fsql,ftype)
select 'update a set a.fisdelete=1 from  chl_INSTANCESNAP_fcid  a join '+b.ftablename+' b on  a.fformid='''+a.fformid+''' and a.fid=b.fbillid and b.FSPLITED=1','snap' 
from chl_INSTANCESNAP_fformid a join T_BAS_SPLITTABLEREGISTER b on a.fformid=b.FFORMID 
where b.FTABLENAME in (select name from sys.tables)
--执行未执行的脚本
select fsql from chl_executesqllist where fisexec=0 and ftype='snap'

--删除反写（可能超时）
delete a from t_bf_instancesnap a join chl_INSTANCESNAP_fcid b on a.FCID=b.fcid  and b.fisdelete=1
delete a from T_BF_INSTANCESNAPHIS  a join chl_INSTANCESNAP_fcid b on a.FCID=b.fcid  and b.fisdelete=1
delete a from t_bf_snapbackup  a join chl_INSTANCESNAP_fcid b on a.FCID=b.fcid  and b.fisdelete=1

下面是oracle版本：

declare
  v_cnt number;
begin
  select count(1) INTO v_cnt  from user_tables where table_name=upper('chl_instance_self');
  if v_cnt = 1 then
    EXECUTE IMMEDIATE 'DROP TABLE chl_instance_self';
  end if;
EXECUTE IMMEDIATE 'create table chl_instance_self(ftablename varchar2(100),fentryid number,fseq number)';
end;
--fid 没有id自动生成逻辑
declare
  v_cnt number;
begin
  select count(1) INTO v_cnt  from user_tables where table_name=upper('chl_executesqllist');
  if v_cnt = 1 then
    EXECUTE IMMEDIATE 'DROP TABLE chl_executesqllist';
  end if;
EXECUTE IMMEDIATE 'create table chl_executesqllist(fid number,fisexec number default 0,fsql varchar2(4000),ftype varchar2(50))';
end;
--更新chlT_BF_TABLEDEFINE
merge into   chlT_BF_TABLEDEFINE a using    T_BF_TABLEDEFINE b on (a.FTABLENUMBER=b.FTABLENUMBER )
when matched then update
set a.FSEQ=nvl(b.FSEQ,0) ;

insert into chl_executesqllist(fsql,ftype)
select    'insert into chl_instance_self(ftablename,fentryid,fseq) select '''||a.FTABLENUMBER||''',fbillid,'||to_char(a.FSEQ)||' from  '||b.FTABLENAME||';' ,'instance' 
from  chlT_BF_TABLEDEFINE a join T_BAS_SPLITENTRYREGISTER b on a.ftablename=b.FBILLTABLE 
where upper(b.FTABLENAME) in (select upper(table_name) from user_tables);
--执行chl_executesqllist中的sql,更新执行状态
select * from chl_executesqllist where fisexec=0 and ftype='instance';


--------------------------------流程实例删除------------------------------------------   
declare
  v_cnt number;
begin
  select count(1) INTO v_cnt  from user_tables where table_name=upper('t_transfer_instanceid_self');
  if v_cnt = 1 then
    EXECUTE IMMEDIATE 'DROP TABLE t_transfer_instanceid_self';
  end if;
EXECUTE IMMEDIATE 'create table t_transfer_instanceid_self(FROUTEID varchar2(50) )';
end;
insert into t_transfer_instanceid_self(FROUTEID)
select  a.FROUTEID  
from T_BF_InstanceEntry a join chl_instance_self b on a.FTTABLENAME=b.ftablename
and a.FTID=b.fentryid
union
select  a.FROUTEID
from T_BF_InstanceEntry a join chl_instance_self b on a.FSTABLENAME=b.ftablename
and a.fsID=b.fentryid;
--可能超时
delete  from T_BF_INSTANCEAMOUNT where FROUTEID in (select FROUTEID from t_transfer_instanceid_self);
delete  from T_BF_InstanceEntry where FROUTEID in (select  FROUTEID from t_transfer_instanceid_self) ;
delete from T_BF_Instance where FINSTANCEID not in  (select  FINSTANCEID from T_BF_InstanceEntry);

---------------------------------流程实例历史数据------------------------------------------------------
declare
  v_cnt number;
begin
  select count(1) INTO v_cnt  from user_tables where table_name=upper('t_transfer_instanceid_self');
  if v_cnt = 1 then
    EXECUTE IMMEDIATE 'DROP TABLE t_transfer_instanceid_self';
  end if;
EXECUTE IMMEDIATE 'create table t_transfer_instanceid_self(FROUTEID varchar2(50) )';
end;

insert into t_transfer_instanceid_self(FROUTEID)
select  FROUTEID 
 from T_BF_InstanceEntryhis a join t_transfer_instance b on a.FSTABLEID=b.fseq and a.FSID=b.fid  and b.fseq <>0
union
select  FROUTEID 
from T_BF_InstanceEntryhis a join t_transfer_instance b on a.FTTABLEID=b.fseq and a.FTID=b.fid  and b.fseq<>0;

delete  from T_BF_INSTANCEAMOUNTHIS where FROUTEID in (select FROUTEID from t_transfer_instanceid_self);
delete  from T_BF_INSTANCEENTRYHIS where FROUTEID in (select  FROUTEID from t_transfer_instanceid_self) ;
delete from T_BF_INSTANCEHIS where FINSTANCEID not in  (select  FINSTANCEID from T_BF_INSTANCEENTRYHIS ) ;

-----------------------------------------------备份实例数据删-----------------------------------0------------
declare
  v_cnt number;
begin
  select count(1) INTO v_cnt  from user_tables where table_name=upper('t_transfer_instanceid_self');
  if v_cnt = 1 then
    EXECUTE IMMEDIATE 'DROP TABLE t_transfer_instanceid_self';
  end if;
  EXECUTE IMMEDIATE 'create table t_transfer_instanceid_self(FROUTEID varchar2(50) )';
end;

insert into t_transfer_instanceid_self(FROUTEID)
select  FROUTEID 
 from T_BF_INSTENTRYBACKUP a join t_transfer_instance b on a.FSTABLEID=b.fseq and a.FSID=b.fid  and b.fseq<>0
union
select  FROUTEID 
from T_BF_INSTENTRYBACKUP a join t_transfer_instance b on a.FTTABLEID=b.fseq and a.FTID=b.fid   and b.fseq<>0;

delete  from T_BF_INSTAMOUNTBACKUP where FROUTEID in (select FROUTEID from t_transfer_instanceid_self);
delete  from T_BF_INSTENTRYBACKUP where FROUTEID in (select  FROUTEID from t_transfer_instanceid_self);
delete from T_BF_INSTBACKUP where FINSTANCEID not in  (select  FINSTANCEID from T_BF_INSTENTRYBACKUP ) ;


--创建表收集反写规则的FCID和单据类型，单据内码
declare
  v_cnt number;
begin
  select count(1) INTO v_cnt  from user_tables where table_name=upper('chl_INSTANCESNAP_fcid');
  if v_cnt = 1 then
    EXECUTE IMMEDIATE 'DROP TABLE chl_INSTANCESNAP_fcid';
  end if;
EXECUTE IMMEDIATE 'create table chl_INSTANCESNAP_fcid(fcid varchar2(200),fformid varchar2(100),fid number,fisdelete char(1))';
end;
insert into chl_INSTANCESNAP_fcid(fcid,fformid,fid,fisdelete)
select fcid,SUBSTR(fcid,1,INSTR(FCID,',')-1),SUBSTR(fcid, INSTR(FCID,',',INSTR(FCID,',')+1)+1,LENGTH(fcid)),0  
from T_BF_INSTANCESNAP ;
insert into chl_INSTANCESNAP_fcid(fcid,fformid,fid,fisdelete)
select fcid,SUBSTR(fcid,1,INSTR(FCID,',')-1),SUBSTR(fcid, INSTR(FCID,',',INSTR(FCID,',')+1)+1,LENGTH(fcid)),0  
from T_BF_INSTANCESNAPHIS ;
insert into chl_INSTANCESNAP_fcid(fcid,fformid,fid,fisdelete)
select fcid,SUBSTR(fcid,1,INSTR(FCID,',')-1),SUBSTR(fcid, INSTR(FCID,',',INSTR(FCID,',')+1)+1,LENGTH(fcid)),0  
from t_bf_snapbackup ;

--单据不存在的反写数据标识为1
declare
  v_cnt number;
begin
  select count(1) INTO v_cnt  from user_tables where table_name=upper('chl_INSTANCESNAP_fformid');
  if v_cnt = 1 then
    EXECUTE IMMEDIATE 'DROP TABLE chl_INSTANCESNAP_fformid';
  end if;
EXECUTE IMMEDIATE 'create table  chl_INSTANCESNAP_fformid(fformid varchar2(40),ftablename varchar2(100),ffield varchar2(40))';
end;
insert into chl_INSTANCESNAP_fformid(fformid) select distinct  fformid from  chl_INSTANCESNAP_fcid;
--插入需要执行的脚本
 insert into chl_executesqllist(fsql,ftype)
select 'merge into chl_INSTANCESNAP_fcid a using '||b.ftablename||' b on (a.fformid='''||a.fformid||''' and a.fid=b.fbillid and b.FSPLITED=1)
when matched then update
set a.fisdelete=1;','snap' 
from chl_INSTANCESNAP_fformid a join T_BAS_SPLITTABLEREGISTER b on a.fformid=b.FFORMID 
 where upper(b.FTABLENAME) in (select upper(table_name) from user_tables);
 
--执行未执行的脚本
select fsql from chl_executesqllist where fisexec=0 and ftype='snap';
--执行未执行的脚本
--删除反写（可能超时）
delete  from  t_bf_instancesnap a where exists(select  1 from  chl_INSTANCESNAP_fcid b where a.FCID=b.fcid  and b.fisdelete=1);
delete  from  T_BF_INSTANCESNAPHIS a where exists(select  1 from  chl_INSTANCESNAP_fcid b where a.FCID=b.fcid  and b.fisdelete=1);
delete  from  t_bf_snapbackup a where exists(select  1 from  chl_INSTANCESNAP_fcid b where a.FCID=b.fcid  and b.fisdelete=1);