389 votes

Oracle : si la table existe

Je suis en train d'écrire des scripts de migration pour une base de données Oracle, et j'espérais qu'Oracle avait quelque chose de similaire à MySQL. IF EXISTS construire.

Plus précisément, lorsque je veux supprimer une table dans MySQL, je fais quelque chose comme suit

DROP TABLE IF EXISTS `table_name`;

De cette façon, si la table n'existe pas, la fonction DROP ne produit pas d'erreur, et le script peut continuer.

Oracle dispose-t-il d'un mécanisme similaire ? Je réalise 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 une DROP m'échappe.

660voto

Jeffrey Kemp Points 26050

La meilleure façon, et la plus efficace, est d'attraper l'exception "table non trouvée" : cela évite de vérifier deux fois si la table existe ; et ne souffre pas du problème que si le DROP échoue pour une autre raison (qui pourrait être importante), l'exception est toujours levée pour l'appelant :

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

ADDENDUM À titre de référence, voici les blocs équivalents pour les autres types d'objets :

Séquence

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

Voir

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 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Lien vers la 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;

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 AND SQLCODE != -942 THEN
      RAISE;
    END IF;
END;

Emploi de 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;

Synonyme

BEGIN
  EXECUTE IMMEDIATE 'DROP SYNONYM ' || synonym_name;
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -1434 THEN
      RAISE;
    END IF;
END;

14 votes

Et pour supprimer un USER, le SQLCODE à ignorer est -1918.

15 votes

Il faut écrire une procédure pour faire cela ? N'y a-t-il pas un meilleur moyen de le faire ?

3 votes

@WilsonFreitas - Non, vous n'avez pas besoin d'écrire une procédure. Vous pouvez utiliser un bloc anonyme, comme dans l'exemple ici.

156voto

Marius Burz Points 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;

C'est pour vérifier si une table dans le schéma actuel existe. Pour vérifier si une table donnée existe déjà dans un schéma différent, vous devez utiliser la méthode suivante all_tables au lieu de user_tables et ajoutez la condition all_tables.owner = upper('schema_name')

42 votes

+1 C'est mieux car il ne faut pas compter sur le décodage des exceptions pour comprendre ce qu'il faut faire. Le code sera plus facile à gérer et à comprendre.

4 votes

Je suis d'accord avec @daitangio - les performances ne l'emportent généralement pas sur la maintenabilité avec les scripts de déploiement "run-once".

1 votes

Je serais intéressé de comprendre si le commit implicite joue un rôle ici. Vous voudriez que le SELECT et le DROP soient dans la même transaction. [ En ignorant évidemment tout DDL ultérieur qui pourrait être exécuté. ]

29voto

Robert Vabo Points 158

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 vous aidera

0 votes

Après avoir créé la proc. delobject ci-dessus, j'ai essayé de l'appeler en émettant le SQL suivant. Mais cela n'a pas fonctionné. delobject('MyTable', 'TABLE') ; Je reçois l'erreur suivante -------------------------------- Erreur commençant à la ligne 1 dans la commande : delobject('MyTable', 'TABLE') Rapport d'erreur : Commande inconnue

1 votes

Utiliser la commande EXECUTE - EXECUTE DelObject ('MyTable', 'TABLE') ;

14voto

mishkin Points 1680

Je voulais juste poster un code complet qui va créer une table et la supprimer 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;

2 votes

Personnellement, je placerais la création d'une table dans une étape distincte, car elle n'a pas besoin d'être effectuée de manière dynamique et ne nécessite pas de gestionnaire d'exception.

1voto

Erich Points 2966

Il n'y a pas de "DROP TABLE IF EXISTS" dans oracle, vous devez utiliser l'instruction select.

essayez ceci (je ne connais pas la syntaxe d'oracle, donc si mes variables sont fausses, veuillez me pardonner) :

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

0 votes

J'ai fait une tentative de traduction du script en syntaxe oracle.

3 votes

Declare count nombre ; begin select count(*) into count from all_tables where table_name = 'x' ; if count > 0 then execute immediate 'drop table x' ; end if ; end ; Vous ne pouvez pas exécuter DDL directement à partir d'un bloc de transaction, vous devez utiliser execute.

0 votes

Merci beaucoup ! Je n'avais pas réalisé que la syntaxe était si différente. Je savais que vous deviez envelopper le tout dans un begin/end, mais je pensais qu'il était exécuté au milieu d'un autre script. Tom : J'ai décidé de laisser ma version et de ne pas copier la vôtre, donc je ne prends pas de voix de vous, qui a évidemment la bonne ansswer.

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X