ORACLE函数，视图记录

1.函数




create or replace function f_friendly_num(num in number)
  return varchar2 is
 /***********************************************************
  转化为万或亿
  by huangwen 2017-04-26

 *************************************************************/
begin
    if num <10000 then
      return to_char(num);
     elsif num >=10000 and num < 100000000 then
       return round(num/10000,2) || '万';
     else
        return round(num/100000000,2) || '亿';
     end if ;
end;

create or replace function f_add_pre_zero(num in number)
  return char is
 /***********************************************************
  小数点不够零往前面补零
  by huangwen 2017-04-26

 *************************************************************/
begin
    if num < 1 and num > 0 then
      return to_char(num,'fm9999990.9999');
     else
       return num;
     end if ;
end;

CREATE OR REPLACE FUNCTION f_get_empty_month(i_user_id in varchar2)
 /***********************************************************************************
  获取尚未填写的月报 --BY/HUANGWEN 2017-1-3
 **********************************************************************************/
  RETURN empty_DATE
  PIPELINED AS
  v_begin_month NUMBER;
  v_end_month   NUMBER;
  v_row        table_empty_DATE;
  v_med_id     varchar2(50);
  v_count      number;
  v_warning_level char(1);
BEGIN

  select to_number(t.di_vlaue)   --201501
    into v_begin_month
    from CFW.t_a1_dic_item t, wjs.t_a1_dic_type s
   where t.dt_id = s.dt_id
     and s.dt_key = 'YGYY-START-TIME'
     and di_code = 'YGYY-START-TIME-MONTH';



 select to_char(add_months(trunc(sysdate),-1),'yyyyMM')
  into v_end_month
  from dual;

  select gdyc_drug_usage.f_get_medid_by_userid(i_user_id)
    into v_med_id
    from dual;

  while 
    v_begin_month <= v_end_month loop
    
    select gdyc_drug_usage.f_get_month_alarm(to_date(v_begin_month,'yyyyMM'),sysdate)
    into v_warning_level from dual ;
    

    select count(1)
      into v_count
      from gdyc_drug_usage.tb_usage_file f
     where to_char(f.report_date,