如何讓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