设置customer_id

 update t_user_identification u
     set u.customer_id = (select c.customer_id
                            from t_customer c
                           where exists (select 1
                                    from t_user us
                                   where us.customer_id = c.customer_id
                                     and exists
                                   (select 1
                                            from t_user_login_way y
                                           where y.user_id = us.user_id
                                             and y.user_name = u.open_id
                                             and y.user_type = '02'))
                             and rownum <= 1)
   where u.identify_status in ('1');

 上面的方法有个缺陷,当数据过多时,数据库会因为执行时间太长而报错ORA-01555,导致全盘扫描中断,

改成下面这样问题就都解决啦

 

  
declare
 v_num number;
begin
  v_num := 0;
  for f in (select id,open_id from t_user_identification where identify_status = '1') loop  
  update t_user_identification u
     set u.customer_id = (select c.customer_id
                            from t_customer c
                           where exists (select 1
                                    from t_user us
                                   where us.customer_id = c.customer_id
                                     and exists
                                   (select 1
                                            from t_user_login_way y
                                           where y.user_id = us.user_id
                                             and y.user_name = f.open_id
                                             and y.user_type = '02'))
                             and rownum <= 1)
   where u.identify_status in ('1') and u.id = f.id;
   v_num := v_num + 1;
   if v_num > 50 then
    begin
    commit;
    v_num := 0;
    end;
    end if;
end loop;
commit;
end;