oracle删除重复数据的存储过程

诗文1613

　　
　　1.oracle删除重复数据的存储过程
　　(1)CREATE OR REPLACE PROCEDURE DELETE_test
　　AS
　　BEGIN 
　　delete from test inck_tem where rowid not in ( 
　　select max(inck1.rowid) from test inck1 where  
　　inck1.id=inck_tem.id and inck1.name=inck_tem.name);
　　END DELETE_test; 
　　
　　(2)----------------------------------------------------
　　delete from employee where rowid not in ( 
　　select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以。
(3)---------------------------------------------------------


create table a_dist(id int,name varchar(20))


insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')
insert into a_dist values(1,'abc')


exec up_distinct 'a_dist','id'


select * from a_dist


create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
--f_key表示是分組字段﹐即主鍵字段
as
begin
 declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
    select @sql = 'declare cur_rows cursor  for select '+@f_key+' ,count(*) from ' +@t_name +'  group by ' +@f_key +'  having count(*) > 1'
    exec(@sql)
 open cur_rows 
 fetch cur_rows into @id,@max 
 while @@fetch_status=0 
 begin 
 select @max = @max -1 
 set rowcount @max 
    select @type = xtype from syscolumns where id=object_id(@t_name)  and name=@f_key
    if @type=56
    select @sql = 'delete from '+@t_name+' where ' + @f_key+'  = '+ @id 
    if @type=167
    select @sql = 'delete from '+@t_name+' where ' + @f_key+'  = '+''''+ @id +'''' 
    exec(@sql)
 fetch cur_rows into @id,@max 
 end 
 close cur_rows 
    deallocate cur_rows
 set rowcount 0
end