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

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