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

Application

Application Object Library

Description

CNY character translation

Code

Tag

From

To

Enabled

[ ]

零

*

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

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;

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

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]