CREATE OR REPLACE FUNCTION F_PJ_GDL_SCORE(mXZQDM VARCHAR2,
mNF INT, mYF INT, mFZ FLOAT)
RETURN FLOAT IS
VSCORE FLOAT;
BEGIN
SELECT
(CASE WHEN GDL>=0.75 THEN mFZ
WHEN GDL<0.75 AND GDL>=0.70 THEN (mFZ - 1*1.5)
WHEN GDL<0.70 AND GDL>=0.65 THEN (mFZ - 2*1.5)
WHEN GDL<0.65 AND GDL>=0.60 THEN (mFZ - 3*1.5)
WHEN GDL<0.60 AND GDL>=0.55 THEN (mFZ - 4*1.5)
WHEN GDL<0.55 AND GDL>=0.55 THEN (mFZ - 5*1.5)
WHEN GDL<0.50 AND GDL>=0.55 THEN (mFZ - 6*1.5)
WHEN GDL<0.45 AND GDL>=0.55 THEN (mFZ - 7*1.5)
WHEN GDL<0.40 AND GDL>=0.55 THEN (mFZ - 8*1.5)
WHEN GDL<0.35 AND GDL>=0.30 THEN (mFZ - 9*1.5)
ELSE 0
END ) as FS INTO vSCORE FROM T_PJ_GDL
WHERE XZQ_DM=mXZQDM AND NF=mNF AND YF=mYF;
RETURN vSCORE;
END;注意SELECT 查询没问题,部知道什么原因无法返回数据的。
mNF INT, mYF INT, mFZ FLOAT)
RETURN FLOAT IS
VSCORE FLOAT;
BEGIN
SELECT
(CASE WHEN GDL>=0.75 THEN mFZ
WHEN GDL<0.75 AND GDL>=0.70 THEN (mFZ - 1*1.5)
WHEN GDL<0.70 AND GDL>=0.65 THEN (mFZ - 2*1.5)
WHEN GDL<0.65 AND GDL>=0.60 THEN (mFZ - 3*1.5)
WHEN GDL<0.60 AND GDL>=0.55 THEN (mFZ - 4*1.5)
WHEN GDL<0.55 AND GDL>=0.55 THEN (mFZ - 5*1.5)
WHEN GDL<0.50 AND GDL>=0.55 THEN (mFZ - 6*1.5)
WHEN GDL<0.45 AND GDL>=0.55 THEN (mFZ - 7*1.5)
WHEN GDL<0.40 AND GDL>=0.55 THEN (mFZ - 8*1.5)
WHEN GDL<0.35 AND GDL>=0.30 THEN (mFZ - 9*1.5)
ELSE 0
END ) as FS INTO vSCORE FROM T_PJ_GDL
WHERE XZQ_DM=mXZQDM AND NF=mNF AND YF=mYF;
RETURN vSCORE;
END;注意SELECT 查询没问题,部知道什么原因无法返回数据的。
--沒有符合這個倏件的記錄,檢查你的三個參數
WHERE XZQ_DM=mXZQDM AND NF=mNF AND YF=mYF;
XZQ_DM 是 VARCHAR2(6)我执行的时候 F_PJ_GDL_SCORE('4401',2012,9,15) 返回市空值,不知道问题出现在哪来我试了一下,我写死了,参数我写死的好像可以有值的。
--经测,没问题
SQL> create table T_PJ_GDL(XZQ_DM varchar2(20),NF int,YF int,GDL float);表已创建。SQL> insert into T_PJ_GDL values('4401',2012,9,0.7);已创建 1 行。SQL> CREATE OR REPLACE FUNCTION F_PJ_GDL_SCORE(mXZQDM VARCHAR2,
2 mNF INT, mYF INT, mFZ FLOAT)
3 RETURN FLOAT IS
4 VSCORE FLOAT;
5 BEGIN
6 SELECT
7 (CASE WHEN GDL>=0.75 THEN mFZ
8 WHEN GDL<0.75 AND GDL>=0.70 THEN (mFZ - 1*1.5)
9 WHEN GDL<0.70 AND GDL>=0.65 THEN (mFZ - 2*1.5)
10 WHEN GDL<0.65 AND GDL>=0.60 THEN (mFZ - 3*1.5)
11 WHEN GDL<0.60 AND GDL>=0.55 THEN (mFZ - 4*1.5)
12 WHEN GDL<0.55 AND GDL>=0.55 THEN (mFZ - 5*1.5)
13 WHEN GDL<0.50 AND GDL>=0.55 THEN (mFZ - 6*1.5)
14 WHEN GDL<0.45 AND GDL>=0.55 THEN (mFZ - 7*1.5)
15 WHEN GDL<0.40 AND GDL>=0.55 THEN (mFZ - 8*1.5)
16 WHEN GDL<0.35 AND GDL>=0.30 THEN (mFZ - 9*1.5)
17 ELSE 0
18 END ) as FS INTO vSCORE FROM T_PJ_GDL
19 WHERE XZQ_DM=mXZQDM AND NF=mNF AND YF=mYF;
20 RETURN vSCORE;
21 END;
22 /函数已创建。SQL> select F_PJ_GDL_SCORE('4401',2012,9,15) from dual;F_PJ_GDL_SCORE('4401',2012,9,15)
--------------------------------
13.5