17,089
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure abc is
TYPE pv_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
web_pv pv_array;
wap_pv pv_array;
type type_array is varray(9) of varchar(10);
var_array type_array:=type_array('220100','220200','220300','220400','220500','220600','220700','220800','222400');
test int;
begin
for a in 1..var_array.count loop
select sum(round(webpvpercent*webpv)),
sum(round(wappvpercent*wappv))
into
web_pv(var_array(a)), wap_pv(var_array(a))
from t_visit_pvpercent a
join
(
select substr(visittime,1,8) as days,
sum(case when portaltype='0' then 1 else 0 end) as webpv,
sum(case when portaltype='1' then 1 else 0 end) as wappv
from t_visit_source
where visittime like '201203%' and restype='9' and userareacode is null
group by substr(visittime,1,8)
) b on a.datetime=b.days
where datetime like '201203%' and userareacode=var_array(a);
insert into ccc values (var_array(a),web_pv(var_array(a)),wap_pv(var_array(a));
end loop;
exception
when others then
null;
end abc;