오라클 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~

카테고리: 개발일반
태그: drop, If Exists, Oracle, SQLCODE

게시자: 토니아빠

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

답글 남기기 응답 취소

여기에 댓글을 입력하세요...

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

이메일 (필수) (주소는 공개되지 않습니다)

이름 (필수)

웹사이트

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

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

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

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

취소

%s에 연결하는 중

	토니아빠 (HiveServer2 설치와 ES 연결)
	토니아빠 (HiveServer2 설치와 ES 연결)
	김헤진 (Mac OS – KoNLPy 설치하…)
	이상윤 (How to install zombodb on Cent…)
	토니아빠 (Mac OS – KoNLPy 설치하…)

이것을 평가하기:

이것이 좋아요:

이 글 공유하기:

게시자: 토니아빠

답글 남기기 응답 취소