开发者社区> 流浪的猫666> 正文

ORACLE数字转换人民币大写示例分析

简介: ORACLE数字转换人民币大写示例分析,代码解析。
+关注继续查看

示例.

数字 :183066999230.68
人民币大写 :壹仟捌佰参拾亿陆仟陆佰玖拾玖万玖仟贰佰参拾圆陆角捌分

数字 :999900000000
人民币大写 :玖仟玖佰玖拾玖亿圆整

Step 1. Create Lookups: CUX_CNY_DICTIONARY
(N) Application Developer > Application > Lookups > Application Object Library
clip_image002[4]

Application Object Library Lookups
Type
CUX_CNY_DICTIONARY
Access Level

l
User

Extensible

System
Meaning
CUX_CNY_DICTIONARY
Application
Application Object Library
Description
CNY character translation

Code
Meaning
Description
Tag
From
To
Enabled
[ ]
0

*

1

*

2

*

3

*

4

*

5

*

6

*

7

*

8

*

9

*

Q

*

B

*

S

*

Y
亿

*

W

*

U

*

Z

*

J

*

F

*

Step 2. Create Function: CUX_CONVERT_CNY
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 fnd_lookup_values_vl flv
where 1 = 1

   and flv.enabled_flag = 'Y'
   and sysdate between nvl (flv.start_date_active, sysdate)
                   and nvl (flv.end_date_active, sysdate + 1)      
   and flv.lookup_code = transNum
   and flv.lookup_type = 'CUX_CNY_DICTIONARY'
   and flv.view_application_id = 0
   and flv.security_group_id = 0;

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)', 'U1') 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;
exception when others
then
fnd_file.put_line (fnd_file.output, sqlcode);
fnd_file.put_line (fnd_file.output, sqlerrm);
end;

Step 3. Apply Function: CUX_CONVERT_CNY

select cux_convert_cny (183066999230.68) cny from dual;
clip_image003[4]

select cux_convert_cny (999900000000) cny from dual;
clip_image004[4]

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Oracle各种版本下“示例数据库的创建”的创建
Oracle各种版本下“示例数据库的创建”的创建
104 0
SSM-Mybatis调用Oracle存储过程返回结果集(游标)示例
SSM-Mybatis调用Oracle存储过程返回结果集(游标)示例
368 0
+关注
流浪的猫666
文章
问答
视频
文章排行榜
最热
最新
相关电子书
更多
迁移 ORACLE 最佳实践
立即下载
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
相关镜像