漢字轉拼音pl/sql

taogchan發表於2012-01-05
遺憾的是隻有到千萬,,,
create or replace function getBigMoneyStr(money In number) return varchar2 is
  tmp_str   varchar2(20);
  money_str varchar2(20);
  val_j     nvarchar2(40);
  len_j     number;
  k         number;
  i         number;
  j         number;
  m         number;
  result    varchar2(40);
  type my_type is varray(20) of varchar2(4);
  num     constant my_type := my_type('1',
                                      '2',
                                      '3',
                                      '4',
                                      '5',
                                      '6',
                                      '7',
                                      '8',
                                      '9',
                                      '0');
  num_str constant my_type := my_type('壹',
                                      '貳',
                                      '叄',
                                      '肆',
                                      '伍',
                                      '陸',
                                      '柒',
                                      '捌',
                                      '玖',
                                      '零');
begin
  tmp_str := to_char(money * 100);
  len_j   := length(tmp_str);
  k       := 11 - len_j;
  result  := substr(tmp_str, 1, 1);
  m       := 2;
  for i in 1 .. len_j loop
    begin
      if k = 1 then
        money_str := '仟';
      elsif k = 2 then
        money_str := '佰';
      elsif k = 3 then
        money_str := '拾';
      elsif k = 4 then
        money_str := '萬';
      elsif k = 5 then
        money_str := '仟';
      elsif k = 6 then
        money_str := '佰';
      elsif k = 7 then
        money_str := '拾';
      elsif k = 8 then
        money_str := '元';
      elsif k = 9 then
        money_str := '角';
      elsif k = 10 then
        money_str := '分';
      end if;
    
      k     := k + 1;
      m     := i + 1;
      val_j := nvl(substr(tmp_str, m, 1), '');
      if len_j = m + 5 and substr(result, length(result), 1) = '0' then
        result := substr(result, 0, length(result) - 1) || '萬0';
      end if;
   
      if len_j = m + 1 and substr(result, length(result), 1) = '0' then
        result := substr(result, 0, length(result) - 1) || '元0';
      end if;
   
      if val_j = '0' and substr(result, length(result), 1) = '0' then
        goto TheEnd;
      end if;
   
      if substr(result, length(result), 1) != '0' then
        result := result || money_str;
      end if;
   
      result := result || val_j;
   
      <>
      null;
    end;
  end loop;
  --除去最後的0
  for j in 1 .. 3 loop
    begin
      if substr(result, length(result), 1) = '0' then
        result := substr(result, 0, length(result) - 1);
      end if;
    end;
  end loop;
  for i in 1 .. 10 loop
    result := replace(result, num(i), num_str(i));
  end loop;

  return result || '整';
end;
 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22392018/viewspace-714443/,如需轉載,請註明出處,否則將追究法律責任。

相關文章