工作流常用sql語句

1、說明SQL語句某個流程已經(jīng)辦結的所有流程實例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某個流程所有狀態(tài)的流程實例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將一個流程實例狀態(tài)改為正常正在 辦理update WFI_PROINST set PROINST_STATUS=1,PROINST_TYPE=1

4、where proinst_id=#將一個活動實例狀態(tài)改為正常正在 辦理UPDATE WFI_ACTINSTSET INSTANCE_TYPE = 1, OPERATE_TYPE = 1, ACTINST_STAUS =1 where actinst_id=#查詢一種流程的所有相關實例數(shù)據(jù)/*流程實例*/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)刪除一種流程的所有相關實例數(shù)據(jù)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獲得員工地區(qū)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=*查詢某一項目的所有相關實例數(shù)據(jù)/*流程實例*/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 =*)刪除某一項目的所有相關實例數(shù)據(jù)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=*查詢已經(jīng)產(chǎn)生某一活動的所有項目select * from wfi_project where proinst_id in(select proinst_id from wfi_actinst where actdef_id in(*)用工作流定義工具導流程的時候，并 發(fā)路由只能導入