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