可重复执行SQL

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


  --删除重复数据，用rowid来关联(重复数据中rowid不等于临时表的row_id的数据删除，这时只剩下一条)


  sSql := 'delete from 表名称 s '
        ||'  where Exists (select ''X'' from 表名称 b '
        ||'              where b.字段1 = s.字段1 '  --主键字段必须要
        ||'                and b.字段2 = s.字段2 '  --主键字段必须要
        ||'                and b.row_id <> s.rowid) ';          --rowid来判断删除
  execute immediate sSql ; 
 commit;
  --操作完成后删除临时表
  execute immediate 'drop table 表名称';
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 
<span style="white-space:pre">	</span>  where lower(table_name)=Lower('table_name') 
<span style="white-space:pre">	</span>    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('表名') 
    and lower(column_name)=lower('字段1') And Lower(NULLABLE) = Lower('N');  
  if tm_i > 0 then  
    execute immediate 'alter table 表名 Modify 字段1 varchar2(1) default ''0'' not null';
  else
    execute immediate 'alter table 表名 Add 字段1 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('表名1');  
  if tm_i=0 then  
    execute immediate 'create table 表名2 as select * from 表名1';  
  end if;
end;    


-- table  删除表
declare
  tm_i integer;      
begin
  select count(*) into tm_i from user_tables 
   where lower(table_name)=lower('表名1');  
  if tm_i>0 then  
    execute immediate 'drop table 表名1';    
  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('表名1');  
  if tm_i>0 then  
    execute immediate 'drop table 表名1';    
  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'
<span style="white-space:pre">	</span>    ||' 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 
<span style="white-space:pre">		</span>  from user_constraints a 
<span style="white-space:pre">		</span>  inner join user_constraints b on a.r_constraint_name=b.constraint_name
        where lower(b.table_name) = Lower('表名1') 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 
<span style="white-space:pre">		</span>  from user_constraints a 
<span style="white-space:pre">		</span>  inner join user_constraints b on a.r_constraint_name=b.constraint_name
        where lower(b.table_name) = Lower('表名1') 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 
<span style="white-space:pre">	</span> where Lower(table_name)=Lower('table_name') 
<span style="white-space:pre">	</span>   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 
<span style="white-space:pre">	</span> where table_name='table_name' 
<span style="white-space:pre">	</span>   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;


/