记录一段存储过程

create or replace procedure getPartTimeDatas(investor in varchar2,v_cursor out CLOB) as
v_billcount number;
v_wincount number;
v_winrate number;
v_avgwin number;
v_avgloss number;
v_maxcloseprofit number;
v_mincloseprofit number;
v_sumcloseprofit number;

v_daycloseprofit number;
v_pdate CLOB;

v_str CLOB;
begin
DECLARE CURSOR myCusor is
select sum(t.billcount) billcount,sum(t.wincount) wincount,sum(t.winrate) winrate,sum(t.sumwin/t.billcount) avgwin,sum(t.sumloss/t.billcount) avgloss,max(t.closeprofit) maxcloseprofit,min(t.closeprofit) mincloseprofit,sum(t.closeprofit) sumcloseprofit from T_AccountDayProfit t where t.investorid=investor and t.month=to_number(to_char(sysdate,'mm')) and t.year=to_number(to_char(sysdate,'yyyy'));
begin
OPEN myCusor;
LOOP
fetch myCusor into v_billcount,v_wincount,v_winrate,v_avgwin,v_avgloss,v_maxcloseprofit,v_mincloseprofit,v_sumcloseprofit;
EXIT WHEN myCusor%NOTFOUND;
v_str:= v_billcount||','||v_wincount||','||v_winrate||','||v_avgwin||','||v_avgloss||','||v_maxcloseprofit||','||v_mincloseprofit||','||v_sumcloseprofit||',';
v_cursor:=v_cursor||v_str;
END LOOP;
CLOSE myCusor;
end;

DECLARE CURSOR myCusor is
select sum(t.billcount) billcount,sum(t.wincount) wincount,sum(t.winrate) winrate,sum(t.sumwin/t.billcount) avgwin,sum(t.sumloss/t.billcount) avgloss,max(t.closeprofit) maxcloseprofit,min(t.closeprofit) mincloseprofit,sum(t.closeprofit) sumcloseprofit from T_AccountDayProfit t where t.investorid=investor and t.pdate>=trunc(ADD_MONTHS(SYSDATE,-2),'mm');
begin
OPEN myCusor;
LOOP
fetch myCusor into v_billcount,v_wincount,v_winrate,v_avgwin,v_avgloss,v_maxcloseprofit,v_mincloseprofit,v_sumcloseprofit;
EXIT WHEN myCusor%NOTFOUND;
v_str:= v_billcount||','||v_wincount||','||v_winrate||','||v_avgwin||','||v_avgloss||','||v_maxcloseprofit||','||v_mincloseprofit||','||v_sumcloseprofit||',';
v_cursor:=v_cursor||v_str;
END LOOP;
CLOSE myCusor;
end;

DECLARE CURSOR myCusor is
select sum(t.billcount) billcount,sum(t.wincount) wincount,sum(t.winrate) winrate,sum(t.sumwin/t.billcount) avgwin,sum(t.sumloss/t.billcount) avgloss,max(t.closeprofit) maxcloseprofit,min(t.closeprofit) mincloseprofit,sum(t.closeprofit) sumcloseprofit from T_AccountDayProfit t where t.investorid=investor and t.pdate>=trunc(ADD_MONTHS(SYSDATE,-5),'mm');
begin
OPEN myCusor;
LOOP
fetch myCusor into v_billcount,v_wincount,v_winrate,v_avgwin,v_avgloss,v_maxcloseprofit,v_mincloseprofit,v_sumcloseprofit;
EXIT WHEN myCusor%NOTFOUND;
v_str:= v_billcount||','||v_wincount||','||v_winrate||','||v_avgwin||','||v_avgloss||','||v_maxcloseprofit||','||v_mincloseprofit||','||v_sumcloseprofit||',';
v_cursor:=v_cursor||v_str;
END LOOP;
CLOSE myCusor;
end;

DECLARE CURSOR myCusor is
select sum(t.billcount) billcount,sum(t.wincount) wincount,sum(t.winrate) winrate,sum(t.sumwin/t.billcount) avgwin,sum(t.sumloss/t.billcount) avgloss,max(t.closeprofit) maxcloseprofit,min(t.closeprofit) mincloseprofit,sum(t.closeprofit) sumcloseprofit from T_AccountDayProfit t where t.investorid=investor and t.pdate>=trunc(ADD_MONTHS(SYSDATE,-11),'mm');
begin
OPEN myCusor;
LOOP
fetch myCusor into v_billcount,v_wincount,v_winrate,v_avgwin,v_avgloss,v_maxcloseprofit,v_mincloseprofit,v_sumcloseprofit;
EXIT WHEN myCusor%NOTFOUND;
v_str:= v_billcount||','||v_wincount||','||v_winrate||','||v_avgwin||','||v_avgloss||','||v_maxcloseprofit||','||v_mincloseprofit||','||v_sumcloseprofit||',';
v_cursor:=v_cursor||v_str;
END LOOP;
CLOSE myCusor;
end;

DECLARE CURSOR myCusor is
select to_char(t.pdate,'yyyymmdd') pdate,t.closeprofit from T_AccountDayProfit t where t.investorid=investor and t.pdate>=trunc(ADD_MONTHS(SYSDATE,-11),'mm') order by t.pdate asc;
begin
OPEN myCusor;
LOOP
fetch myCusor into v_pdate,v_daycloseprofit;
EXIT WHEN myCusor%NOTFOUND;
v_str:= v_pdate||','||v_daycloseprofit||',';
v_cursor:=v_cursor||v_str;
END LOOP;
CLOSE myCusor;
end;

select substr(v_cursor,1,length(v_cursor)-1) into v_cursor from dual;

end getPartTimeDatas;

/

 

调用：

set serverout on;
declare
v_ret CLOB;
begin
getPartTimeDatas('1',v_ret);
dbms_output.put_line(v_ret);
end;
/

转载于:https://www.cnblogs.com/zhuzhenyu/p/5750535.html