oracle 如果我删除一个表并且该表不存在，我会收到一个错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2052697/
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
If I drop a table and the table doesn't exist, I get an error
提问by Gold
I need to drop a table and make a new one. If I drop the table and the table doesn't exist, I get an error
我需要放下一张桌子并制作一张新桌子。如果我删除该表并且该表不存在，则会出现错误
How can I check if the table exists?
如何检查表是否存在？
I'm working on Oracle 11g
我正在研究 Oracle 11g
Thanks in advance.
提前致谢。
回答by Scott Anderson
You could do something like this:
你可以这样做：
DECLARE v_exist PLS_INTEGER;
BEGIN
SELECT COUNT(*) INTO v_exist
FROM user_tables
WHERE table_name = 'YOURTABLEHERE';
IF v_exist = 1 THEN
EXECUTE IMMEDIATE 'DROP TABLE YOURTABLEHERE';
END IF;
回答by Bob Jarvis - Reinstate Monica
DECLARE
eTABLE_OR_VIEW_DOES_NOT_EXIST EXCEPTION;
PRAGMA EXCEPTION_INIT(eTABLE_OR_VIEW_DOES_NOT_EXIST, -942);
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE SCHEMA.WHATEVER';
EXCEPTION
WHEN eTABLE_OR_VIEW_DOES_NOT_EXIST THEN
NULL;
END;
Share and enjoy.
分享和享受。
回答by Philip Schlump
I have been using the following procedure to take care of this:
我一直在使用以下程序来处理这个问题：
create or replace procedure drop_table_if_exists ( p_table_name varchar2 )
is
it_exist number;
begin
select count(*)
into it_exists
from user_tables
where table_name = p_table_name
;
if it_exists >= 1 then
execute immediate 'drop table '||p_table_name;
end if;
end;
/
exec drop_table_if_exists ( 'TABLE_TO_DROP' );
回答by davek
something like
就像是
select count(*) from user_tables
where table_name= :table name
or
或者
select count(*) from dba_tables
where owner = :table owner
and table_name = :table name
or a heavy-handed alternative:
或严厉的替代方案：
begin execute immediate 'drop table table_name';
exception when others then null;
end;
