OracleSQL金額小寫轉(zhuǎn)大寫函數(shù)

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;