oracle数字转换为人民币大写

最新推荐文章于 2024-06-17 14:59:25 发布

xwawa2012

最新推荐文章于 2024-06-17 14:59:25 发布

阅读量1.2k

点赞数

分类专栏： oracle

oracle 专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一个用于将阿拉伯数字转换为中文大写金额的Oracle函数实现。通过建立对照表并运用正则表达式及字符串操作，该函数能够准确地完成数字到中文金额的大写转换，适用于财务系统中货币数值的规范化展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

建表con_dmb_numtoprice

code meaning

0 零
1 壹
2 贰
3 参
4 肆
5 伍
6 陆
7 柒
8 捌
9 玖
Q 仟
B 佰
S 拾
Y 亿
W 万
U 圆
Z 整
J 角
F 分

写函数

create or replace function cux_convert_cny(num in number) return varchar2
is
l_receive_number number(14,2);
l_number varchar2(100);
l_num_length number;
l_unit varchar2(100) := 'QBSGYQBSGWQBSG.JF';
l_cny varchar2(200);
l_trans_cny varchar2(200);

function translater(transNum in varchar2) return varchar2
is
l_meaning varchar2(80);
begin
select flv.meaning
into l_meaning
from con_dmb_numtoprice flv
where flv.code = transNum;

return l_meaning;
end translater;

begin

--checking input number throw exception
select decode(num, 0, 'x', num) into l_receive_number from dual;
l_number := trim(to_char(l_receive_number, '9999,9999,9999.99'));
l_num_length := length(l_number);

--linking number and unit
for numIndex in 1..l_num_length loop
l_cny := l_cny || (substr(l_number, numIndex, 1) || substr(l_unit, -(l_num_length-numIndex+1), 1));
end loop;

--replace symbol
select regexp_replace(l_cny, ',|\.', '') into l_cny from dual;
--insert UZ to 0J0F
select regexp_replace(l_cny, '(0J0F)', 'UZ') into l_cny from dual;
--insert U to xJxF
select regexp_replace(l_cny, '([0-9]J[0-9]F)', 'U\1') into l_cny from dual;
--replace more zero to blank
select regexp_replace(l_cny, '(0Q0B0S0GW)|(0Q0B0S0G)|(0B0S0G)|(0S0G)|(0G)|(G)|(0J)|(0F)', '') into l_cny from dual;
--replace more zero to zero
select regexp_replace(l_cny, '(0Q0B0S)|(0Q0B)|(0Q)|(0B)|(0S)', '0') into l_cny from dual;

--translation
for transIndex in 1..length(l_cny) loop
l_trans_cny := l_trans_cny || translater(substr(l_cny,transIndex, 1));
end loop;

return l_trans_cny;

end;