ORACLE PL/SQL中把数字转中文数字
CREATE OR REPLACE FUNCTION PARSE_FEE_CHINESE(N_FEE IN NUMBER)
002
RETURN VARCHAR2 AS
003
V_CHINESE_NUMBER VARCHAR2(30) := '零壹贰叁肆伍陆柒捌玖'; www.2cto.com
004
V_CHINESE_POINT VARCHAR2(30) := '分角元拾佰仟万亿';
005
V_RESULT VARCHAR2(100) := '';
006
V_TEMP VARCHAR2(100);
007
V_FLAG VARCHAR2(10);
008
V_MAIN VARCHAR2(40);
009
V_SUB VARCHAR2(20);
010
N_TEMPFEE NUMBER(30, 2);
011
C_CH1 CHAR(1);
012
C_CH2 VARCHAR2(10);
013
N_LENGTH NUMBER(10) := 0;
014
I NUMBER(10) := 0;
015
J NUMBER(10) := 0;
016
T NUMBER(10) := 0;
017
BEGIN
018
IF N_FEE < 0 THEN
019
V_FLAG := '负';
020
N_TEMPFEE := -1 * N_FEE;
021
ELSE
022
N_TEMPFEE := N_FEE;
023
V_FLAG := '';
024
END IF;
025
SELECT TRIM(TO_CHAR(ROUND(N_TEMPFEE, 2), '9999999999999999999.99'))
026
INTO V_TEMP
027
FROM DUAL;
028
IF N_FEE = 0 THEN
029
V_RESULT := '零元整';
030
ELSE
031
--先处理整数，再处理小数
032
V_MAIN := SUBSTR(V_TEMP, 1, INSTR(V_TEMP, '.') - 1);
033
V_SUB := SUBSTR(V_TEMP, INSTR(V_TEMP, '.') + 1);
034
035
036
N_LENGTH := LENGTH(V_MAIN);
037
I := N_LENGTH;
038
T := 0;
039
IF V_MAIN IS NULL OR '0' = V_MAIN THEN
040
V_RESULT := '零' || V_RESULT;
041
END IF;
042
WHILE I > 0 LOOP
043
I := I - 1;
044
T := T + 1;
045
C_CH1 := SUBSTR(V_MAIN, I + 1);
046
V_MAIN := SUBSTR(V_MAIN, 1, I);
047
J := TO_NUMBER(C_CH1);
048
IF T = 5 OR t= 13 THEN
049
V_RESULT := '万' || V_RESULT;
050
ELSIF T = 9 THEN
051
IF '万' = SUBSTR(V_RESULT, 1, 1) THEN
052
V_RESULT := '亿' || SUBSTR(V_RESULT, 2);
053
ELSE
054
V_RESULT := '亿' || V_RESULT;
055
END IF;
056
END IF;
057
IF '0' = C_CH1 THEN
058
IF I <> N_LENGTH AND '零' <> C_CH2 THEN
059
IF T <> 5 AND T <> 9 AND T <> 13 THEN
060
V_RESULT := '零' || V_RESULT;
061
ELSIF T = 5 OR T = 9 OR t=13 THEN
062
V_RESULT := SUBSTR(V_RESULT, 1, 1) || '零' ||
063
SUBSTR(V_RESULT, 2);
064
END IF;
065
C_CH2 := SUBSTR(V_CHINESE_NUMBER, J, 1);
066
END IF;
067
ELSE
068
J := MOD(T, 4);
069
IF T <> 5 AND T <> 9 AND T <> 13 THEN
070
IF J <> 1 THEN
071
IF J = 0 THEN
072
J := J + 4;
073
END IF;
074
J := J + 2;
075
V_RESULT := SUBSTR(V_CHINESE_POINT, J, 1) || V_RESULT;
076
END IF;
077
END IF;
078
J := TO_NUMBER(C_CH1) + 1;
079
C_CH2 := SUBSTR(V_CHINESE_NUMBER, J, 1);
080
V_RESULT := C_CH2 || V_RESULT;
081
END IF;
082
END LOOP;
083
V_RESULT := V_RESULT || '元';
084
IF V_SUB IS NULL OR LENGTH(TRIM(V_SUB)) <= 0 OR TO_NUMBER(V_SUB) = 0 THEN
085
V_RESULT := V_RESULT || '整';
086
ELSE
087
C_CH1 := SUBSTR(V_SUB, 1, 1);
088
J := TO_NUMBER(C_CH1) + 1;
089
C_CH2 := SUBSTR(V_CHINESE_NUMBER, J, 1);
090
IF '0' = C_CH1 THEN
091
V_RESULT := V_RESULT || C_CH2 ;
092
ELSE
093
V_RESULT := V_RESULT || C_CH2 || '角';
094
END IF;
095
C_C