oracle 中字符串保函数字和非数字,现将字符串中数字转成中文的大写
CREATE OR REPLACE FUNCTION TO_UPPER_CNY(YEAR_IN IN VARCHAR2)
RETURN VARCHAR2
IS
TEMP VARCHAR2(32767);
RESULT VARCHAR2(32767);
--STR VARCHAR2(32767) := '〇一二三四五六七八九';
STR VARCHAR2(32767) := '零壹贰叁肆伍陆柒捌玖';
BEGIN
IF YEAR_IN IS NULL THEN
RETURN NULL;
END IF;
FOR I IN 1 .. LENGTH(YEAR_IN)
LOOP
TEMP:=SUBSTR(YEAR_IN,I, 1);
if f_is_num(TEMP)=1 then
SELECT SUBSTR(STR, SUBSTR(YEAR_IN,I, 1) + 1, 1)
INTO TEMP
FROM DUAL;
end if;
RESULT := RESULT || TEMP;
END LOOP;
RETURN RESULT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
RETURN '';
END;
调用
金额钱数转化成汉字大写
CREATE OR REPLACE Function Money_TO_C