不需要任何cursor循环
第一个函数可以优化成:
CREATE OR REPLACE FUNCTION SB_GETUSEFEEBYEQUID (
m_dep VARCHAR2,
m_equid VARCHAR2,
m_starttime DATE,
m_endtime DATE
)
RETURN NUMBER IS
retvalue NUMBER;
BEGIN
SELECT (allusetime - hangupusetime) / 365
INTO retvalue
FROM (SELECT SUM(LEAST(NVL(handontime, m_endtime), m_endtime) - fetchtime) allusetime
FROM sbequfetchhandon
WHERE equid = m_equid AND dep = m_dep AND fetchtime >= m_starttime),
(SELECT SUM(LEAST (NVL(reusedate, m_endtime), m_endtime) - hangupdate) hangupusetime
FROM sbuseapplyrow r,
sbuseapplyform f
WHERE equipmentid = m_equid
AND (r.hangupformid = f.ID OR r.reuseformid = f.ID)
AND dep = m_dep
AND hanguptime >=
(SELECT MIN (fetchtime)
FROM sbequfetchhandon
WHERE equid = m_equid
AND dep = m_dep
AND fetchtime >= m_starttime));
END;
/第二个函数只要一句sql就可以完成:
SELECT SUM ( decrate
* sb_getusefeebyequid (m_dep, ID, starttime, endtime)
* addprice)
FROM sbequipment
WHERE dep = m_dep;
第一个函数可以优化成:
CREATE OR REPLACE FUNCTION SB_GETUSEFEEBYEQUID (
m_dep VARCHAR2,
m_equid VARCHAR2,
m_starttime DATE,
m_endtime DATE
)
RETURN NUMBER IS
retvalue NUMBER;
BEGIN
SELECT (allusetime - hangupusetime) / 365
INTO retvalue
FROM (SELECT SUM(LEAST(NVL(handontime, m_endtime), m_endtime) - fetchtime) allusetime
FROM sbequfetchhandon
WHERE equid = m_equid AND dep = m_dep AND fetchtime >= m_starttime),
(SELECT SUM(LEAST (NVL(reusedate, m_endtime), m_endtime) - hangupdate) hangupusetime
FROM sbuseapplyrow r,
sbuseapplyform f
WHERE equipmentid = m_equid
AND (r.hangupformid = f.ID OR r.reuseformid = f.ID)
AND dep = m_dep
AND hanguptime >=
(SELECT MIN (fetchtime)
FROM sbequfetchhandon
WHERE equid = m_equid
AND dep = m_dep
AND fetchtime >= m_starttime));
END;
/第二个函数只要一句sql就可以完成:
SELECT SUM ( decrate
* sb_getusefeebyequid (m_dep, ID, starttime, endtime)
* addprice)
FROM sbequipment
WHERE dep = m_dep;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货