将数字转换为中文表示的function

将数字转换为中文表示的function

Function get_number_chinese (p_number Number)
Return Varchar2 Is
l_chinese Varchar2(80);
l_wei Varchar2(20);
l_need Varchar2(80);
flag number:=0;
Begin
select TRANSLATE(p_number,'0123456789','零壹贰叁肆伍陆柒捌玖')
Into l_chinese
from dual;
Select '元十百千万十百千亿十'
Into l_wei
From dual;
For cur In 1..length(p_number) Loop
If flag=8 Then l_need:=substr(l_need,2,20);End If;


If substr(l_chinese,length(l_chinese)+1-cur,1)<>'零' Then
If 6<=flag Then l_need:=substr(l_chinese ,length(l_chinese)-cur+1,1)||substr( l_wei,cur,1)||substr(l_need,2,40); FLAG:=0;
Else
l_need:=substr(l_chinese ,length(l_chinese)-cur+1,1)||substr( l_wei,cur,1)||l_need;
FLAG:=0;
End If;
Else
If MOD(CUR,4)=1 Then l_need:=substr( l_wei,cur,1)||l_need;FLAG:=5;
Else
If FLAG=0 Or FLAG=5 Then l_need:='零'||l_need;
FLAG:=flag+1;
Else FLAG:=flag+1;
End If;
End If;
End If;
End Loop;
Return (l_need);
End ;

我自己写的（能处理小数）：

CREATE OR REPLACE FUNCTION number_to_ch(n NUMBER)
RETURN VARCHAR2 IS
TYPE tab_type IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
str_b VARCHAR2(200);
str_a VARCHAR2(100);
v_subs tab_type;
v_subs_ tab_type;
d_subs tab_type;
len NUMBER;
MAX_LEN NUMBER:=15;
DECIMAL NUMBER:=4;
i_string VARCHAR2(200);
d_string VARCHAR2(200);
string_ VARCHAR2(500):='';
BEGIN
str_b := translate(to_char(trunc(n)),'0123456789','零壹贰叁肆伍陆柒捌玖');
len := length(str_b);
str_a := translate(to_char(n-trunc(n)),'0.123456789','零点壹贰叁肆伍陆柒捌玖');

FOR i IN 1..MAX_LEN LOOP
v_subs(i) := substr(str_b,-i,1);
v_subs_(i) := substr(str_b,-i,1);
END LOOP;

FOR j IN 1..DECIMAL LOOP
d_subs(j) := substr(str_a,j,1);
END LOOP;

FOR i IN 1..len LOOP
/*v_subs(i) := substr(str,-i,1);
v_subs_(i) := substr(str,-i,1);*/
IF i=1 THEN
IF len=1 AND v_subs(i)='零' THEN
v_subs(i):='零';
ELSIF v_subs(i)='零' THEN
v_subs(i) := '';
END IF;
ELSIF i=2 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'十';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
END IF;
ELSIF i=3 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'佰';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
END IF;
ELSIF i=4 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'仟';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := '零';
ELSE
v_subs(i) := ''||'仟';
END IF;
ELSIF i=5 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'万';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
IF v_subs(6)='零' AND v_subs(7)='零' AND v_subs(8)='零' THEN
v_subs(i) := '';
ELSE
v_subs(i) := '万';
END IF;
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
IF v_subs(6)='零' AND v_subs(7)='零' AND v_subs(8)='零' THEN
v_subs(i) := '零';
ELSE
v_subs(i) := ''||'万零';
END IF;
ELSE
v_subs(i) := ''||'万';
END IF;
ELSIF i=6 THEN
IF v_subs(i)<>'零' THEN
IF v_subs_(i+1) IS NULL THEN
v_subs(i) :=''||'十';
ELSE
v_subs(i) := v_subs(i)||'十';
END IF;
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := ''||'零';
ELSE
v_subs(i) := ''||'十';
END IF;
ELSIF i=7 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'佰';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := '零';
ELSE
v_subs(i) := v_subs(i)||'佰';
END IF;
ELSIF i=8 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'仟';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := '零';
ELSE
v_subs(i) := ''||'仟';
END IF;
ELSIF i=9 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'亿';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '亿';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := ''||'亿零';
ELSE
v_subs(i) := ''||'亿';
END IF;
ELSIF i=10 THEN
IF v_subs(i)<>'零' THEN
IF v_subs_(i+1) IS NULL THEN
v_subs(i) :=''||'十';
ELSE
v_subs(i) := v_subs(i)||'十';
END IF;
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := ''||'零';
ELSE
v_subs(i) := ''||'十';
END IF;
ELSIF i=11 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'佰';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := ''||'零';
ELSE
v_subs(i) := ''||'佰';
END IF;
ELSIF i=12 THEN
IF v_subs(i)<>'零' THEN
v_subs(i) := v_subs(i)||'仟';
ELSIF v_subs(i)='零' AND v_subs_(i-1)='零' THEN
v_subs(i) := '';
ELSIF v_subs(i)='零' AND v_subs_(i-1)<>'零' THEN
v_subs(i) := '零';
ELSE
v_subs(i) := ''||'仟';
END IF;
END IF;

END LOOP;

FOR i IN REVERSE 1..len LOOP
i_string := i_STRING||v_subs(i);
END LOOP;

IF d_subs(2)<>'零' THEN
d_string := d_string||d_subs(2)||'角';
IF d_subs(3)<>'零' THEN
d_string := d_string||d_subs(3)||'分';
END IF;
END IF;

string_ := i_string||'元'||d_string;
RETURN string_;
END;
/

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

转载于:http://blog.itpub.net/8466688/viewspace-45320/