oracle06531,请教：ORA-06531: 引用未初始化的收集

create or replace type type_stat_memb_prod_trade as object (cnt_members integer ,cnt_members_buy  integer ,cnt_members_sell integer , cnt_members_sell_bm integer , cnt_members_sell_cqs  integer ,cnt_prod integer,cnt_trade integer);

create or replace type tab_memb_prod_trade is

table of type_stat_memb_prod_trade;

create or replace function p_tab_memb_prod_trade(

p_start_date DATE DEFAULT SYSDATE-30 --起始时间

,p_end_date DATE DEFAULT SYSDATE     --结束时间

) return tab_memb_prod_trade

/*20070621 陈海 统计某时间段内会员，产品，商机总数

必须预先:

create or replace type type_stat_memb_prod_trade

as object (cnt_members integer ,cnt_members_buy  integer ,

cnt_members_sell integer , cnt_members_sell_bm integer ,

cnt_members_sell_cqs  integer ,cnt_prod integer,cnt_trade integer

);

create or replace type tab_memb_prod_trade is

table of type_stat_memb_prod_trade;

调用示例：

select * from table(p_tab_memb_prod_trade)

*/

is

result_count tab_memb_prod_trade := tab_memb_prod_trade(); --table 初始化

begin

result_count.extend;

result_count(1):=type_stat_memb_prod_trade(NULL,NULL,NULL,NULL,NULL,NULL,NULL); --每个元素初始化

select (select count(*)

from e_acc_members m1 ,e_acc_companies c

where m1.comp_id=c.id

and m1.create_time between p_start_date and p_end_date

) cnt_members                               --1：会员数

,(Select count(*)

from e_acc_members m1 ,e_acc_companies c

where m1.comp_id=c.id

and  m1.create_time between p_start_date and p_end_date

and c.role=1

) cnt_members_buy                        --2：买方会员数

,(Select count(*)

from e_acc_members m1 ,e_acc_companies c

where m1.comp_id=c.id

and  m1.create_time between p_start_date and p_end_date

and c.role=2

) cnt_members_sell                      --3：卖方会员数

,(Select count(*)

from e_acc_members m1 ,e_acc_companies c

where m1.comp_id=c.id

and  m1.create_time between p_start_date and p_end_date

and c.role=2

and c.comp_level in (1,2)

) cnt_members_sell_bm                 --4：卖方免费会员数

,(Select count(*)

from e_acc_members m1 ,e_acc_companies c

where m1.comp_id=c.id

and  m1.create_time between p_start_date and p_end_date

and c.role=2

and comp_level=3

) cnt_members_sell_cqs               --5 卖方付费会员数

,(SELECT count(*)

FROM e_data_products p

WHERE p.create_time  between p_start_date and p_end_date

) cnt_prod                          --6 产品数

,(SELECT count(*)

FROM e_data_trade_leads l

WHERE l.post_date   between p_start_date and p_end_date

) cnt_trade                        --7 商机数

INTO result_count(1).cnt_members   --见上1-7

,result_count(1).cnt_members_buy

,result_count(1).cnt_members_sell

,result_count(1).cnt_members_sell_bm

,result_count(1).cnt_members_sell_cqs

,result_count(1).cnt_prod

,result_count(1).cnt_trade

FROM dual;

result_count.extend;                      --for测试多记录情况

result_count(2):=type_stat_memb_prod_trade(NULL,NULL,NULL,NULL,NULL,NULL,NULL);--每个元素初始化

RETURN result_count;

end p_tab_memb_prod_trade;