blocks|key|132082|text|请尝试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|132083|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;|code-block|syntax|javascript|132084|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please try:

<pre><code>SET SERVEROUTPUT ON
DECLARE
v_emp int:=0;
BEGIN
 SELECT count(*) into v_emp FROM dba_tables where table_name = 'EMPLOYEE'; 

 if v_emp&lt;=0 then
 EXECUTE IMMEDIATE 'create table EMPLOYEE ( ID NUMBER(3), NAME VARCHAR2(30) NOT NULL)';
 end if;
END;
</code></pre>

blocks|key|350081|text|正如Rene还评论的那样，先检查再创建表是非常不常见的。如果你想根据你的方法运行代码，这将是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350082|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;|code-block|syntax|javascript|350083|但我宁愿去捕捉异常，为您节省一些不必要的代码行：|350084|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;+
/|350085|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>declare
nCount NUMBER;
v_sql LONG;

begin
SELECT count(*) into nCount FROM dba_tables where table_name = 'EMPLOYEE';
IF(nCount &lt;= 0)
THEN
v_sql:='
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)';
execute immediate v_sql;

END IF;
end;
</code></pre>

But I'd rather go catch on the Exception, saves you some unnecessary lines of code: 

<pre><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; 
/
</code></pre>

blocks|key|132134|text|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;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|132135|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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;
</code></pre>

blocks|key|350136|text|我知道这个话题有点老了，但我认为我做了一些可能对某人有用的事情，所以我把它贴了出来。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350137|我将这个帖子的答案中的建议汇编成了一个过程：|350138|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;|code-block|syntax|javascript|350139|然后，您可以通过以下方式使用它：|350140|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
++)'
);|350141|我知道传递两次表名有点多余，但我认为这在这里是最简单的。|350142|希望有人发现上面的内容很有用:-)。|350143|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

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:

<pre><code>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;
</code></pre>

You can then use it in a following way:

<pre><code>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
 )'
);
</code></pre>

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 :-).

blocks|key|132221|text|我的解决方案是在线程中编译最好的想法，并做一些改进。我既使用专用过程(@Tomasz+Borowiec)来促进重用，又使用异常处理(@Tobias+Twardon)来减少代码，并在过程中去除多余的表名。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|132222|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;
/|code-block|syntax|javascript|132223|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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.

<pre><code>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;
/
</code></pre>

blocks|key|132244|text|任何在创建之前依赖于测试的解决方案都可能会遇到一个“竞争”条件，即另一个进程在您测试它不存在和创建它之间创建表。-我知道一点小问题.|type|unstyled|depth|inlineStyleRanges|entityRanges|data|132245|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|350243|text|--检查特定架构中的表：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350244|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;|code-block|syntax|javascript|350245|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

-- checks for table in specfic schema:

<pre><code>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;
</code></pre>

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.

<pre><code>IF((SELECT count(*) FROM dba_tables where table_name = 'EMPLOYEE') &lt;= 0)
THEN
create table EMPLOYEE
(
ID NUMBER(3),
NAME VARCHAR2(30) NOT NULL
)
END IF;
</code></pre>

Which gives me error

<pre><code>Error: ORA-00900: invalid SQL statement
SQLState: 42000
ErrorCode: 900
Position: 1
</code></pre>

I search for the syntax for <code>IF</code> condition, I think which is also write.
Please suggest me....

Check table exist or not before create it in Oracle

正在尝试检查Oracle中的create之前是否存在表。搜索Stackoverflow和其他网站上的大部分帖子。找到一些查询，但它对我不起作用。IF((SELECT count(*) FROM dba_tables where table_name = 'EMPLOYEE') <= 0)THENcreate table...

问在Oracle中创建表之前检查表是否存在
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Oracle中创建表之前检查表是否存在EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Oracle中创建表之前检查表是否存在
EN