create or replace function comm.F_upper_money(p_num in number default null)

  return nvarchar2 is

  /*Ver:1.0 Created By xsb on 2003-8-18 For:

  將金額數(shù)字(單位元)轉(zhuǎn)換為大寫(采用從低至高算法)

  數(shù)字整數(shù)部分不得超過16位,可以是負數(shù)。

  Ver：1.1 Modified By xsb on 2003-8-20 For:個位數(shù)處理也放在For循環(huán)中。

  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 '無參數(shù)時='||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))); --轉(zhuǎn)換數(shù)字為小數(shù)點后2位的字符(正數(shù))

  num_left nvarchar2(100); --小數(shù)點左邊的數(shù)字

  num_right nvarchar2(2); --小數(shù)點右邊的數(shù)字

  str1 nchar(10) := '零壹貳叁肆伍陸柒捌玖'; --數(shù)字大寫

  str2 nchar(16) := '元拾佰仟萬拾佰仟億拾佰仟萬拾佰仟'; --數(shù)字位數(shù)(從低至高)

  num_pre number(1) := 1; --前一位上的數(shù)字

  num_current number(1); --當前位上的數(shù)字

  num_count number := 0; --當前數(shù)字位數(shù)

begin

  if p_num is null then

    return null;

  end if; --轉(zhuǎn)換數(shù)字為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; --取得小數(shù)點左邊的數(shù)字

  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; --取得小數(shù)點右邊的數(shù)字

  if length(num_left) > 16 then

    return '**********';

  end if; --數(shù)字整數(shù)部分超過16位時

  --采用從低至高的算法，先處理小數(shù)點右邊的數(shù)字

  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;

  --再處理小數(shù)點左邊的數(shù)字

  for i in reverse 1 .. length(num_left) loop

    --(從低至高)

    num_count := num_count + 1; --當前數(shù)字位數(shù)

    num_current := to_number(substr(num_left, i, 1)); --當前位上的數(shù)字

    if num_current > 0 then

      --當前位上數(shù)字不為0按正常處理

      result := substr(str1, num_current + 1, 1) ||

                substr(str2, num_count, 1) || result;

    else

      --當前位上數(shù)字為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

        --上一位數(shù)字不為0或只有個位時

        result := substr(str1, num_current + 1, 1) || result;

      end if;

    end if;

    num_pre := num_current;

  end loop;

  if p_num < 0 then

    --轉(zhuǎn)換數(shù)字是負數(shù)時

    result := '負' || result;

  end if;

  return Result;

exception

  when others then

    raise_application_error(-20001, '數(shù)字轉(zhuǎn)換大寫出現(xiàn)錯誤！' || sqlerrm);

end;