不同數(shù)據(jù)庫(kù)中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、任務(wù)類(lèi)型 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 ''任務(wù)類(lèi)型''';
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、買(mǎi)入產(chǎn)品代碼 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 ''買(mǎi)入產(chǎn)品代碼''';
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;
/
此例用在數(shù)據(jù)倉(cāng)庫(kù)項(xiàng)目的建T99代碼表腳本方案上;此腳本在方案中用Perl根據(jù)Excel生成。
本文出自:億恩科技【mszdt.com】
服務(wù)器租用/服務(wù)器托管中國(guó)五強(qiáng)!虛擬主機(jī)域名注冊(cè)頂級(jí)提供商!15年品質(zhì)保障!--億恩科技[ENKJ.COM]
|