Oracle 小写金额转换为大写

来源：互联网发布：mac迅雷2.6.7 编辑：程序博客网时间：2024/05/21 22:56

在开发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;

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

0 0