Проверить таблицу существует или нет, прежде чем создавать ее в Oracle

Попытка проверить, существует ли таблица перед созданием в 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;

Что дает мне ошибку

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

Я ищу синтаксис для условия IF, я думаю, что тоже пишет. Пожалуйста, предложите мне....

Ответ 1

Как и заметил Рене, довольно редко проверять сначала, а затем создавать таблицу. Если вы хотите иметь текущий код в соответствии с вашим методом, это будет:

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, сохранит вам ненужные строки кода:

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

Ответ 2

Попробуйте:

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;

Ответ 3

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;

Ответ 4

Я знаю, что эта тема немного устарела, но я думаю, что я сделал кое-что, что может быть полезно кому-то, поэтому я отправляю его.

Я скомпилировал предложения из этого потока ответов в процедуру:

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;

Затем вы можете использовать его следующим образом:

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
  )'
);

Я знаю, что это немного избыточно, чтобы передать имя таблицы дважды, но я думаю, что это самый простой.

Надеюсь, что кто-то найдет полезное: -).

Ответ 5

Мое решение - просто сборник лучших идей в потоке, с небольшим улучшением. Я использую как специальную процедуру (@Tomasz Borowiec) для облегчения повторного использования, так и обработку исключений (@Tobias Twardon) для сокращения кода и избавления от избыточного имени таблицы в процедуре.

DECLARE

    PROCEDURE create_table_if_doesnt_exist(
        p_create_table_query VARCHAR2
    ) IS
    BEGIN
        EXECUTE IMMEDIATE 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;
/

Ответ 6

Любое решение, которое основано на тестировании перед созданием, может столкнуться с условием "гонки", когда другой процесс создает таблицу между тем, как вы проверяете, что она не существует, и создаете ее. - Незначительный момент, который я знаю.

Ответ 7

- проверяет таблицу в специальной схеме:

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;