日期:2014-05-17  浏览次数:20910 次

新手请教:oracle中怎么把数字转换成汉字?
现在有一个表,表中某一列的数据为1到1000000000(10亿)之间的随机数(字符串),需要全部替换为汉字
例如:2,110,028,568 转换为 贰拾壹亿壹千零贰万捌千伍百陆拾捌。没办法基础太差,想半天不知道怎么
整,用存储过程和sql都行..


------解决方案--------------------
SQL code
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
---------------------------------------------

贰十壹亿壹千零贰万捌千伍佰陆十捌