wake-up-neo.com

Oracle: Wenn Tabelle vorhanden ist

Ich schreibe einige Migrationsskripte für eine Oracle-Datenbank und hoffte, dass Oracle etwas Ähnliches wie das IF EXISTS-Konstrukt von MySQL hatte.

Immer wenn ich eine Tabelle in MySQL löschen möchte, mache ich so etwas

DROP TABLE IF EXISTS `table_name`;

Wenn die Tabelle nicht existiert, erzeugt die Variable DROP keinen Fehler und das Skript kann fortfahren.

Hat Oracle einen ähnlichen Mechanismus? Ich erkenne, dass ich die folgende Abfrage verwenden könnte, um zu überprüfen, ob eine Tabelle vorhanden ist oder nicht

SELECT * FROM dba_tables where table_name = 'table_name';

aber die Syntax, um das mit einer DROP zu verbinden, entgeht mir.

sqloraclesql-drop
291
Alan Storm

Die beste und effizienteste Methode ist, die Ausnahme "Tabelle nicht gefunden" aufzufangen: Dadurch wird der Aufwand vermieden, wenn die Tabelle zweimal geprüft wird. und leidet nicht unter dem Problem, dass die Ausnahme beim Aufruf des DROP aus einem anderen Grund (der möglicherweise wichtig ist) immer noch an den Aufrufer gesendet wird

BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
      END IF;
END;

ADDENDUM Hier sind die entsprechenden Blöcke für andere Objekttypen:

Sequenz

BEGIN
  EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2289 THEN
      RAISE;
    END IF;
END;

Aussicht

BEGIN
  EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Auslösen

BEGIN
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4080 THEN
      RAISE;
    END IF;
END;

Index

BEGIN
  EXECUTE IMMEDIATE 'DROP INDEX ' || index_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1418 THEN
      RAISE;
    END IF;
END;

Säule

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                || ' DROP COLUMN ' || column_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -904 THEN
      RAISE;
    END IF;
END;

Datenbank-Link

BEGIN
  EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2024 THEN
      RAISE;
    END IF;
END;

Materialisierte Ansicht

BEGIN
  EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -12003 THEN
      RAISE;
    END IF;
END;

Art

BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Zwang

BEGIN
  EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
            || ' DROP CONSTRAINT ' || constraint_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -2443 THEN
      RAISE;
    END IF;
END;

Scheduler-Job

BEGIN
  DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -27475 THEN
      RAISE;
    END IF;
END;

Benutzer/Schema

BEGIN
  EXECUTE IMMEDIATE 'DROP USER ' || user_name;
  /* you may or may not want to add CASCADE */
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1918 THEN
      RAISE;
    END IF;
END;

Paket

BEGIN
  EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Verfahren

BEGIN
  EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Funktion

BEGIN
  EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4043 THEN
      RAISE;
    END IF;
END;

Tabellenbereich

BEGIN
  EXECUTE IMMEDIATE 'DROP TABLESPACE' || tablespace_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -959 THEN
      RAISE;
    END IF;
END;
484
Jeffrey Kemp
declare
   c int;
begin
   select count(*) into c from user_tables where table_name = upper('table_name');
   if c = 1 then
      execute immediate 'drop table table_name';
   end if;
end;

Hiermit wird geprüft, ob eine Tabelle im aktuellen Schema vorhanden ist ..__ Um zu überprüfen, ob eine bestimmte Tabelle bereits in einem anderen Schema vorhanden ist, müssen Sie all_tables anstelle von user_tables verwenden und die Bedingung all_tables.owner = upper('schema_name') hinzufügen.

121
Marius Burz

Ich habe nach dem gleichen gesucht, aber am Ende habe ich ein Verfahren geschrieben, um mir zu helfen:

CREATE OR REPLACE PROCEDURE DelObject(ObjName varchar2,ObjType varchar2)
IS
 v_counter number := 0;   
