CREATE OR REPLACE FUNCTION money_to_chinese (money IN VARCHAR2) RETURN VARCHAR2 IS c_money VARCHAR2 (12); m_string VARCHAR2 (60) := '分角圆拾佰仟万拾佰仟亿'; n_string VARCHAR2 (40) := '壹贰叁肆伍陆柒捌玖'; b_string VARCHAR2 (80); n CHAR; len NUMBER (3); i NUMBER (3); tmp NUMBER (12); is_zero BOOLEAN; z_count NUMBER (3); l_money NUMBER; l_sign VARCHAR2 (10); BEGIN l_money := ABS (money); IF money < 0 THEN l_sign := '负'; ELSE l_sign := ''; END IF; tmp := ROUND (l_money, 2) * 100; c_money := RTRIM (LTRIM (TO_CHAR (tmp, '999999999999'))); len := LENGTH (c_money); is_zero := TRUE; z_count := 0; i := 0; WHILE i < len LOOP i := i + 1; n := SUBSTR (c_money, i, 1 ); IF n = '0' THEN IF len - i = 6 OR len - i = 2 OR len = i THEN IF is_zero THEN b_string := SUBSTR (b_string, 1, LENGTH (b_string) - 1 ); is_zero := FALSE; END IF; IF len - i = 6 THEN b_string := b_string || '万'; END IF; IF len - i = 2 THEN b_string := b_string || '圆'; END IF; IF len = i THEN b_string := b_string || '整'; END IF; z_count := 0; ELSE IF z_count = 0 THEN b_string := b_string || '零'; is_zero := TRUE; END IF; z_count := z_count + 1; END IF; ELSE b_string := b_string || SUBSTR (n_string, TO_NUMBER (n), 1 ) || SUBSTR (m_string, len - i + 1, 1 ); z_count := 0; is_zero := FALSE; END IF; END LOOP; b_string := l_sign || b_string; RETURN b_string; EXCEPTION WHEN OTHERS THEN RETURN (SQLERRM); END;/ 使用方法
SELECT money_to_chinese(ssje) as dx FROM ywdjb WHERE djh='02x1995052119950140'