工作流常用sql语句

1、说明SQL语句某个流程已经办结的所有流程实例Select Distinct po.* FROM WFI_ACTINST ai, VWFI_PROJECTLIST po Where ai.PROINST_ID = po.PROINST_ID And po.PRODEF_IDIn(441) And po.ProInst_Status In(0) Order By po.ProInst_ID Desc正在申请注销的所有流程实例SELECT DISTINCT ab.Learder_ID, po.* ,ai.ActInst_ID,ai.Staff_ID,ai.ActDef_ID ,ai.ACTINST_

2、STATUS, ai.START_DATE, ai.WILLFINISH_DATE as ACTWILLFINISH_DATE, ai.ACTINST_NAME, ai.ACTINS_COMMENT, ai.OPERATE_TYPE FROM WFI_ABNORMAL ab, WFI_ACTINST ai, VWFI_PROJECTLIST po Where ab.ACTINST_ID = ai.ACTINST_ID And po.PROINST_ID = ai.PROINST_ID And ai.OPERATE_TYPE In(19) And po.ProInst_Status != 0 A

3、nd ab.OPERATE_TYPE = ai.OPERATE_TYPE Order By ai.ActInst_ID Desc某个流程所有状态的流程实例Select Distinct po.* FROM WFI_ACTINST ai, VWFI_PROJECTLIST po Where ai.PROINST_ID = po.PROINST_ID And po.PRODEF_ID In(441) Order By po.ProInst_ID Desc将一个流程实例状态改为正常正在 办理update WFI_PROINST set PROINST_STATUS=1,PROINST_TYPE=1

4、where proinst_id=#将一个活动实例状态改为正常正在 办理UPDATE WFI_ACTINSTSET INSTANCE_TYPE = 1, OPERATE_TYPE = 1, ACTINST_STAUS =1 where actinst_id=#查询一种流程的所有相关实例数据/*流程实例*/select proinst_id from wfi_proinst where prodef_id=187/*项目实例*/select * from wfi_project where prodef_id=187/*子流程实例*/select * from wfi_subproinst wh

5、ere proinst_id in(select proinst_id from wfi_proinst where prodef_id=187)/*活动实例*/select actinst_id from wfi_actinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id=187)/*异常实例*/select * from wfi_abnormal where actinst_id in(select actinst_id from wfi_actinst where proinst_id in

6、(select proinst_id from wfi_proinst where prodef_id=187)/*路由实例*/select routeinst_id from wfi_routeinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id=187) select * from wfi_routeinsttoactinst where routeinst_id in(select routeinst_id from wfi_routeinst where proinst_id in(sel

7、ect proinst_id from wfi_proinst where prodef_id=187)清除所有流程的实例delete from wfi_routeinsttoactinst where routeinst_id in(select routeinst_id from wfi_routeinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id in (select prodef_id from wfd_prodef)delete from wfi_routeinst where pro

8、inst_id in(select proinst_id from wfi_proinst where prodef_id in (select prodef_id from wfd_prodef)delete from wfi_abnormal where actinst_id in(select actinst_id from wfi_actinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id in (select prodef_id from wfd_prodef)delete from w

9、fi_actinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id in (select prodef_id from wfd_prodef)delete from wfi_subproinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id in (select prodef_id from wfd_prodef)delete from wfi_project where prodef_id in (se

10、lect prodef_id from wfd_prodef)delete from wfi_proinst where prodef_id in (select prodef_id from wfd_prodef)删除一种流程的所有相关实例数据delete from wfi_routeinsttoactinst where routeinst_id in(select routeinst_id from wfi_routeinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id=187)delete

11、 from wfi_routeinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id=187)delete from wfi_abnormal where actinst_id in(select actinst_id from wfi_actinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id=187)delete from wfi_actinst where proinst_id in(select

12、 proinst_id from wfi_proinst where prodef_id=187)delete from wfi_subproinst where proinst_id in(select proinst_id from wfi_proinst where prodef_id=187)delete from wfi_project where prodef_id=187delete from wfi_proinst where prodef_id=187查询某一个分局受理的所有活动select ai.* FROM WFI_ACTINST ai， WFI_PROinst proi

13、 where ai PROINST_ID = proi PROINST_IDAnd ai.ActDef_ID In(select START_ACT from wfd_prodef)And proi DIS_ID In(440902获得员工地区Select Distinct cv Const_Value As Dist_ID, cv Const_Desc As Dist_Name From WFD_Staff, WFD_Dept. WFD_DeptStaff, WFD_CONSTVALUE cv, WFD_Const Where WFD_Staff.Staff_ID WFD_DeptStaff

14、 Staff_ID And WFD_DeptStaff DEPT_ID = WFD_Dept Dept_ID And cv.Const_ValueWFD_Dept DEPT_DISTRICT And cv CONST_ID = WFD_CONST CONST_ID And WFD_CONST.CONST_CODE DeptDist And WFD_Staff Staff_ID = 317 Order By cv.Const_Value某个活动即将完成时间加1天update wfi_actinst set WILLFINISH_DATE =dateadd(day,1,WILLFINISH_DAT

15、E) where actinst_id=*查询某一项目的所有相关实例数据/*流程实例*/select proinst_id from wfi_proinst where proinst_id =*/*项目实例*/select * from wfi_project where proinst_id=*/*子流程实例*/select * from wfi_subproinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)/*活动实例*/select actinst_id from wfi_ac

16、tinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)/*异常实例*/select * from wfi_abnormal where actinst_id in(select actinst_id from wfi_actinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)/*路由实例*/select routeinst_id from wfi_routeinst where p

17、roinst_id in(select proinst_id from wfi_proinst where proinst_id =*) select * from wfi_routeinsttoactinst where routeinst_id in(select routeinst_id from wfi_routeinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)删除某一项目的所有相关实例数据delete from wfi_routeinsttoactinst where ro

18、uteinst_id in(select routeinst_id from wfi_routeinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)delete from wfi_routeinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)delete from wfi_abnormal where actinst_id in(select actinst_id from wfi

19、_actinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)delete from wfi_actinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)delete from wfi_subproinst where proinst_id in(select proinst_id from wfi_proinst where proinst_id =*)delete from wfi_project where proinst _id=*delete from wfi_proinst where proinst _id=*查询已经产生某一活动的所有项目select * from wfi_project where proinst_id in(select proinst_id from wfi_actinst where actdef_id in(*)用工作流定义工具导流程的时候，并 发路由只能导入