begin    
  if ObjType = 'TABLE' then
    select count(*) into v_counter from user_tables where table_name = upper(ObjName);
    if v_counter > 0 then          
      execute immediate 'drop table ' || ObjName || ' cascade constraints';        
    end if;   
  end if;
  if ObjType = 'PROCEDURE' then
    select count(*) into v_counter from User_Objects where object_type = 'PROCEDURE' and OBJECT_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP PROCEDURE ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'FUNCTION' then
    select count(*) into v_counter from User_Objects where object_type = 'FUNCTION' and OBJECT_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP FUNCTION ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'TRIGGER' then
    select count(*) into v_counter from User_Triggers where TRIGGER_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP TRIGGER ' || ObjName;
      end if; 
  end if;
  if ObjType = 'VIEW' then
    select count(*) into v_counter from User_Views where VIEW_NAME = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP VIEW ' || ObjName;        
      end if; 
  end if;
  if ObjType = 'SEQUENCE' then
    select count(*) into v_counter from user_sequences where sequence_name = upper(ObjName);
      if v_counter > 0 then          
        execute immediate 'DROP SEQUENCE ' || ObjName;        
      end if; 
  end if;
end;

Hoffe das hilft

24
Robert Vabo

ich wollte nur einen vollständigen Code posten, der eine Tabelle erstellt und löscht, wenn sie bereits mit Jeffreys Code vorhanden ist (kudos für ihn, nicht für mich!).

BEGIN
    BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE tablename';
    EXCEPTION
         WHEN OTHERS THEN
                IF SQLCODE != -942 THEN
                     RAISE;
                END IF;
    END;

    EXECUTE IMMEDIATE 'CREATE TABLE tablename AS SELECT * FROM sourcetable WHERE 1=0';

END;
12
mishkin

Mit SQL * PLUS können Sie auch den WHENEVER SQLERROR-Befehl verwenden:

WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;

WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;

Mit CONTINUE NONE wird ein Fehler gemeldet, das Skript wird jedoch fortgesetzt. Mit EXIT SQL.SQLCODE wird das Skript im Fehlerfall beendet.

siehe auch: WHENEVER SQLERROR Docs

9
trunkc

Eine Möglichkeit ist die Verwendung von DBMS_ASSERT.SQL_OBJECT_NAME :

Diese Funktion überprüft, ob der Eingabeparameter ein qualifizierter SQL-Bezeichner eines vorhandenen SQL-Objekts ist.

DECLARE
    V_OBJECT_NAME VARCHAR2(30);
BEGIN
   BEGIN
        V_OBJECT_NAME  := DBMS_ASSERT.SQL_OBJECT_NAME('tab1');
        EXECUTE IMMEDIATE 'DROP TABLE tab1';

        EXCEPTION WHEN OTHERS THEN NULL;
   END;
END;
/

DBFiddle Demo

3
Lukasz Szozda

Es gibt in Oracle keine 'DROP TABLE IF EXISTS', Sie müssten die select-Anweisung ausführen.

versuchen Sie Folgendes (ich bin nicht mit der Oracle-Syntax vertraut, wenn also meine Variablen ify sind, bitte verzeihen Sie mir):

declare @count int
select @count=count(*) from all_tables where table_name='Table_name';
if @count>0
BEGIN
    DROP TABLE tableName;
END
3
Erich

Eine andere Methode besteht darin, eine Ausnahme zu definieren und dann nur diese Ausnahme abzufangen, wobei sich alle anderen ausbreiten lassen. 

Declare
   eTableDoesNotExist Exception;
   PRAGMA EXCEPTION_INIT(eTableDoesNotExist, -942);
Begin
   EXECUTE IMMEDIATE ('DROP TABLE myschema.mytable');
Exception
   When eTableDoesNotExist Then
      DBMS_Output.Put_Line('Table already does not exist.');
End;
2
Leigh Riffel

