任务归档执行的相关sql语句
1. 将需要归档的数据版本号设置为-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'
2. 将符合标记的数据的数据迁移到历史表
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)3. 从正式表中删除归档完的数据
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`