oracle转金额,ORACLE金额转换成英文大写的函数

用法如下：get_capital_money(Currency, Money) Currency： 货币或货币描述，将放在英文大写的前面； Money：金额。支持两位小数点。如果需要更多的小数点，请自行修改。

create or replace function GET_CAPITAL_MONEY (

P_currency varchar2,

P_money number)

return varchar2 IS

type DIME is table of varchar2(20) index by binary_integer;

numberstr DIME;

degratstr DIME;

convertm DIME;

tmpstr varchar2(200);

T_money_int varchar2(10);

i int;

Len_T_money_int int;

T_money_dec varchar2(2);

R_value varchar2(1000) := '';

NUM number;

begin

numberstr(1) :='zero';

numberstr(2) :='one';

numberstr(3) :='two';

numberstr(4) :='three';

numberstr(5) :='four';

numberstr(6) :='five';

numberstr(7) :='six';

numberstr(8) :='seven';

numberstr(9) :='eight';

numberstr(10) :='nine';

numberstr(11) :='ten';

numberstr(12) :='eleven';

numberstr(13) :='twelve';

numberstr(14) :='thirteen';

numberstr(15) :='fourteen';

numberstr(16) :='fifteen';

numberstr(17) :='sixteen';

numberstr(18) :='seventeen';

numberstr(19) :='eighteen';

numberstr(20) :='nineteen';

numberstr(21) :='';

numberstr(22) :='';

numberstr(23) :='twenty';

numberstr(24) :='thirty';

numberstr(25) :='forty';

numberstr(26) :='fifty';

numberstr(27) :='sixty';

numberstr(28) :='seventy';

numberstr(29) :='eighty';

numberstr(30) :='ninety';

degratstr(1) :='thousand';

degratstr(2) :='million';

degratstr(3) :='billion';

degratstr(4) :='trillion';

num := P_money;

if num = 0 then

R_value := 'ZERO';

end if;

T_money_int := trim(to_char(num,'9999999999'));

T_money_dec := substr(TRIM(to_char(num,'9999999999.99')),length(T_money_int)+2,2);

select decode(mod(length(T_money_int),3),1,'00'||T_money_int,2,'0'||T_money_int,T_money_int) into T_money_int from dual;

Len_T_money_int := length(T_money_int);

i := 0;

while i < Len_T_money_int loop

i := i+3;

convertm(1) := SUBSTR(T_money_int,Len_T_money_int-i+1,1);

convertm(2) := SUBSTR(T_money_int,Len_T_money_int-i+2,1);

convertm(3) := SUBSTR(T_money_int,Len_T_money_int-i+3,1);

tmpstr := '';

if convertm(1) <> '0' then

tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(1))+1)||' hundred');

end if;

if convertm(2) = '1' then

tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(3))+11));

else

if convertm(2) <> '0' then

if convertm(3) = '0' then

tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(2))+21));

else

tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(2))+21)||'-'||numberstr(to_number(convertm(3))+1));

end if;

else

if convertm(3) <> '0' then

tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(3))+1));

end if;

end if;

end if;

if i = 3 then

R_value := trim(tmpstr||' '||R_value);

else

R_value := trim(tmpstr||' '||degratstr(i/3-1)||' '||R_value);

end if;

end loop;

tmpstr := '';

if to_number(T_money_dec)>0 then

if substr(T_money_dec,1,1) = '0' then

tmpstr := 'and '||substr(T_money_dec,2,1)||'/100';

else

tmpstr := 'and '||t_money_dec||'/100';

end if;

end if;

if length(tmpstr)<1 then

R_value := upper(R_value||' only.');

else

R_value := upper(R_value||' '||tmpstr||' only.');

end if;

return P_currency||' '||R_value;

end;