오라클 DDL: If Exists 객체 삭제

토니아빠 켬 2016년 4월 7일

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~

게시자: 토니아빠

웰시코기 토니와 함께 제주도에 살고 있는 프리랜서 개발자입니다. 웹앱 개발과 데이터분석을 합니다. 제주도 여행/관광 관련 큐레이션 서비스를 준비중입니다.

답글 남기기

아래 항목을 채우거나 오른쪽 아이콘 중 하나를 클릭하여 로그 인 하세요:

Gravatar
WordPress.com 로고

WordPress.com의 계정을 사용하여 댓글을 남깁니다. ( 로그아웃 /  변경 )

Google photo

Google의 계정을 사용하여 댓글을 남깁니다. ( 로그아웃 /  변경 )

Twitter 사진

Twitter의 계정을 사용하여 댓글을 남깁니다. ( 로그아웃 /  변경 )

Facebook 사진

Facebook의 계정을 사용하여 댓글을 남깁니다. ( 로그아웃 /  변경 )

취소

%s에 연결하는 중

%d 블로거가 이것을 좋아합니다: