ORACLE 数字转换人民币大写

 

 

 

数字                    ：183066999230.68

 

数字                    ：999900000000

 

 

 

Step 1.  Create Lookups:   CUX_CNY_DICTIONARY

(N) Application Developer > Application > Lookups > Application Object Library

 

 

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)', '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;

exception when others

then

fnd_file.put_line (fnd_file.output, sqlcode);

fnd_file.put_line (fnd_file.output, sqlerrm);

end;

 

 

 

 

 

select cux_convert_cny (183066999230.68) cny from dual;

 

select cux_convert_cny (999900000000) cny from dual;