oracle存储过程：去掉重复的记录

rmsebfdfdo

create or replace procedure SP_STRIP_PK(
/*
去掉重复的记录（保证主键值唯一）
*/
p_table       in varchar2, --物理表
partition_by  in varchar2, --分区依据 PK字段
order_by      in varchar2  --排序依据
) is
begin
EXECUTE IMMEDIATE(
'DELETE FROM '||p_table||'
WHERE rowid in (
SELECT rid
FROM (
select rowid rid,
row_number() over(partition by '||nvl(partition_by,1)||' order by '||order_by||') rn
from '||p_table||'
)
WHERE rn > 1
) ');
end SP_STRIP_PK;


MySQL版：

CREATE PROCEDURE `sp_strip_pk`(tb_name varchar(255), partition_by varchar(255), order_by varchar(255), rowid_field varchar(50))
MODIFIES SQL DATA
DETERMINISTIC
COMMENT '去掉重复的记录（保证主键值唯一） '
BEGIN
set @sql = concat(
'DELETE FROM ',tb_name,'
WHERE (', rowid_field ,') IN (
select ', rowid_field ,' from (
select t1.*
, if(@g1=t1.group1, @rank:=@rank+1, @rank:=1) as rank
, (@g1:=t1.group1) as g1
from
(select ', rowid_field ,', concat_ws(char(5), ', partition_by ,') as group1
from ',tb_name,'
order by ', partition_by ,' , ', order_by ,') t1
, (select @g1:=null, @rank:=0) t2
) t where rank > 1
)' );
prepare stm from @sql;
execute stm;
END