存储过程实例

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

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

?

?

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;
/