存储过程实例

下面这段存储过程实现的逻辑：

统计昨天注册的新用户的数据，插入到指定的表中，提供给应用程序查询，生成报表。

 

 

CREATE OR REPLACE PROCEDURE agent_reguser_static
-- 统计昨天的用户注册信息：                                                                       
                                                                                          
is                                                                                                                           
ls_user_id number(10);                                                                                              
ls_remarks varchar2(10);                                                                                                    
ls_websiteid varchar2(15);
ls_salesid varchar2(150);  
ls_agentid varchar2(15);                                                                                                                           
                                                                                                                             
begin
 delete from agent_reg_user where to_char(time,'yyyy-mm-dd') = to_char( sysdate-1,'yyyy-mm-dd' );--删除掉历史数据，重新统计
 commit;
 
 declare  cursor cur_reg_user is    --统计昨天新注册的用户   
  select distinct w.userid,w.sitevalue from web_listen w,agent_user_info a where to_char(w.regdate,'yyyy-mm-dd')
   =to_char( sysdate-1,'yyyy-mm-dd') and ((w.sitevalue = a.agentid and a.agentlevel = 3) or (w.sitevalue like 'lerss%'));
    
 begin
  open cur_reg_user;
        loop
         fetch cur_reg_user into ls_user_id, ls_websiteid;
         exit when cur_reg_user%notfound;

  if substr(ls_websiteid,1,5) ='lerss' then
   ls_salesid := '080829005000001';
   ls_agentid := '080829005';
  else  
   select agentid,parentid into ls_salesid,ls_agentid from agent_user_info where agentid in( select parentid from agent_user_info
    where agentid = ls_websiteid and agentlevel = 3);
  end if;
    
  --将注册信息插入到表agent_reg_user中
  insert into agent_reg_user(id,userid,username,truename,cardnum,mobile,email,area,time,websiteid,salesid,agentid)
 
  select SEQ_AGENT_REG_USER.nextval,ls_user_id,username,truename,cardid,phone,email,substr(cardid,1,2)||'0000',
   time,ls_websiteid,ls_salesid,ls_agentid from luser where id = ls_user_id;
  commit;

 end loop ;
 close cur_reg_user;                                                                                               
    
  --更新area信息，如果不属于基本省的地区信息，则均属于其它
  update agent_reg_user set area = '100000' where to_char(time,'yyyy-mm-dd') = to_char( sysdate-1,'yyyy-mm-dd' )
   and area not in  (select area_id from agent_area_dic);
  commit;
end;
                                                                                                                         
end agent_reguser_static;
/