这种方法是笨了了一点,但绝对可以达到目的select translate(to_char(sysdate,'yyyy-mm-dd'),'0123456789','零一二三四五六七八九十') from dualselect translate('1341413414','0123456789','零一二三四五六七八九十') from dual
select translate(to_char(sysdate,'yyyy-mm-dd'),'0123456789','零一二三四五六七八九十') from dual用这种方法的话,显示的是“二零零五-一一-二八” 怎样可以改为“二零零五-十一-二十八”呢?
create or replace function f_al_to_zh(v_al in char) return varchar2 isbegin case v_al when '0' then return '零'; when '1' then return '一'; when '2' then return '二'; when '3' then return '三'; when '4' then return '四'; when '5' then return '五'; when '6' then return '六'; when '7' then return '七'; when '8' then return '八'; when '9' then return '九'; end case;
end f_al_to_zh;CREATE OR REPLACE FUNCTION F_YY_TO_ZH(V_DATE IN DATE) RETURN VARCHAR2 IS V_DATE_CHAR VARCHAR2(8); I INT; V_STR VARCHAR2(10); V_CHAR VARCHAR2(100);BEGIN V_DATE_CHAR := TO_CHAR(V_DATE, 'yyyymmdd'); --年 FOR I IN 1 .. 4 LOOP V_STR := SUBSTR(V_DATE_CHAR, I, 1); V_CHAR := V_CHAR || F_AL_TO_ZH(V_STR); END LOOP; V_CHAR := V_CHAR || '年'; --月 V_STR := SUBSTR(V_DATE_CHAR, 5, 2); IF TO_NUMBER(V_STR) > 10 THEN V_CHAR := V_CHAR || '十' || F_AL_TO_ZH(SUBSTR(V_DATE_CHAR, 6, 1)); ELSE V_CHAR := V_CHAR || F_AL_TO_ZH(SUBSTR(V_DATE_CHAR, 6, 1)); END IF; V_CHAR := V_CHAR || '月'; --日 V_STR := SUBSTR(V_DATE_CHAR, 7, 1); CASE V_STR WHEN '1' THEN V_CHAR := V_CHAR || '十'; WHEN '2' THEN V_CHAR := V_CHAR || '二十'; WHEN '3' THEN V_CHAR := V_CHAR || '三十'; END CASE; V_STR := SUBSTR(V_DATE_CHAR, 8, 1); IF V_STR > 0 THEN V_CHAR := V_CHAR || F_AL_TO_ZH(V_STR); END IF; V_CHAR := V_CHAR || '日'; RETURN V_CHAR; END F_YY_TO_ZH;
怎样可以改为“二零零五-十一-二十八”呢?
case v_al
when '0' then return '零';
when '1' then return '一';
when '2' then return '二';
when '3' then return '三';
when '4' then return '四';
when '5' then return '五';
when '6' then return '六';
when '7' then return '七';
when '8' then return '八';
when '9' then return '九';
end case;
end f_al_to_zh;CREATE OR REPLACE FUNCTION F_YY_TO_ZH(V_DATE IN DATE) RETURN VARCHAR2 IS
V_DATE_CHAR VARCHAR2(8);
I INT;
V_STR VARCHAR2(10);
V_CHAR VARCHAR2(100);BEGIN
V_DATE_CHAR := TO_CHAR(V_DATE,
'yyyymmdd'); --年
FOR I IN 1 .. 4 LOOP
V_STR := SUBSTR(V_DATE_CHAR,
I,
1);
V_CHAR := V_CHAR || F_AL_TO_ZH(V_STR);
END LOOP; V_CHAR := V_CHAR || '年';
--月
V_STR := SUBSTR(V_DATE_CHAR,
5,
2);
IF TO_NUMBER(V_STR) > 10 THEN
V_CHAR := V_CHAR || '十' || F_AL_TO_ZH(SUBSTR(V_DATE_CHAR,
6,
1));
ELSE
V_CHAR := V_CHAR || F_AL_TO_ZH(SUBSTR(V_DATE_CHAR,
6,
1));
END IF;
V_CHAR := V_CHAR || '月';
--日
V_STR := SUBSTR(V_DATE_CHAR,
7,
1); CASE V_STR
WHEN '1' THEN
V_CHAR := V_CHAR || '十';
WHEN '2' THEN
V_CHAR := V_CHAR || '二十';
WHEN '3' THEN
V_CHAR := V_CHAR || '三十';
END CASE; V_STR := SUBSTR(V_DATE_CHAR,
8,
1);
IF V_STR > 0 THEN
V_CHAR := V_CHAR || F_AL_TO_ZH(V_STR);
END IF; V_CHAR := V_CHAR || '日'; RETURN V_CHAR;
END F_YY_TO_ZH;