PL/SQL中的几种异常处理方法

这是Pona的文章，我斗胆将其贴上来，Pona不要介意哦！^_^

PL/SQL里，有三种方法可以在处理大批量数据时不会因为一条或几条数据错误而导致异常中止程序。

1、用Fetch into a cursor%TYPE把要处理的数据放到记录集里。当一条数据不符条件时，用标签<

  1<next_record>&gt;和GOTO NEXT_RECORD跳转语句使程序忽略这一条，转到下一条继续处理。 
  2
  3\------------------------------------------------------------------------------- 
  4
  5\-- Function Name  :  CalculateImportCharge 
  6
  7\-- Function Desc  :  Calculate Import Charge 
  8
  9\-- Created by  :  Author 
 10
 11\-- Created Date  :  2003-05-16 
 12
 13\------------------------------------------------------------------------------- 
 14
 15FUNCTION CalculateImportCharge ( 
 16
 17p_i_job_id  IN VARCHAR2, 
 18
 19p_i_as_of_date_id IN VARCHAR2) RETURN NUMBER 
 20
 21AS 
 22
 23CURSOR  cur_ShipBlHeader  IS 
 24
 25SELECT import_folder_no 
 26
 27FROM GMY_SHIP_BL_HEADER 
 28
 29WHERE CANCEL_FLG = GMY_GA000_PKG.BL_CANCEL_FLG_OFF; 
 30
 31rec_ShipBlHeader  cur_ShipBlHeader%ROWTYPE; 
 32
 33BEGIN 
 34
 35OPEN  cur_ShipBlHeader; 
 36
 37FETCH  cur_ShipBlHeader INTO rec_ShipBlHeader; 
 38
 39WHILE cur_ShipBlHeader%FOUND  LOOP 
 40
 41x_num_error_code := GMY_GA000_PKG.CheckValidMasterBlNo ( 
 42
 43p_i_job_id, 
 44
 45p_i_as_of_date_id, 
 46
 47rec_ShipBlHeader.import_folder_no, 
 48
 49x_vch_message); 
 50
 51IF x_num_error_code 
 52
 53IN (GMY_GA000_PKG.gn#NG, GMY_GA000_PKG.INVALID_BL_NO) THEN 
 54
 55x_vch_message := 
 56
 57p_i_job_id 
 58
 59|| ' WARNING: Function CheckValidMasterBlNo @' 
 60
 61|| ' Import folder ' 
 62
 63|| rec_ShipBlHeader.import_folder_no 
 64
 65|| ' - Invalid BL No.'; 
 66
 67COM_LOG.PUTLINE (p_i_job_id, x_vch_message); 
 68
 69GOTO NEXT_RECORD  ; 
 70
 71END IF; 
 72
 73x_num_error_code := CheckExistsOfAccDate ( 
 74
 75p_i_job_id, 
 76
 77p_i_as_of_date_id, 
 78
 79rec_ShipBlHeader.import_folder_no); 
 80
 81IF x_num_error_code = GMY_GA000_PKG.gn#NG THEN 
 82
 83GOTO NEXT_RECORD  ; 
 84
 85END IF; 
 86
 87COMMIT; 
 88
 89&lt;<next_record>&gt;
 90
 91FETCH  cur_ShipBlHeader INTO rec_ShipBlHeader; 
 92
 93END  LOOP  ; 
 94
 95CLOSE cur_ShipBlHeader; 
 96
 97RETURN GMY_GA000_PKG.gn#OK; 
 98
 99EXCEPTION 
100
101WHEN OTHERS THEN 
102
103x_vch_message := 
104
105p_i_job_id 
106
107|| ' ERROR:  Function CalculateImportCharge @ ' 
108
109|| SUBSTR (SQLERRM (SQLCODE), 1, 100); 
110
111COM_LOG.PUTLINE (p_i_job_id, x_vch_message); 
112
113RETURN GMY_GA000_PKG.gn#NG; 
114
115END CalculateImportCharge; 
116
1172、当使用the Cursor FOR Loop循环时，在Loop循环里，把会出问题的情况写进一个独立的block块中，这个块包括完整的begin、end部分及exception异常处理部分。这样即使一条数据出现异常，也会继续执行下一条。 
118
119\------------------------------------------------------------------------------- 
120
121\-- Function Name  : GenerateInsCostInfRec 
122
123\-- Function Desc  : Generate records to transmit in INF table 
124
125\-- Created by  : SISS(AP) 
126
127\-- Created Date  : 2003-03-26 
128
129\-- ---------------------------------------------------------------------------- 
130
131FUNCTION GenerateInsCostInfRec ( 
132
133p_i_job_id  IN  VARCHAR2, 
134
135p_i_as_of_date_id  IN  VARCHAR2) RETURN NUMBER 
136
137AS 
138
139CURSOR cur_cost IS 
140
141SELECT cost.ROWID costRowId, 
142
143cost.import_folder_no,, 
144
145cost.insur_trans_id 
146
147FROM GMY_COST_BL cost, 
148
149GMY_COMMON_MST mst 
150
151WHERE cost.import_folder_no=invheader.import_folder_no 
152
153AND cost.billing_amt_num IS NOT NULL 
154
155AND cost.billing_amt_num!=0 
156
157AND cost.insur_db_cr!=0; 
158
159BEGIN 
160
161FOR rec_cost IN cur_cost  LOOP 
162
163BEGIN 
164
165x_num_ret_value := GMY_GA000_PKG.CheckValidMasterBlNo( 
166
167p_i_job_id, 
168
169p_i_as_of_date_id, 
170
171rec_cost.import_folder_no, 
172
173x_vch_error_msg); 
174
175IF x_num_ret_value = GMY_GA000_PKG.VALID_BL_NO THEN 
176
177INSERT INTO GMY_COST_INS_INF( 
178
179cost_trx_id,, 
180
181created_by, 
182
183program_name) 
184
185VALUES( 
186
187GMY_COST_INS_INF_S.NEXTVAL, 
188
189PRG_NAME, 
190
191PRG_NAME); 
192
193ELSIF x_num_ret_value = GMY_GA000_PKG.INVALID_BL_NO THEN 
194
195x_vch_error_msg := p_i_job_id 
196
197|| ' Import folder ' 
198
199|| rec_cost.import_folder_no 
200
201|| ' has repeated BL No. with other import folder.' 
202
203|| ' Failed in insurance cost transmission.'; 
204
205COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg); 
206
207END IF; 
208
209EXCEPTION 
210
211WHEN OTHERS THEN 
212
213IF SQL%ROWCOUNT &gt; 0 THEN  \-- check for 'too many rows' 
214
215x_vch_error_msg := p_i_job_id||' '|| 
216
217SUBSTR(SQLERRM(SQLCODE),1,100); 
218
219COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg); 
220
221ELSE 
222
223x_vch_error_msg := p_i_job_id||' '|| 
224
225SUBSTR(SQLERRM(SQLCODE),1,100); 
226
227COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg); 
228
229END IF; 
230
231END; 
232
233END  LOOP  ; 
234
235COMMIT; 
236
237RETURN GMY_GA000_PKG.gn#OK; 
238
239EXCEPTION 
240
241WHEN OTHERS THEN 
242
243x_vch_error_msg := p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100); 
244
245COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg); 
246
247ROLLBACK; 
248
249RETURN GMY_GA000_PKG.gn#NG; 
250
251END GenerateInsCostInfRec; 
252
2533、当使用the Cursor FOR Loop循环时，在Loop循环里，把会出问题的情况拆分成子函数，分别处理。 
254
255\---------------------------------------------------------------------------- 
256
257\-- Function Name  :  CopyDsToActualDs 
258
259\-- Function Desc  :  Copy the records from DS DB to Actual DS DB. 
260
261\-- Created by  :  Author 
262
263\-- Created Date  :  2003-02-20 
264
265\---------------------------------------------------------------------------- 
266
267FUNCTION CopyDsToActualDs ( 
268
269p_i_job_id  IN  VARCHAR2, 
270
271p_i_as_of_date_id  IN  VARCHAR2)  RETURN NUMBER 
272
273IS 
274
275CURSOR cur_DsScc IS 
276
277SELECT * 
278
279FROM  GMY_DS_SCC; 
280
281BEGIN 
282
283FOR rec_DsHead IN cur_DsScc  LOOP 
284
285x_num_error_code := InsToActualScc( 
286
287p_i_job_id, 
288
289p_i_as_of_date_id, 
290
291rec_DsHead.order_by_code, 
292
293rec_DsHead.po_code, 
294
295rec_DsHead.wh); 
296
297END  LOOP  ; 
298
299EXCEPTION 
300
301WHEN OTHERS THEN 
302
303x_vch_error_msg := p_i_job_id 
304
305||' Function Name: CopyDsToActualDs'; 
306
307COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg); 
308
309x_vch_error_msg:=p_i_job_id||' '||SUBSTR(SQLERRM(SQLCODE),1,100); 
310
311COM_LOG.PUTLINE(p_i_job_id, x_vch_error_msg); 
312
313ROLLBACK; 
314
315RETURN GMY_GA000_PKG.gn#NG; 
316
317END CopyDsToActualDs; 
318
319\---------------------------------------------------------------------------- 
320
321\-- Function Name  :  InsToActualScc 
322
323\-- Function Desc  :  Deal with insert section. 
324
325\-- Created by  :  Author 
326
327\-- Created Date  :  2003-03-13 
328
329\---------------------------------------------------------------------------- 
330
331FUNCTION  InsToActualScc  ( 
332
333p_i_job_id  IN  VARCHAR2, 
334
335p_i_as_of_date_id  IN  VARCHAR2, 
336
337p_i_order_by_code  IN  VARCHAR2, 
338
339p_i_po_code  IN  VARCHAR2, 
340
341p_i_wh  IN  VARCHAR2 
342
343) RETURN NUMBER 
344
345IS 
346
347x_vch_error_msg VARCHAR2(255); 
348
349BEGIN 
350
351INSERT INTO GMY_ACTUAL_DS_SCC( 
352
353order_by_code, 
354
355po_code, 
356
357wh ) 
358
359VALUES( p_i_order_by_code, 
360
361p_i_po_code, 
362
363p_i_wh); 
364
365COMMIT; 
366
367RETURN GMY_GA000_PKG.gn#OK; 
368
369EXCEPTION 
370
371WHEN OTHERS THEN 
372
373x_vch_error_msg := p_i_job_id||' Function Name: InsToActualScc'; 
374
375COM_LOG.PUTLINE(p_i_job_id,x_vch_error_msg); 
376
377x_vch_error_msg := p_i_job_id 
378
379&lt;SPAN lang=EN-US style="COLOR: black</next_record></next_record>
Published At
Categories with 数据库类
Tagged with
comments powered by Disqus