SQL> create or replace
2 function spell_number( p_number in number )
3 return varchar2
4 -- original by Tom Kyte
5 -- modified to include decimal places
6 as
7 type myArray is table of varchar2(255);
8 l_str myArray := myArray( '',
9 ' thousand ', ' million ',
10 ' billion ', ' trillion ',
11 ' quadrillion ', ' quintillion ',
12 ' sextillion ', ' septillion ',
13 ' octillion ', ' nonillion ',
14 ' decillion ', ' undecillion ',
15 ' duodecillion ' );
16 l_num varchar2(50) default trunc( p_number );
17 l_return varchar2(4000);
18 begin
19 for i in 1 .. l_str.count
20 loop
21 exit when l_num is null;
22
23 if ( substr(l_num, length(l_num)-2, 3) <> 0 )
24 then
25 l_return := to_char(
26 to_date(
27 substr(l_num, length(l_num)-2, 3),
28 'J' ),
29 'Jsp' ) || l_str(i) || l_return;
30 end if;
31 l_num := substr( l_num, 1, length(l_num)-3 );
32 end loop;
33
34 -- beginning of section added to include decimal places:
35 if to_char( p_number ) like '%.%'
36 then
37 l_num := substr( p_number, instr( p_number, '.' )+1 );
38 if l_num > 0
39 then
40 l_return := l_return || ' point';
41 for i in 1 .. length (l_num)
42 loop
43 exit when l_num is null;
44 if substr( l_num, 1, 1 ) = '0'
45 then
46 l_return := l_return || ' zero';
47 else
48 l_return := l_return
49 || ' '
50 || to_char(
51 to_date(
52 substr( l_num, 1, 1),
53 'j' ),
54 'jsp' );
55 end if;
56 l_num := substr( l_num, 2 );
57 end loop;
58 end if;
59 end if;
60 -- end of section added to include decimal places
61
62 return l_return;
63 end spell_number;
64 /
网上找到的一个