if exists drop table oracle,sql - drop table如果Oracle中存在该表（IF EXIST）[重复] - 堆栈内存溢出...

我有一个类似的问题-我需要一种无需修改即可重复DDL脚本的方法。 映像以下脚本：

create table tab1(...);

create table tab2(...);

create table tab3{...}; /*

create table tab4(...);

因此，现在我们处于以下情况：已成功创建表“ tab1”和“ tab2”，缺少“ tab3”和“ tab4”。 因此，在修复了“ tab3”表的语句之后，我们将不得不注释掉“ tab1”和“ tab2”的创建语句-当使用包含许多DDL和许多错误的大型SQL脚本时，这可能会很烦人。

因此，我提出了以下过程，该过程允许重新运行DDL语句：

create or replace procedure re_run_ddl (p_sql in varchar2)

AUTHID CURRENT_USER

as

l_line varchar2(500) default rpad('-',20,'-');

l_cr varchar2(2) default chr(10);

l_footer varchar2(500) default l_cr||rpad('*',20,'*');

l_ignore_txt varchar2(200) default 'IGNORING --> ';

ORA_00955 EXCEPTION;

ORA_01430 EXCEPTION;

ORA_02260 EXCEPTION;

ORA_01408 EXCEPTION;

ORA_00942 EXCEPTION;

ORA_02275 EXCEPTION;

ORA_01418 EXCEPTION;

ORA_02443 EXCEPTION;

ORA_01442 EXCEPTION;

ORA_01434 EXCEPTION;

ORA_01543 EXCEPTION;

ORA_00904 EXCEPTION;

ORA_02261 EXCEPTION;

ORA_04043 EXCEPTION;

ORA_02289 EXCEPTION;

PRAGMA EXCEPTION_INIT(ORA_00955, -00955); --ORA-00955: name is already used by an existing object

PRAGMA EXCEPTION_INIT(ORA_01430, -01430); --ORA-01430: column being added already exists in table

PRAGMA EXCEPTION_INIT(ORA_02260, -02260); --ORA-02260: table can have only one primary key

PRAGMA EXCEPTION_INIT(ORA_01408, -01408); --ORA-01408: such column list already indexed

PRAGMA EXCEPTION_INIT(ORA_00942, -00942); --ORA-00942: table or view does not exist

PRAGMA EXCEPTION_INIT(ORA_02275, -02275); --ORA-02275: such a referential constraint already exists in the table

PRAGMA EXCEPTION_INIT(ORA_01418, -01418); --ORA-01418: specified index does not exist

PRAGMA EXCEPTION_INIT(ORA_02443, -02443); --ORA-02443: Cannot drop constraint - nonexistent constraint

PRAGMA EXCEPTION_INIT(ORA_01442, -01442); --ORA-01442: column to be modified to NOT NULL is already NOT NULL

PRAGMA EXCEPTION_INIT(ORA_01434, -01434); --ORA-01434: private synonym to be dropped does not exist

PRAGMA EXCEPTION_INIT(ORA_01543, -01543); --ORA-01543: tablespace '' already exists

PRAGMA EXCEPTION_INIT(ORA_00904, -00904); --ORA-00904: "%s: invalid identifier"

PRAGMA EXCEPTION_INIT(ORA_02261, -02261); --ORA-02261: "such unique or primary key already exists in the table"

PRAGMA EXCEPTION_INIT(ORA_04043, -04043); --ORA-04043: object %s does not exist

PRAGMA EXCEPTION_INIT(ORA_02289, -02289); --ORA-02289: sequence does not exist

procedure p(

p_str in varchar2

,p_maxlength in int default 120

)

is

i int := 1;

begin

dbms_output.enable( NULL );

while ( (length(substr(p_str,i,p_maxlength))) = p_maxlength ) loop

dbms_output.put_line(substr(p_str,i,p_maxlength));

i := i + p_maxlength;

end loop;

dbms_output.put_line(substr(p_str,i,p_maxlength));

end p;

begin

p( 'EXEC:'||l_cr||l_line||l_cr||p_sql||l_cr||l_line );

execute immediate p_sql;

p( 'done.' );

exception

when ORA_00955 or ORA_01430 or ORA_02260 or ORA_01408 or ORA_00942

or ORA_02275 or ORA_01418 or ORA_02443 or ORA_01442 or ORA_01434

or ORA_01543 or ORA_00904 or ORA_02261 or ORA_04043 or ORA_02289

then p( l_ignore_txt || SQLERRM || l_footer );

when OTHERS then

p( SQLERRM );

p( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE );

p( l_footer );

RAISE;

end;

/

show err

用法示例：

set serveroutput on

begin

re_run_ddl('

create table test

(

id number,

s varchar2(30)

)

');

end;

/

exec re_run_ddl('drop table test');

exec re_run_ddl('drop table test');

exec re_run_ddl('drop table test');

输出：

EXEC:

--------------------

create table test

(

id number,

s varchar2(30)

)

--------------------

done.

PL/SQL procedure successfully completed.

EXEC:

--------------------

drop table test

--------------------

done.

PL/SQL procedure successfully completed.

stx11de2> EXEC:

--------------------

drop table test

--------------------

IGNORING --> ORA-00942: table or view does not exist

********************

PL/SQL procedure successfully completed.

stx11de2> EXEC:

--------------------

drop table test

--------------------

IGNORING --> ORA-00942: table or view does not exist

********************

PL/SQL procedure successfully completed.