Oracle中判斷表是否存在再刪除表避免報錯與MySql和SqlServer的不同

發布時間: 2012/8/21 16:45:11

不同數據庫中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:

BEGIN

EXECUTE IMMEDIATE 'DROP TABLE [table_name]';

EXCEPTION WHEN OTHERS THEN NULL;

END;

SQL Server:

IF EXISTS (

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = '[table_name]')

DROP TABLE [table_name]

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

1.declare
2. v_exists number;
3.begin
4.       --1、任務類型 TASK_TYPE_CD 建表...
5.    select count(*) into v_exists from user_tables where table_name = 'EDW_T99_TASK_TYPE_CD';
6.    if v_exists > 0 then
7.    execute immediate 'drop table EDW_T99_TASK_TYPE_CD';   8.    end if;   9.    execute immediate '
10.    create table EDW_T99_TASK_TYPE_CD
11.    (
12.      CODE_CD   VARCHAR2(20) PRIMARY KEY,
13.      CODE_DESC VARCHAR2(100)
14.    )';
15.    execute immediate 'comment on table EDW_T99_TASK_TYPE_CD is ''任務類型''';   16.    execute immediate 'comment on column EDW_T99_TASK_TYPE_CD.CODE_CD is ''代碼''';   17.    execute immediate 'comment on column EDW_T99_TASK_TYPE_CD.CODE_DESC is ''代碼描述''';   18.
19.       --2、買入產品代碼 BUY_TYPE_CD 建表...
20.    select count(*) into v_exists from user_tables where table_name = 'EDW_T99_BUY_TYPE_CD';   21.    if v_exists > 0 then   22.    execute immediate 'drop table EDW_T99_BUY_TYPE_CD';   23.    end if;   24.    execute immediate '
25.    create table EDW_T99_BUY_TYPE_CD
26.    (
27.      CODE_CD   VARCHAR2(20) PRIMARY KEY,
28.      CODE_DESC VARCHAR2(100)
29.    )';
30.    execute immediate 'comment on table EDW_T99_BUY_TYPE_CD is ''買入產品代碼''';   31.    execute immediate 'comment on column EDW_T99_BUY_TYPE_CD.CODE_CD is ''代碼''';   32.    execute immediate 'comment on column EDW_T99_BUY_TYPE_CD.CODE_DESC is ''代碼描述''';   33.
34.end;
35./
此例用在數據倉庫項目的建T99代碼表腳本方案上；此腳本在方案中用Perl根據Excel生成。