create or replace procedure test000(I_FUNDCODE in varchar2,I_PROVINCE in varchar2,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;怎么得到游标的值,并打印出来
v_cur sys_refcursorj;
begin
create or replace procedure test000(I_FUNDCODE ,I_PROVINCE ,v_cur);
for c in v_cur loop
dbms_output.putline(name);
--......
end loop;
end;
declare
v_cur sys_refcursorj;
begin
test000(I_FUNDCODE ,I_PROVINCE ,v_cur);
for c in v_cur loop
dbms_output.putline(name);
--......
end loop;
end;
--把中文改成了英文
create or replace procedure test000(I_FUNDCODE in varchar2,I_PROVINCE in varchar2,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) less20,
count(case when ct.age between 21 and 40 then 1 else null end) less40,
count(case when ct.age > 41 then 1 else null end) more40,
sum(ts.curshare) 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;
--调用
declare
I_FUNDCODE varchar2(50);
I_PROVINCE varchar2(50);
p_name varchar2(50);
f_name varchar2(50);
v1 number;
v2 number;
v3 number;
v4 number;
cur sys_refcursor;
begin
I_FUNDCODE:='110001';
I_PROVINCE:='北京';
test000(I_FUNDCODE,I_PROVINCE,cur);
fetch cur into p_name,f_name,v1,v2,v3,v4;
while cur%found loop
dbms_output.put_line(p_name||'-'||f_name||'-'||to_char(v1)||'-'||to_char(v2)||'-'||to_char(v3)||'-'||to_char(v4));
fetch cur into p_name,f_name,v1,v2,v3,v4;
end loop;
close cur;
end;
v1 number; --少于20的
v2 number; --大于20少于40
v3 number; --大于40的
v4 number; --sum(ts.curshare)