CREATE OR REPLACE FUNCTION FUN_GET_YJJB(V_SCORE IN NUMBER) RETURN VARCHAR2 IS
O_YJJB VARCHAR2(1);
O_4_SCORE NUMBER;
O_3_SCORE NUMBER;
O_2_SCORE NUMBER;
O_1_SCORE NUMBER;
O_4_COLO VARCHAR2(1);
O_3_COLO VARCHAR2(1);
O_2_COLO VARCHAR2(1);
O_1_COLO VARCHAR2(1);
BEGIN
SELECT SCORE, COLOR
INTO O_1_SCORE, O_1_COLO
FROM T_PVBDP_MODEL_ALERT_LEVEL
WHERE ID = 1;
SELECT SCORE, COLOR
INTO O_2_SCORE, O_2_COLO
FROM T_PVBDP_MODEL_ALERT_LEVEL
WHERE ID = 2;
SELECT SCORE, COLOR
INTO O_3_SCORE, O_3_COLO
FROM T_PVBDP_MODEL_ALERT_LEVEL
WHERE ID = 3;
SELECT SCORE, COLOR
INTO O_4_SCORE, O_4_COLO
FROM T_PVBDP_MODEL_ALERT_LEVEL
WHERE ID = 4;
SELECT CASE
WHEN V_SCORE >= O_4_SCORE THEN
O_4_COLO
WHEN O_3_SCORE <= V_SCORE AND V_SCORE < O_4_SCORE THEN
O_3_COLO
WHEN O_2_SCORE <= V_SCORE AND V_SCORE < O_3_SCORE THEN
O_2_COLO
WHEN O_1_SCORE <= V_SCORE AND V_SCORE < O_2_SCORE THEN
O_1_COLO
ELSE
'9'
END
INTO O_YJJB
FROM DUAL;
RETURN O_YJJB;
EXCEPTION
WHEN OTHERS THEN
RETURN '9';
END;