--每次清理1000条，清理180天以前的
--流程上下文数据
delete t_wfr_procinstdata
WHERE FPROCINSTID IN (SELECT top 1000 FPROCINSTID
FROM t_wfr_procinstdata wrid
WHERE EXISTS(SELECT fprocinstid
FROM t_wfr_procinst pid
WHERE (fstate = 'closed.completed' OR fstate = 'closed.aborted') AND
pid.fprocinstid = wrid.fprocinstid) AND DATEDIFF(DD, ftime, NOW()) >= 180);
--流程上下历史
DELETE t_wfr_procinstdatahst
WHERE FPROCINSTID IN (SELECT top 1000 FPROCINSTID
FROM t_wfr_procinstdatahst wrid
WHERE EXISTS(SELECT fprocinstid
FROM t_wfr_procinst pid
WHERE (fstate = 'closed.completed' OR fstate = 'closed.aborted') AND
pid.fprocinstid = wrid.fprocinstid) AND DATEDIFF(DD, ftime, NOW()) >= 180);
--清理运行数据
DELETE T_WFR_Runtime
WHERE FPROCINSTID IN (SELECT top 1000 FPROCINSTID
FROM T_WFR_Runtime wrid
WHERE EXISTS(SELECT fprocinstid
FROM t_wfr_procinst pid
WHERE (fstate = 'closed.completed' OR fstate = 'closed.aborted') AND
pid.fprocinstid = wrid.fprocinstid) AND
DATEDIFF(DD, FLASTUPDATEDTIME, NOW()) >= 180) ;
--清理不存在引用的流程定义数据
DELETE t_wfr_procdef
WHERE FMD5HASH IN (SELECT top 1000 FMD5HASH
FROM t_wfr_procdef prd
WHERE NOT EXISTS(SELECT
pci.fprocdefid,
fprocdefver,
fprocdefhash
FROM t_wfr_procinst pci
WHERE pci.fprocdefhash = prd.fmd5hash) AND EXISTS(SELECT procdefid
FROM (SELECT
MAX(
pdb.finnerversion) maxversion,
pdb.fprocdefid procdefid
FROM t_wfr_procdef pdb
GROUP BY
pdb.fprocdefid) temp
WHERE
temp.procdefid = prd.fprocdefid
AND prd.finnerversion <
temp.maxversion) AND 180 > -1) ;
--清理流程日志信息
DELETE T_WFR_PROCESSINSTLOG
WHERE fid IN (SELECT top 1000 fid
FROM T_WFR_PROCESSINSTLOG wrid
WHERE EXISTS(SELECT fprocinstid
FROM t_wfr_procinst pid
WHERE (fstate = 'closed.completed' OR fstate = 'closed.aborted') AND
pid.fprocinstid = wrid.FPROCESSINSTID) AND DATEDIFF(DD, ftime, NOW()) >= 180);
--清理引擎日志
DELETE T_WFR_ENGINELOG
WHERE fid IN (SELECT top 1000 fid
FROM T_WFR_ENGINELOG
WHERE DATEDIFF(DD, ftime, NOW()) >= 180);
;