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

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