oracle删除重复数据只保留一条

1、存过实现：

declare 
type insue_record_type is record(
  HOSNUM   MEDITEM_INUSE_BAK.HOSNUM%type,
  NODECODE MEDITEM_INUSE_BAK.NODECODE%type,
  MEDCODE  MEDITEM_INUSE_BAK.MEDCODE%type
);

insue_record            insue_record_type;
c_hosnum                MEDITEM_INUSE_BAK.HOSNUM%type;
c_nodecode              MEDITEM_INUSE_BAK.NODECODE%type;
c_medcode               MEDITEM_INUSE_BAK.MEDCODE%type;

cursor r_records is 
SELECT T.HOSNUM,T.NODECODE,T.MEDCODE
  FROM MEDITEM_INUSE_BAK T
 WHERE T.HOSNUM = '1254'
   and (T.Hosnum, T.Nodecode, T.MEDCODE) in
       (SELECT TT.HOSNUM, TT.NODECODE, TT.MEDCODE
          FROM MEDITEM_INUSE_BAK TT
         WHERE TT.HOSNUM = '1254'
           and TT.NODECODE = '1254'
         GROUP BY TT.HOSNUM, TT.NODECODE, TT.MEDCODE
        having count(*) > 1)
 order by T.Hosnum, T.Nodecode, T.MEDCODE;  
 
BEGIN
  for insue_record in r_records loop 
     if (c_hosnum is null or c_nodecode is null 
      or c_medcode is null) or 
      (insue_record.hosnum != c_hosnum 
      or insue_record.nodecode != c_nodecode 
      or insue_record.medcode != c_medcode)
     then  
      c_hosnum := insue_record.HOSNUM;
      c_nodecode := insue_record.nodecode;
      c_medcode := insue_record.medcode;
    else 
      delete from MEDITEM_INUSE_BAK where hosnum = insue_record.hosnum and nodecode = insue_record.nodecode and medcode = insue_record.medcode;
    end if;
  end loop;
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('sqlerrm-->' || sqlerrm);
    ROLLBACK;
END ;

2、脚本 删除

delete from MEDITEM_INUSE m
 where (m.hosnum, m.nodecode, m.medcode, m.codesource) in
       (SELECT T.HOSNUM, T.NODECODE, T.MEDCODE, T.codesource
          FROM MEDITEM_INUSE T
         GROUP BY T.HOSNUM, T.NODECODE, T.MEDCODE, T.codesource
        having count(*) > 1)
   and rowid not in (select min(rowid)
                       from MEDITEM_INUSE
                      GROUP BY HOSNUM, NODECODE, MEDCODE, codesource
                     having count(*) > 1);

删除数据须谨慎，提前备份表哈！！！