任务归档执行的相关 SQL 语句

将需要归档的数据版本号设置为-1

update SA_Task set version=-1 where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and sActualFinishTime>? and sActualFinishTime<?

其中 sActualFinishTime 可以根据实际需求设置时间范围

如下： update SA_Task set version=-1 where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and sActualFinishTime>'2022-01-10' and sActualFinishTime<'2022-01-12'

将符合标记的数据的数据迁移到历史表

insert into SA_Task_Histroy select * from SA_Task where sID in (select sID from SA_Task where sFlowID in (select sFlowID from SA_Task where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and version=-1)) 

insert into SA_TaskRelation_Histroy select * from SA_TaskRelation where sTaskID1 in (select sID from SA_Task where sFlowID in (select sFlowID from SA_Task where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and version=-1)) 

insert into SA_TokenInstance_Histroy select * from SA_TokenInstance where sProcessInstanceID in (select sFlowID from SA_Task where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and version=-1)

从正式表中删除归档完的数据

delete from SA_TaskRelation where sTaskID1 in (select sID from SA_Task where sFlowID in (select sFlowID from SA_Task where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and version=-1))

delete from SA_TokenInstance where sProcessInstanceID in (select sFlowID from SA_Task where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and version=-1)

delete from SA_Task where sFlowID in (select sFlowID from (select sFlowID from SA_Task where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and version=-1) as t_tmp)

注意由于 oracle 子查询不能使用 as, 所以最后一个语句 oralce 改成

delete from SA_Task where sFlowID in (select sFlowID from (select sFlowID from SA_Task where sKindID='tkProcessInstance' and (sStatusID='tesAborted' or sStatusID='tesFinished') and version=-1))

注意事项

• 归档前， 检查任务表中是否有 version=-1 的记录，理论上不应该有， 如果有，需要执行以下语句 update SA_Task set version=0 where version=-1