[PL/SQL] 數值金額轉英文範例

很久以前google搜尋修改的數值金額轉英文範例，給友需要的朋友

create or replace function number2eng_sf(in_number in number ) return varchar2 is

type eArray is table of varchar2(255);

l_str eArray := eArray('',
' Thousand ', ' Million ',
' Billion ', ' Trillion ',
' Quadrillion ', ' Quintillion ',
' Sextillion ', ' Septillion ',
' Octillion ', ' Nonillion ',
' Decillion ', ' Undecillion ',
' Duodecillion ');

l_num varchar2(50) default trunc(in_number);
h_number number;
v_total varchar2(4000);

begin
   h_number := round(in_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
          v_total := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),'Jsp' )||l_str(i)||v_total;
       end if;
      
       l_num := substr( l_num, 1, length(l_num)-3 );
   end loop;
 
   if v_total is null then
      v_total := 'Zero' ;
   end if;

   if trunc( h_number ) > 1 then
      v_total := v_total || ' Dollars ';
   else
      v_total := v_total || ' 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
            v_total := v_total ||'And '||l_num||' Cents ';
         else
            v_total := v_total ||'And '||l_num||' Cent ';
         end if;
      end if;
   end if;

   return v_total;
end;