Home  >  Article  >  Database  >  Convert amount from upper case to lower case SQL

Convert amount from upper case to lower case SQL

大家讲道理
大家讲道理Original
2016-11-12 10:50:391550browse

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) <> &#39;0000&#39; Then
 
          strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
 
        End If;
 
      Else
 
        If substr(strYuan, j + 1, 1) <> &#39;0&#39; 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 <> &#39;0&#39; Then
 
      strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
 
                   strRstFen;
 
    End If;
 
  End Loop;
 
  If strRstYuan Is Not Null Then
 
    strRstYuan := strRstYuan || &#39;圆&#39;;
 
  End If;
 
  If strRstFen Is Null Then
 
    strRstYuan := strRstYuan || &#39;整&#39;;
 
  Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = &#39;角&#39; Then
 
    strRstFen := strRstFen || &#39;整&#39;;
 
  End If;
 
  strRst := strRstYuan || strRstFen;
 
  --strRst := Replace(strRst, &#39;亿零&#39;, &#39;亿&#39;);
 
  --strRst := Replace(strRst, &#39;万零&#39;, &#39;万&#39;);
 
  Return strRst;
 
End Money2Chinese;


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn