阿拉伯数字转简体中文数字

这是一个可以再进一步优化的存储过程，借鉴了网上流传的金额转换代码。

  --将阿拉伯数字转为中文小数
  function araNum2CnDec(p_num number) return varchar2 is
    sdec   varchar2(50);
    i      pls_integer;
    len    pls_integer;
    sch    varchar2(20) := '〇一二三四五六七八九';
    scndec varchar2(100);
    num    pls_integer;
  begin
    sdec := to_char(p_num);
    if instr(sdec, '.') = 0 then
      return null;
    else
      sdec := substr(sdec, instr(sdec, '.') + 1);
      len  := length(sdec);
      for i in 1 .. len loop
        num    := to_number(substr(sdec, i, 1));
        scndec := scndec || substr(sch, num + 1, 1);
      end loop;
      return '〇点' || scndec;
    end if;
  end;

 

  --将阿拉伯数字转为中文整数
  function araNum2CnInt(p_num number) RETURN VARCHAR2 IS
    i      pls_integer;
    sint   varchar2(50) := ltrim(to_char(abs(p_num),
                                         '99999999999999999999999999999990'));
    len    pls_integer := length(sint);
    sch    varchar2(20) := '〇一二三四五六七八九';
    sjin   varchar2(100) := '十百千万十百千亿十百千万十百千万十百千万十百千万十百千万十百千万';
    scnint varchar2(100) := '';
    num    pls_integer;
    s_num  pls_integer := 0; --'0'起始位置  
    e_num  pls_integer := 0; --'0'结束位置  
  begin
    for i in 1 .. len loop
      num := to_number(substr(sint, i, 1));
      if num <> 0 then
        --非'0'时处理：  
        if s_num = 0 then
          --前面无零时处理
          if i = len then
            --最后一位
            scnint := scnint || substr(sch, num + 1, 1);
          else
            scnint := scnint || substr(sch, num + 1, 1) ||
                      substr(sjin, len - i, 1); --前面字符非'0', 正常联接...
          end if;
        else
          --前面有零时处理
          scnint := scnint || case
                      when s_num < 8 and s_num >= 5 and e_num <= 5 then --前面只有一个'0'  
                       '万'
                      when s_num < 12 and s_num >= 8 and e_num <= 8 then --否则(多个'0'处理)  
                       '亿'
                      when s_num >= 12 and e_num < 8 then
                       trim(lpad('  ', ((s_num - 12) / 4 + 1) * 2, '万')) || '亿'
                      when s_num >= 12 and e_num >= 8 then
                       trim(lpad('  ', ((s_num - e_num) / 4 + 1) * 2, '万'))
                    end || case
                      when s_num - e_num >= 0 then
                       '〇'
                    end;
          if i = len then
            scnint := scnint || substr(sch, num + 1, 1);
          else
            scnint := scnint || substr(sch, num + 1, 1) ||
                      substr(sjin, len - i, 1);
          end if;
        end if;
        s_num := 0; --重置为前面无零
        e_num := 0;
      else
        --遇零时处理
        if s_num = 0 then
          --当s_num = 0时'0'串起始，  
          s_num := len - i + 1; --记录开始  
          e_num := s_num; --结束位置。  
        else
          e_num := len - i + 1; --新的结束位置。  
        end if;
      end if;
   
    end loop;
    --一直0到结尾的情况
    if s_num <> 0 then
      scnint := scnint || case
                  when s_num = len then
                   '〇'
                  when s_num < 8 and s_num >= 5 then
                   '万'
                  when s_num > 8 then
                   trim(lpad('  ', trunc((s_num - 9) / 4+1) * 2, '万')) || '亿'
                end;
    end if;
    --处理"一十"开头的数字为"十"的情况
    if instr(scnint, '一十') = 1 then
      scnint := substr(scnint, 2);
    end if;
    if p_num < 0 then
      scnint := '负' || scnint;
    end if;
    RETURN scnint;
  end;

  --将阿拉伯数字转为中文数字
  function to_cnNumber(p_num number) return string is
  begin
    return araNum2CnInt(p_num) || substr(araNum2CnDec(p_num), 2);
  end;