ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 数据转换为金额大写

数据转换为金额大写

原创 Linux操作系统 作者:hjgluguo 时间:2009-06-10 15:51:16 0 删除 编辑

创建包说明:

CREATE OR REPLACE PACKAGE NUMTOAMOUNT IS
     FUNCTION SAY_MONEY(P_NUM NUMBER) RETURN VARCHAR2;
     FUNCTION SAY_PERMILLION(P_NUM NUMBER) RETURN VARCHAR2;
     FUNCTION SAY_CHINESE(P_NUM NUMBER) RETURN VARCHAR2;
END ;

创建包体:

CREATE OR REPLACE PACKAGE BODY NUMTOAMOUNT IS

-- 0到9十个数字转换为大写
FUNCTION SAY_MONEY(P_NUM NUMBER) RETURN VARCHAR2 IS
    W_STR VARCHAR2(400);
BEGIN
  SELECT TRANSLATE(P_NUM, '0123456789', '零壹贰叁肆伍陆柒捌玖') INTO W_STR FROM dual;
    RETURN W_STR;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

-- 按每四位来转换
FUNCTION SAY_PERMILLION(P_NUM NUMBER) RETURN VARCHAR2 IS
    W_STR VARCHAR2(400);
    W_CTN NUMBER;
    W_NUM NUMBER;
BEGIN
    W_STR := '';
    W_NUM := P_NUM;
   
    W_CTN := TRUNC(W_NUM / 1000);
    W_NUM := W_NUM MOD 1000;
    IF W_CTN > 0 THEN
      W_STR := W_STR || SAY_MONEY(W_CTN) || '仟';
    END IF;
  
   W_CTN := TRUNC(W_NUM / 100);
   W_NUM := W_NUM MOD 100;
   IF W_CTN > 0 THEN
      W_STR := W_STR || SAY_MONEY(W_CTN) || '佰';
   END IF;
   -- 仟位非空,且佰位为0,把零取出
   IF (W_STR IS NOT NULL AND W_CTN = 0)  THEN 
      W_STR := W_STR || SAY_MONEY(0);
   END IF;
  
   W_CTN := TRUNC(W_NUM / 10);
   W_NUM := W_NUM MOD 10;
   IF W_CTN > 0 THEN
     W_STR := W_STR || SAY_MONEY(W_CTN) || '拾';
   END IF;
   -- 拾位之前非空,佰位非0,拾位为0,且个位不为0,把零取出
   IF (W_STR IS NOT NULL AND W_CTN = 0 AND INSTR(W_STR, '零') != 3 AND W_NUM != 0)  THEN
     W_STR := W_STR || SAY_MONEY(0);
   END IF;
  
   W_CTN := TRUNC(W_NUM / 1);
   W_NUM := W_NUM MOD 1;
   IF W_CTN > 0 THEN
      W_STR := W_STR || SAY_MONEY(W_CTN);
   END IF;
   -- 仟位非零,佰位、拾位和个位为0,把佰位的零去掉(这种情况,佰位的零会取出,而拾位和个位的不会取出,因此要把多余的零去掉)
   IF (W_CTN = 0 AND INSTR(W_STR,'仟') > 0 AND INSTR(W_STR,'零') = 3 AND INSTR(W_STR,'拾') != 5) THEN
    W_STR := REPLACE(W_STR, '零', '');
   END IF;
 
     RETURN W_STR;
   EXCEPTION WHEN NO_DATA_FOUND THEN
    RETURN NULL;
END;

-- 数字转换为金额大写
FUNCTION SAY_CHINESE(P_NUM NUMBER) RETURN VARCHAR2 IS
    W_CTN NUMBER;
    W_NUM NUMBER;
    W_STR VARCHAR2(2000);
  BEGIN
    W_STR := '';
    W_NUM := P_NUM;
    IF W_NUM > 999999999999 OR W_NUM < 0 THEN
      RETURN NULL;
    ELSIF W_NUM = 0 THEN
      RETURN '零圆';
    END IF;
 
    -- 读取亿位
    W_CTN := TRUNC(W_NUM / 100000000);
    W_NUM := W_NUM MOD 100000000;
    IF W_CTN > 0 THEN
        W_STR := SAY_PERMILLION(W_CTN) || '亿';
    END IF;

    -- 读取万位
    W_CTN := TRUNC(W_NUM / 10000);
    W_NUM := W_NUM MOD 10000;
    IF W_CTN > 0 THEN
     -- 亿位非空,而仟万位为0,在读万的数据之前要加个零
      IF (W_STR IS NOT NULL AND TRUNC(W_CTN / 1000) = 0 ) THEN
        W_STR := W_STR || '零' || SAY_PERMILLION(W_CTN) || '万';
      ELSE
        W_STR := W_STR || SAY_PERMILLION(W_CTN) || '万' ; 
      END IF;
    END IF;

    -- 读取个位
    W_CTN := TRUNC(W_NUM);
    W_NUM := W_NUM MOD 1;
    IF W_CTN > 0 THEN
      -- 万位之前非空,而仟位为0,在读仟位的数据之前要加个零
      IF (W_STR IS NOT NULL AND TRUNC(W_CTN / 1000) = 0 ) THEN
        -- 数值为整数,在圆之后加个整字
        IF W_NUM =0 THEN
          W_STR := W_STR || '零' || SAY_PERMILLION(W_CTN) || '圆整';
        ELSE
          W_STR := W_STR || '零' || SAY_PERMILLION(W_CTN) || '圆';
        END IF;
      ELSE
        IF W_NUM =0 THEN
          W_STR := W_STR || SAY_PERMILLION(W_CTN) || '圆整';
        ELSE
          W_STR := W_STR || SAY_PERMILLION(W_CTN) || '圆';
        END IF;
      END IF;

    END IF;
  
   -- 读取小数
    IF W_NUM > 0 THEN
        -- 读取角
        W_CTN := TRUNC(W_NUM*10);
        W_NUM := (W_NUM*10) MOD 1;
        IF W_CTN > 0 THEN
          W_STR := W_STR || SAY_PERMILLION(W_CTN) || '角';
        END IF;
       
        -- 读取分
        W_CTN := TRUNC(W_NUM*10);
        W_NUM := (W_NUM*10) MOD 1;
        IF W_CTN > 0 THEN
          W_STR := W_STR || SAY_PERMILLION(W_CTN) || '分';
        END IF;
    END IF;
    RETURN LTRIM(W_STR);
  END;

 END;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7865774/viewspace-605816/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 控制文件的备份
请登录后发表评论 登录
全部评论

注册时间:2008-07-11

  • 博文量
    97
  • 访问量
    182568