[转]小写金额串转换成大写金额串(oracle版)

    最近需要oracle存储过程完成功能：传入金额，转换成大写金额，如传入3002.03，转换成 叁仟零贰圆零叁分，本来想自己写，但无奈越写越繁琐，于是从网上找到几位大牛写的，很强大，学习了。

版本一：

(原文链接：http://blog.sina.com.cn/s/blog_4b9e847f010006rf.html)

create or replace function F_upper_money(p_num in number default null)
  return nvarchar2 is
  /*Ver:1.0 Created By xsb on 2003-8-18 For:
  将金额数字(单位元)转换为大写(采用从低至高算法)
  数字整数部分不得超过16位,可以是负数。
  Ver：1.1 Modified By xsb on 2003-8-20 For:个位数处理也放在For循环中。
  Ver：1.2 Modified By xsb on 2003-8-22 For:分后不带整字。
  Ver：1.3 Modified By xsb on 2003-8-28 For:完善测试用例。
  测试用例:
  SET HEAD OFF
  SET FEED OFF
  select '无参数时='||f_upper_money() from dual;
  select 'null='||f_upper_money(null) from dual;
  select '0='||f_upper_money(0) from dual;
  select '0.01='||f_upper_money(0.01) from dual;
  select '0.126='||f_upper_money(0.126) from dual;
  select '01.234='||f_upper_money(01.234) from dual;
  select '10='||f_upper_money(10) from dual;
  select '100.1='||f_upper_money(100.1) from dual;
  select '100.01='||f_upper_money(100.01) from dual;
  select '10000='||f_upper_money(10000) from dual;
  select '10012.12='||f_upper_money(10012.12) from dual;
  select '20000020.01='||f_upper_money(20000020.01) from dual;
  select '3040506708.901='||f_upper_money(3040506708.901) from dual;
  select '40005006078.001='||f_upper_money(40005006078.001) from dual;
  select '-123456789.98='||f_upper_money(-123456789.98) from dual;
  select '123456789123456789.89='||f_upper_money(123456789123456789.89) from dual;
  */
  Result      nvarchar2(100); --返回字符串
  num_round   nvarchar2(100) := to_char(abs(round(p_num, 2))); --转换数字为小数点后2位的字符(正数)
  num_left    nvarchar2(100); --小数点左边的数字
  num_right   nvarchar2(2); --小数点右边的数字
  str1        nchar(10) := '零壹贰参肆伍陆柒捌玖'; --数字大写
  str2        nchar(16) := '元拾佰仟万拾佰仟亿拾佰仟万拾佰仟'; --数字位数(从低至高)
  num_pre     number(1) := 1; --前一位上的数字
  num_current number(1); --当前位上的数字
  num_count   number := 0; --当前数字位数

begin
  if p_num is null then
    return null;
  end if; --转换数字为null时返回null

  select to_char(nvl(substr(to_char(num_round),
                            1,
                            decode(instr(to_char(num_round), '.'),
                                   0,
                                   length(num_round),
                                   instr(to_char(num_round), '.') - 1)),
                     0))
    into num_left
    from dual; --取得小数点左边的数字
  select substr(to_char(num_round),
                decode(instr(to_char(num_round), '.'),
                       0,
                       length(num_round) + 1,
                       instr(to_char(num_round), '.') + 1),
                2)
    into num_right
    from dual; --取得小数点右边的数字

  if length(num_left) > 16 then
    return '**********';
  end if; --数字整数部分超过16位时

  --采用从低至高的算法，先处理小数点右边的数字
  if length(num_right) = 2 then
    if to_number(substr(num_right, 1, 1)) = 0 then
      result := '零' ||
                substr(str1, to_number(substr(num_right, 2, 1)) + 1, 1) || '分';
    else
      result := substr(str1, to_number(substr(num_right, 1, 1)) + 1, 1) || '角' ||
                substr(str1, to_number(substr(num_right, 2, 1)) + 1, 1) || '分';
    end if;
  elsif length(num_right) = 1 then
    result := substr(str1, to_number(substr(num_right, 1, 1)) + 1, 1) || '角整';
  else
    result := '整';
  end if;
  --再处理小数点左边的数字
  for i in reverse 1 .. length(num_left) loop
    --(从低至高)
    num_count   := num_count + 1; --当前数字位数
    num_current := to_number(substr(num_left, i, 1)); --当前位上的数字
    if num_current > 0 then
      --当前位上数字不为0按正常处理
      result := substr(str1, num_current + 1, 1) ||
                substr(str2, num_count, 1) || result;
    else
      --当前位上数字为0时
      if mod(num_count - 1, 4) = 0 then
        --当前位是元、万或亿时
        result  := substr(str2, num_count, 1) || result;
        num_pre := 0; --元、万,亿前不准加零
      end if;
      if num_pre > 0 or length(num_left) = 1 then
        --上一位数字不为0或只有个位时
        result := substr(str1, num_current + 1, 1) || result;
      end if;
    end if;
    num_pre := num_current;
  end loop;

  if p_num < 0 then
    --转换数字是负数时
    result := '负' || result;
  end if;

  return Result;

exception
  when others then
    raise_application_error(-20001, '数字转换大写出现错误！' || sqlerrm);
end;

 版本二：

(原文链接：http://hi.baidu.com/sunbo05/item/82270c418615e50e6cc2f07d)

CREATE OR REPLACE FUNCTION money_to_chinese(money IN VARCHAR2)
  RETURN VARCHAR2 IS
  c_money  VARCHAR2(12);
  m_string VARCHAR2(60) := '分角圆拾佰仟万拾佰仟亿';
  n_string VARCHAR2(40) := '壹贰叁肆伍陆柒捌玖';
  b_string VARCHAR2(80);
  n        CHAR;
  len      NUMBER(3);
  i        NUMBER(3);
  tmp      NUMBER(12);
  is_zero  BOOLEAN;
  z_count  NUMBER(3);
  l_money  NUMBER;
  l_sign   VARCHAR2(10);
BEGIN
  l_money := ABS(money);

  IF money < 0 THEN
    l_sign := '负';
  ELSE
    l_sign := '';
  END IF;

  tmp     := ROUND(l_money, 2) * 100;
  c_money := RTRIM(LTRIM(TO_CHAR(tmp, '999999999999')));
  len     := LENGTH(c_money);
  is_zero := TRUE;
  z_count := 0;
  i       := 0;

  WHILE i < len LOOP
    i := i + 1;
    n := SUBSTR(c_money, i, 1);
  
    IF n = '0' THEN
      IF len - i = 6 OR len - i = 2 OR len = i THEN
        IF is_zero THEN
          b_string := SUBSTR(b_string, 1, LENGTH(b_string) - 1);
          is_zero  := FALSE;
        END IF;
      
        IF len - i = 6 THEN
          b_string := b_string || '万';
        END IF;
      
        IF len - i = 2 THEN
          b_string := b_string || '圆';
        END IF;
      
        IF len = i THEN
          b_string := b_string || '整';
        END IF;
      
        z_count := 0;
      ELSE
        IF z_count = 0 THEN
          b_string := b_string || '零';
          is_zero  := TRUE;
        END IF;
      
        z_count := z_count + 1;
      END IF;
    ELSE
      b_string := b_string || SUBSTR(n_string, TO_NUMBER(n), 1) ||
                  SUBSTR(m_string, len - i + 1, 1);
      z_count  := 0;
      is_zero  := FALSE;
    END IF;
  END LOOP;

  b_string := l_sign || b_string;
  RETURN b_string;
EXCEPTION
  WHEN OTHERS THEN
    RETURN(SQLERRM);
END;