删除或者更新重复的记录

create or replace procedure sp_tran_delete_manyone_data
/********************************************
功能：删除接口表port_rk_czrk内的重复数据
没有使用,但是可以应用
20071129 BY LIHJ
********************************************/
as
--定义
type_err t_tran_sys_errors%rowtype;
type_log t_tran_sys_log%rowtype;
type_process t_tran_sys_process%rowtype;

LN_LXZ NUMBER:=0;
begin
--初始化值
type_process.ins_rows:=0;
type_process.upd_rows:=0;
type_process.err_rows:=0;
type_log.total_rows:=0;
type_log.err_rows:=0;
type_process.pro_id:='000007';---------------------------update by lihj
type_process.object:='port_gnlk_hotel';------------------update by lihj
type_log.pro_id:=type_process.pro_id;
type_log.object:=type_process.object;
---------------------更新表示---------------------------
update port_rk_czrk a--抽取的数据尽量不多就可以拉
set sjzt='3'
where rowid < (select max(b.rowid) from port_rk_czrk b where a.jmzh=b.jmzh ); --还得加条件！
commit;
----------------------删除数据---------------------------
for cur_date in (select t.*,t.rowid from port_rk_czrk t where sjzt='3') loop
delete from port_rk_czrk where rowid=cur_date.rowid;
LN_LXZ :=LN_LXZ +1;
if mod(LN_LXZ,500)=0 then
sp_tran_sys_log(type_log);
commit;
end if;
end loop;
commit;
----------------------异常处理-------------------------
exception when others then
type_process.err_rows:=type_process.err_rows+1;
type_err.pro_id:='000007';
type_err.err_text:=substrb(sqlerrm,1,200);
type_err.sou_table:='port_gnlk_hotel';
type_err.sou_prk:='yzj';

type_err.des_table:='port_gnlk_hotel';
sp_tran_sys_errors(type_err);
end;

要灵活的更新数据呀！

select a.* from xz_yjxxfk a--抽取的数据尽量不多就可以拉

where rowid < (select max(b.rowid) from xz_yjxxfk b where a.ybbh=b.ybbh)

order by bdsj desc;