一个有用的东西：如何将数字转换为大写英文金额
create or replace function tcos_spell_number( p_number in number )
return varchar2
-- CopyRight Toshiba Shenzhen Han Fangquan 2002.04.10
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' Thousand ', ' Million ',
' Billion ', ' Trillion ',
' Quadrillion ', ' Quintillion ',
' Sextillion ', ' Septillion ',
' Octillion ', ' Nonillion ',
' Decillion ', ' Undecillion ',
' Duodecillion ' );
l_num varchar2(50) default trunc( p_number );
h_number number;
l_return varchar2(4000);
begin
h_number := round(p_number*100)/100;
for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-2, 3) 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'jsp' ) l_str(i) l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
-- add Dollars string. Han Fangquan
-- Han Fangquan modified begin
if l_return is null then l_return := 'Zero' ;
end if;
-- p_number 1 then add Dollars else Add Dollar.
if trunc( h_number )1
then
l_return := l_return ' Dollars ';
else
l_return := l_return ' Dollar ';
end if;
if to_char( h_number ) like '%.%'
then
l_num := substr(h_number,instr(h_number,'.')+1);
if length(l_num)=1
then
l_num := l_num '0';
end if;
if l_num 0
then
if l_num 1
then
l_return := l_return 'And 'l_num' Cents ';
else l_return := l_return 'And 'l_num' Cent ';
end if;
end if;
end if;
-- end (Han Fangquan modified )
/*
-- beginning of section added to include decimal places:
if to_char( h_number ) like '%.%'
then
l_num := substr( h_number, instr( h_number, '.' )+1 );
if l_num 0
then
l_return := l_return ' point';
for i in 1 .. length (l_num)
loop
exit when l_num is null;
if substr( l_num, 1, 1 ) = '0'
then
l_return := l_return ' zero';
else
l_return := l_return
' '
to_char(
to_date(
substr( l_num, 1, 1),
'j' ),
'jsp' );
end if;
l_num := substr( l_num, 2 );
end loop;
end if;
end if;
*/
-- end of section added to include decimal places
-- Han if the length fo l_trun less than 100 ,i will add **
-- if length(l_return)
-- then
-- l_return := l_return
-- substr('****************************************************************************************************',
-- length(l_return)-1) ;
-- end if;
l_return := l_return'*************';
return l_return;
end tcos_spell_number;