不同數據庫中drop a table if it exists的不同：

In MySQL it is pretty easy to drop a table if it exists already. In Oracle and Microsoft’s SQL Server it is a little more complicated. Today I want to present you the solutions for these two DBMS’.

MySQL:

DROP TABLE IF EXISTS [table_name]

Oracle:

BEGINEXECUTE IMMEDIATE 'DROP TABLE [table_name]';EXCEPTION WHEN OTHERS THEN NULL;
END;

SQL Server:

IF EXISTS (SELECT ?TABLE_NAME FROM INFORMATION_SCHEMA.TABLESWHERE ? TABLE_NAME = '[table_name]')
DROP TABLE ?[table_name]

ORACLE中先判斷表是否存在再新建表一例：

declarev_exists number;
begin--1、任務類型 TASK_TYPE_CD 建表...select count(*) into v_exists from user_tables where table_name = 'EDW_T99_TASK_TYPE_CD';if v_exists > 0 thenexecute immediate 'drop table EDW_T99_TASK_TYPE_CD';end if;execute immediate 'create table EDW_T99_TASK_TYPE_CD(CODE_CD   VARCHAR2(20) PRIMARY KEY,CODE_DESC VARCHAR2(100))';execute immediate 'comment on table EDW_T99_TASK_TYPE_CD is ''任務類型''';execute immediate 'comment on column EDW_T99_TASK_TYPE_CD.CODE_CD is ''代碼''';execute immediate 'comment on column EDW_T99_TASK_TYPE_CD.CODE_DESC is ''代碼描述''';--2、買入產品代碼 BUY_TYPE_CD 建表...select count(*) into v_exists from user_tables where table_name = 'EDW_T99_BUY_TYPE_CD';if v_exists > 0 thenexecute immediate 'drop table EDW_T99_BUY_TYPE_CD';end if;execute immediate 'create table EDW_T99_BUY_TYPE_CD(CODE_CD   VARCHAR2(20) PRIMARY KEY,CODE_DESC VARCHAR2(100))';execute immediate 'comment on table EDW_T99_BUY_TYPE_CD is ''買入產品代碼''';execute immediate 'comment on column EDW_T99_BUY_TYPE_CD.CODE_CD is ''代碼''';execute immediate 'comment on column EDW_T99_BUY_TYPE_CD.CODE_DESC is ''代碼描述''';end;
/

此例用在數據倉庫項目的建T99代碼表腳本方案上；此腳本在方案中用Perl根據Excel生成。