CREATE OR REPLACE FUNCTION sb_mx_sum_yj ( pyear CHAR, pmoon CHAR, psmbm VARCHAR2,
pswjgbm VARCHAR2, pqy_id VARCHAR2 ) RETURN NUMBER IS tmpvar NUMBER; BEGIN tmpvar := 0; SELECT SUM (a.yzsj_zbmx) INTO tmpvar FROM sb_sbmx a, sb_sbzb b WHERE a.wdh_sbzb = b.wdh_sbzb AND TO_CHAR (fsrq_sbzb, 'yyyy') = pyear AND TO_CHAR (fsrq_sbzb, 'mm') = pmoon AND b.qy_id = pqy_id
AND smbm = psmbm AND swjgbm = pswjgbm GROUP BY smbm; IF tmpvar IS NULL THEN tmpvar := 0; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN tmpvar := 0; -- NULL; WHEN OTHERS THEN tmpvar := 0; -- Consider logging the error and then re-raise -- RAISE; -----去掉这个raise RETURN tmpvar; END sb_mx_sum_yj; /
最方便的就是 SELECT SUM (a.yzsj_zbmx) 改成 SELECT NVL(SUM(a.yzsj_zbmx),0)
没有仔细看,rickfeng(VC菜鸟) 不失为一个简单办法,但只能用于NUMBER型。wiler(@_@) 的写法需要多注意一下: create or replace function ... IS BEGIN ... BEGIN ... EXCEPTION when no_data_found then ... when others then ... END; return; END;这样才符合逻辑,不过内层块中的"when others then"可以不用写,反正你最外层又没有对异常进行处理。
pyear CHAR,
pmoon CHAR,
psmbm VARCHAR2,
pswjgbm VARCHAR2,
pqy_id VARCHAR2
)
RETURN NUMBER
IS
tmpvar NUMBER;
BEGIN
tmpvar := 0; SELECT SUM (a.yzsj_zbmx)
INTO tmpvar
FROM sb_sbmx a, sb_sbzb b
WHERE a.wdh_sbzb = b.wdh_sbzb
AND TO_CHAR (fsrq_sbzb, 'yyyy') = pyear
AND TO_CHAR (fsrq_sbzb, 'mm') = pmoon
AND b.qy_id = pqy_id
AND smbm = psmbm
AND swjgbm = pswjgbm
GROUP BY smbm; IF tmpvar IS NULL
THEN
tmpvar := 0;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
tmpvar := 0;
-- NULL;
WHEN OTHERS THEN
tmpvar := 0; -- Consider logging the error and then re-raise
-- RAISE; -----去掉这个raise
RETURN tmpvar;
END sb_mx_sum_yj;
/
create or replace function
...
IS
BEGIN
...
BEGIN
...
EXCEPTION
when no_data_found then
...
when others then
...
END;
return;
END;这样才符合逻辑,不过内层块中的"when others then"可以不用写,反正你最外层又没有对异常进行处理。