人民大寫轉換

create or replace function F_upper_money(p_num in number default null)
return nvarchar2 is

Result nvarchar2(100);
num_round nvarchar2(100) :=to_char(abs(round(p_num,2)));
num_left nvarchar2(100);
num_right nvarchar2(2);
str1 nchar(10) :='零壹貳叄肆伍陸柒捌玖';
str2 nchar(16) :='元拾佰仟萬拾佰仟億拾佰仟萬拾佰仟';
num_pre number(1):=1;
num_current number(1);
num_count number:=0;

begin
if p_num is null then return null;end if;

select to_char(
nvl(substr(to_char(num_round),1,
decode(instr(to_char(num_round),'.'),0,
length(num_round),instr(to_char(num_round),'.')-1)),
0)) into num_left from dual;
select substr(to_char(num_round),
decode(instr(to_char(num_round),'.'),0,
length(num_round)+1,instr(to_char(num_round),'.')+1),2)
into num_right from dual;

if length(num_left)>16 then return '**********'; end if;

if length(num_right)=2 then
if to_number(substr(num_right,1,1))=0 then
result:='零'||substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
else
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角'||
substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
end if;
elsif length(num_right)=1 then
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角整';
else
result :='整';
end if;

for i in reverse 1..length(num_left) loop
num_count:=num_count+1;
num_current:=to_number(substr(num_left,i,1));
if num_current>0 then
result:=substr(str1,num_current+1,1)||substr(str2,num_count,1)||result;
else
if mod(num_count-1,4)=0 then
result:=substr(str2,num_count,1)||result;
num_pre:=0;
end if;
if num_pre>0 or length(num_left)=1 then
result:=substr(str1,num_current+1,1)||result;
end if;
end if;
num_pre:=num_current;
end loop;

if p_num<0 then
result:='負'||result;
end if;

return Result;

exception
when others then
raise_application_error(-20001,'數字轉換大寫出現錯誤!'||sqlerrm);
end ;