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;