Aug 31th 2005

1、修改欠费用户bug，原来程序中批量导入时判断user_push_time表没有根据policyid和username结合，而是单独根据username判断
 2、修改删除的逻辑错误，原来删除后所有推送策略的validflag都置为0

修改后的存储过程

CREATE OR REPLACE Procedure due_ImportArrearsUser
(
p_policyId        In               ics001.user_push_times.policyid%Type,
p_result          Out              Integer
---  -1001,ɾ³ýÁÙʱ±íÖÐÔÚ°×Ãûµ¥ÖдæÔڵļǼʱ³ö´í
---  -1002,ɾ³ýÁÙʱ±íÖÐÓëdue_user±íÖÐÖظ´µÄ¼Ç¼³ö´í
---  -1003,½«ÁÙʱ±íÖеÄÊý¾Ý²åÈëdue_user±í³ö´í
---  -1004,ÁÙʱ±íÖÐÔÚuser_push_time±íÖÐÒÑÓеļǼ£¬¸üгö´í
---  -1005,ÁÙʱ±íÖв»´æÔÚÓÚuser_push_time±íÖеļǼ£¬²åÈë³ö´í
---  -1006,Çå¿ÕÁÙʱ±íÊý¾Ý³ö´í
---   0,³É¹¦
)
Is
v_userCount Integer;

Begin
  -----½«ÁÙʱ±ídue_user_temp±íÖв»ÔÚ°×Ãûµ¥ÖкÍÓÚdue_user²»Öظ´µÄ¼Ç¼²åÈëdue_user
     /**//*Insert Into due_user
     Select a.*  
     From due_user_temp a,due_user b, due_whiteuser c 
     Where ( a.province = b.province And a.city = b.city) And (a.username=b.username(+)  And b.username Is Null) )
     And (a.province = c.province And a.city = c.city And (a.username = c.username(+) And c.username Is Null) )*/
     
     -----ɾ³ýÁÙʱ±íÖÐÔÚ°×Ãûµ¥ÖдæÔڵļǼ------
     
     Delete From due_user_temp a
     Where Exists( Select 1 
                   From due_whiteuser b 
                   Where a.username = b.username And a.province = b.province And a.city = b.city );
                   
      /**//*If Sql%Notfound Then
          p_result := -1001;
          Return;
       End If;  */            
     
     
     -----ɾ³ýÁÙʱ±íÖÐÓëdue_user±íÖÐÖظ´µÄ¼Ç¼---
     Delete From due_user_temp a
     Where Exists( Select 1 
                   From due_user b 
                   Where a.username = b.username And a.province = b.province And a.city = b.city );
     
     /**//*If Sql%Notfound Then
          p_result := -1002;
          Return;
       End If;*/ 
       
     ---commitÁÙʱ±íÖеÄÊý¾Ý¸ü¸Ä£­£­£­£­              
     --Commit;
     
     -----½«ÁÙʱ±íÖеÄÊý¾Ý²åÈëdue_user±í----
     Insert Into due_user
     Select * From due_user_temp;
     
     /**//*If Sql%Notfound Then
          p_result := -1003;
          Return;
       End If; */
     
     ----¸ù¾ÝÁÙʱ±íÖеÄÊý¾Ý,update»òinsert ics001.user_push_times±í---
     ----ÁÙʱ±íÖÐÔÚuser_push_time±íÖÐÒÑÓеļǼ£¬¸üÐÂ-----
     Update
       (Select push.validflag, push.pushtimes,push.pushdaytimes, push.lastpushedtime
       From ics001.user_push_times push, due_user_temp temp
       Where push.username = temp.username And push.policyid = p_policyId)
     Set validflag = 1, pushtimes = 0, pushdaytimes = 0, lastpushedtime = 0;
     
     /**//*If Sql%Notfound Then
          p_result := -1004;
          Return;
       End If; */
     
     ---ÁÙʱ±íÖв»´æÔÚÓÚuser_push_time±íÖеļǼ£¬²åÈë----
     INSERT INTO ics001.user_push_times(username,ip,policyid,pushtimes,pushdaytimes,validflag,fileId,createtime)
      Select temp.username, 0, p_policyId, 0, 0, 1, 999997, Sysdate 
      From due_user_temp temp Left Outer Join (Select push.username 
                                              From ics001.user_push_times push 
                                              Where policyid=p_policyId) push  
      On temp.username = push.username
      Where  push.Username Is Null;
      
      /**//*If Sql%Notfound Then
          p_result := -1005;
          Return;
       End If; */
     
     Select Count( * ) Into v_userCount From due_user_temp;
     If(v_userCount > 0 ) Then
         Begin
              Delete From due_user_temp;
              
              If Sql%Notfound Then
              p_result := -1006;
              Return;
              End If;
         End;
    End If;
       
    p_result := v_userCount;
    Return;
       
     
     
End due_importarrearsuser;