1.将重复记录放入时间表

create table tab_base_moneylist_bak as select * from (
select * from tab_base_moneylist a
　　where (a.memo,a.tradetype_id) in
(select memo,tradetype_id from tab_base_moneylist group by memo,tradetype_id having count(*) > 1)
　　and rowid not in (select min(rowid) from tab_base_moneylist group by memo,tradetype_id having count(*) > 1)
);

2.原表保留一条记录，其他重复记录删除
delete from tab_base_moneylist a
　　where (a.memo,a.tradetype_id) in
(select memo,tradetype_id from tab_base_moneylist group by memo,tradetype_id having count(*) > 1)
　　and rowid not in (select min(rowid) from tab_base_moneylist group by memo,tradetype_id having count(*) > 1);

commit;

3.根据业务逻辑处理空值

--516097
select * from tab_base_moneylist a where a.memo is null
--516093
select * from tab_base_moneylist a where a.pk is null

update tab_base_moneylist set memo='OrderItemMobile='||pk where memo is null;
commit;

4.根据需求建立联合唯一索引

CREATE UNIQUE INDEX TOPUP.IDX_MONEYLIST_MEMO_TRADEID ON TOPUP.TAB_BASE_MONEYLIST
(MEMO,TRADETYPE_ID)
LOGGING
TABLESPACE BASE_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

5. 处理时间表中的重复记录

CREATE OR REPLACE PROCEDURE TOPUP.sp_double_value_treat
AS
   v_count   NUMBER;                                            --数字型计数器
   v_num     VARCHAR2 (50);                                     --字符型计数器
BEGIN
   v_count := 0;                                                --数字型计数器
   v_num := '0';                                                --字符型计数器

   --定义游标
   DECLARE
      CURSOR double_treat
      IS
         SELECT   id FROM tab_base_moneylist_bak;

c_double_treat double_treat%ROWTYPE;
--开始使用游标取数据

   BEGIN
      FOR c_double_treat IN double_treat
      LOOP
         v_count := v_count + 1;

v_num := TO_CHAR (v_count);

         UPDATE   tab_base_moneylist_bak
            SET   memo = v_num || memo
          WHERE   id = c_double_treat.id;

         COMMIT;
      END LOOP;
   END;
--发生异常时返回错误码

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
         'DOUBLE_VALUE_TREAT ERROR,PLEASE OPERATION AGAIN,THANKS!'
      );
      ROLLBACK;
END;
/

6.执行存储过程处理重复记录
exec TOPUP.sp_double_value_treat;

7. 将临时表中处理过的重复记录导入原表

insert into tab_base_moneylist select * from tab_base_moneylist_bak;
commit;

ITPub博客

重复及空值处理

最新文章