oracle删除重复数据的存储过程1.oracle删除重复数据的存储过程(1)CREATE OR REPLACE PROCEDURE DELETE_tes
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表示是分組字段﹐即主鍵字段asbegin?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 0end
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
