④内部公开请勿外传---此存储过程把当前流程实例数据归档到历史表中，归档12个月前的数据，每运行一次归档1万实例，if(selectobject_id('Proc_BF_BackUpData'))isnotnullbegindropprocProc_BF_BackUpDataendgo--业务流程实例归档是以一个FMasterID中的内容为操作单元的。createprocProc_BF_BackUpDataasbeginDECLARE@everyCountint;DECLARE@AllCountint;DECLARE@Indexint;DECLARE@beginIndexint;DECLARE@endIndexint;set@everyCount=100;set@AllCount=100;set@Index=0;--得到10000个业务流程实例SELECTtop10000ROW_NUMBER()OVER(ORDERBYFMasterId)ASFId,FMasterIdinto#masterTbFROMtemp_instanceGROUPBYFMasterIdHAVINGdatediff(month,max(FStartTime),getdate())>=12createindexinx_tempMaterIdon#masterTb(FMasterId)--设置需要循环的次数Declare@trueCountint;select@trueCount=MAX(FId)from#masterTb;set@AllCount=@trueCount/@everyCountif@trueCount%@everyCount>0beginset@AllCount=@AllCount+1;end--携带量数据处理临时表createtable#tempAmount(FRouteIdvarchar(36))createindexinx_tempAmounton#tempAmount(FRouteId)---循环处理While@Index<@AllCountbeginset@beginIndex=@Index*@everyCount;1/3④内部公开请勿外传set@endIndex=(@Index+1)*@everyCount;begintry--开启事务begintransaction--携带量数据处理insertinto#tempAmountSELECTTEntry.FRouteIdFROM#masterTbTMasterINNERJOINtemp_instanceTInstON(TInst.FMasterId=TMaster.FMasterId)INNERJOINtemp_instanceEntryTEntryON(TEntry.FInstanceId=TInst.FInstanceId)WHERETMaster.FID>@beginIndexANDTMaster.FID<=@endIndex-----归档到历史表INSERTINTOt_BF_InstanceAmountHis(FDetailId,FRouteId,FSourceField,FTargetField,FAmount,FCreateTime,FBackUpTime)SELECTt0.FDetailId,t0.FRouteId,t0.FSourceField,t0.FTargetField,t0.FAmount,t0.FCreateTime,GETDATE()ASFBackUpTimeFROMtemp_instanceAmountt0innerjoin#tempAmountt1ont1.FRouteId=t0.FRouteId-----删除当前表数据DELETEFROMtemp_instanceAmountWHEREFRouteIdIN(selectFRouteIdfrom#tempAmount);--业务流程路线图数据处理，归档到历史表INSERTINTOt_BF_InstanceEntryHis(FRouteId,FInstanceId,FLineId,FSTableId,FSId,FTTableId,FTId,FFirstNode,FCreateTime,FBackUpTime)SELECTt0.FRouteId,t0.FInstanceId,t0.FLineId,ISNULL(t1.FSeq,0)ASFSTableID,t0.FSId,ISNULL(t2.FSeq,0)ASFTTableId,t0.FTId,t0.FFirstNode,t0.FCreateTime,GETDATE()ASFBackUpTimeFROMtemp_instanceEntryt0LEFTJOINT_BF_TableDefinet1ON(t0.FSTableName=t1.FTableNumber)LEFTJOINT_BF_TableDefinet2ON(t0.FTTableName=t2.FTableNumber)WHEREFInstanceIdIN(SELECTTInst.FInstanceIdFROM#masterTbTMasterINNERJOINtemp_instanceTInstON(TInst.FMasterId=TMaster.FMasterId)WHERETMaster.FID>@beginIndexANDTMaster.FID<=@endIndex);--业务流程路线图数据，删除当前表数据DELETEFROMtemp_instanceEntryWHEREFInstanceIdIN(SELECTTInst.FInstanceIdFROM#masterTbTMaster2/3④内部公开请勿外传INNERJOINtemp_instanceTInstON(TInst.FMasterId=TMaster.FMasterId)WHERETMaster.FID>@beginIndexANDTMaster.FID<=@endIndex);--业务流程实例数据，归档到历史表INSERTINTOt_BF_InstanceHis(FInstanceId,FFlowId,FSourceId,FMasterId,FStatus,FFirstFormId,FFirstBillId,FFirstBillNo,FStartTime,FBackUpTime)SELECTFInstanceId,FFlowId,FSourceId,FMasterId,FStatus,FFirstFormId,FFirstBillId,FFirstBillNo,FStartTime,GETDATE()ASFBackUpTimeFROMtemp_instanceWHEREFMasterIdIN(SELECTFMasterIdFROM#masterTbWHEREFId>=@beginIndexANDFID<=@endIndex);--业务流程实例数据，删除当前表数据DELETEFROMtemp_instanceWHEREFMasterIdIN(SELECTFMasterIdFROM#masterTbWHEREFId>=@beginIndexANDFID<=@endIndex);committransaction--select@Index,getDate();endtrybegincatchrollbacktransactionselectERROR_NUMBER()errorNumber,--错误代码ERROR_SEVERITY()errorSeverity,--错误严重级别ERROR_STATE()errorState,--错误状态码ERROR_PROCEDURE()errorProcedure,--出现错误的存储过程或触发器的名称ERROR_LINE()errorLine,--发生错误的行号ERROR_MESSAGE()errorMessageendcatch--下一次循环set@Index=@Index+1;endend3/3
