Oracle 人民币小写转大写

练手用用，写得很复杂 无 CREATE OR REPLACE FUNCTION PARSE_FEE_CHINESE(N_FEE IN NUMBER) RETURN VARCHAR2 AS V_CHINESE_NUMBER VARCHAR2(30) := '零壹贰叁肆伍陆柒捌玖'; V_CHINESE_POINT VARCHAR2(30) := '分角元拾佰仟万亿'; V_RESULT VARCHAR2(100) :=

练手用用，写得很复杂

CREATE OR REPLACE FUNCTION PARSE_FEE_CHINESE(N_FEE IN NUMBER)
  RETURN VARCHAR2 AS
  V_CHINESE_NUMBER VARCHAR2(30) := '零壹贰叁肆伍陆柒捌玖';
  V_CHINESE_POINT  VARCHAR2(30) := '分角元拾佰仟万亿';
  V_RESULT         VARCHAR2(100) := '';
  V_TEMP           VARCHAR2(100);
  V_FLAG           VARCHAR2(10);
  V_MAIN           VARCHAR2(40);
  V_SUB            VARCHAR2(20);
  N_TEMPFEE        NUMBER(30, 2);
  C_CH1            CHAR(1);
  C_CH2            VARCHAR2(10);
  N_LENGTH         NUMBER(10) := 0;
  I                NUMBER(10) := 0;
  J                NUMBER(10) := 0;
  T                NUMBER(10) := 0;
BEGIN
  IF N_FEE < 0 THEN
    V_FLAG    := '负';
    N_TEMPFEE := -1 * N_FEE;
  ELSE
    N_TEMPFEE := N_FEE;
    V_FLAG    := '';
  END IF;
  SELECT TRIM(TO_CHAR(ROUND(N_TEMPFEE, 2), '9999999999999999999.99'))
    INTO V_TEMP
    FROM DUAL;
  IF N_FEE = 0 THEN
    V_RESULT := '零元整';
  ELSE
    --先处理整数，再处理小数
    V_MAIN := SUBSTR(V_TEMP, 1, INSTR(V_TEMP, '.') - 1);
    V_SUB  := SUBSTR(V_TEMP, INSTR(V_TEMP, '.') + 1);


    N_LENGTH := LENGTH(V_MAIN);
    I        := N_LENGTH;
    T        := 0;
    IF V_MAIN IS NULL OR '0' = V_MAIN THEN
      V_RESULT := '零' || V_RESULT;
    END IF;
    WHILE I > 0 LOOP
      I      := I - 1;
      T      := T + 1;
      C_CH1  := SUBSTR(V_MAIN, I + 1);
      V_MAIN := SUBSTR(V_MAIN, 1, I);
      J      := TO_NUMBER(C_CH1);
      IF T = 5 OR t= 13 THEN
        V_RESULT := '万' || V_RESULT;
      ELSIF T = 9 THEN
        IF '万' = SUBSTR(V_RESULT, 1, 1) THEN
          V_RESULT := '亿' || SUBSTR(V_RESULT, 2);
        ELSE
          V_RESULT := '亿' || V_RESULT;
        END IF;
      END IF;
      IF '0' = C_CH1 THEN
        IF I <> N_LENGTH AND '零' <> C_CH2 THEN
          IF T <> 5 AND T <> 9 AND T <> 13 THEN
            V_RESULT := '零' || V_RESULT;
          ELSIF T = 5 OR T = 9 OR t=13 THEN
            V_RESULT := SUBSTR(V_RESULT, 1, 1) || '零' ||
                        SUBSTR(V_RESULT, 2);
          END IF;
          C_CH2 := SUBSTR(V_CHINESE_NUMBER, J, 1);
        END IF;
      ELSE
        J := MOD(T, 4);
        IF T <> 5 AND T <> 9 AND T <> 13 THEN
          IF J <> 1 THEN
            IF J = 0 THEN
              J := J + 4;
            END IF;
            J        := J + 2;
            V_RESULT := SUBSTR(V_CHINESE_POINT, J, 1) || V_RESULT;
          END IF;
        END IF;
        J        := TO_NUMBER(C_CH1) + 1;
        C_CH2    := SUBSTR(V_CHINESE_NUMBER, J, 1);
        V_RESULT := C_CH2 || V_RESULT;
      END IF;
    END LOOP;
    V_RESULT := V_RESULT || '元';
    IF V_SUB IS NULL OR LENGTH(TRIM(V_SUB)) <= 0 OR TO_NUMBER(V_SUB) = 0 THEN
      V_RESULT := V_RESULT || '整';
    ELSE
      C_CH1    := SUBSTR(V_SUB, 1, 1);
      J        := TO_NUMBER(C_CH1) + 1;
      C_CH2    := SUBSTR(V_CHINESE_NUMBER, J, 1);
      IF '0' = C_CH1 THEN
              V_RESULT := V_RESULT || C_CH2 ;
      ELSE
      V_RESULT := V_RESULT || C_CH2 || '角';
      END IF;
      C_CH1 := SUBSTR(V_SUB, 2, 1);
      IF '0' <> C_CH1 THEN
        J        := TO_NUMBER(C_CH1) + 1;
        C_CH2    := SUBSTR(V_CHINESE_NUMBER, J, 1);
        V_RESULT := V_RESULT || C_CH2 || '分';
      END IF;
    END IF;
  END IF;
  V_RESULT := V_FLAG || V_RESULT;
  RETURN V_RESULT;
END;