sql - usuario - pivot oracle columns to rows

Oracle: si la tabla existe

Question

Estoy escribiendo algunos scripts de migración para una base de datos Oracle, y esperaba que Oracle tuviera algo similar a la estructura IF EXISTS MySQL.

Específicamente, cada vez que quiero colocar una tabla en MySQL, hago algo como

DROP TABLE IF EXISTS `table_name`;

De esta manera, si la tabla no existe, el DROP no produce un error y la secuencia de comandos puede continuar.

¿Tiene Oracle un mecanismo similar? Me doy cuenta de que podría usar la siguiente consulta para verificar si existe una tabla o no

SELECT * FROM dba_tables where table_name = ''table_name'';

pero la sintaxis para unir eso junto con un DROP me está escapando.

Answer 1

Con SQL * PLUS también puede usar el comando WHENEVER SQLERROR:

WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;

WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;

Con CONTINUE NONE se informa un error, pero el script continuará. Con EXIT SQL.SQLCODE el script terminará en el caso de un error.

Véase también: WHEREVER SQLERROR Docs

Answer 2

He estado buscando lo mismo pero terminé escribiendo un procedimiento para ayudarme:

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;

Espero que esto ayude

Answer 3

La forma mejor y más eficiente es capturar la excepción "tabla no encontrada": esto evita la sobrecarga de verificar si la tabla existe dos veces; y no tiene el problema de que si el DROP falla por alguna otra razón (que podría ser importante), la excepción aún se presenta al llamante:

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

ADDENDUM Para referencia, aquí están los bloques equivalentes para otros tipos de objetos:

Secuencia

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

Ver

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

Desencadenar

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

Índice

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

Columna

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

Enlace a la base de datos

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

Vista materializada

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

Tipo

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

Restricción

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

Trabajo programador

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

Usuario / Esquema

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;

Paquete

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

Procedimiento

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

Función

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

Espacio de tabla

BEGIN
  EXECUTE IMMEDIATE ''DROP TABLESPACE'' || tablespace_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -959 THEN
      RAISE;
    END IF;
END;

Answer 4

Lamentablemente no, no hay tal cosa como soltar si existe, o CREAR SI NO EXISTE

Usted podría escribir un script plsql para incluir la lógica allí.

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

No me gusta mucho la sintaxis de Oracle, pero creo que el script de @ Erich sería algo como esto.

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;

Answer 5

No hay un ''DROP TABLE IF EXISTS'' en oracle, tendría que hacer la declaración de selección.

intente esto (no estoy al tanto de la sintaxis de Oracle, así que si mis variables son dudosas, por favor, perdónenme):

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

Answer 6

Otro método es definir una excepción y luego capturar esa excepción, permitiendo que todos los demás se propaguen.

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;

Answer 7

Prefiero especificar la tabla y el propietario del esquema.

Cuidado con la sensibilidad de los casos también. (ver la cláusula "superior" a continuación).

Tiré un par de objetos diferentes para mostrar que se pueden usar en lugares además de TABLAS.

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

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;  

Y un ejemplo de TABLA:

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

Answer 8

Prefiero seguir la solución económica

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;

Answer 9

Siempre podrías atrapar el error tú mismo.

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

Se considera una mala práctica abusar de esto, similar a las capturas vacías en otros idiomas.

Saludos
K

Answer 10

Un bloque como este podría ser útil para ti.

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;  

Answer 11

Una forma es usar DBMS_ASSERT.SQL_OBJECT_NAME :

Esta función verifica que la cadena del parámetro de entrada es un identificador de SQL calificado de un objeto SQL existente.

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

Demostración de DBFiddle

Answer 12

Y si desea que sea reenterable y minimice los ciclos de soltar / crear, puede almacenar el DDL utilizando dbms_metadata.get_ddl y volver a crear todo utilizando una construcción como esta: 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; 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; Esto es solo una muestra, debe haber un bucle dentro con el tipo de DDL, el nombre y el propietario son variables.

Answer 13

solo quería publicar un código completo que cree una tabla y la suelte si ya existe usando el código de Jeffrey (¡felicitaciones a él, no a mí!).

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;

Answer 14

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;

// Al hacer este código, comprueba si la tabla existe y luego crea la tabla max. Esto simplemente funciona en una sola compilación.

Answer 15

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;

Eso es para verificar si existe una tabla en el esquema actual. Para verificar si una tabla dada ya existe en un esquema diferente, tendría que usar all_tables lugar de user_tables y agregar la condición all_tables.owner = upper(''schema_name'')