Case when then代替if elsif else

今天写个过程，看到多选项条件判断开始想着用多个if elsif  else 语句 ，后来一想还是用case when  then语句好一点

 

FUNCTION GET_PDF_TYPE(AS_CERTIFICATE_NO IN VARCHAR2)
  RETURN VARCHAR2 AS

  LV_ORDER_NUM        WL.T_WL_CERTIFICATE_M.ORDER_NO%TYPE; --合同号
  LV_PSR_NUM          JK.T_HIS_CONTRACT_D.PSR%TYPE; --psr码
  LV_CONTRACT_TYPE    JK.T_HIS_CONTRACT_D.ORDER_TYPE_CODE%TYPE; --合同性质
  LV_MAKE_METHOD_CODE JK.T_HIS_CONTRACT_D.MAKE_METHOD_CODE%TYPE; --制造方法代码
  LV_COUNT            NUMBER;
  LV_TG_CODE          VARCHAR2(10); --特钢公司代码 BGSW
  LV_RETURN_CODE      VARCHAR2(10);
  LV_ORDER_FLAG       VARCHAR2(2); --合同号第一位标记
BEGIN
  --取合同号
  SELECT TM.ORDER_NO
    INTO LV_ORDER_NUM
    FROM WL.T_WL_CERTIFICATE_M TM
   WHERE TM.CERTIFICATE_NO = AS_CERTIFICATE_NO;

  --取psr码，合同性质
  SELECT COUNT(*)
    INTO LV_COUNT
    FROM JK.T_HIS_CONTRACT_D TD
   WHERE TD.ORDER_NUM = LV_ORDER_NUM;
  IF LV_COUNT = 0 THEN
    RAISE_APPLICATION_ERROR('-20000',
                            '没有找到质保书对应的合同信息,合同号为: ' || LV_ORDER_NUM);
  ELSE
    SELECT TD.PSR, TD.ORDER_TYPE_CODE, TD.MAKE_METHOD_CODE
      INTO LV_PSR_NUM, LV_CONTRACT_TYPE, LV_MAKE_METHOD_CODE
      FROM JK.T_HIS_CONTRACT_D TD
     WHERE TD.ORDER_NUM = LV_ORDER_NUM;
    --特钢质保书PDF模板获取
    --通过质保书号去的特钢公司代码
    LV_TG_CODE := SUBSTR(AS_CERTIFICATE_NO, 1, 4);
    IF LV_TG_CODE = 'BGSW' THEN
      IF (SUBSTR(LV_PSR_NUM, 0, 1) = 'I' AND
         (SUBSTR(LV_PSR_NUM, 0, 2) <> 'IA')) OR LV_MAKE_METHOD_CODE <> '1' OR
         SUBSTR(LV_PSR_NUM, 0, 1) = 'J' THEN
        LV_RETURN_CODE := 'TD'; --// 板带(固定格式)
      ELSE
        LV_RETURN_CODE := 'TN'; --//长材(动态格式)
      END IF;
    ELSE
      --/非特钢质保书PDF模板获取
      LV_CONTRACT_TYPE := SUBSTR(LV_CONTRACT_TYPE, 0, 3);
      LV_ORDER_FLAG    := SUBSTR(LV_ORDER_NUM, 0, 1);
      CASE
        WHEN LV_ORDER_FLAG = 'A' THEN
          NULL;
        WHEN LV_ORDER_FLAG = 'B' THEN
          --铸坯
          IF (SUBSTR(LV_CONTRACT_TYPE, 0, 1) = 'X' AND
             SUBSTR(LV_CONTRACT_TYPE, 1, 2) = 'F') OR
             SUBSTR(LV_CONTRACT_TYPE, 0, 1) = 'T' THEN
            LV_RETURN_CODE := 'CP'; -- 次品
          ELSE
            LV_RETURN_CODE := SUBSTR(LV_ORDER_NUM, 0, 2); -- 正品
          END IF;
        WHEN LV_ORDER_FLAG = 'D' THEN
          -- 线材
          IF SUBSTR(LV_CONTRACT_TYPE, 0, 1) = 'T' THEN
            LV_RETURN_CODE := 'CP'; --次品
          ELSE
            LV_RETURN_CODE := SUBSTR(LV_ORDER_NUM, 0, 2); --正品
          END IF;
          -- I:热轧 , C : 冷轧或硅钢 N :涂镀 T: 涂镀或镀铬 O:彩涂
     
        WHEN LV_ORDER_FLAG = 'I' OR LV_ORDER_FLAG = 'C' OR
             LV_ORDER_FLAG = 'N' OR LV_ORDER_FLAG = 'T' OR
             LV_ORDER_FLAG = 'O' THEN
          -- 热轧
          IF LV_CONTRACT_TYPE = 'TCA' OR LV_CONTRACT_TYPE = 'TDA' THEN
            LV_RETURN_CODE := 'CP'; --正品
          ELSIF LV_CONTRACT_TYPE = 'TZA' THEN
            LV_RETURN_CODE := 'TH'; --/统货
          ELSE
            LV_RETURN_CODE := SUBSTR(LV_ORDER_NUM, 0, 2); --正品
          END IF;
        WHEN LV_ORDER_FLAG = 'L' THEN
          NULL;
        WHEN LV_ORDER_FLAG = 'X' THEN
          NULL;
        WHEN LV_ORDER_FLAG = 'M' THEN
          NULL;
        WHEN LV_ORDER_FLAG = 'Q' THEN
          NULL;
        WHEN LV_ORDER_FLAG = 'J' OR LV_ORDER_FLAG = 'S'  THEN
          --厚板次品单独列出  --硅钢
          IF SUBSTR(LV_CONTRACT_TYPE, 0, 1) = 'T' THEN
            LV_RETURN_CODE := 'CP'; --次品
          ELSE
            LV_RETURN_CODE := SUBSTR(LV_ORDER_NUM, 0, 2); -- 正品
          END IF;
        WHEN LV_ORDER_FLAG = 'R' THEN
          NULL;
        WHEN LV_ORDER_FLAG = 'G' THEN
          --钢管
       
          LV_RETURN_CODE := SUBSTR(LV_ORDER_NUM, 0, 2); --正品         
        ELSE
          LV_RETURN_CODE := '';
      END CASE;
   
    END IF;
  END IF;
  RETURN LV_RETURN_CODE ;

END GET_PDF_TYPE;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7551038/viewspace-614198/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7551038/viewspace-614198/