转帖：小写金额转化为大写金额

将小写金额转换为大写金额
2007-03-26 13:48:35

create or replace function MoneyToEnglish(p_money in number )return varchar2 is
   type myArray is table of varchar2(255);
   l_str myArray := myArray('',' Thousand ',' Million ',
                               ' Billon ',' Trillion ',
                               ' Quadrillion ',' Quintillion ',
                               ' Sextillion ',' Septillion ',
                               ' Octillion ',' Nonillion ',
                               ' Decillion ',' Undecillion ',
                               ' Duodecillion ');

   l_num varchar2(50) default trunc(p_money);
   h_number number;
   l_return varchar2(4000);
begin
   h_number := round(p_money * 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;

   if l_return is null then
      l_return := 'Zero' ;
   end if;

   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;
   return l_return;
end MoneyToEnglish;

create or replace function MoneyToChina(p_money in number) return varchar2 is
   type myArray is table of varchar2(255);
 
    n_str myArray := myArray('壹','贰','叁','肆','伍','陆','柒','捌','玖','零');
    u_str myArray := myArray('分','角','圆','拾','佰','仟','万','拾','佰','仟','亿','拾','佰','仟'); 

    signal varchar2(2) := null;

    cur_digit    number(1) := 0;
    pre_digit    number(1) := 0;
    str_length number(2) := 0;     
  
    w_cash   varchar2(20);
    l_return     varchar2(200);
begin
   if p_money < 0 then
       signal := '负';
   end if;
 
   w_cash := to_char(abs(p_money)*100);
   str_length := length(w_cash);
 
   for i in 1..str_length loop
       cur_digit := to_number(substr(w_cash,str_length - i + 1,1));
       if i = 3 and l_return is null then
           l_return := '整';         
       end if ;
     
       if i > 2 or cur_digit <> 0 then
           if cur_digit = 0 then
              if i = 3 or i = 7 or i = 11 then
                  l_return := u_str(i)||l_return;
              elsif pre_digit <> 0 then  
                  l_return := '零'||l_return;
              end if;
           else
              l_return := n_str(cur_digit)||u_str(i)||l_return;  
           end if;
       end if;
       pre_digit := cur_digit;
   end loop;
 
   if instr(l_return,'万',1) - instr(l_return,'亿',1) = 1 then
       l_return := replace(l_return,'万',null);
   end if;
 
   l_return := signal||l_return;
 
   return(l_return);
end MoneyToChina;