数字与大写换算

CREATE OR REPLACE FUNCTION SMALLTOBIG(SMALLMONEY 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 BIGNUM:='仟';
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('该函数只能转换长度不大于14位或整数位不大于12的钱数');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('不是有效的钱数');
END;



DECLARE
MONEY VARCHAR2(100);
BEGIN
MONEY:=NUMTOSTR('1234567.89');
DBMS_OUTPUT.PUT_LIKE(MONEY);
END;