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

답글 남기기

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

WordPress.com 로고

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

Twitter 사진

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

Facebook 사진

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

Google+ photo

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

%s에 연결하는 중

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