Oracle: Si La Table Existe
J'écris des scripts de migration pour une base de données Oracle, et j'espérais Qu'Oracle avait quelque chose de similaire à la construction IF EXISTS
de MySQL.
Plus précisément, chaque fois que je veux déposer une table dans MySQL, je fais quelque chose comme
DROP TABLE IF EXISTS `table_name`;
De cette façon, si la table n'existe pas, le DROP
ne produit pas d'erreur et le script peut continuer.
Oracle a-t-il un mécanisme similaire? Je me rends compte que je pourrais utiliser la requête suivante pour vérifier si une table existe ou non
SELECT * FROM dba_tables where table_name = 'table_name';
Mais la syntaxe pour lier cela avec un DROP
m'échappe.
15 réponses
Le meilleur et le plus efficace est d'attraper l'exception" table not found": cela évite la surcharge de vérifier si la table existe deux fois; et ne souffre pas du problème que si la chute échoue pour une autre raison (cela peut être important) l'exception est toujours soulevée à l'appelant:
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ' || table_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
Additif Pour référence, voici les blocs équivalents pour d'autres objets types:
Séquence
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2289 THEN
RAISE;
END IF;
END;
Vue
BEGIN
EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
Déclencheur
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;
Colonne
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
|| ' DROP COLUMN ' || column_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -904 THEN
RAISE;
END IF;
END;
Lien De Base De Données
BEGIN
EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2024 THEN
RAISE;
END IF;
END;
Vue Matérialisée
BEGIN
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -12003 THEN
RAISE;
END IF;
END;
De Type
BEGIN
EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Contrainte
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
|| ' DROP CONSTRAINT ' || constraint_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -2443 THEN
RAISE;
END IF;
END;
Tâche Du Planificateur
BEGIN
DBMS_SCHEDULER.drop_job(job_name);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -27475 THEN
RAISE;
END IF;
END;
Utilisateur / Schéma
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;
Paquet
BEGIN
EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Procédure
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Fonction
BEGIN
EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -4043 THEN
RAISE;
END IF;
END;
Tablespace
BEGIN
EXECUTE IMMEDIATE 'DROP TABLESPACE' || tablespace_name;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -959 THEN
RAISE;
END IF;
END;
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;
C'est pour vérifier si une table dans le schéma actuel existe.
Pour vérifier si une table existe déjà dans un schéma différent, vous devez utiliser le all_tables
au lieu de user_tables
et ajouter la condition all_tables.owner = upper('schema_name')
J'ai cherché la même chose mais j'ai fini par écrire une procédure pour m'aider:
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;
J'espère que cela aide
Je voulais juste poster un code complet qui va créer une table et la déposer si elle existe déjà en utilisant le code de Jeffrey (bravo à lui, pas à moi!).
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;
Avec SQL * PLUS, vous pouvez également utiliser la commande WHENEVER SQLERROR:
WHENEVER SQLERROR CONTINUE NONE
DROP TABLE TABLE_NAME;
WHENEVER SQLERROR EXIT SQL.SQLCODE
DROP TABLE TABLE_NAME;
Avec CONTINUE NONE
une erreur est signalée, mais le script va continuer. Avec EXIT SQL.SQLCODE
, le script sera terminé en cas d'erreur.
Voir Aussi: chaque fois que Sqlerror Docs
Il N'y a pas de 'DROP TABLE IF EXISTS' dans oracle, vous devrez faire l'instruction select.
Essayez ceci (Je ne suis pas sur la syntaxe oracle, donc si mes variables sont ify, pardonnez-moi):
declare @count int
select @count=count(*) from all_tables where table_name='Table_name';
if @count>0
BEGIN
DROP TABLE tableName;
END
Une autre méthode consiste à définir une exception et à attraper uniquement cette exception en laissant toutes les autres se propager.
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;
Une façon est d'utiliser DBMS_ASSERT.SQL_OBJECT_NAME :
Cette fonction vérifie que la chaîne de paramètre d'entrée est un identifiant SQL qualifié d'un objet SQL existant.
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;
/
Malheureusement non, il n'y a pas de drop if exists, ou CREATE IF NOT EXIST
Vous pouvez écrire un script plsql pour y inclure la logique.
Http://download.oracle.com/docs/cd/B12037_01/server.101/b10759/statements_9003.htm
Je ne suis pas très dans la syntaxe Oracle, mais je pense que le script de @ Erich serait quelque chose comme ça.
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;
Vous pouvez toujours attraper l'erreur vous-même.
begin
execute immediate 'drop table mytable';
exception when others then null;
end;
Il est considéré comme une mauvaise pratique de surutiliser ceci, similaire à empty catch () ' es dans d'autres langues.
Cordialement
K
Je préfère spécifier la table et le propriétaire du schéma.
Attention à la sensibilité à la casse ainsi. (voir la clause "supérieure" ci-dessous).
J'ai jeté quelques objets différents pour montrer que cela peut être utilisé dans des endroits en plus des TABLEs.
.............
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;
Et un exemple de tableau:
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;
/
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;
/ / en faisant ce code, vérifie si la table existe et plus tard, il crée la table max. cela fonctionne simplement en compilation unique
Et si vous voulez le rendre ré-saisissable et minimiser les cycles drop / create, vous pouvez mettre en cache le DDL en utilisant dbms_metadata.get_ddl et recréer tout en utilisant une construction comme celle-ci:
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;
Ceci est juste un exemple, il devrait y avoir une boucle à l'intérieur avec le type DDL, le nom et le propriétaire étant des variables.
Un bloc comme celui-ci pourrait vous être utile.
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;
Je préfère suivre la solution économique
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;