数据库结构操作脚本

 

/*        
  数据库操作脚本汇集

*/

--删除重复数据
-------------删除重复数据--------------------------
declare
  pvar_i_cunt          integer;
  sStockcode           varchar2(20);
  sExchange            varchar2(10);
  sSql                 varchar2(1000);
begin
 /*
   删除重复数据：
     用一个临时表保存重复数据,但只记录重复数据中的一条(rowid最大的一条)，然后将这表来关联实际表，
    把重复数据中rowid不是最大其它数据删除,这样就剩下一条了。
  */
  --创建一个临时表来保存重复数据(这个表里面：只记录其中一条--rowid最大的一条)
  select count(1) into pvar_i_cunt from user_tables s where s.TABLE_NAME='TMP_STOCKCOMPS';
  if pvar_i_cunt>0 then
    execute immediate 'drop table TMP_STOCKCOMPS';
  end if ;
  execute immediate ' create table TMP_STOCKCOMPS
                      (
                        row_id        ROWID,
                 -----------下面字段是主键字段----------------
                        vc_stock_code VARCHAR2(20) not null,
                        vc_exchange   VARCHAR2(20) default '' '' not null
                      )' ;
 
 
  for cur in (select a.cn, a.vc_stock_code, vc_exchange
                from (select count(*) cn, s.vc_stock_code, s.vc_exchange
                        from STOCKCOMPS s
                       group by s.vc_stock_code, s.vc_exchange) a
               where a.cn > 1) loop
    sStockcode := cur.vc_stock_code;
    sExchange  := cur.vc_exchange;
   
    --取出重复数据的其中一条 (max(rowid)那条）
    sSql :=' insert into TMP_STOCKCOMPS '
         ||'      (row_id, vc_stock_code, vc_exchange) '
         ||'  select max(a.rowid), a.vc_stock_code, a.vc_exchange '
         ||'    from STOCKCOMPS a '
         ||'  where a.vc_stock_code = '''|| sStockcode||''''
         ||'    and a.vc_exchange = '''||sExchange||''''
         ||'   group by  a.vc_stock_code, a.vc_exchange ';
    
    execute immediate sSql ;
  end loop;

  --删除重复数据，用rowid来关联(重复数据中rowid不等于临时表的row_id的数据删除，这时只剩下一条)
  sSql := 'delete from STOCKCOMPS s '
        ||'  where Exists (select ''X'' from TMP_STOCKCOMPS b '
        ||'              where b.vc_stock_code=s.vc_stock_code '--主键字段必须要
        ||'                and b.vc_exchange = s.vc_exchange '  --主键字段必须要
        ||'                and b.row_id <> s.rowid) ';          --rowid来判断删除
       
  execute immediate sSql ;
 
  commit;
 
  --操作完成后删除临时表
  execute immediate 'drop table TMP_STOCKCOMPS';
end;
/

-- user_constraints   主键约束
declare
  tm_i integer;
  pvar_PK_constraint   varchar2(100);
begin
  --查出主键，并删除，重新建立
  select count(*) into tm_i from user_constraints
    where table_name='表名' and constraint_type='P' ;
 
  if tm_i>0 then
      select constraint_name into pvar_PK_constraint from user_constraints
        where table_name='表名' and constraint_type='P' ;
      --删除主键
       execute immediate 'alter table 表名 drop constraint '|| pvar_PK_constraint ||' cascade';
  end if ;
  --再检查是否有重名的索引
  select count(*) into tm_i from user_indexes
    where index_name='主键名称';
  if tm_i>0 then
    execute immediate 'DROP INDEX 主键名称';
  end if;
 
  ---创建主键
  execute immediate 'alter table 表名 add constraint 主键名称 primary key (字段1,字段2,....)';  
end;

-- user_tab_cols 表增加字段
declare
  tm_i integer;     
Begin
 
  --如果字段关联了主键则先删除主键
    select count(*) into tm_i from user_constraints where lower(table_name)=Lower('table_name') and lower(constraint_name)=lower('PK_Constraints_name'); 
  if tm_i>0 then
    execute immediate 'alter table table_name  drop constraint PK_Constraints_name cascade';
  end if;
 --删除索引
  select count(*) into tm_i from user_indexes where lower(table_name)=Lower('table_name') and lower(index_name)=lower('PK_Constraints_name'); 
  if tm_i>0 then
    execute immediate 'DROP INDEX  PK_Constraints_name';   
  end if;             
 --删除主键END----------------------------------------------
 
  select count(*) into tm_i from user_tab_cols where lower(table_name)=lower('table_name') and lower(column_name)=lower('Column_name'); 
  if tm_i=0 then 
    execute immediate 'alter table table_name add Column_name varchar2(1) default ''0'' not null';   
  end if;  
 
  --创建主键          
  execute immediate 'alter table table_name add constraint PK_Constraints_name primary key (Column_name1, Column_name2)';
end;

--modify user_tab_cols      表修改字段
declare
  tm_i integer;     
begin
  select count(*) into tm_i from user_tab_cols where lower(table_name)=lower('INSTRUCTIONS') and lower(column_name)=lower('VC_ONLINE_FLAG') And Lower(NULLABLE) = Lower('N'); 
  if tm_i > 0 then 
    execute immediate 'alter table INSTRUCTIONS Modify VC_ONLINE_FLAG varchar2(1) default ''0'' not null';
  else
    execute immediate 'alter table INSTRUCTIONS Add VC_ONLINE_FLAG varchar2(1) default ''0'' not null';
  end if;
end;

-- table   增加表
declare
  tm_i integer;     
begin
  select count(*) into tm_i from user_tables where lower(table_name)=lower('qs_filedictionary'); 
  if tm_i=0 then 
    execute immediate 'create table tmp___tmp as select * from qs_filedictionary'; 
  end if;
end;

   
-- table   删除表
declare
  tm_i integer;     
begin
  select count(*) into tm_i from user_tables where lower(table_name)=lower('qs_filedictionary'); 
  if tm_i>0 then 
    execute immediate 'drop table qs_filedictionary';   
  end if;       
  Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('table_name') And Lower(t.OBJECT_TYPE) = Lower('Table');
  If tm_i > 0 Then
     Execute Immediate 'Drop table table_name';
  End If;
end;

declare
  tm_i integer;     
begin
  select count(*) into tm_i from user_tables where lower(table_name)=lower('userprogramlinks'); 
  if tm_i>0 then 
    execute immediate 'drop table userprogramlinks';   
  end if;

end;

--删除触发器
Declare
  tm_i Integer;
Begin
  Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Trigger_name') And Lower(t.OBJECT_TYPE) = Lower('Trigger');
  If tm_i > 0 Then
     Execute Immediate 'Drop Trigger Trigger_name';
  End If;
End;

--禁用触发器
Declare
  tm_i Integer;
Begin
 Select Count(*) Into tm_i From User_Triggers t Where Lower(t.trigger_name) = Lower('trigger_name') And Lower(t.status) = Lower('ENABLED');
 If tm_i > 0 Then
  Execute Immediate 'ALTER TRIGGER trigger_name DISABLE';
 End If;
End;

--删除视图，
Declare
  tm_i Integer;
Begin
  Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Viewer_name') And Lower(t.OBJECT_TYPE) = Lower('View');
  If tm_i > 0 Then
     Execute Immediate 'Drop view viewer_name';  
  End If;
End;   

--删除函数，
Declare
  tm_i Integer;
Begin
  Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('function_name') And Lower(t.OBJECT_TYPE) = Lower('Function');
  If tm_i > 0 Then
     Execute Immediate 'Drop Function function_name';  
  End If;
End;

 

-- sequence  创建Sequence
declare
  tm_i integer; 
begin
  select count(*) into tm_i from User_Objects t where Lower(t.OBJECT_NAME) = Lower('SEQ_PARAMLOGS') And Lower(t.OBJECT_TYPE) = Lower('Sequence'); 
  if tm_i=0 then 
    execute immediate 'create sequence SEQ_PARAMLOGS_ID minvalue 1 maxvalue 999999999999999999999999999 start with 1 increment by 1 cache 20';   
  end if; 
end; 

-- sequence  删除Sequence
declare
  tm_i integer; 
begin
  select count(*) into tm_i from User_Objects t where Lower(t.OBJECT_NAME) = Lower('SEQ_PARAMLOGS') And Lower(t.OBJECT_TYPE) = Lower('Sequence'); 
  if tm_i>0 then 
    execute immediate 'Drop sequence seq_paramlogs';   
  end if; 
end;

-- forientkey      创建外键
begin
    for x in (
        select a.constraint_name, a.table_name from user_constraints a inner join user_constraints b on a.r_constraint_name=b.constraint_name
        where lower(b.table_name) = Lower('stockinfos') and Lower(a.constraint_type)=Lower('R')
        ) loop
      execute immediate 'alter table '|| x.table_name || '  disable constraint '||x.constraint_name;
    end loop;

    for x in (
        select a.constraint_name, a.table_name from user_constraints a inner join user_constraints b on a.r_constraint_name=b.constraint_name
        where lower(b.table_name) = Lower('stockinfos') and Lower(a.constraint_type)=Lower('R')
        ) loop
      execute immediate 'alter table '|| x.table_name || '  enable constraint '||x.constraint_name;
    end loop;
end;

-- procedure        删除过程
declare
  tm_i integer; 
begin
  select count(*) into tm_i from User_Objects t Where lower(t.object_name) = lower('up_report_data_10259') And Lower(t.OBJECT_TYPE) = Lower('Procedure'); 
  if tm_i>0 then 
    execute immediate 'drop procedure up_report_data_10259';   
  end if; 
end;

--Index   删除索引
Declare
  tm_i Integer;
Begin
  Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Index_name') And Lower(t.OBJECT_TYPE) = Lower('Index');
  If tm_i > 0 Then
     Execute Immediate 'Drop Index index_name';
  End If;
End;

--    修改字段类型
declare
  tm_i integer;
Begin
  --如果字段关联了主键先删除相关主键
    select count(*) into tm_i from user_constraints where Lower(table_name)=Lower('table_name') and lower(constraint_name)=lower('PK_Constraints_name'); 
    if tm_i>0 then
      execute immediate 'alter table table_name  drop constraint PK_Constraints_name cascade';
    end if;        
  --判断索引
    select count(*) into tm_i from user_indexes where table_name='table_name' and lower(index_name)=lower('PK_Constraints_name'); 
    if tm_i>0 then
      execute immediate 'DROP INDEX  PK_Constraints_name';   
    end if;
  --判断字段  
  select count(*) into tm_i from user_tab_cols where table_name = upper('table_name') and column_name =upper('column_name_temp');
  if tm_i<=0  then
    execute immediate 'alter table  table_name  add  column_name_temp  varchar2(20) default '' '' not null';
  End If;
  --将旧列值更新到新列
  execute immediate 'update table_name set column_name_temp=column_name';
  Execute Immediate 'Update table_name Set column_name_temp = '' '' where column_name_temp = ''0'' ';
  --删除原列 
  execute immediate 'alter Table table_name drop column column_name';
 --重命名新列
  execute immediate 'alter table table_name rename column column_name_temp to column_name';
 
  --如果有主键重新创建主键          
  execute immediate 'alter table risk_table_index add constraint PK_risk_table_index primary key (TABLE_NAME, FIELD_NAMES)';

end;
/