请各位大侠看看,如何获得游标的最后一行,这个函数,想拼成一个字符串返回,初次写函数,大家可以帮我优化一下。
create or replace function UF_CW_YLJTSB(VS_HOSPITAL IN VARCHAR2) return VARCHAR2 is
Result VARCHAR2(1000);
ll_bl number(8,2); /*SELECT SUM(TCSJZF) INTO LL_ZJ FROM YW_DYYWB WHERE YWLB='1' AND HOSPITAL_ID=VS_HOSPITAL;*/
CURSOR GETTSBBL IS
SELECT B.DISEASE,DECODE(SUM(C.ZJ),0,0,ROUND(SUM(a.tcsjzf)/C.ZJ,2))AS BL
FROM YW_DYYWB A,BS_DISEASE B,(SELECT SUM(TCSJZF) AS ZJ FROM YW_DYYWB WHERE YWLB='1' AND HOSPITAL_ID=VS_HOSPITAL) C
WHERE A.ICD=B.ICD AND
A.YWLB='1' AND
A.HOSPITAL_ID=VS_HOSPITAL
GROUP BY B.DISEASE,C.ZJ
having SUM(a.tcsjzf)/C.ZJ >0; BEGIN
Result := '';
FOR I IN GETTSBBL LOOP
ll_bl := ll_bl + i.bl;
--注释部分如何获得最后一行
/* if @@fetch_status=0 then
i.bl=1-ll_bl;
fetch last from GETTSBBL into i.bl ;
end if;*/
Result := Result || i.disease ||to_char(i.bl) ||' ';
END LOOP;
return Result;
END;
create or replace function UF_CW_YLJTSB(VS_HOSPITAL IN VARCHAR2) return VARCHAR2 is
Result VARCHAR2(1000);
ll_bl number(8,2); /*SELECT SUM(TCSJZF) INTO LL_ZJ FROM YW_DYYWB WHERE YWLB='1' AND HOSPITAL_ID=VS_HOSPITAL;*/
CURSOR GETTSBBL IS
SELECT B.DISEASE,DECODE(SUM(C.ZJ),0,0,ROUND(SUM(a.tcsjzf)/C.ZJ,2))AS BL
FROM YW_DYYWB A,BS_DISEASE B,(SELECT SUM(TCSJZF) AS ZJ FROM YW_DYYWB WHERE YWLB='1' AND HOSPITAL_ID=VS_HOSPITAL) C
WHERE A.ICD=B.ICD AND
A.YWLB='1' AND
A.HOSPITAL_ID=VS_HOSPITAL
GROUP BY B.DISEASE,C.ZJ
having SUM(a.tcsjzf)/C.ZJ >0; BEGIN
Result := '';
FOR I IN GETTSBBL LOOP
ll_bl := ll_bl + i.bl;
--注释部分如何获得最后一行
/* if @@fetch_status=0 then
i.bl=1-ll_bl;
fetch last from GETTSBBL into i.bl ;
end if;*/
Result := Result || i.disease ||to_char(i.bl) ||' ';
END LOOP;
return Result;
END;
我也想知道正确的简单的方法
学习一下