ORACLE金额转换成英文大写的函数

最新推荐文章于 2021-04-04 17:07:55 发布

范永强

最新推荐文章于 2021-04-04 17:07:55 发布

阅读量2.8k

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/fyq891014/article/details/42008313

版权

数据库专栏收录该内容

15 篇文章 0 订阅

订阅专栏

用法如下：get_capital_money(Currency, Money)
Currency：货币或货币描述，将放在英文大写的前面；
Money：金额。支持两位小数点。如果需要更多的小数点，请自行修改。

create or  replace function GET_CAPITAL_MONEY (
           P_currency varchar2,
           P_money number) 
           return varchar2 IS
      type DIME is table of varchar2(20) index by binary_integer;
      numberstr DIME;
      degratstr DIME;
      convertm DIME;
      tmpstr      varchar2(200);
      T_money_int   varchar2(10);
      i             int;
      Len_T_money_int int;
      T_money_dec   varchar2(2);
      R_value   varchar2(1000) := '';
      NUM       number;       
      
begin
    numberstr(1) :='zero';
    numberstr(2) :='one';
    numberstr(3) :='two';
    numberstr(4) :='three';
    numberstr(5) :='four';
    numberstr(6) :='five';
    numberstr(7) :='six';
    numberstr(8) :='seven';
    numberstr(9) :='eight';
    numberstr(10) :='nine';

    numberstr(11) :='ten';
    numberstr(12) :='eleven';
    numberstr(13) :='twelve';
    numberstr(14) :='thirteen';
    numberstr(15) :='fourteen';
    numberstr(16) :='fifteen';
    numberstr(17) :='sixteen';
    numberstr(18) :='seventeen';
    numberstr(19) :='eighteen';
    numberstr(20) :='nineteen';

    numberstr(21) :='';
    numberstr(22) :='';
    numberstr(23) :='twenty';
    numberstr(24) :='thirty';
    numberstr(25) :='forty';
    numberstr(26) :='fifty';
    numberstr(27) :='sixty';
    numberstr(28) :='seventy';
    numberstr(29) :='eighty';
    numberstr(30) :='ninety';
    degratstr(1) :='thousand';
    degratstr(2) :='million';
    degratstr(3) :='billion';
    degratstr(4) :='trillion';
    num := P_money;
    if num = 0 then
       R_value := 'ZERO';
    end if;
    T_money_int := trim(to_char(num,'9999999999'));
    T_money_dec := substr(TRIM(to_char(num,'9999999999.99')),length(T_money_int)+2,2);
    select decode(mod(length(T_money_int),3),1,'00'||T_money_int,2,'0'||T_money_int,T_money_int) into T_money_int from dual;
    Len_T_money_int := length(T_money_int);
    i := 0;
    while i < Len_T_money_int loop
          i := i+3; 
          convertm(1) := SUBSTR(T_money_int,Len_T_money_int-i+1,1);
          convertm(2) := SUBSTR(T_money_int,Len_T_money_int-i+2,1);
          convertm(3) := SUBSTR(T_money_int,Len_T_money_int-i+3,1);
          tmpstr := '';
          if convertm(1) <> '0' then
             tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(1))+1)||' hundred');
          end if;
          if convertm(2) = '1' then
             tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(3))+11));
          else
              if convertm(2) <> '0' then
                 if convertm(3) = '0' then
                    tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(2))+21));
                 else
                    tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(2))+21)||'-'||numberstr(to_number(convertm(3))+1));
                 end if;
              else
                 if convertm(3) <> '0' then
                    tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(3))+1));
                 end if;
              end if;
          end if;
          if i = 3 then
              R_value := trim(tmpstr||' '||R_value);
          else
              R_value := trim(tmpstr||' '||degratstr(i/3-1)||' '||R_value);
          end if;
    end loop;
    tmpstr := '';
    if to_number(T_money_dec)>0 then
       if substr(T_money_dec,1,1) = '0' then
          tmpstr := 'and '||substr(T_money_dec,2,1)||'/100';
       else
          tmpstr := 'and '||t_money_dec||'/100';
       end if;
    end if;
    if length(tmpstr)<1 then
       R_value := upper(R_value||' only.');
    else
       R_value := upper(R_value||' '||tmpstr||' only.');
    end if;
    return P_currency||' '||R_value;
end;