数据库一些自定义的函数积累

  金融项目在金额、字符中都会有很多格式的处理，比如报表中金额数字右对齐，使用￥00,000.00格式，字符串优化后展示等。我们除了在前端通过java或者前端的校验处理外，可以在数据库层上做一层包装。这样做不仅能提高查询性能，而且通用性很强，易于拓展。这些在做统计报表时效果特别明显。部分总结有下：

• 计算同比增速

         用法：select fn_calc_growth(100, 20) from dual;

create or replace function fn_calc_growth( p_current number, p_last number)

return number

----- 计算增速-- 用法: select fn_calc_growth(100, 20) from dual

---- @param p_current: 本期数

-- @param p_last: 同期数

-- @date: 2016-11-16

---is v_current number;

v_last number;

v_null number;

begin v_current := nvl(p_current, 0);

 v_last := nvl(p_last, 0);

if v_current = 0 and v_last = 0 then return 0;

 end if;

  if v_last = 0 then

-- 如果v_last小于0，则是负增长

 --if v_current > 0

--then

-- return 1;

 --else -- return -1;

-- end if; -- 考虑是否直接返回空

return v_null;

end if;

return round((v_current - v_last) / v_last * 100, 10);

end fn_calc_growth;

• 金额格式化，###，###.00

create or replace function fn_format_amount(

p_amount number := 0

)

return varchar2

----- 金额格式化

-- usage: select fn_format_amount(100) from dual;

-- @param p_amount 金额，支持最大位数为18

-- @date 2016-11-03

is

begin

if p_amount is null or p_amount = 0

then

 return '0.00';

 end if;

return trim(to_char(p_amount, '999,999,999,999,999,999.99'));

end fn_format_amount;

• 字符串分割函数

create or replace function fn_split_str(

 p_value clob,

 p_split varchar2 := ','

)

return ty_str_list

-- 字符串分割函数

-- 用法: select * from table(fn_str_list('1,2,3,4,5', ','))

-- @param p_value: 长度不能超过4000个字符

-- @param p_split: 默认值为","

-- @date: 2016-10-27

--·

is

 v_str_list ty_str_list := ty_str_list();

 v_counter number := 0;

 v_current_index number := 1;

 v_index number := 1;

 v_str varchar2(500);

begin

 -- 为空

 if length(p_value) = 0 or p_value is null

 then

  return v_str_list;

 end if;

 loop

  v_index := instr(p_value, p_split, v_current_index);

  --dbms_output.put_line('v_index: ' || v_index);

  if v_index = 0

  then

   v_str := substr(p_value, v_current_index);

  else

   v_str := substr(p_value, v_current_index, v_index - v_current_index);

  end if;

  v_counter := v_counter + 1;

  v_str_list.extend;

  v_str_list(v_counter) := v_str;

  v_current_index := v_index + 1;

  -- 最后一个，退出

  exit when v_index = 0;

 end loop;

 return v_str_list;

end fn_split_str;