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~