ORACLE的DDL语句模板

--表tDstPsDifBody 增加OriCyCount字段
declare
  vi_TabNum  integer;
  vi_ColNum  integer;
Begin 
  select count(1) into vi_TabNum 
    from user_tables 
   where table_name = upper('tDstPsDifBody');
   
  select count(1) into vi_ColNum 
    from user_tab_columns
   where table_name = upper('tDstPsDifBody')
     and column_name = upper('OriCyCount');
     
  if vi_TabNum>0 and vi_ColNum=0 then 
    execute immediate(' alter table tDstPsDifBody add OriCyCount number(19,4) default 0 not null ');
    execute immediate(' comment on column tDstPsDifBody.OriCyCount is ''记录原始差异数量/必填'' ');
  end if;
End;
/


/*===============================================*/
/*               创建和修改表                    */
/*===============================================*/
/*=================创建正式表=================*/
--注意主键命名。注意字符串内单引号需要使用两个来表示。
declare
  vi_Num  integer;
Begin 
  --判断表是否存在
  select count(1) into vi_Num 
    from user_tables 
   where table_name = upper('tStkMyDemo');
  if vi_Num=0 then --注意是等于零
    --创建表
    execute immediate('
      create table tStkMyDemo(
         OrgCode  varchar2(10) default ''*'' not null ,
         DepId    number(19)   default 0  not null ,
         JhDate   date         null ,
         JhCount  number(19,4) default 0  not null ,
         HJPrice  number(19,4) default 0  not null ,
         HCost    number(19,2) default 0  not null ,
      constraint PK_tStkMyDemo 
         primary key (OrgCode,DepId) )
                      ');
    --创建表注释来说明表的用途
    execute immediate(' comment on table tStkMyDemo is ''用演示如何创建正式表''  ');
  end if;
End;
/
/*=================创建事务级临时表=================*/
--可以先删除再创建，重要字段需要标明用途
declare
  vi_Num  integer;
Begin 
  select count(1) into vi_Num 
    from user_tables 
   where table_name = upper('tSysMyDemo_TempDL');
  if vi_Num>0 then --注意是大于零
    execute immediate('Drop table tSysMyDemo_TempDL');
  end if;
End;
/
Begin
  execute immediate('
    create global temporary table tSysMyDemo_TempDL
       (Table_Name   varchar2(20)  not null ,
        Table_What   varchar2(100) not null ,
        Table_Type   varchar2(4)   default(''0'') not null ,
        GenType      varchar2(4)   default(''1'') not null ,
        AnalyzeDate  date ,
        AnalyzeTimes number(5)     default(0)   not null ,
    constraint PK_tSysMyDemo_TempDL primary key(Table_Name))
    on commit delete rows ');
end;
/
Begin
  execute immediate(' comment on table tSysMyDemo_TempDL is 
    ''数据库表管理列表(其中的预置数据是要自动进行统计分析的数据库表信息)'' ');
    
  execute immediate(' comment on column tSysMyDemo_TempDL.Table_Name is 
     ''表名称(表的名称，全大写/主键/必填/)'' ');
     
  execute immediate(' comment on column tSysMyDemo_TempDL.Table_What is 
     ''表描述(必填)'' ');


  execute immediate(' comment on column tSysMyDemo_TempDL.Table_Type is 
     ''分析频度(确定在何时以及如何进行分析/枚举值：“0-周；1-月；2-年；3-双月；”/默认“0”/必填)'' ');


  execute immediate(' comment on column tSysMyDemo_TempDL.GenType is 
     ''生成类型(描述数据是被谁插入的/枚举值：“0-系统；1-人工；”/默认“1”/必填/凡是为“0”的数据不允许编辑)'' ');


  execute immediate(' comment on column tSysMyDemo_TempDL.AnalyzeDate is 
     ''分析日期(最后一次分析的日期/初始值为空)'' ');


  execute immediate(' comment on column tSysMyDemo_TempDL.AnalyzeTimes is 
     ''分析次数(总共进行了多少次统计分析，即分析次数/必填/默认为0)'' ');
end;
/
/*=================创建会话级临时表=================*/
--会话级临时表与事务级临时表模板类似，区别是将“on commit delete rows”改为“on commit preserve rows”
/*=================编辑表的字段=================*/
--增加表的字段
declare
  vi_TabNum  integer;
  vi_ColNum  integer;
Begin 
  select count(1) into vi_TabNum 
    from user_tables 
   where table_name = upper('tSysMyDemo_TempDL');
   
  select count(1) into vi_ColNum 
    from user_tab_columns
   where table_name = upper('tSysMyDemo_TempDL')
     and column_name = upper('CreatDate');
     
  if vi_TabNum>0 and vi_ColNum=0 then 
    execute immediate(' alter table tSysMyDemo_TempDL add CreatDate varchar2(10) not null ');
    execute immediate(' comment on column tSysMyDemo_TempDL.CreatDate is ''记录表的创建日期YYYY-MM-DD/必填'' ');
  end if;
End;
/
--修改表的字段:注意是否为空的修改要试验是否会被重复执行，是否在存在非法数据时会被重复执行；必要时需编程实现。
declare
  vi_TabNum  integer;
  vi_ColNum  integer;
Begin 
  select count(1) into vi_TabNum 
    from user_tables 
   where table_name = upper('tSysMyDemo_TempDL');
   
  select count(1) into vi_ColNum 
    from user_tab_columns
   where table_name = upper('tSysMyDemo_TempDL')
     and column_name = upper('CreatDate');
     
  if vi_TabNum>0 and vi_ColNum>0 then 
    execute immediate(' alter table tSysMyDemo_TempDL Modify CreatDate varchar2(20) ');
  end if;
End;
/
--删除表的字段:注意字段被删除后，包含此字段的索引(或主键)会被自动删除
declare
  vi_TabNum  integer;
  vi_ColNum  integer;
Begin 
  select count(1) into vi_TabNum 
    from user_tables 
   where table_name = upper('tSysMyDemo_TempDL');
   
  select count(1) into vi_ColNum 
    from user_tab_columns
   where table_name = upper('tSysMyDemo_TempDL')
     and column_name = upper('CreatDate');
     
  if vi_TabNum>0 and vi_ColNum>0 then 
    execute immediate(' alter table tSysMyDemo_TempDL drop Column CreatDate ');
  end if;
End;
/ 
/*===============================================*/
/*               创建和修改索引                  */
/*===============================================*/
/*=================增加主键=================*/
--注意：++++++++++增加主键和唯一性索引一定要慎重，需要经过讨论方可编写更新脚本。++++++++++
--      ++++++++++因为它涉及到原有数据是否唯一的问题。在此，无法写出具体模板。  ++++++++++
declare
  vi_TabNum  integer;
  vi_IndNum  integer;
Begin 
  select count(1) into vi_TabNum 
    from user_tables 
   where table_name = upper('tStkMyDemo');
   
  select count(1) into vi_IndNum 
    from user_indexes
   where table_name = upper('tStkMyDemo')
     and index_name = upper('PK_tStkMyDemo');
     
  if vi_TabNum>0 and vi_IndNum=0 then 
    execute immediate(' 
      alter table tStkMyDemo
        add constraint PK_tStkMyDemo primary key (OrgCode,DepId) ');
    execute immediate(' analyze table tStkMyDemo compute statistics ');
  end if;
exception when others then null;
End;
/ 
/*=================重建主键=================*/
--注意：尽量不要删除主键，要修改主键往往会造成索引失效。
--      删除主键的语法：alter table tStkMyDemo drop constraint PK_tStkMyDemo;
--      删除主键前请将所有的索引删除，然后建立主键，然后建立被删除的索引。
--      假设已经有一个已知索引IDX_tStkMyDemo(OrgCode,DepId,JhDate) 
--      create index IDX_tStkMyDemo on tStkMyDemo(orgCode,DepId,Jhdate);
--脚本如下：
declare
  vs_table_name   varchar2(100);
  vs_index_name   varchar2(100);
  vs_index_list   varchar2(500);
  vs_table_spac   varchar2(100);
  vs_colList      varchar2(100);
  vs_SQL          varchar2(2000);
  cursor Cur_GetIndexs is
    select tablespace_name,index_name
      from user_indexes
     where table_name = vs_table_name;
     
  cursor cur_GetIndex_Col(ms_ind  user_indexes.index_name%type) is
    select column_name
      from user_ind_columns
     where index_name=ms_ind
     order by column_position Asc; 
  vc_Data  sys_refcursor;
begin
  vs_table_name := upper('tStkMyDemo');      --输入表名
  vs_index_name := upper('PK_tStkMyDemo');   --输入主键
  vs_index_list := '(OrgCode,DepId)';        --输入新的主键列表
  
  vs_index_list := upper(vs_index_list);
  open vc_Data for 
    select index_name 
      from user_indexes
     where table_name = vs_table_name
       and index_name = vs_index_name;
  fetch vc_Data into vs_index_name;
  if vc_Data%notfound then vs_index_name:='***'; end if;
  close vc_Data;
  
  if vs_index_name<>'***' then 
    begin
      execute immediate('drop table MyTable_SaveIndex');
    exception when others then null;
    end;
    execute immediate('
      create table MyTable_SaveIndex
         (index_name   varchar2(100),
          index_list   varchar2(500),
          table_spac   varchar2(100)) ');
          
    for vr_D in Cur_GetIndexs
    Loop
      vs_colList:='(';
      
      for vr_L in cur_GetIndex_Col(vr_D.index_name)
      Loop
        vs_colList:=vs_colList||vr_L.column_name||',';
      end Loop;
      
      vs_colList:=substr(vs_colList,1,length(vs_colList)-1)||')';
      
      vs_SQL:=' insert into MyTable_SaveIndex(index_name,index_list,table_spac) '||
              ' values(:mm_index,:mm_list,:mm_space) ';
      
      execute immediate(vs_SQL) using vr_D.index_name,vs_colList,vr_D.tablespace_name;
      if vr_D.index_name<>vs_index_name then
        execute immediate(' drop index '||vr_D.index_name);
      end if;
    end Loop;
    vs_SQL:=' delete from MyTable_SaveIndex where index_list=:mm_List or index_name=:mm_index ';
    
    execute immediate(vs_SQL) using vs_index_list,vs_index_name;    
    
    execute immediate(' alter table '||vs_table_name||' drop constraint '||vs_index_name );
    
    execute immediate('alter table '||vs_table_name||' add constraint '||vs_index_name||
                      ' primary key '||vs_index_list);
    open vc_Data for 'select index_name,index_list,table_spac from MyTable_SaveIndex';
    Loop
      fetch vc_Data into vs_index_name,vs_colList,vs_table_spac;
       exit when vc_Data%notfound;
      vs_SQL:=' create index '||vs_index_name||' on '||vs_table_name||vs_colList||' tablespace '||vs_table_spac; 
      execute immediate(vs_SQL);
    end Loop;
    close vc_Data;
  end if;
  
  begin
      execute immediate('drop table MyTable_SaveIndex');
  exception when others then null;
  end;
  
  begin
      execute immediate(' analyze table '||vs_table_name||' compute statistics ');
  exception when others then null;
  end;  
end;
/
/*=================增加索引=================*/
--当索引名已被使用或索引的字段列表与建立此索引的表上的其它索引重复时Oracle会报错
--注意“商定天下”约定，增加月表和年表（YYYYMM/YYYY）时要往数据字典中预置数据以便月初或年初由系统自动建立索引。
Begin
  execute immediate(' create index IDX_tStkMyDemo on tStkMyDemo(DepId,JhDate) ');
  execute immediate(' analyze table tStkMyDemo compute statistics ');
exception when others then null;
End;
/
/*=================删除索引=================*/
--注意“商定天下”约定，删除月表和年表（YYYYMM/YYYY）的索引时，要将数据字典中预置数据一并删除。
Begin
  execute immediate(' drop index IDX_tStkMyDemo');
exception when others then null;
End;
/
/*===============================================*/
/*               创建序列/触发器                 */
/*===============================================*/
--为表“数据任务日志表”的自增长字段“SerialNo”创建序列：QID_tSysJobLog_SerialNo
declare
  vi_Num  integer;
  vs_QID  varchar2(30);
Begin 
  vs_QID:=upper('QID_tSysJobLog_SerialNo');
  select count(1) into vi_Num 
    from user_sequences
   where sequence_name = vs_QID;
  if vi_Num=0 then 
    execute immediate(' create sequence QID_tSysJobLog_SerialNo increment by 1 start with 1 nomaxvalue nocycle ');
  end if;
End;
/
--为表“数据任务日志表”的自增长字段“SerialNo”创建触发器
create or replace trigger Trg_tSysJobLog_Idt_BI 
   before insert on tSysJobLog for each row
begin
  --实现表“数据任务日志表”字段“SerialNo”的自增长
  select QID_tSysJobLog_SerialNo.NEXTVAL INTO :new.SerialNo from dual;
exception
  when Others then 
    raise_application_error(-20004,'触发器(Trg_tSysJobLog_Idt_BI)发生异常！');
end;
/
--创建“数据库表管理列表”的触发器，限制数据是否允许更改
create or replace trigger Trg_tSysTblMagItem_BDU
  before delete or update
  on tSysTblMagItem for each row
declare
  vs_TableName  varchar2(100);
begin
  --限制“数据库表管理列表”的数据是否允许更改
  vs_TableName:='表（'||:old.Table_Name||'）属于系统预置数据，不允许';
  if updating then
    if (:old.GenType='0') then
      raise_application_error(-20005,vs_TableName||'修改！'); 
    end if;
  end if;
  if deleting then
    if (:old.GenType='0') then
      raise_application_error(-20006,vs_TableName||'删除！'); 
    end if;
  end if;
end;
/
--创建“数据任务日志表”的触发器，限制数据是否允许删除
create or replace trigger Trg_tSysJobLog_BD
  before delete
  on tSysJobLog for each row
begin
  --限制“数据任务日志表”的数据是否允许删除
  if (:old.IsDel='0') then
    raise_application_error(-20007,'系统日志（IsDel='0'）不允许删除！'); 
  end if;
end;
/