以下是我们所用到的一部分代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
|
begin
-- 定义事务变量
declare
t_error
integer
default
0;
declare
v_cerror
integer
default
0;
-- 定义其他
declare
v_count
int
(11);
-- 定义接收游标数据的变量
declare
v_id
varchar
(255);
declare
v_create_date datetime;
declare
v_del_flag
varchar
(1);
declare
v_remarks
varchar
(255);
declare
v_update_date datetime;
declare
v_address
varchar
(255);
declare
v_appstate
varchar
(255);
declare
v_area_id
varchar
(255);
declare
v_belong
varchar
(255);
declare
v_borough_id
varchar
(255);
declare
v_borough_name
varchar
(255);
declare
v_broker_id
varchar
(255);
declare
v_check_name
varchar
(255);
declare
v_check_time
varchar
(255);
declare
v_consigner_id
varchar
(255);
declare
v_down_time
varchar
(255);
declare
v_draw_url
varchar
(255);
declare
v_house_age
varchar
(255);
declare
v_house_desc
varchar
(255);
declare
v_house_drawing
varchar
(255);
declare
v_house_feature
varchar
(255);
declare
v_house_fitment
varchar
(255);
declare
v_house_floor
varchar
(255);
declare
v_house_hall
varchar
(255);
declare
v_house_no
varchar
(255);
declare
v_house_price
double
;
declare
v_house_room
varchar
(255);
declare
v_house_thumb
varchar
(255);
declare
v_house_title
varchar
(255);
declare
v_house_toilet
varchar
(255);
declare
v_house_topfloor
varchar
(255);
declare
v_house_totalarea
double
;
declare
v_house_toward
varchar
(255);
declare
v_house_type
varchar
(255);
declare
v_house_veranda
varchar
(255);
declare
v_is_down
varchar
(255);
declare
v_is_promote
varchar
(255);
declare
v_is_synchro
varchar
(255);
declare
v_is_whether
varchar
(255);
declare
v_owner_name
varchar
(255);
declare
v_owner_notes
varchar
(255);
declare
v_owner_phone
varchar
(255);
declare
v_status
varchar
(255);
declare
v_submit_name
varchar
(255);
declare
v_submit_time
varchar
(255);
declare
v_synchro_time
varchar
(255);
declare
v_video
varchar
(255);
declare
v_whether_time
varchar
(255);
declare
v_create_by
varchar
(255);
declare
v_update_by
varchar
(255);
declare
v_area_name
varchar
(255);
declare
v_is_new
varchar
(255);
declare
v_log_flag
varchar
(255);
declare
v_log_status
varchar
(255);
declare
v_lot_NO
varchar
(255);
-- 遍历数据结束标志
declare
done
int
default
false
;
-- 游标
declare
cur
cursor
for
select
`id`,`create_date`,`del_flag`,`remarks`,`update_date`,
`address`,`appstate`,`area_id`,`belong`,`borough_id`,
`borough_name`,`broker_id`,`check_name`,`check_time`,`consigner_id`,
`down_time`,`draw_url`,`house_age`,`house_desc`,`house_drawing`,
`house_feature`,`house_fitment`,`house_floor`,`house_hall`,`house_no`,
`house_price`,`house_room`,`house_thumb`,`house_title`,`house_toilet`,
`house_topfloor`,`house_totalarea`,`house_toward`,`house_type`,`house_veranda`,
`is_down`,`is_promote`,`is_synchro`,`is_whether`,`owner_name`,
`owner_notes`,`owner_phone`,`status`,`submit_name`,`submit_time`,
`synchro_time`,`video`,`whether_time`,`create_by`,`update_by`,
`area_name`,`is_new`,
`log_flag`,`log_status`,`lot_NO`
from
zj_front_ihouse.zj_front_housesell_swp
where
lot_NO = p_lot_NO
and
(log_flag =
'i'
or
log_flag =
'd'
or
log_flag =
'u'
)
order
by
update_date
asc
;
-- 将结束标志绑定到游标
declare
continue
handler
for
not
found
set
done =
true
;
-- 事务错误默认值
declare
continue
handler
for
sqlexception
set
t_error=1;
-- 开启事务
start
transaction
;
-- 打开游标
open
cur;
-- 开始循环
read_loop: loop
-- 赋初始值
set
v_id=
''
,v_create_date=
'0000-01-01 00:00:00'
,v_del_flag=
''
,v_remarks=
''
,v_update_date=
'0000-01-01 00:00:00'
,
v_address=
''
,v_appstate=
''
,v_area_id=
''
,v_belong=
''
,v_borough_id=
''
,
v_borough_name=
''
,v_broker_id=
''
,v_check_name=
''
,v_check_time=
''
,v_consigner_id=
''
,
v_down_time=
''
,v_draw_url=
''
,v_house_age=
''
,v_house_desc=
''
,v_house_drawing=
''
,
v_house_feature=
''
,v_house_fitment=
''
,v_house_floor=
''
,v_house_hall=
''
,v_house_no=
''
,
v_house_price=0.0,v_house_room=
''
,v_house_thumb=
''
,v_house_title=
''
,v_house_toilet=
''
,
v_house_topfloor=
''
,v_house_totalarea=0.0,v_house_toward=
''
,v_house_type=
''
,v_house_veranda=
''
,
v_is_down=
''
,v_is_promote=
''
,v_is_synchro=
''
,v_is_whether=
''
,v_owner_name=
''
,
v_owner_notes=
''
,v_owner_phone=
''
,v_status=
''
,v_submit_name=
''
,v_submit_time=
''
,
v_synchro_time=
''
,v_video=
''
,v_whether_time=
''
,v_create_by=
''
,v_update_by=
''
,
v_area_name=
''
,v_is_new=
''
,
v_log_flag =
''
,v_log_status =
''
,v_lot_NO =
''
;
-- 提取游标里的数据
fetch
cur
into
v_id,v_create_date,v_del_flag,v_remarks,v_update_date,
v_address,v_appstate,v_area_id,v_belong,v_borough_id,
v_borough_name,v_broker_id,v_check_name,v_check_time,v_consigner_id,
v_down_time,v_draw_url,v_house_age,v_house_desc,v_house_drawing,
v_house_feature,v_house_fitment,v_house_floor,v_house_hall,v_house_no,
v_house_price,v_house_room,v_house_thumb,v_house_title,v_house_toilet,
v_house_topfloor,v_house_totalarea,v_house_toward,v_house_type,v_house_veranda,
v_is_down,v_is_promote,v_is_synchro,v_is_whether,v_owner_name,
v_owner_notes,v_owner_phone,v_status,v_submit_name,v_submit_time,
v_synchro_time,v_video,v_whether_time,v_create_by,v_update_by,
v_area_name,v_is_new,
v_log_flag,v_log_status,v_lot_NO;
-- 声明结束的时候
if done
then
leave read_loop;
end
if;
-- 这里做你想做的循环的事件
set
v_count = (
select
count
(*)
from
zj_front_ihouse.zj_front_housesell
where
id = v_id);
if t_error = 1
then
set
v_cerror = v_cerror + t_error;
rollback
;
leave read_loop;
end
if;
-- select v_count,v_log_flag;
if v_log_flag =
'i'
then
if v_count != 0
then
update
zj_front_ihouse.zj_front_housesell
set
`id`=v_id,`create_date`=v_create_date,`del_flag`=v_del_flag,`remarks`=v_remarks,`update_date`=v_update_date,
`address`=v_address,`appstate`=v_appstate,`area_id`=v_area_id,`belong`=v_belong,`borough_id`=v_borough_id,
`borough_name`=v_borough_name,`broker_id`=v_broker_id,`check_name`=v_check_name,`check_time`=v_check_time,`consigner_id`=v_consigner_id,
`down_time`=v_down_time,`draw_url`=v_draw_url,`house_age`=v_house_age,`house_desc`=v_house_desc,`house_drawing`=v_house_drawing,
`house_feature`=v_house_feature,`house_fitment`=v_house_fitment,`house_floor`=v_house_floor,`house_hall`=v_house_hall,`house_no`=v_house_no,
`house_price`=v_house_price,`house_room`=v_house_room,`house_thumb`=v_house_thumb,`house_title`=v_house_title,`house_toilet`=v_house_toilet,
`house_topfloor`=v_house_topfloor,`house_totalarea`=v_house_totalarea,`house_toward`=v_house_toward,`house_type`=v_house_type,`house_veranda`=v_house_veranda,
`is_down`=v_is_down,`is_promote`=v_is_promote,`is_synchro`=v_is_synchro,`is_whether`=v_is_whether,`owner_name`=v_owner_name,
`owner_notes`=v_owner_notes,`owner_phone`=v_owner_phone,`status`=v_status,`submit_name`=v_submit_name,`submit_time`=v_submit_time,
`synchro_time`=v_synchro_time,`video`=v_video,`whether_time`=v_whether_time,`create_by`=v_create_by,`update_by`=v_update_by,
`area_name`=v_area_name,`is_new`=v_is_new
where
id = v_id;
if t_error = 1
then
set
v_cerror = v_cerror + t_error;
rollback
;
leave read_loop;
end
if;
else
insert
into
zj_front_ihouse.zj_front_housesell (
`id`,`create_date`,`del_flag`,`remarks`,`update_date`,
`address`,`appstate`,`area_id`,`belong`,`borough_id`,
`borough_name`,`broker_id`,`check_name`,`check_time`,`consigner_id`,
`down_time`,`draw_url`,`house_age`,`house_desc`,`house_drawing`,
`house_feature`,`house_fitment`,`house_floor`,`house_hall`,`house_no`,
`house_price`,`house_room`,`house_thumb`,`house_title`,`house_toilet`,
`house_topfloor`,`house_totalarea`,`house_toward`,`house_type`,`house_veranda`,
`is_down`,`is_promote`,`is_synchro`,`is_whether`,`owner_name`,
`owner_notes`,`owner_phone`,`status`,`submit_name`,`submit_time`,
`synchro_time`,`video`,`whether_time`,`create_by`,`update_by`,
`area_name`,`is_new`
)
values
(
v_id,v_create_date,v_del_flag,v_remarks,v_update_date,
v_address,v_appstate,v_area_id,v_belong,v_borough_id,
v_borough_name,v_broker_id,v_check_name,v_check_time,v_consigner_id,
v_down_time,v_draw_url,v_house_age,v_house_desc,v_house_drawing,
v_house_feature,v_house_fitment,v_house_floor,v_house_hall,v_house_no,
v_house_price,v_house_room,v_house_thumb,v_house_title,v_house_toilet,
v_house_topfloor,v_house_totalarea,v_house_toward,v_house_type,v_house_veranda,
v_is_down,v_is_promote,v_is_synchro,v_is_whether,v_owner_name,
v_owner_notes,v_owner_phone,v_status,v_submit_name,v_submit_time,
v_synchro_time,v_video,v_whether_time,v_create_by,v_update_by,
v_area_name,v_is_new
);
if t_error = 1
then
set
v_cerror = v_cerror + t_error;
rollback
;
leave read_loop;
end
if;
end
if;
elseif v_log_flag =
'd'
then
if v_count !=0
then
delete
from
zj_front_ihouse.zj_front_housesell
where
id = v_id;
if t_error = 1
then
set
v_cerror = v_cerror + t_error;
rollback
;
leave read_loop;
end
if;
end
if;
elseif v_log_flag =
'u'
then
if v_count != 0
then
update
zj_front_ihouse.zj_front_housesell
set
`id`=v_id,`create_date`=v_create_date,`del_flag`=v_del_flag,`remarks`=v_remarks,`update_date`=v_update_date,
`address`=v_address,`appstate`=v_appstate,`area_id`=v_area_id,`belong`=v_belong,`borough_id`=v_borough_id,
`borough_name`=v_borough_name,`broker_id`=v_broker_id,`check_name`=v_check_name,`check_time`=v_check_time,`consigner_id`=v_consigner_id,
`down_time`=v_down_time,`draw_url`=v_draw_url,`house_age`=v_house_age,`house_desc`=v_house_desc,`house_drawing`=v_house_drawing,
`house_feature`=v_house_feature,`house_fitment`=v_house_fitment,`house_floor`=v_house_floor,`house_hall`=v_house_hall,`house_no`=v_house_no,
`house_price`=v_house_price,`house_room`=v_house_room,`house_thumb`=v_house_thumb,`house_title`=v_house_title,`house_toilet`=v_house_toilet,
`house_topfloor`=v_house_topfloor,`house_totalarea`=v_house_totalarea,`house_toward`=v_house_toward,`house_type`=v_house_type,`house_veranda`=v_house_veranda,
`is_down`=v_is_down,`is_promote`=v_is_promote,`is_synchro`=v_is_synchro,`is_whether`=v_is_whether,`owner_name`=v_owner_name,
`owner_notes`=v_owner_notes,`owner_phone`=v_owner_phone,`status`=v_status,`submit_name`=v_submit_name,`submit_time`=v_submit_time,
`synchro_time`=v_synchro_time,`video`=v_video,`whether_time`=v_whether_time,`create_by`=v_create_by,`update_by`=v_update_by,
`area_name`=v_area_name,`is_new`=v_is_new
where
id = v_id;
if t_error = 1
then
set
v_cerror = v_cerror + t_error;
rollback
;
leave read_loop;
end
if;
else
insert
into
zj_front_ihouse.zj_front_housesell (
`id`,`create_date`,`del_flag`,`remarks`,`update_date`,
`address`,`appstate`,`area_id`,`belong`,`borough_id`,
`borough_name`,`broker_id`,`check_name`,`check_time`,`consigner_id`,
`down_time`,`draw_url`,`house_age`,`house_desc`,`house_drawing`,
`house_feature`,`house_fitment`,`house_floor`,`house_hall`,`house_no`,
`house_price`,`house_room`,`house_thumb`,`house_title`,`house_toilet`,
`house_topfloor`,`house_totalarea`,`house_toward`,`house_type`,`house_veranda`,
`is_down`,`is_promote`,`is_synchro`,`is_whether`,`owner_name`,
`owner_notes`,`owner_phone`,`status`,`submit_name`,`submit_time`,
`synchro_time`,`video`,`whether_time`,`create_by`,`update_by`,
`area_name`,`is_new`
)
values
(
v_id,v_create_date,v_del_flag,v_remarks,v_update_date,
v_address,v_appstate,v_area_id,v_belong,v_borough_id,
v_borough_name,v_broker_id,v_check_name,v_check_time,v_consigner_id,
v_down_time,v_draw_url,v_house_age,v_house_desc,v_house_drawing,
v_house_feature,v_house_fitment,v_house_floor,v_house_hall,v_house_no,
v_house_price,v_house_room,v_house_thumb,v_house_title,v_house_toilet,
v_house_topfloor,v_house_totalarea,v_house_toward,v_house_type,v_house_veranda,
v_is_down,v_is_promote,v_is_synchro,v_is_whether,v_owner_name,
v_owner_notes,v_owner_phone,v_status,v_submit_name,v_submit_time,
v_synchro_time,v_video,v_whether_time,v_create_by,v_update_by,
v_area_name,v_is_new
);
if t_error = 1
then
set
v_cerror = v_cerror + t_error;
rollback
;
leave read_loop;
end
if;
end
if;
end
if;
end
loop;
-- 关闭游标
close
cur;
-- 清理swp表
if v_cerror = 0
then
delete
from
zj_front_ihouse.zj_front_housesell_swp
where
lot_NO = p_lot_NO
and
(log_flag =
'i'
or
log_flag =
'd'
or
log_flag =
'u'
);
if t_error = 1
then
set
v_cerror = v_cerror + t_error;
rollback
;
end
if;
end
if;
-- 结束事务
if v_cerror = 0
then
commit
;
else
rollback
;
end
if;
end
|
本文转自 tanzhenchao 51CTO博客，原文链接：http://blog.51cto.com/cmdschool/1698697，如需转载请自行联系原作者
![2024Mysql And Redis基础与进阶操作系列（9）作者——LJS[含MySQL存储过程之局部、系统变量、参数传递、流程控制-判断/case具体详步骤；注意点及常见报错问题所对应的解决方法]](https://ucc.alicdn.com/pic/developer-ecology/56pfyutwi4tv4_c53df19098d2427391fe36eeb35f84c8.png?x-oss-process=image/format,webp/resize,h_160,m_lfit)