--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,但是执行的结果为空。
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,但是执行的结果为空。
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)
-----------------------------
8tony@ORCL1>