Use Database functions to convert the lower case of RMB into the upper case of the invoice:
Create or replace function l2u -- converts the lower-case amount to uppercase.
(
N_lowermoney in number
)
Return varchar2
As
V_lowerstr varchar2 (200); -- lower case amount
V_upperpart varchar2 (200 );
V_upperstr varchar2 (200); -- Capital Amount
Begin
V_lowerstr: = ltrim (rtrim (to_char (round (n_lowermoney, 2), '192. 99 ')));
If substr (v_lowerstr, 1,1) = '# 'then
Return 'the conversion amount exceeds the calculation range (calculation range: 0-9,999,999,999,999.99 )';
End if;
For I in 1 .. length (v_lowerstr) loop
Select decode (substr (v_lowerstr, length (v_lowerstr)-I + 1, 1 ),
'.', 'Meta ',
'0', '0', '1', 'yi', '2', 'er', '3', 'san', '4', 'Ta ',
'5', 'wu', '6', 'lu', '7', 'hangzhou', '8', 'hangzhou', '9', 'hangzhou ') |
Decode (I, 1, 'mine', 2, 'angular ', 3, null, 4, null, 5, 'scuse', 6, 'hangzhou', 7, 'hangzhou', 8, 'wan ',
9. 'start', 10, 'start', 11, 'start', 12, 'yi', 13, 'start', 14, 'start', 15, 'taobao', 16, 'wan', null)
Into v_upperpart from dual;
V_upperstr: = v_upperpart | v_upperstr;
End loop;
V_upperstr: = Replace (v_upperstr, 'zero restart', 'zero ');
V_upperstr: = Replace (v_upperstr, 'zero failed', '0 ');
V_upperstr: = Replace (v_upperstr, 'zero failed', '0 ');
V_upperstr: = Replace (v_upperstr, 'zero zero 0', 'zero ');
V_upperstr: = Replace (v_upperstr, '0', '0 ');
V_upperstr: = Replace (v_upperstr, 'zero-angle and zero-point', 'Integral ');
V_upperstr: = Replace (v_upperstr, 'zero point', 'Integral ');
V_upperstr: = Replace (v_upperstr, '0', '0 ');
V_upperstr: = Replace (v_upperstr, '100, 0 billion, 0 thousand, RMB ');
V_upperstr: = Replace (v_upperstr, '100 million RMB 0 thousand ', '100 billion RMB ');
V_upperstr: = Replace (v_upperstr, '1970, 0001 ');
V_upperstr: = Replace (v_upperstr, '000000', '000000 ');
V_upperstr: = Replace (v_upperstr, '10 thousand yuan ', '10 thousand yuan ');
V_upperstr: = Replace (v_upperstr, '20140901', '661 ');
V_upperstr: = Replace (v_upperstr, '20140901', 'wan ');
V_upperstr: = Replace (v_upperstr, '0 meta', 'meta ');
V_upperstr: = Replace (v_upperstr, '0', '0 ');
-- Processing of less than 1 RMB
V_upperstr: = ltrim (v_upperstr, 'meta'), '0'), 'angular '), 'point ');
If substr (v_upperstr, 1, 1) = 'integer' then
V_upperstr: = 'zero yuan ';
End if;
Return v_upperstr;
Exception when others then
Return 'error: '| sqlcode |' -- '| sqlerrm;
End l2u;