转换英文金额

create or replace function GET_CAPITAL_MONEY(p_number number)
  return varchar2 IS
  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);
  H        NUMBER := 0;
  AA       VARCHAR2(2000);
  A2       NUMBER := 0;
  B        VARCHAR2(240);
  BB       VARCHAR2(1000);

begin
  aa := 'One      Two      Three    Four     Five     Six      Seven    ' ||
        'Eight    Nine     Ten      Eleven   Twelve   Thirteen Fourteen Fifteen  ' ||
        'Sixteen  Seventeen Eighteen Nineteen ';
  bb := 'Twenty Thirty Forty  Fifty  Sixty  Seventy Eighty Ninety ';

  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;

  if to_char(h_number) like '%.%' then
 
    l_num := substr(h_number, instr(h_number, '.') + 1);
    --DBMS_OUTPUT.PUT_LINE(l_num);
 
    if length(l_num) = 1 then
      l_num := l_num || '0';
    end if;
    if l_num <> ' ' and l_num <> '00' then
      h := TO_NUMBER(SUBSTR(l_num, 1, 2));
   
      if h < 20 then
        b := Rtrim(SUBSTR(aa, (h - 1) * 9 + 1, 9));
     
      else
        A2 := TO_NUMBER(SUBSTR(l_num, 1, 1));
        b  := Rtrim(SUBSTR(bb, (a2 - 2) * 7 + 1, 7));
        a2 := TO_NUMBER(SUBSTR(l_num, 2, 1));
        -- DBMS_OUTPUT.PUT_LINE('b - '||b);
        if a2 <> 0 then
          b := Rtrim(b || ' ' || SUBSTR(aa, (a2 - 1) * 9 + 1, 9));
        end if;
      end if;
      if TO_NUMBER(l_num) = 1 then
        L_RETURN := RTRIM(Ltrim(L_RETURN)) || ' And Cent ' || b;
      else
        L_RETURN := RTRIM(Ltrim(L_RETURN)) || ' And Cents ' || b;
      end if;
    end if;
  END IF;
  return (L_RETURN);
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13600840/viewspace-669559/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13600840/viewspace-669559/