create or replace function Func1(p_num in number default null)
return varchar2
is
Result varchar2(100);
num_round varchar2(100) :=to_char(abs(round(p_num,2)));
--小数点后保留两位(四舍五入)
num_left varchar2(100);
num_right varchar2(2);
str1 char(20) :='零壹贰叁肆伍陆柒捌玖';
str2 char(30) :='分角元拾佰仟万拾佰仟亿拾佰千万';
num_pre number(1):=1;
num_current number(1);--用于从str1中取字符
num_count number:=2;--用于从str2中取字符
begin
--如果是空就返回
if p_num is null
then return null;
end if;
select to_char(nvl(substr(num_round,1,
decode(instr(num_round,'.'),0,length(num_round),instr(num_round,'.')-1)),0))
--0表示没有找到,说明没有小数
into num_left from dual;
select substr(num_round,
decode(instr(num_round,'.'),0,
length(num_round)+1,instr(num_round,'.')+1),2)
into num_right from dual;
--从小数点后取两位,如果没有小数部分,取两位返回值应为空
if length(num_left)>13 then return '您输入的数字太大,无法转换!';
--如果整数数字大于13位,返回**********
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-3,4)=0 then--第5位、9位
result:=substr(str2,num_count,1)||result;
num_pre:=0;
end if;
end if;
end loop;
--处理小数
--角
--num_count number:=1;
num_current:=to_number(nvl(substr(num_right,1,1),0));
result:=result||substr(str1,num_current+1,1)||substr(str2,2,1);
--分
num_current:=to_number(nvl(substr(num_right,2,1),0));
result:=result||substr(str1,num_current+1,1)||substr(str2,1,1);
--负数
if p_num<0 then
result:='负'||result;
end if;
return Result;
exception
when others then
dbms_output.put_line('转换出现错误!');
raise_application_error(-20001,'转换出现错误!');
end Func1;
/
Oracle中用pl/sql实现货币数字格式到汉字格式的转化
最新推荐文章于 2021-10-15 10:57:13 发布