ORACLE 小写金额转大写金额

Create Or Replace Function Money2Chinese(Money In Number) Return Varchar2 Is
  strYuan       Varchar2(150);
  strYuanFen    Varchar2(152);
  numLenYuan    Number;
  numLenYuanFen Number;
  strRstYuan    Varchar2(600);
  strRstFen     Varchar2(200);
  strRst        Varchar2(800);
  Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer;
  tabNumMapping  typeTabMapping;
  tabUnitMapping typeTabMapping;
  numUnitIndex   Number;
  i              Number;
  j              Number;
  charCurrentNum Char(1);
Begin
  If Money Is Null Then
    Return Null;
  End If;
  strYuan := TO_CHAR(FLOOR(Money));
  If strYuan = '0' Then
    numLenYuan := 0;
    strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');
  Else
    numLenYuan := length(strYuan);
    strYuanFen := TO_CHAR(FLOOR(Money * 100));
  End If;
  If strYuanFen = '0' Then
    numLenYuanFen := 0;
  Else
    numLenYuanFen := length(strYuanFen);
  End If;
  If numLenYuan = 0 Or numLenYuanFen = 0 Then
    strRst := '零圆整';
    Return strRst;
  End If;
  tabNumMapping(0) := '零';
  tabNumMapping(1) := '壹';
  tabNumMapping(2) := '贰';
  tabNumMapping(3) := '叁';
  tabNumMapping(4) := '肆';
  tabNumMapping(5) := '伍';
  tabNumMapping(6) := '陆';
  tabNumMapping(7) := '柒';
  tabNumMapping(8) := '捌';
  tabNumMapping(9) := '玖';
  tabUnitMapping(-2) := '分';
  tabUnitMapping(-1) := '角';
  tabUnitMapping(1) := '';
  tabUnitMapping(2) := '拾';
  tabUnitMapping(3) := '佰';
  tabUnitMapping(4) := '仟';
  tabUnitMapping(5) := '万';
  tabUnitMapping(6) := '拾';
  tabUnitMapping(7) := '佰';
  tabUnitMapping(8) := '仟';
  tabUnitMapping(9) := '亿';
  For i In 1 .. numLenYuan Loop
    j            := numLenYuan - i + 1;
    numUnitIndex := Mod(i, 8);
    If numUnitIndex = 0 Then
      numUnitIndex := 8;
    End If;
    If numUnitIndex = 1 And i > 1 Then
      strRstYuan := tabUnitMapping(9) || strRstYuan;
    End If;
    charCurrentNum := substr(strYuan, j, 1);
    If charCurrentNum <> 0 Then
      strRstYuan := tabNumMapping(charCurrentNum) ||
                    tabUnitMapping(numUnitIndex) || strRstYuan;
    Else
      If (i = 1 Or i = 5) Then
        If substr(strYuan, j - 3, 4) <> '0000' Then
          strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
        End If;
      Else
        If substr(strYuan, j + 1, 1) <> '0' Then
          strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
        End If;
      End If;
    End If;
  End Loop;
  For i In -2 .. -1 Loop
    j              := numLenYuan - i;
    charCurrentNum := substr(strYuanFen, j, 1);
    If charCurrentNum <> '0' Then
      strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
                   strRstFen;
    End If;
  End Loop;
  If strRstYuan Is Not Null Then
    strRstYuan := strRstYuan || '圆';
  End If;
  If strRstFen Is Null Then
    strRstYuan := strRstYuan || '整';
  Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then
    strRstFen := strRstFen || '整';
  End If;
  strRst := strRstYuan || strRstFen;
  --strRst := Replace(strRst, '亿零', '亿');
  --strRst := Replace(strRst, '万零', '万');
  Return strRst;
End Money2Chinese;

 

测试SQL如下：

Select Money2Chinese(0932402934024.213) From dual;

结果：

玖仟叁佰贰拾肆亿零贰佰玖拾叁万肆仟零贰拾肆圆贰角壹分

测试通过

 

转自：http://www.cnblogs.com/Ira_Young/archive/2009/11/11/1601168.html