oracle负数金额大写,Oracle 小写金额转换为大写

在开发EBS的合同报表打印的时候需要将小写金额转换为大写。

如下是本人自己写的转换函数。

主要思路：先获取小数点位置，在区分整数与小数点处理，根据位数和数字组合读取金额。

最后再处理特殊显示部分。

/*******************************************************************

*  FUNCTION get_big_amount 数字金额转换为大写

*  p_amount 输入数据金额

*  返回大写金额，位数 ：千亿----厘

********************************************************************/

FUNCTION get_big_amount(p_amount IN NUMBER) RETURN VARCHAR2

IS

smallmoney VARCHAR2(25);

bigwrite   VARCHAR2(200);

bignum     VARCHAR2(4);

rmb        VARCHAR2(4);

moneyplace NUMBER;

dotplace   NUMBER;

moneynum   NUMBER;

BEGIN

IF p_amount < 0 OR p_amount > 999999999999.9999 then  -- 0 - 千亿

bigwrite := ‘转换金额超出计算范围.‘;

RETURN bigwrite;

END IF;

IF p_amount = 0 THEN

bigwrite := ‘零圆.‘;

RETURN bigwrite;

END IF;

smallmoney := p_amount;

dotplace := instr(smallmoney,‘.‘);--得出小数点的位置

IF dotplace = 0 THEN

moneyplace := length(smallmoney);--整数的位数

ELSE

moneyplace := dotplace - 1;

END IF;

FOR moneynum IN 1..length(smallmoney) LOOP

IF moneynum < dotplace THEN             --有小数点的前半部分处理

CASE (substr(smallmoney,moneynum,1))  --从左往右，高位开始读，判断转换数字

WHEN ‘1‘ THEN bignum := ‘壹‘;

WHEN ‘2‘ THEN bignum := ‘贰‘;

WHEN ‘3‘ THEN bignum := ‘叁‘;

WHEN ‘4‘ THEN bignum := ‘肆‘;

WHEN ‘5‘ THEN bignum := ‘伍‘;

WHEN ‘6‘ THEN bignum := ‘陆‘;

WHEN ‘7‘ THEN bignum := ‘柒‘;

WHEN ‘8‘ THEN bignum := ‘捌‘;

WHEN ‘9‘ THEN bignum := ‘玖‘;

WHEN ‘0‘ THEN bignum := ‘零‘;    --‘零’

END CASE;

CASE moneyplace                  --高位开始读，判断位数

WHEN 1  THEN rmb := ‘圆‘;

WHEN 2  THEN rmb := ‘拾‘;

WHEN 3  THEN rmb := ‘佰‘;

WHEN 4  THEN rmb := ‘仟‘;

WHEN 5  THEN rmb := ‘萬‘;

WHEN 6  THEN rmb := ‘拾‘;

WHEN 7  THEN rmb := ‘佰‘;

WHEN 8  THEN rmb := ‘仟‘;

WHEN 9  THEN rmb := ‘亿‘;  --亿

WHEN 10 THEN rmb := ‘拾‘;

WHEN 11 THEN rmb := ‘佰‘;

WHEN 12 THEN rmb := ‘仟‘;  --千亿

ELSE null;

END CASE;

moneyplace := moneyplace - 1;           --位数递减

bigwrite := bigwrite || bignum || rmb;  --组合读取

ELSIF moneynum = dotplace THEN

bigwrite := bigwrite || ‘‘;   --‘点’

ELSE                                    --无小数点全整数处理 与 有小数点的后半部分处理

CASE (substr(smallmoney,moneynum,1))

WHEN ‘1‘ THEN bignum := ‘壹‘;

WHEN ‘2‘ THEN bignum := ‘贰‘;

WHEN ‘3‘ THEN bignum := ‘叁‘;

WHEN ‘4‘ THEN bignum := ‘肆‘;

WHEN ‘5‘ THEN bignum := ‘伍‘;

WHEN ‘6‘ THEN bignum := ‘陆‘;

WHEN ‘7‘ THEN bignum := ‘柒‘;

WHEN ‘8‘ THEN bignum := ‘捌‘;

WHEN ‘9‘ THEN bignum := ‘玖‘;

WHEN ‘0‘ THEN bignum := ‘零‘;  --‘零’

END CASE;

CASE moneyplace

WHEN -2 THEN rmb := ‘厘‘;

WHEN -1 THEN rmb := ‘分‘;

WHEN 0  THEN rmb := ‘角‘;

WHEN 1  THEN rmb := ‘圆‘;

WHEN 2  THEN rmb := ‘拾‘;

WHEN 3  THEN rmb := ‘佰‘;

WHEN 4  THEN rmb := ‘仟‘;

WHEN 5  THEN rmb := ‘萬‘;

WHEN 6  THEN rmb := ‘拾‘;

WHEN 7  THEN rmb := ‘佰‘;

WHEN 8  THEN rmb := ‘仟‘;

WHEN 9  THEN rmb := ‘亿‘;  --亿

WHEN 10 THEN rmb := ‘拾‘;

WHEN 11 THEN rmb := ‘佰‘;

WHEN 12 THEN rmb := ‘仟‘;  --千亿

ELSE rmb := null;

END CASE;

moneyplace := moneyplace - 1;

bigwrite := bigwrite || bignum||rmb;

END IF;

END LOOP;

--大写显示处理

IF bigwrite IS NOT NULL THEN

bigwrite := REPLACE(bigwrite, ‘零拾‘, ‘零‘);

bigwrite := REPLACE(bigwrite, ‘零佰‘, ‘零‘);

bigwrite := REPLACE(bigwrite, ‘零仟‘, ‘零‘);

bigwrite := REPLACE(bigwrite, ‘零零零‘, ‘零‘);

bigwrite := REPLACE(bigwrite, ‘零零‘, ‘零‘);

--  bigwrite := REPLACE(bigwrite, ‘零角零分零厘‘, ‘整‘);

--  bigwrite := REPLACE(bigwrite, ‘零厘‘, ‘整‘);

bigwrite := REPLACE(bigwrite, ‘零角‘, ‘零‘);

bigwrite := REPLACE(bigwrite, ‘零亿零萬零圆‘, ‘亿圆‘);

bigwrite := REPLACE(bigwrite, ‘亿零萬零圆‘, ‘亿圆‘);

bigwrite := REPLACE(bigwrite, ‘零亿零萬‘, ‘亿‘);

bigwrite := REPLACE(bigwrite, ‘零萬零圆‘, ‘萬圆‘);

bigwrite := REPLACE(bigwrite, ‘萬零圆‘, ‘萬圆‘);

bigwrite := REPLACE(bigwrite, ‘零亿‘, ‘亿‘);

bigwrite := REPLACE(bigwrite, ‘零萬‘, ‘萬‘);

bigwrite := REPLACE(bigwrite, ‘零圆‘, ‘圆‘);

bigwrite := REPLACE(bigwrite, ‘零零‘, ‘零‘);

END IF;

--壹元以下处理

bigwrite := LTRIM(LTRIM(LTRIM(LTRIM(LTRIM(bigwrite, ‘圆‘), ‘零‘), ‘角‘), ‘分‘), ‘厘‘);

IF bigwrite = ‘圆‘ THEN

bigwrite := ‘零圆‘;

END IF;

RETURN(bigwrite);

END get_big_amount;

--------------------------------------------------------------------

原文：http://blog.csdn.net/zfj0597/article/details/51360652