ITPub博客

首页 > 数据库 > Oracle > oracle 存储过程案例(去重操作)

oracle 存储过程案例(去重操作)

原创 Oracle 作者:贺子_DBA时代 时间:2015-09-09 20:59:27 0 删除 编辑
今天开发人员提了一个需求,要把一个表的垃圾数据去掉,原则是根据两个字段,如果这两个字段都相同去重只保留一个,下面展示具体表的结构:

SQL> desc liuwenhe.tb_acl
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TB_ACL_ID                            not null            NUMBER(10)              ###主键 
 ENTITY_ID                                            NUMBER(10)                       
 ENTITY_TYPE                                        VARCHAR2(10)
 TB_RES_ID                                          NUMBER(10)
 PERMISSION                                         VARCHAR2(10)
 CREATE_DATE                                        DATE
判断字段: ENTITY_ID  , TB_RES_ID   而且这俩字段都可以重复,但是不可以同时重复。
就类似于如下图的是需要去重的数据,

方法一:用sql语句完成任务。
1,先查出这两个字段不同时重复的所有数据,注意distinct 后面如果有多个字段,他会判断这几个字段的是否同时都一样,然后过滤。
select distinct ENTITY_ID,TB_RES_ID  from  ustest_zh_cn.tb_acl;  
   
  
2,create table  ustest_zh_cn.tb_acl_bak   as  select distinct ENTITY_ID,TB_RES_ID  from  ustest_zh_cn.tb_acl; 
3,添加上主键的字段 
alter table   ustest_zh_cn.tb_acl_bak  add    TB_ACL_ID   number(38)  ;  
4.  根据原来的源表,更新主键值。
update  ustest_zh_cn.tb_acl_bak  a  set  a.TB_ACL_ID=( select  b.TB_ACL_ID  from  ustest_zh_cn.tb_acl  b where a.ENTITY_ID=b.ENTITY_ID and a.TB_RES_ID  =b.TB_RES_ID  and rownum=1)  ;
方法二:用存储过程:
  SQL>  create or replace procedure insert_distinct_tb_acl   is
    begin
    declare  CURSOR   c_number   is
    select distinct ENTITY_ID,TB_RES_ID  from  ustest_zh_cn.tb_acl;
    my_re     c_number%rowtype;
    begin
    open   c_number;
    loop
   exit  when c_number%notfound;
   fetch c_number  into  my_re;
   insert  into   liuwenhe.tb_acl   select * from ustest_zh_cn.tb_acl b where b.ENTITY_ID=my_re.ENTITY_ID and b.tb_res_id=my_re.tb_res_id and  
    rownum=1;
   end loop;
   close c_number;
   commit;
   end;
   end  insert_distinct_tb_acl ;
    /
利用这个存储过程得到的结果正好有一个是重复的,也就是最后一个是重复的,原因是:(摘自网络)
v_cur%notfound:

文档中的解释:It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.
这个解释更加精妙:
%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row
错误的例子:
tableA
id  name
1   a
2   b

declare
cursor v_cur is select name from tableA;

n varchar2(10);
begin
open v_cur;
loop
exit when v_cur%notfound;
fetch v_cur into n;

dbms_output.put_line(n);

close v_cur;
end loop;

end;


执行上面的语句,结果为:
a
b
b
发现最后一条记录被打印了两次。原因是%notfound是判断最后一次fetch的结果,把b fetch到变量n中之后再执行exit when %notfound判断得到的是false的记过,也就是说是有返回行的,所以判断通过,再此执行了打印语句。

小结 :也正是上面的这个错误才导致,如果 liuwenhe.tb_acl 这个表有主键或者唯一键什么的,他就会报错,有冲突,然后只需要先把他的主键或者唯一键给删掉,这时候插入进去的数据,肯定会有一个重复的,然后再利用下面这个语句查出这条重复的语句:

SQL> select ENTITY_ID,TB_RES_ID from liuwenhe.tb_acl group by ENTITY_ID,TB_RES_ID  having count(*) >1;
 ENTITY_ID  TB_RES_ID
---------- ----------
      2081      38077

看具体的这两条数据:
SQL> select * from liuwenhe.tb_acl where ENTITY_ID=2081 and TB_RES_ID=38077;
 TB_ACL_ID  ENTITY_ID ENTITY_TYP  TB_RES_ID PERMISSION CREATE_DATE
---------- ---------- ---------- ---------- ---------- ------------------
     79049       2081 de_DE           38077            24-AUG-15
     79049       2081 de_DE           38077            24-AUG-15
然后删除一条  即可







来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29654823/viewspace-1795780/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
记录工作中遇到的问题,积少成多,坚持就是胜利,工作经历:曾就职于国美、中国采购于招标网、目前就职于一家正规消费金融公司负责Oracle和MySQL以及hadoop相关运维和优化的工作

注册时间:2014-05-12

  • 博文量
    231
  • 访问量
    1632912