很2 的方法select a, case a when 1 then '一' when 2 then '二' end as x from (select 1 as a from dual union select 2 from dual)
提供一个转成英文的简单函数哈:select to_char(to_date(n,'yyyy'),'year') from dual; 比如n=2 则output=two
可以参考小写钱转换成大写钱 CREATE OR REPLACE FUNCTION smalltobig(smallmoney IN VARCHAR2) RETURN VARCHAR2 IS BigWrite VARCHAR2(54); Bignum VARCHAR2(2); RMB VARCHAR2(2); moneyplace NUMBER; dotplace NUMBER; moneynum NUMBER; myexception EXCEPTION;BEGIN dotplace := INSTR(smallmoney, '.'); IF (LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN RAISE myexception; END IF; IF dotplace = 0 THEN moneyplace := 0; ELSE moneyplace := dotplace - LENGTH(smallmoney); END IF; FOR moneynum IN REVERSE 1 .. LENGTH(smallmoney) LOOP IF moneynum <> dotplace THEN CASE SUBSTR(smallmoney, moneynum, 1) WHEN '1' THEN Bignum := '壹'; WHEN '2' THEN Bignum := '贰'; WHEN '3' THEN Bignum := '叁'; WHEN '4' THEN Bignum := '肆'; WHEN '5' THEN Bignum := '伍'; WHEN '6' THEN Bignum := '陆'; WHEN '7' THEN Bignum := '柒'; WHEN '8' THEN Bignum := '捌'; WHEN '9' THEN Bignum := '玖'; WHEN '0' THEN Bignum := '零'; END CASE; CASE moneyplace WHEN '-2' THEN RMB := '分'; WHEN '-1' THEN RMB := '角'; WHEN '0' THEN RMB := '元'; WHEN '1' THEN RMB := '拾'; WHEN '2' THEN RMB := '佰'; WHEN '3' THEN RMB := '仟'; WHEN '4' THEN RMB := '萬'; WHEN '5' THEN RMB := '拾'; WHEN '6' THEN RMB := '佰'; WHEN '7' THEN RMB := '仟'; WHEN '8' THEN RMB := '亿'; WHEN '9' THEN RMB := '拾'; WHEN '10' THEN RMB := '佰'; WHEN '11' THEN RMB := '仟'; END CASE; moneyplace := moneyplace + 1; IF BigWrite IS NULL THEN BigWrite := Bignum || RMB; ELSE BigWrite := Bignum || RMB || BigWrite; END IF; END IF; END LOOP; RETURN BigWrite; EXCEPTION WHEN myexception THEN DBMS_OUTPUT.put_line('THIS IS ONLY TO CONVERT LESS THAN 14 OR NOT GREATER THAN 12'); WHEN OTHERS THEN DBMS_OUTPUT.put_line('NOT INVALID MONEY.'); END;
http://www.iteye.com/topic/1117188
case a
when 1 then
'一'
when 2 then
'二'
end as x
from (select 1 as a
from dual
union
select 2 from dual)
比如n=2 则output=two
CREATE OR REPLACE FUNCTION smalltobig(smallmoney IN VARCHAR2) RETURN VARCHAR2 IS
BigWrite VARCHAR2(54);
Bignum VARCHAR2(2);
RMB VARCHAR2(2);
moneyplace NUMBER;
dotplace NUMBER;
moneynum NUMBER;
myexception EXCEPTION;BEGIN
dotplace := INSTR(smallmoney, '.');
IF (LENGTH(smallmoney) > 14) OR ((LENGTH(smallmoney) > 12) AND (dotplace = 0)) THEN
RAISE myexception;
END IF;
IF dotplace = 0 THEN
moneyplace := 0;
ELSE
moneyplace := dotplace - LENGTH(smallmoney);
END IF;
FOR moneynum IN REVERSE 1 .. LENGTH(smallmoney) LOOP
IF moneynum <> dotplace THEN
CASE SUBSTR(smallmoney, moneynum, 1)
WHEN '1' THEN
Bignum := '壹';
WHEN '2' THEN
Bignum := '贰';
WHEN '3' THEN
Bignum := '叁';
WHEN '4' THEN
Bignum := '肆';
WHEN '5' THEN
Bignum := '伍';
WHEN '6' THEN
Bignum := '陆';
WHEN '7' THEN
Bignum := '柒';
WHEN '8' THEN
Bignum := '捌';
WHEN '9' THEN
Bignum := '玖';
WHEN '0' THEN
Bignum := '零';
END CASE;
CASE moneyplace
WHEN '-2' THEN
RMB := '分';
WHEN '-1' THEN
RMB := '角';
WHEN '0' THEN
RMB := '元';
WHEN '1' THEN
RMB := '拾';
WHEN '2' THEN
RMB := '佰';
WHEN '3' THEN
RMB := '仟';
WHEN '4' THEN
RMB := '萬';
WHEN '5' THEN
RMB := '拾';
WHEN '6' THEN
RMB := '佰';
WHEN '7' THEN
RMB := '仟';
WHEN '8' THEN
RMB := '亿';
WHEN '9' THEN
RMB := '拾';
WHEN '10' THEN
RMB := '佰';
WHEN '11' THEN
RMB := '仟';
END CASE;
moneyplace := moneyplace + 1;
IF BigWrite IS NULL THEN
BigWrite := Bignum || RMB;
ELSE
BigWrite := Bignum || RMB || BigWrite;
END IF;
END IF;
END LOOP;
RETURN BigWrite;
EXCEPTION
WHEN myexception THEN
DBMS_OUTPUT.put_line('THIS IS ONLY TO CONVERT LESS THAN 14 OR NOT GREATER THAN 12');
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('NOT INVALID MONEY.');
END;