Und wenn Sie die Eingabe und die Minimierung der Drop/Create-Zyklen wieder herstellen möchten, können Sie die DDL mit dbms_metadata.get_ddl zwischenspeichern und alles mit einem Konstrukt wie dem folgenden neu erstellen: declare v_ddl varchar2(4000); begin select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') into v_ddl from dual; [COMPARE CACHED DDL AND EXECUTE IF NO MATCH] exception when others then if sqlcode = -31603 then [GET AND EXECUTE CACHED DDL] else raise; end if; end; Dies ist nur ein Beispiel. Es sollte eine Schleife enthalten, in der DDL-Typ, Name und Besitzer Variablen sind.

0
Andrei Nossov

Sie können den Fehler immer selbst einfangen.

begin
execute immediate 'drop table mytable';
exception when others then null;
end;

Es wird als schlechte Praxis betrachtet, dies zu übertreffen, ähnlich wie leere catch () 's in anderen Sprachen.

Grüße
K

0
Khb

Ich möchte lieber die Tabelle und den Schemabesitzer angeben.

Achten Sie auch auf die Fallempfindlichkeit. (Siehe "obere" Klausel weiter unten).

Ich habe ein paar verschiedene Objekte hineingeworfen, um zu zeigen, dass es an anderen Stellen als TABLE verwendet werden kann.

.............

declare
   v_counter int;
begin
 select count(*) into v_counter from dba_users where upper(username)=upper('UserSchema01');
   if v_counter > 0 then
      execute immediate 'DROP USER UserSchema01 CASCADE';
   end if; 
end;
/



CREATE USER UserSchema01 IDENTIFIED BY pa$$Word
  DEFAULT TABLESPACE users
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON users;

grant create session to UserSchema01;

Und ein TABELLE Beispiel:

declare
   v_counter int;
begin
 select count(*) into v_counter from all_tables where upper(TABLE_NAME)=upper('ORDERS') and upper(OWNER)=upper('UserSchema01');
   if v_counter > 0 then
      execute immediate 'DROP TABLE UserSchema01.ORDERS';
   end if; 
end;
/
0
granadaCoder
BEGIN
   EXECUTE IMMEDIATE 'DROP TABLE "IMS"."MAX" ';
EXCEPTION
   WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
         RAISE;
          END IF;
         EXECUTE IMMEDIATE ' 
  CREATE TABLE "IMS"."MAX" 
   (    "ID" NUMBER NOT NULL ENABLE, 
    "NAME" VARCHAR2(20 BYTE), 
     CONSTRAINT "MAX_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSAUX"  ';


END;

// Überprüft anhand dieses Codes, ob die Tabelle vorhanden ist, und erstellt später die Tabelle max. das funktioniert einfach in einer einzigen Zusammenstellung

0
Mahesh Pandeya

Ich bevorzuge eine wirtschaftliche Lösung

BEGIN
    FOR i IN (SELECT NULL FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OBJECT_NAME = 'TABLE_NAME') LOOP
            EXECUTE IMMEDIATE 'DROP TABLE TABLE_NAME';
    END LOOP;
END;
0
Pavel S

Leider nein, es gibt kein Drop, wenn es existiert, oder CREATE IF NOT EXIST

Sie könnten ein plsql-Skript schreiben, um die Logik dort aufzunehmen.

http://download.Oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9003.htm

Ich beschäftige mich nicht sehr mit Oracle-Syntax, aber ich denke, @ Erichs Skript wäre so ähnlich.

declare 
cant integer
begin
select into cant count(*) from dba_tables where table_name='Table_name';
if count>0 then
BEGIN
    DROP TABLE tableName;
END IF;
END;
0
Tom

Ein solcher Block könnte für Sie nützlich sein.

DECLARE
    table_exist INT;

BEGIN
    SELECT Count(*)
    INTO   table_exist
    FROM   dba_tables
    WHERE  owner = 'SCHEMA_NAME' 
    AND table_name = 'EMPLOYEE_TABLE';

    IF table_exist = 1 THEN
      EXECUTE IMMEDIATE 'drop table EMPLOYEE_TABLE';
    END IF;
END;
0
user9213118