造周数据存储过程
由于工作需要要求把天的数据转成周的，花了一点时间写了一个。
记录如下： www.2cto.com
[sql]
create or replace procedure g_yearweek is
i number :=1;
CURSOR c_chn IS
select distinct(n_chn_id) from auditplatform.sum_mchn_mtgt_day_n_fina;
begin
for chn_rec in c_chn
loop
dbms_output.put_line(chn_rec.n_chn_id);
for i in 158..321
loop
insert into sum_mchn_schn_yearweek (
n_week_id,n_chn_id,
n_pv,n_pv_user,n_sch)
select i,chn_rec.n_chn_id,trunc(avg(d_n_pv)),trunc(avg(d_n_pv_user)),trunc(avg(d_n_sch))
from auditplatform.sum_mchn_mtgt_day_n_fina t, auditplatform.dim_sys_yearweek w
where to_date(
vc_stat_date, 'yyyymmdd') <= w.dt_date_end
and to_date(vc_stat_date, 'yyyymmdd') >= w.dt_date_beg
and w.n_week_id = i and t.n_chn_id = chn_rec.n_chn_id;
end loop;
end loop;
commit;
end g_yearweek;
执行： www.2cto.com
[sql]
SQL> set serveroutput on;
SQL> exec g_yearweek;