1 USE [NyghSql]
2 GO
3 /****** Object: StoredProcedure [dbo].[delete_date] Script Date: 10/13/2015 17:30:06 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 ALTER procedure [dbo].[delete_date]
9 (
10 @applycode nvarchar(100)
11 )
12 as
13 --声明变量 定义变量用逗号隔开
14 declare @applycode_var nvarchar(100),
15 @instanceid_var nvarchar(100),
16 @t_ywsloid_var nvarchar(100),
17 @areaPart_oid nvarchar(100),
18 @areaPartoid nvarchar(100)
19 begin
20 --查出 工作流实例
21 select @instanceid_var=Instanceid from t_ywsl where applycode=@applycode
22 --删除 工作流实例对应的任务表（待办和已办）
23 delete from AssignTask where AssignTasK.Instanceid=@instanceid_var
24 --查询业务受理oid
25 select @t_ywsloid_var=t_ywsloid from t_ywsl where applycode=@applycode
26
27 --循环删除对应的坐标范围
28 --sql server
29 --定义游标
30 declare my_cursor cursor
31 --读取数据放到游标中
32 for(select AreaPartoid from AreaPart where T_YWSLOID=@t_ywsloid_var)
33 --打开游标
34 open my_cursor
35 --读取集合中的第一行数据
36 fetch next from my_cursor into @areaPart_oid
37 --全局变量,0表示fetch语句成功；-1表示语句失败或此行不在结果集中；-2被提取的行不存在
38 while @@FETCH_STATUS=0
39 begin
40 delete from AreaPoints where AreaPartOID=@areaPart_oid
41 --读取下一行
42 fetch next from my_cursor into @areaPart_oid
43 end
44 close my_cursor
45 deallocate my_cursor
46
47 --删除对应的地块列表
48 delete from AreaPart where T_YWSLOID=@t_ywsloid_var
49 --删除对应的附件管理
50 delete from FileManage where T_YWSL_FK=@t_ywsloid_var
51 --审批意见
52 delete from SPYJ where T_YWSL_FK=@t_ywsloid_var
53 --流程意见
54 --工作流实例记录本
55 delete from FlowInstance where Instanceid=@instanceid_var;
56 --活动转移条件集
57 declare t_cursor cursor for
58 select TaskTicketoid from TaskTicket where Instanceid=@instanceid_var
59 open t_cursor
60 fetch next from t_cursor into @areaPartoid
61 while @@FETCH_STATUS=0
62 begin
63 delete from TransConditionEx where TaskTicket_FK=@areaPartoid
64 fetch next from t_cursor into @areaPartoid
65 end
66 close t_cursor
67 deallocate t_cursor
68 --任务表
69 delete from TaskTicket where Instanceid=@instanceid_var
70 --业务受理表
71 delete from t_ywsl where applycode=@applycode
72 end
