create or replace procedure test000(I_FUNDCODE varchar2(10),I_PROVINCE varchar2(10),RT_CURSOR out sys_refcursor) is
begin
open RT_CURSOR for select PV.NAME, PS.FUNDNAME,
count(case when ct.age between 1 and 20 then 1 else null end) "1~20岁",
count(case when ct.age between 21 and 40 then 1 else null end) "21~40岁",
count(case when ct.age > 41 then 1 else null end) "41岁以上",
sum(ts.curshare)
from CUSTOMERS ct, PROVINCE pv, TA_SHARE ts, PRODUCTS1 ps
where pv.id = ct.province
and ct.taaccoid = TS.TAACCOID
and TS.FUNDCODE = I_FUNDCODE
and pv.name=I_PROVINCE
group by PV.NAME, PS.FUNDNAME
order by 1, 2;
end;
begin
open RT_CURSOR for select PV.NAME, PS.FUNDNAME,
count(case when ct.age between 1 and 20 then 1 else null end) "1~20岁",
count(case when ct.age between 21 and 40 then 1 else null end) "21~40岁",
count(case when ct.age > 41 then 1 else null end) "41岁以上",
sum(ts.curshare)
from CUSTOMERS ct, PROVINCE pv, TA_SHARE ts, PRODUCTS1 ps
where pv.id = ct.province
and ct.taaccoid = TS.TAACCOID
and TS.FUNDCODE = I_FUNDCODE
and pv.name=I_PROVINCE
group by PV.NAME, PS.FUNDNAME
order by 1, 2;
end;
begin
open RT_CURSOR for select PV.NAME, PS.FUNDNAME,
count(case when ct.age between 1 and 20 then 1 else null end) "1~20岁",
count(case when ct.age between 21 and 40 then 1 else null end) "21~40岁",
count(case when ct.age > 41 then 1 else null end) "41岁以上",
sum(ts.curshare)
from CUSTOMERS ct, PROVINCE pv, TA_SHARE ts, PRODUCTS1 ps
where pv.id = ct.province
and ct.taaccoid = TS.TAACCOID
and TS.FUNDCODE = I_FUNDCODE
and pv.name=I_PROVINCE
group by PV.NAME, PS.FUNDNAME
order by 1, 2;
end;
begin
open RT_CURSOR for select PV.NAME, PS.FUNDNAME,
count(case when ct.age between 1 and 20 then 1 else null end) "1~20岁",
count(case when ct.age between 21 and 40 then 1 else null end) "21~40岁",
count(case when ct.age > 41 then 1 else null end) "41岁以上",
sum(ts.curshare)
from CUSTOMERS ct, PROVINCE pv, TA_SHARE ts, PRODUCTS1 ps
where pv.id = ct.province
and ct.taaccoid = TS.TAACCOID
and TS.FUNDCODE = I_FUNDCODE
and pv.name=I_PROVINCE
group by PV.NAME, PS.FUNDNAME
order by 1, 2;
end;