函数如下:
CREATE OR REPLACE FUNCTION test (mXZQDM VARCHAR2,mNF INT,mYF INT)
RETURN FLOAT IS
vSCORE FLOAT;BEGIN
SELECT
CASE
WHEN NVL(COUNT(*),0)>0 THEN 0
else 0.5
END AS sfz into vSCORE
FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)=mXZQDM AND NF=mNF AND YF=mYF;
RETURN vSCORE;
END test;
执行以下SQL语句值为0.5.
SELECT
CASE
WHEN NVL(COUNT(*),0)>0 THEN 0
else 0.5
END AS sfz
FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9;
但是在select语句中使用此函数时为空。
select test(xzq_dm,nf,yf) FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9; 按常理说结果应该是0.5,为什么结果会为空。
CREATE OR REPLACE FUNCTION test (mXZQDM VARCHAR2,mNF INT,mYF INT)
RETURN FLOAT IS
vSCORE FLOAT;BEGIN
SELECT
CASE
WHEN NVL(COUNT(*),0)>0 THEN 0
else 0.5
END AS sfz into vSCORE
FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)=mXZQDM AND NF=mNF AND YF=mYF;
RETURN vSCORE;
END test;
执行以下SQL语句值为0.5.
SELECT
CASE
WHEN NVL(COUNT(*),0)>0 THEN 0
else 0.5
END AS sfz
FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9;
但是在select语句中使用此函数时为空。
select test(xzq_dm,nf,yf) FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9; 按常理说结果应该是0.5,为什么结果会为空。
select test(SUBSTR(XZQ_DM,1,4)--你此处传的值有问题,看下你的函数的写法,应是很明显的
,nf,yf) FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9
select test(substr(xzq_dm,1,4),nf,yf) FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9;
其中SUBSTR(XZQ_DM,1,4)=mXZQDM AND NF=mNF AND YF=mYF
而你SQL语句里面的函数写的参数却是
select test(xzq_dm,nf,yf) FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9很显然你的SQL语句里面的函数传入的并不是'4402',2012,9;而是XZQ_DM,2012,9;所以函数里面的SQL语句
SELECT
CASE
WHEN NVL(COUNT(*),0)>0 THEN 0
else 0.5
END AS sfz into vSCORE
FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)=mXZQDM AND NF=mNF AND YF=mYF;
找不到满足条件的值,所以函数返回的是空的,你的查询SQL语句应该修改为
select test(substr(xzq_dm,1,4),nf,yf) FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9
select test(xzq_dm,nf,yf) FROM T_PJ_WYZD WHERE NVL(WY_ZDS,0)>10 AND SUBSTR(XZQ_DM,1,4)='4402' AND NF=2012 AND YF=9;