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,