将数字转换为单词

Description:    You can easly convert Numbers to Words from this function.
Range:0.00

CREATE OR REPLACE FUNCTION NUMTOCHAR(MNUM IN NUMBER) RETURN VARCHAR2 IS
  TCHAR VARCHAR2(16);
  MCHAR VARCHAR2(255);
BEGIN
  TCHAR := TO_CHAR(MNUM, '099999999999.99');
  IF TO_NUMBER(SUBSTR(TCHAR, 1, 4)) <> 0 THEN
    MCHAR := MCHAR ||
             TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR, 1, 4)), 'J'), 'JSP') ||
             ' BILLION ';
  END IF;
  IF TO_NUMBER(SUBSTR(TCHAR, 5, 3)) <> 0 THEN
    MCHAR := MCHAR ||
             TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR, 5, 3)), 'J'), 'JSP') ||
             ' MILLION ';
  END IF;
  IF TO_NUMBER(SUBSTR(TCHAR, 8, 3)) <> 0 THEN
    MCHAR := MCHAR ||
             TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR, 8, 3)), 'J'), 'JSP') ||
             ' THOUSAND ';
  END IF;
  IF TO_NUMBER(SUBSTR(TCHAR, 11, 3)) <> 0 THEN
    MCHAR := MCHAR ||
             TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR, 11, 3)), 'J'), 'JSP') || ' ';
  END IF;
  IF TO_NUMBER(SUBSTR(TCHAR, 15, 2)) <> 0 THEN
    MCHAR := MCHAR || 'AND POINT ' ||
             TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TCHAR, 15, 2)), 'J'), 'JSP') || ' ';
  END IF;
  IF MCHAR IS NOT NULL THEN
    MCHAR := MCHAR;
  END IF;
  RETURN MCHAR;
END;

阅读(593) | 评论(0) | 转发(0) |

0

上一篇：检测下一次扩展将失败的对象

下一篇：设置sql*plus提示符