金额转换为大写金额的SQL函数实现-优快云博客

在项目开发当中，经常会遇到小写金额转换成大写的情况的。

点击(此处)折叠或打开

CREATE OR REPLACE Function print_money(pn_num_in Number) Return Varchar2 Is
g_int_var Varchar2(60) := \'\';
g_flo_var Varchar2(20) := \'\';
g_num Number;
g_ww number;
Ln_Int_Num Number;
Ln_Flo_Num Number;
g_hb_j number;
g_hb_f number;
g_num_xs number;
Type al_to_zh Is Varray(10) Of Varchar2(15);
Type t_Umint_Arr Is Varray(4) Of Varchar2(15);
al_zh_arr al_to_zh := al_to_zh(\'零\',
\'壹\',
\'贰\',
\'叁\',
\'肆\',
\'伍\',
\'陆\',
\'柒\',
\'捌\',
\'玖\');
Larr_Umint t_Umint_Arr := t_Umint_Arr(\'\', \'拾\', \'佰\', \'仟\');
Function fn_p_4(Pcn_Num Number) Return Varchar2 Is
Lcv_Res Varchar2(200);
Lcn_Len Number;
Lcn_Cur_Num Number(1);
Begin
Lcn_Len := Length(Pcn_Num);
For i In 1 .. Lcn_Len Loop
Lcn_Cur_Num := To_Number(Substr(Pcn_Num, Lcn_Len - i + 1, 1)); --当前数字
If Lcn_Cur_Num > 0 Then
Lcv_Res := al_zh_arr(Lcn_Cur_Num + 1) || Larr_Umint(i) || Lcv_Res;
Elsif Lcn_Cur_Num = 0 And i < Lcn_Len And
Substr(Lcv_Res, 1, 1) <> \'零\' Then
Lcv_Res := al_zh_arr(Lcn_Cur_Num + 1) || Lcv_Res;
End If;
End Loop;
Return Lcv_Res;
End;
Begin
Ln_Int_Num := Trunc(pn_num_in);
Ln_Flo_Num := Mod(pn_num_in, 1);
--处理整数
If Ln_Int_Num = 0 Then
g_int_var := \'零\';
Else
g_num := Trunc(Mod(Ln_Int_Num, 10000));
g_int_var := fn_p_4(g_num);
--判断是否需要加0
If g_num < 1000 And g_num > 0 and length(Ln_Int_Num) > 4 Then
g_int_var := \'零\' || g_int_var;
else
g_int_var := g_int_var;
End If;
If Length(Trunc(Ln_Int_Num)) > 4 and Length(Trunc(Ln_Int_Num)) < 8 Then
--大于万的
g_num := Trunc(Mod(Ln_Int_Num, 100000000) / 10000);
g_int_var := fn_p_4(g_num) || \'万\' || g_int_var;
End If;
--可以扩展到千万以上级别
If Length(Trunc(Ln_Int_Num)) > 8 and Length(Trunc(Ln_Int_Num)) < 12 Then
g_num := Trunc(Mod(Ln_Int_Num, 1000000000000) / 100000000);
g_ww := Trunc(Mod(Ln_Int_Num, 100000000) / 10000);
g_int_var := fn_p_4(g_num) || \'亿\' || fn_p_4(g_ww) || \'万\' || g_int_var;
End If;
End If;
--处理小数
g_num_xs := Trunc(Ln_Flo_Num * 100);
g_hb_j := Trunc(g_num_xs / 10);
g_hb_f := Mod(g_num_xs, 10);
If g_num_xs = 0 Then
Return g_int_var || \'元整\';
End If;
if Ln_Int_Num = 0 and g_hb_j = 0 and g_hb_f <> 0 then
g_flo_var := al_zh_arr(g_hb_f + 1) || \'分\';
elsif Ln_Int_Num = 0 and g_hb_j <> 0 and g_hb_f <> 0 then
g_flo_var := al_zh_arr(g_hb_j + 1) || \'角\' || al_zh_arr(g_hb_f + 1) || \'分\';
elsif Ln_Int_Num = 0 and g_hb_j <> 0 and g_hb_f = 0 then
g_flo_var := al_zh_arr(g_hb_j + 1) || \'角\';
elsif Ln_Int_Num <> 0 and g_hb_j <> 0 and g_hb_f <> 0 then
g_flo_var := al_zh_arr(g_hb_j + 1) || \'角\' ||
al_zh_arr(g_hb_f + 1) || \'分\';
elsif Ln_Int_Num <> 0 and g_hb_j=0 and g_hb_f <> 0 then
g_flo_var :=\'零\'||al_zh_arr(g_hb_f + 1) || \'分\';
end if;
return g_int_var || \'元\'||g_flo_var;
End;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29507357/viewspace-1347775/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29507357/viewspace-1347775/

金额小写转成大写的函数