오라클 DDL: If Exists 객체 삭제

MySQL 등에서는 IF EXISTS 구절로 기존 삭제를 한 후 객체를 생성한다.

• MySQL 쿼리문

  DROP TABLE IF EXISTS `table_name`;
  

오라클 SQL에서 기존 객체가 있는 경우 IF EXISTS를 사용하지 못하고
USER_TABLES, DBA_TABLES 등의 메타 테이블을 조회하여
PL/SQL 구문으로 처리를 해야 한다.

이런 경우 테이블 존재를 검사하는 오버헤드가 발생하기 때문에
이보다는 일단 없는 객체를 삭제하더라도 오류 메시지를 처리하는 방법을 추천한다.
* 참고문서
Oracle: If Table Exists

• 추천하는 Oracle 쿼리문

  /* drop if exists: Table */
  BEGIN
     EXECUTE IMMEDIATE 'DROP TABLE mytable';
  EXCEPTION
     WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
           RAISE;
        END IF;
  END;
  
  /* drop if exists: Sequence */
  BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE ' || sequence_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -2289 THEN
        RAISE;
      END IF;
  END;
  View
  
  /* drop if exists: Function */
  BEGIN
    EXECUTE IMMEDIATE 'DROP VIEW ' || view_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Trigger */
  BEGIN
    EXECUTE IMMEDIATE 'DROP TRIGGER ' || trigger_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -4080 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Index */
  BEGIN
    EXECUTE IMMEDATE 'DROP INDEX ' || index_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -1418 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Column */
  BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
                  || ' DROP COLUMN ' || column_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -904 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Database Link */
  BEGIN
    EXECUTE IMMEDIATE 'DROP DATABASE LINK ' || dblink_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -2024 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Materialized View */
  BEGIN
    EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || mview_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -12003 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Type */
  BEGIN
    EXECUTE IMMEDIATE 'DROP TYPE ' || type_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -4043 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Constraint */
  BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE ' || table_name
              || ' DROP CONSTRAINT ' || constraint_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -2443 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Scheduler Job */
  BEGIN
    DBMS_SCHEDULER.drop_job(job_name);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -27475 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: User / Schema */
  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;
  
  /* drop if exists: Package */
  BEGIN
    EXECUTE IMMEDIATE 'DROP PACKAGE ' || package_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -4043 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Procedure */
  BEGIN
    EXECUTE IMMEDIATE 'DROP PROCEDURE ' || procedure_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -4043 THEN
        RAISE;
      END IF;
  END;
  
  /* drop if exists: Function */
  BEGIN
    EXECUTE IMMEDIATE 'DROP FUNCTION ' || function_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -4043 THEN
        RAISE;
      END IF;
  END;
  

즐 SQL~