Oracle SQL 金额小写转大写函数

原文链接：http://www.cnblogs.com/Ira_Young/archive/2009/11/11/1601168.html 无 Create Or Replace Function Money2Chinese(Money In Number) Return Varchar2 Is strYuan Varchar2(150); strYuanFen Varchar2(152); numLenYuan Number; numLenYuanFen Num

原文链接：http://www.cnblogs.com/Ira_Young/archive/2009/11/11/1601168.html <style> .CodeEntity .code_pieces ul.piece_anchor{width:25px;position:absolute;top:25px;left:-30px;z-index:1000;} .CodeEntity .code_pieces ul.piece_anchor li{width:25px;background: #efe;margin-bottom:2px;} .CodeEntity .code_pieces ul.piece_anchor li{border-left:3px #40AA63 solid;border-right:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li:hover{border-right:3px #40AA63 solid;border-left:3px #efe solid;} .CodeEntity .code_pieces ul.piece_anchor li a{color: #333;padding:来@源gao*daima.com搞@代#码网 3px 10px;} .CodeEntity .code_pieces .jump_to_code{visibility:hidden;position:relative;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code{visibility:visible;} .CodeEntity .code_pieces .code_piece:hover .jump_to_code a{text-decoration:none;} .CodeEntity .code_pieces h2 i{float:right;font-style:normal;font-weight:normal;} .CodeEntity .code_pieces h2 i a{font-size:9pt;background: #FFFFFF;color:#00A;padding: 2px 5px;text-decoration:none;} </style> <!—ecms

–> <!—ecms
• $velocityCount

–> <!—ecms

–>

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;

喜欢 (0)赏

[搞代码]

分享 (0)