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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!