Проверить таблицу существует или нет, прежде чем создавать ее в 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, я думаю, что тоже пишет.
Пожалуйста, предложите мне….

Лучший ответ:

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

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

Ответ №1

Попробуйте:

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;

Ответ №2

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;

Ответ №3

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

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

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

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

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

Ответ №4

Мое решение – просто сборник лучших идей в потоке, с небольшим улучшением. Я использую как специальную процедуру (@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;
/

Ответ №5

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

Ответ №6

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

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;