新手请教：oracle中怎么把数字转换成汉字？
现在有一个表，表中某一列的数据为1到1000000000（10亿）之间的随机数（字符串），需要全部替换为汉字
例如：2，110，028，568 转换为 贰拾壹亿壹千零贰万捌千伍百陆拾捌。没办法基础太差，想半天不知道怎么
整，用存储过程和sql都行..
[解决办法]
lym@TDWORA> CREATE OR REPLACE FUNCTION fn_con_num1(num_in varchar) 2 RETURN VARCHAR2 3 IS 4 v_return VARCHAR2(4); 5 BEGIN 6 IF length(num_in)=1 THEN 7 SELECT decode(num_in,'0','零','1','壹','2','贰','3','参','4','肆','5','伍','6','陆','7','柒','8','捌','9','玖','') AS ret 8 INTO v_return 9 FROM dual; 10 END IF; 11 RETURN v_return; 12 EXCEPTION WHEN OTHERS THEN 13 RETURN NULL; 14 END; 15 /Function created.lym@TDWORA> CREATE OR REPLACE FUNCTION fn_con_num2(num_in number) 2 RETURN VARCHAR2 3 IS 4 v_char VARCHAR2(20); 5 v_char_cvt VARCHAR2(80); 6 BEGIN 7 v_char := lpad(to_char(num_in),10,'0'); 8 SELECT DECODE(SUBSTR(v_char,1,1),'0','',fn_con_num1(SUBSTR(v_char,1,1))||'十')|| 9 DECODE(SUBSTR(v_char,2,1),'0','',fn_con_num1(SUBSTR(v_char,2,1))||'亿')|| 10 DECODE(SUBSTR(v_char,3,1),'0','零',fn_con_num1(SUBSTR(v_char,3,1))||'千')|| 11 DECODE(SUBSTR(v_char,4,1),'0','零',fn_con_num1(SUBSTR(v_char,4,1))||'佰')|| 12 DECODE(SUBSTR(v_char,5,1),'0','零',fn_con_num1(SUBSTR(v_char,5,1))||'十')|| 13 DECODE(SUBSTR(v_char,6,1),'0','零',fn_con_num1(SUBSTR(v_char,6,1))||'万')|| 14 DECODE(SUBSTR(v_char,7,1),'0','零',fn_con_num1(SUBSTR(v_char,7,1))||'千')|| 15 DECODE(SUBSTR(v_char,8,1),'0','零',fn_con_num1(SUBSTR(v_char,8,1))||'佰')|| 16 DECODE(SUBSTR(v_char,9,1),'0','零',fn_con_num1(SUBSTR(v_char,9,1))||'十')|| 17 DECODE(SUBSTR(v_char,10,1),'0','',fn_con_num1(SUBSTR(v_char,10,1))) AS cvt 18 INTO v_char_cvt 19 FROM dual; 20 v_char_cvt:=RTRIM(LTRIM(v_char_cvt,'零'),'零'); 21 WHILE INSTR(v_char_cvt,'零零')>=1 LOOP 22 v_char_cvt:=replace(v_char_cvt,'零零','零'); 23 END LOOP; 24 RETURN v_char_cvt; 25 END; 26 /Function created.lym@TDWORA> SELECT fn_con_num2(2110028568) AS cvt FROM DUAL;CVT---------------------------------------------贰十壹亿壹千零贰万捌千伍佰陆十捌
