oracle剔除重复数据的存储过程
日期：2014-05-16 浏览次数：20464 次
oracle删除重复数据的存储过程
我的异常网推荐解决方案：oracle存储过程,http://www.aiyiweb.com/oracle-develop/177537.html
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
我的异常网推荐解决方案：oracle存储过程,http://www.aiyiweb.com/oracle-develop/177537.html
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
