CREATE OR REPLACE FUNCTION Number_To_English(num_to_en NUMBER)RETURN VARCHAR2
IS
temp_ VARCHAR2(100);
integral_ NUMBER;
decimal_ NUMBER;
en_number_ VARCHAR2(100);
BEGIN
temp_ := to_char(num_to_en,'9999999d99'); --- 如果不格式化,123.20 会在转换中自动变成123.2 从而把 twenty cents 翻译成 two
IF instr(num_to_en,'.') = 0 THEN --- 判断是否为整数
SELECT UPPER(to_char(to_date(num_to_en,'J'),'Jsp'))INTO temp_
FROM dual;
ELSE --- 是小数则进行如下转换
integral_ := trunc(num_to_en);
v_num :=length(temp_)-substr(temp_,instr(temp_,'.');
decimal_ := to_number(substr(temp_,instr(temp_,'.')+1,v_num));
SELECT UPPER(to_char(to_date(integral_,'J'),'Jsp')|| ' AND CENTS '||to_char(to_date(decimal_,'J'),'Jsp')) INTO temp_
FROM dual;
END IF;
RETURN temp_;
END Number_To_English;
IS
temp_ VARCHAR2(100);
integral_ NUMBER;
decimal_ NUMBER;
en_number_ VARCHAR2(100);
BEGIN
temp_ := to_char(num_to_en,'9999999d99'); --- 如果不格式化,123.20 会在转换中自动变成123.2 从而把 twenty cents 翻译成 two
IF instr(num_to_en,'.') = 0 THEN --- 判断是否为整数
SELECT UPPER(to_char(to_date(num_to_en,'J'),'Jsp'))INTO temp_
FROM dual;
ELSE --- 是小数则进行如下转换
integral_ := trunc(num_to_en);
v_num :=length(temp_)-substr(temp_,instr(temp_,'.');
decimal_ := to_number(substr(temp_,instr(temp_,'.')+1,v_num));
SELECT UPPER(to_char(to_date(integral_,'J'),'Jsp')|| ' AND CENTS '||to_char(to_date(decimal_,'J'),'Jsp')) INTO temp_
FROM dual;
END IF;
RETURN temp_;
END Number_To_English;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7185996/viewspace-135973/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7185996/viewspace-135973/