create or replace function Fn_GetMoneyToChina(p_money in number) return varchar2 is
/*
功能:数字金额转化为汉字金额
最大支持金额 9999999999999999.99 (支持千万亿的任何金额)
小数点支持2位
*/
type myArray is table of varchar2(255);
n_str myArray := myArray('壹','贰','叁','肆','伍','陆','柒','捌','玖','零');
u_str myArray := myArray('分','角','圆','拾','佰','仟','万','拾','佰','仟','亿','拾','佰','仟','万','拾','佰','仟');
signal varchar2(2) := null;
cur_digit number(1) := 0;
pre_digit number(1) := 0;
str_length number(2) := 0;
w_cash varchar2(20);
l_return varchar2(200);
begin
if p_money < 0 then
signal := '负';
end if;
if p_money = 0 then
return '零';
end if;
w_cash := to_char(abs(p_money)*100);
str_length := length(w_cash);
for i in 1..str_length loop
cur_digit := to_number(substr(w_cash,str_length - i + 1,1));
if i = 3 and l_return is null then
l_return := '整';
end if ;
if i > 2 or cur_digit <> 0 then
if cur_digit = 0 then
if i = 3 or i = 7 or i = 11 then
l_return := u_str(i)||l_return;
elsif pre_digit <> 0 then
l_return := '零'||l_return;
end if;
else
l_return := n_str(cur_digit)||u_str(i)||l_return;
end if;
end if;
pre_digit := cur_digit;
end loop;
if instr(l_return,'万',1) - instr(l_return,'亿',1) = 1 then
l_return := replace(l_return,'万',null);
end if;
l_return := signal||l_return;
return(l_return);
exception
when others then
l_return := ' ';
return l_return;
end Fn_GetMoneyToChina;