SQL 删除表 如果它存在于 Oracle (IF EXIST)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35156618/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
drop table If it exists in Oracle (IF EXIST)
提问by Abderrahim
I am using Oracle 12c and I am not interesting to have an error while droping my table 'CONTINENT' in case it doesn't exist.
我正在使用 Oracle 12c，并且在删除我的表“CONTINENT”时出现错误并不有趣，以防它不存在。
I did this
我做了这个
set echo on
set serveroutput on
alter session set current_schema=WORK_ODI;
set verify off
set pause off
begin
execute immediate 'drop table continent';
exception when others then null;
end;
This script is work with me well. and I use this script too :
这个脚本很适合我。我也使用这个脚本：
declare
c int;
begin
select count(*) into c from user_tables where table_name = upper('continent');
if c = 1 then
execute immediate 'drop table continent';
end if;
end;
the both scripts work well but my boss wants something like IF EXIT. Anybody can help me please. how to use IF EXIT in this case ?
这两个脚本都运行良好，但我的老板想要类似IF EXIT. 任何人都可以帮助我。在这种情况下如何使用 IF EXIT ？
回答by Emil Moise
Sorry, there is no if existsin the Oracle's drop table syntax.
对不起，没有if exists在Oracle的DROP TABLE语法。
回答by Marmite Bomber
You can do two things
你可以做两件事
define the exception you want to ignore (here ORA-00942)
add an undocumented (and not implemented) hint /*+ IF EXISTS */ that will pleased you management.
定义要忽略的异常（此处为 ORA-00942）
添加一个未记录（且未实施）的提示 /*+ IF EXISTS */ 这会让您的管理感到高兴。
.
.
declare
table_does_not_exist exception;
PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);
begin
execute immediate 'drop table continent /*+ IF EXISTS */';
exception when table_does_not_exist then
DBMS_OUTPUT.PUT_LINE('Ignoring table or view does not exist')
;
end;
/
Additional note: the usage of
补充说明：使用
exception when others then null;
may be dangerous, as for example you ignore also errors such as tablespace offline, when the table is NOT DROPPED.
可能很危险，例如，当表未删除时，您还会忽略诸如表空间脱机之类的错误。
回答by SkyWalker
set echo on
set serveroutput on
alter session set current_schema=WORK_ODI;
set verify off
set pause off
WHENEVER OSERROR EXIT FAILURE ROLLBACK
drop table continent;
WHENEVER OSERROR CONTINUE
回答by MaxU
I had a similar problem - i needed a way how to repeat DDL scripts without modifying them. Imaging the following script:
我有一个类似的问题 - 我需要一种方法来重复 DDL 脚本而不修改它们。对以下脚本进行映像：
create table tab1(...);
create table tab2(...);
create table tab3{...}; /* <--- this one fails*/
create table tab4(...);
so now we have the following situation: tables "tab1" and "tab2" have been successfully created, "tab3" and "tab4" are missing. So after fixing statement for the "tab3" table we would have to comment out create statements for "tab1" and "tab2" - it might be very annoying when working with big SQL scripts containing many DDLs and many bugs.
所以现在我们有以下情况：表“tab1”和“tab2”已成功创建，“tab3”和“tab4”缺失。因此，在修复“tab3”表的语句后，我们必须注释掉“tab1”和“tab2”的创建语句——当使用包含许多 DDL 和许多错误的大型 SQL 脚本时，这可能会非常烦人。
So i came up with the following procedure which allows to re-run DDL statements:
所以我想出了以下允许重新运行 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 '<TBS_NAME>' 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
Usage example:
用法示例：
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');
Output:
输出：
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.
