大橙子网站建设,新征程启航

为企业提供网站建设、域名注册、服务器等服务

plsql小写金额转大写金额函数

  • 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:
  •   将金额数字(单位元)转换为大写(采用从低至高算法)
  •   数字整数部分不得超过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;


  • 当前题目:plsql小写金额转大写金额函数
    网页路径:http://dzwzjz.com/article/jpoccp.html
    在线咨询
    服务热线
    服务热线:028-86922220
    TOP