oracle创建函数程序题目,oracle 创建函数例子

CREATE OR REPLACE FUNCTION FUN_GET_YJJB(V_SCORE IN NUMBER) RETURN VARCHAR2 IS

/*********************************************************

名称 FUN_GET_YJJB

功能描述：获取预警级别

修改记录

版本号 编辑时间 编辑人 修改描述

1.0.0 2016-11-22 aoliu 创建函数

*********************************************************/

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;