调用自定义的oracle 函数获取不到值

[luofujiang]

--oracle函数如下：
CREATE OR REPLACE FUNCTION PJ_GDJHSSL_SCORE (mXZQDM VARCHAR2,mNF number)
RETURN int IS
vSCORE int;
BEGIN
select
case
when jh_ssl>=0.8 and jh_ssl<1.2 then 8
when (jh_ssl>=0.75 and jh_ssl<0.8) or (jh_ssl>=1.2 and jh_ssl<1.25) then 7
when (jh_ssl>=0.7 and jh_ssl<0.75) or (jh_ssl>=1.25 and jh_ssl<1.3) then 6
when (jh_ssl>=0.65 and jh_ssl<0.7) or (jh_ssl>=1.3 and jh_ssl<1.35) then 5
when (jh_ssl>=0.6 and jh_ssl<0.65) or (jh_ssl>=1.35 and jh_ssl<1.4) then 4
when (jh_ssl>=0.55 and jh_ssl<0.6) or (jh_ssl>=1.4 and jh_ssl<1.45) then 3
when (jh_ssl>=0.5 and jh_ssl<0.55) or (jh_ssl>=1.45 and jh_ssl<1.5) then 2
when (jh_ssl>=0.45 and jh_ssl<0.5) or (jh_ssl>=1.5 and jh_ssl<1.55) then 1
else 0
end as fz into vSCORE
from t_pj_gdjhssl where XZQ_DM=mXZQDM AND nf=mNF;
RETURN vSCORE;
END PJ_GDJHSSL_SCORE;

--在SQL语句中使用上面定义的函数。
select PJ_GDJHSSL_SCORE('4406',2012) from t_pj_gdjhssl where xzq_dm='4406' and nf=2012;
查询的记录中jh_ssl=0.8772，按常理说得出来的结果应该是8，但是执行的结果为空。

[fw0124]

应该返回8。

你是不是在另外一个会话中插入数据，但是没有commit?

[luofujiang]

没有在另一个会话插入数据。

[fw0124]

我试下来是返回8的。

tony@ORCL1> create table t_pj_gdjhssl(xzq_dm varchar2(100), nf int, jh_ssl int);

Table created.

tony@ORCL1> insert into t_pj_gdjhssl values('4406', 2012, 0.8772);

1 row created.

tony@ORCL1> commit;

Commit complete.

tony@ORCL1> CREATE OR REPLACE FUNCTION PJ_GDJHSSL_SCORE (mXZQDM VARCHAR2,mNF number)
  2  RETURN int IS
  3  vSCORE int;
  4  BEGIN
  5    select
  6    case
  7    when jh_ssl>=0.8 and jh_ssl<1.2 then 8
  8    when (jh_ssl>=0.75 and jh_ssl<0.8) or (jh_ssl>=1.2 and jh_ssl<1.25) then 7
  9    when (jh_ssl>=0.7 and jh_ssl<0.75) or (jh_ssl>=1.25 and jh_ssl<1.3) then 6
 10    when (jh_ssl>=0.65 and jh_ssl<0.7) or (jh_ssl>=1.3 and jh_ssl<1.35) then 5
 11    when (jh_ssl>=0.6 and jh_ssl<0.65) or (jh_ssl>=1.35 and jh_ssl<1.4) then 4
 12    when (jh_ssl>=0.55 and jh_ssl<0.6) or (jh_ssl>=1.4 and jh_ssl<1.45) then 3
 13    when (jh_ssl>=0.5 and jh_ssl<0.55) or (jh_ssl>=1.45 and jh_ssl<1.5) then 2
 14    when (jh_ssl>=0.45 and jh_ssl<0.5) or (jh_ssl>=1.5 and jh_ssl<1.55) then 1
 15    else 0
 16    end as fz into vSCORE
 17    from t_pj_gdjhssl where XZQ_DM=mXZQDM AND nf=mNF;
 18    RETURN vSCORE;
 19  END PJ_GDJHSSL_SCORE;
 20  /

Function created.

tony@ORCL1> select PJ_GDJHSSL_SCORE('4406',2012) from t_pj_gdjhssl where xzq_dm='4406' and nf=2012;

PJ_GDJHSSL_SCORE('4406',2012)
-----------------------------
                            8

tony@ORCL1>

[luofujiang]

问题已经解决了，是我这边xzq_dm字段的类型为CHAR(6)导致的。因为这个是定长字符类型，所以输入四位的4406执行没有数据。