在Oracle中创建检查表之前是否存在检查表 [英] Check table exist or not before create it in Oracle

问题描述

在Oracle中创建之前，尝试检查表是否存在。从Stackoverflow和其他人那里搜索大部分帖子。找一些查询，但它对我不起作用。

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.

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;

这给了我错误

Error: ORA-00900: invalid SQL statement
SQLState:  42000
ErrorCode: 900
Position: 1

我搜索 IF 条件的语法，我认为这也是写的。
请建议我....

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

推荐答案

正如Rene所评论的那样，首先检查然后创建是很常见的桌子。
如果你想根据你的方法获得一个正在运行的代码，这将是：

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:

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;

但我宁愿去看看Exception，为你节省一些不必要的代码：

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

这篇关于在Oracle中创建检查表之前是否存在检查表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！