在 Oracle 中创建表之前检查表是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15630771/
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
Check table exist or not before create it in Oracle
提问by Navnath
Trying to check is table exist before create in Oracle. Search for most of the post from Stackoverflow and others too. Find some query but it didn't work for me.
在 Oracle 中创建之前尝试检查表是否存在。从 Stackoverflow 和其他人中搜索大部分帖子。找到一些查询，但它对我不起作用。
IF((SELECT count(*) FROM dba_tables where table_name = 'EMPLOYEE') <= 0)
THEN
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)
END IF;
Which gives me error
这给了我错误
Error: ORA-00900: invalid SQL statement
SQLState: 42000
ErrorCode: 900
Position: 1
I search for the syntax for IFcondition, I think which is also write.
Please suggest me....
我搜索条件的语法IF，我认为这也是写。请给我建议....
采纳答案by Tobias Twardon
As Rene also commented, it's quite uncommon to check first and then create the table. If you want to have a running code according to your method, this will be:
正如 Rene 还评论的那样，先检查然后创建表是非常罕见的。如果您想根据您的方法运行代码，这将是：
declare
nCount NUMBER;
v_sql LONG;
begin
SELECT count(*) into nCount FROM dba_tables where table_name = 'EMPLOYEE';
IF(nCount <= 0)
THEN
v_sql:='
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;
END IF;
end;
But I'd rather go catch on the Exception, saves you some unnecessary lines of code:
但我宁愿去抓住异常，为您节省一些不必要的代码行：
declare
v_sql LONG;
begin
v_sql:='create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL; -- suppresses ORA-00955 exception
ELSE
RAISE;
END IF;
END;
/
回答by Tomasz Borowiec
I know this topic is a bit old, but I think I did something that may be useful for someone, so I'm posting it.
我知道这个话题有点老，但我认为我做了一些可能对某人有用的事情，所以我发布了它。
I compiled suggestions from this thread's answers into a procedure:
我将此线程的答案中的建议汇编成一个程序：
CREATE OR REPLACE PROCEDURE create_table_if_doesnt_exist(
p_table_name VARCHAR2,
create_table_query VARCHAR2
) AUTHID CURRENT_USER IS
n NUMBER;
BEGIN
SELECT COUNT(*) INTO n FROM user_tables WHERE table_name = UPPER(p_table_name);
IF (n = 0) THEN
EXECUTE IMMEDIATE create_table_query;
END IF;
END;
You can then use it in a following way:
然后，您可以通过以下方式使用它：
call create_table_if_doesnt_exist('my_table', 'CREATE TABLE my_table (
id NUMBER(19) NOT NULL PRIMARY KEY,
text VARCHAR2(4000),
modified_time TIMESTAMP
)'
);
I know that it's kinda redundant to pass table name twice, but I think that's the easiest here.
我知道两次传递表名有点多余，但我认为这是最简单的。
Hope somebody finds above useful :-).
希望有人发现以上有用:-)。
回答by Arkadiusz Przechodzki
My solution is just compilation of best ideas in thread, with a little improvement. I use both dedicated procedure (@Tomasz Borowiec) to facilitate reuse, and exception handling (@Tobias Twardon) to reduce code and to get rid of redundant table name in procedure.
我的解决方案只是在线程中汇编最佳想法，并稍作改进。我使用专用过程 (@Tomasz Borowiec) 来促进重用，并使用异常处理 (@Tobias Twardon) 来减少代码并消除过程中的冗余表名。
DECLARE
PROCEDURE create_table_if_doesnt_exist(
p_create_table_query VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE p_create_table_query;
EXCEPTION
WHEN OTHERS THEN
-- suppresses "name is already being used" exception
IF SQLCODE = -955 THEN
NULL;
END IF;
END;
BEGIN
create_table_if_doesnt_exist('
CREATE TABLE "MY_TABLE" (
"ID" NUMBER(19) NOT NULL PRIMARY KEY,
"TEXT" VARCHAR2(4000),
"MOD_TIME" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
');
END;
/
回答by Siggi Hergeirs
-- checks for table in specfic schema:
-- 检查特定模式中的表：
declare n number(10);
begin
Select count(*) into n from SYS.All_All_Tables where owner = 'MYSCHEMA' and TABLE_NAME = 'EMPLOYEE';
if (n = 0) then
execute immediate
'create table MYSCHEMA.EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL)';
end if;
end;
回答by Amiya kumar nayak
declare n number(10);
begin
select count(*) into n from tab where tname='TEST';
if (n = 0) then
execute immediate
'create table TEST ( ID NUMBER(3), NAME VARCHAR2 (30) NOT NULL)';
end if;
end;
回答by TechDo
Please try:
请尝试：
SET SERVEROUTPUT ON
DECLARE
v_emp int:=0;
BEGIN
SELECT count(*) into v_emp FROM dba_tables where table_name = 'EMPLOYEE';
if v_emp<=0 then
EXECUTE IMMEDIATE 'create table EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL)';
end if;
END;
回答by Kunal Vohra
Well there are lot of answeres already provided and lot are making sense too.
好吧，已经提供了很多答案，而且很多也很有意义。
Some mentioned it is just warning and some giving a temp way to disable warnings. All that will work but add risk when number of transactions in your DB is high.
有些人提到它只是警告，有些人提供了一种禁用警告的临时方法。当您的数据库中的事务数量很高时，所有这些都会起作用，但会增加风险。
I came across similar situation today and here is very simple queryI came up with...
我今天遇到了类似的情况，这是我想出的非常简单的查询...
declare
begin
execute immediate '
create table "TBL" ("ID" number not null)';
exception when others then
if SQLCODE = -955 then null; else raise; end if;
end;
/
955 is failure code.
955 是故障代码。
This is simple, if exception come while running query it will be suppressed. and you can use same for SQLor Oracle.
这很简单，如果在运行查询时出现异常，它将被抑制。并且您可以将相同的用于SQLor Oracle。
回答by mark d drake
Any solution which relies on testing before creation can run into a 'race' condition where another process creates the table between you testing that it does not exists and creating it. - Minor point I know.
任何在创建之前依赖于测试的解决方案都可能遇到“竞争”条件，在这种情况下，另一个进程会在您测试它不存在和创建它之间创建表。- 次要问题我知道。
