Oracle中判断表是否存在再删除表避免报错与MySql和SqlServer的不同

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

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生成。Oracle及SqlServer的多表关联更新MySQL 导入数据时max_allowed_packet超出的问题相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）