判断表是否删除（MySQL、ORACLE）

不同数据库中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中先判断表是否存在再新建表一例：

 

[c-sharp] view plain copy

print ?

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. /  

declare v_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 then execute 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 then execute 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生成。

            </div>

转载地址:https://blog.csdn.net/nsj820/article/details/6308420