小弟接触DB2不久,遇到个DB2函数循环的问题,始终报错,望大神指点迷津

发布网友发布时间：2022-04-10 11:32

共1个回答

热心网友时间：2022-04-10 13:02

试试这个。

CREATE FUNCTION RRS.F_AVG ( IN cDate date )
RETURNS VARCHAR(26)
LANGUAGE SQL
BEGIN
DECLARE re INTEGER;
DECLARE nYuE INTEGER;
DECLARE nDate INTEGER;
SET RE =0;
SET nDate =TRUNC(cDate,'MM');
WHILE nDate<=cDate
LOOP
select sum(e.DEP_BALANCE/10000*r.EXCHAGERATE_AMT) INTO nYuE from e_deposit e
inner join VIEW_ORG_COLLECT v on v.ORG_ID =e.ORG_ID and v.PRE_ORGID =1
inner join E_EXCHANGE_RATE r on r.CONVERT_CCY_ID =e.V_CCY_ID and r.DATE_ID =to_date(cDate,'yyyymmdd')
where e.DATE_ID =to_date(cDate,'yyyymmdd')
and substr(e.VS_ID,1,4) not in ('2012')
and e.start_date <=to_date(to_char(ndate),'yyyymmdd')
and (e.e_date >to_date(to_char(ndate),'yyyymmdd') or e.e_date is null);
SET RE =RE+nYuE;
SET nDate =nDate+1 day;
END LOOP;
SET re= re/to_number(substr(cDate,7,2));
RETURN (RE);
end