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]