程序的结构是:
CREATE OR REPLACE PROCEDURE AAA
AS
BEGIN BEGIN
--这里执行插入操作,发生类型转换错误
INSERT INTO T2..........
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_cur := DBMS_SQL.OPEN_CURSOR;
sql_stmt := 'TRUNCATE TABLE T1';
DBMS_SQL.PARSE(v_cur,sql_stmt,DBMS_SQL.NATIVE);
v_int := DBMS_SQL.EXECUTE(v_cur);
COMMIT;
DBMS_SQL.CLOSE_CURSOR(v_cur);
END; END;
COMMIT; 其他语句...............
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(v_cur) THEN
DBMS_SQL.CLOSE_CURSOR(v_cur);
END IF; RAISE;END;
CREATE OR REPLACE PROCEDURE AAA
AS
BEGIN BEGIN
--这里执行插入操作,发生类型转换错误
INSERT INTO T2..........
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_cur := DBMS_SQL.OPEN_CURSOR;
sql_stmt := 'TRUNCATE TABLE T1';
DBMS_SQL.PARSE(v_cur,sql_stmt,DBMS_SQL.NATIVE);
v_int := DBMS_SQL.EXECUTE(v_cur);
COMMIT;
DBMS_SQL.CLOSE_CURSOR(v_cur);
END; END;
COMMIT; 其他语句...............
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(v_cur) THEN
DBMS_SQL.CLOSE_CURSOR(v_cur);
END IF; RAISE;END;
EXCEPTION中的异常处理语句完成后,goto l_point;
......
<<l_point>>
......
继续下面的语句
ORA-01843: not a valid month还是没有跳过去执行下面的语句。
例如:
.....
for .... loop
begin
--这里执行插入操作,发生类型转换错误
INSERT INTO T2..........
exception
when others then
exit;
end;
......
end loop;
SQL> set serveroutput on
SQL> declare
2 name varchar2(5);
3 i number;
4 begin
5 for i in 1..6 loop
6 begin
7 name := name || 'a';
8 exception
9 when others then
10 dbms_output.put_line(name);
11 exit;
12 end;
13 end loop;
14 dbms_output.put_line('asdfdadsfafda');
15 end;
16 /
aaaaa
asdfdadsfafdaPL/SQL procedure successfully completed.Elapsed: 00:00:01.62