当前位置:Gxlcms > mysql > 金额大写转小写SQL

金额大写转小写SQL

时间:2021-07-01 10:21:17 帮助过:67人阅读

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;

人气教程排行