如何让procedure存储过程中的异常代码不中断继续执行

SQL> create or replace procedure proc_dep
  2  authid current_user is
  3  cursor cur_test is select object_name,object_type,owner from dba_objects where wner='SCOTT' and object_name in ('PROC_OY','PROC_NON') order by object_name desc;
  4  v_object_type dba_objects.object_type%type;
  5  v_object_name dba_objects.object_name%type;
  6  v_owner dba_objects.object_name%type;
  7  v_text clob;
  8  begin
  9  open cur_test;
 10  loop
 11  fetch cur_test into v_object_name,v_object_type,v_owner;
 12  exit when cur_test%notfound;
 13  dbms_metadata.set_transform_param( dbms_metadata.SESSION_TRANSFORM,'TABLESPACE',false);
 14  select dbms_metadata.get_ddl(v_object_type,v_object_name,v_owner) into v_text from dual;
 15  v_text:=replace(v_text,v_owner,'TBL_BCK');
 16    /************异常模块化处理************/
 17    begin
 18    execute immediate v_text;
 19    exception
 20      when others then
 21        continue;
 22    end;
 23  end loop;
 24  close cur_test;
 25  end;
 26  /
 
Procedure created
 
SQL> select * from user_objects;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
PROC_OY                                                                                                              68402                PROCEDURE           2013/2/28 2 2013/2/28 21: 2013-02-28:21:48:15 INVALID N         N         N                  1
PROC_DEP                                                                                                             68400                PROCEDURE           2013/2/28 2 2013/2/28 22: 2013-02-28:22:06:49 VALID   N         N         N                  1
 
SQL> drop procedure proc_oy;
 
Procedure dropped
 
SQL> exec proc_dep;
 
PL/SQL procedure successfully completed
 
SQL> select * from user_objects
  2  ;
 
OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
PROC_DEP                                                                                                             68400                PROCEDURE           2013/2/28 2 2013/2/28 22: 2013-02-28:22:06:49 VALID   N         N         N                  1
PROC_NON                                                                                                             68401                PROCEDURE           2013/2/28 2 2013/2/28 22: 2013-02-28:22:07:24 VALID   N         N         N                  1
PROC_OY                                                                                                              68402                PROCEDURE           2013/2/28 2 2013/2/28 22: 2013-02-28:22:07:23 INVALID N         N         N                  1

 

 小结：

   1,通过begin exception end模块化异常代码块

   2,异常代码中添加continue
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-754997/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-754997/