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;循环里的INSERT最终会插入9条记录,表中确实已经插入9条记录。用PLSQL里的,TEST测试执行这个存储过程,采用单步执行方式,最后执行到END时,程序就不动了。
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;循环里的INSERT最终会插入9条记录,表中确实已经插入9条记录。用PLSQL里的,TEST测试执行这个存储过程,采用单步执行方式,最后执行到END时,程序就不动了。