如何在存储过程的循环中实现错误的异常处理CREATE OR REPLACE PROCEDURE PRO_TEST16 ( OUT P_COUNTER INTEGER,OUT returnCode INTEGER, OUT returnMsg CHAR(32) )-- returnCode 返回执行中的的错误代码 -- returnMsg 返回执行中的的错误描述 SPECIFIC SQL_OUT_LANGUAGE LANGUAGE SQL--测试循环插入,先执行清空表操作再向表中插入数据。添加异常处理L1: BEGINDECLARE SQLCODE INTEGER DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE errorLabel CHAR(32) DEFAULT ''; DECLARE v_at_end,v_curover INTEGER DEFAULT 0;DECLARE v_num INTEGER;DECLARE v_tabname,v_tabname2,v_tabname3 VARCHAR(100);DECLARE v_sql1,v_sql2,v_sql3,v_sql4 VARCHAR(500);DECLARE c1 CURSOR WITH HOLDFOR select tabname,tabname2,tabname3 FROM test0602; --test0602的两个字段的属性是库中已存在的表名select count(1) into v_num from test0602;open c1;cursorloop1:loopFETCH c1 INTO v_tabname,v_tabname2,v_tabname3;if v_curover=v_num thenleave cursorloop1;end if;set v_sql1= 'delete from '|| v_tabname3;--先执行清空表操作set v_sql2= 'insert into '|| v_tabname3 || ' select * from ' || v_tabname; --向临时表中插入表的数据set v_sql3= 'delete from '|| v_tabname; --清空表的数据set v_sql4= 'insert into '|| v_tabname || ' select * from ' || v_tabname3 || ' union select * from ' || v_tabname2; prepare v_stmt1 from v_sql1;execute v_stmt1;prepare v_stmt2 from v_sql2;execute v_stmt2;prepare v_stmt3 from v_sql3;execute v_stmt3;prepare v_stmt4 from v_sql4;execute v_stmt4;SET v_curover = v_curover + 1;end loop cursorloop1;close c1;SET p_counter = v_curover;DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET returnCode = SQLCODE; SET returnMsg = errorLabel; END;END L1;
具体:以上代码中,打个比方如果当v_num =2时,下面的4个SQL中其中一个执行时报错,则记录错误代码并且跳到v_num=3时执行所有4个SQL,而不是退出存储过程,如何实现?