oracle 存储过程案例（去重操作）

今天开发人员提了一个需求，要把一个表的垃圾数据去掉，原则是根据两个字段，如果这两个字段都相同去重只保留一个，下面展示具体表的结构：

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29654823/viewspace-1795780/