oracle中有一个将数字金额转换为中文形式的内部函数 money_to_chinese
此方法出处:http://hi.baidu.com/hangdoudou/blog/item/82e2c36ec001ecde80cb4a65.html
- 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;