266 votos

Oracle: Si la tabla existe

Estoy escribiendo algunas migraciones scripts para una base de datos Oracle, y esperaba que Oracle tuviera algo similar a MySQL IF EXISTS construir.

En concreto, cada vez que quiero soltar 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 el script puede continuar.

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

SELECT * FROM dba_tables where table_name = 'table_name';

pero la sintaxis para unirlo con un DROP se me escapa.

436voto

Jeffrey Kemp Puntos 26050

La mejor y más eficiente manera es capturar la excepción "tabla no encontrada": esto evita la sobrecarga de comprobar si la tabla existe dos veces; y no sufre el problema de que si el DROP falla por alguna otra razón (que podría ser importante) la excepción sigue siendo lanzada al llamador:

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

ADDENDUM Como 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;

Disparador

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

Índice

BEGIN
  EXECUTE IMMEDATE '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 de 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;

114voto

Marius Burz Puntos 2665
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 comprobar si una tabla en el esquema actual existe. Para comprobar si una tabla dada ya existe en un esquema diferente, tendría que utilizar all_tables en lugar de user_tables y añadir la condición all_tables.owner = upper('schema_name')

24voto

Robert Vabo Puntos 158

He estado buscando lo mismo pero he acabado 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

12voto

mishkin Puntos 1680

Sólo quería publicar un código completo que creará una tabla y la eliminará si ya existe utilizando 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;

3voto

Erich Puntos 2966

En oracle no existe el 'DROP TABLE IF EXISTS', tendrías que hacer la sentencia select.

pruebe esto (no estoy al tanto de la sintaxis de oracle, así que si mis variables están mal, por favor perdóneme):

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

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X