ORACLE函数，视图记录
发布时间：2020-12-12 14:58:15 所属栏目：百科 来源：网络整理
导读：1.函数 create or replace function f_friendly_num(num in number) return varchar2 is /*********************************************************** 转化为万或亿 by huangwen 2017-04-26 ***********************************************************
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,'yyyyMM') = v_begin_month || ''
and f.med_id = gdyc_drug_usage.f_get_medid_by_userid(i_user_id)
and f.removed = '0'
and f.report_type = '0';
if v_count = 0 then
v_row:=table_empty_DATE(to_char(to_date(v_begin_month,'yyyy-MM')||'' --empty_date,'' --fill_time,'-1' --create_user_id,'' --create_user,'0/13' --progress,v_warning_level --warning_level,'' --alarm,'' --file_type,'0' --REPORT_TYPE,'' --review_content,'' --file_id,'' -- med_name,v_med_id -- med_id,'-1' --status,0 --success_apply_count
);
pipe row(v_row);
end if;
select to_char(add_months(to_date(v_begin_month,1),'yyyyMM')
into v_begin_month
from dual;
end loop;
RETURN;
END;
create or replace function f_get_rd_count_4p(i_tablename in varchar2,i_date in date,i_report_type in varchar2,i_create_user in varchar2,i_id in varchar2)
/***********************************************************************************
获取某个报表的记录数 用于 java的存储过程 --BY/HUANGWEN 2016-12-26
i_tablename：如果有冒号 比如tb_usage_drug_info:drug_type=0；将会解读为
select ... from tb_usage_drug_info where ... and drug_type=0
**********************************************************************************/
return number is
v_schema VARCHAR2(20);
v_sqlsmt VARCHAR2(2000);
v_result NUMBER(10);
v_format VARCHAR2(10);
v_table_name VARCHAR2(200);
v_index NUMBER(10);
v_extra_decision VARCHAR2(200);
v_date VARCHAR2(20);
begin
v_schema := 'gdyc_drug_usage';
v_table_name := i_tablename;
v_extra_decision := '';
v_index:=instr(i_tablename,':');
if i_report_type = '1' then
v_format := 'yyyy';
else
v_format := 'yyyy-MM';
end if;
v_date := to_char(i_date,v_format);
if v_index>0 then
v_table_name := substr(i_tablename,v_index-1);
v_extra_decision := ' and ' || substr(i_tablename,v_index+1);
end if;
v_sqlsmt := 'select count(*) from ' || v_schema || '.' || v_table_name ||
' m,' || v_schema ||
'.tb_usage_file f
where f.id = m.file_id and m.removed=''0'' and f.removed = ''0'' and m.id = '''||i_id||''' and (f.create_user = ' ||
i_create_user || ' or gdyc_drug_usage.f_same_medical(f.create_user,'||i_create_user||') = ''1'') and to_char(f.report_date,''' || v_format || ''')=''' ||
v_date || ''' and f.report_type=''' || i_report_type ||'''' || v_extra_decision ;
execute immediate v_sqlsmt
into v_result;
return v_result;
end f_get_rd_count_4p;
create or replace function f_get_rec_fill_status_ex(i_tablename in varchar2,i_date in varchar2,i_file_id in varchar2
)
/***********************************************************************************
获取某个报表的填写状态 --BY/HUANGWEN 2016-12-26
**********************************************************************************/
return VARCHAR2 is
v_schema VARCHAR2(20);
v_sqlsmt VARCHAR2(2000);
v_result VARCHAR2(10);
v_format VARCHAR2(10);
v_table_name VARCHAR2(200);
v_date VARCHAR2(20);
begin
v_schema := 'gdyc_drug_usage';
v_table_name := i_tablename;
if i_report_type = '1' then
v_format := 'yyyy';
else
v_format := 'yyyy-MM';
end if;
v_date := i_date;
v_sqlsmt := 'select max( m.data_type) from ' || v_schema || '.' || v_table_name ||
' m inner join ' || v_schema ||
'.tb_usage_file f
on f.id = m.file_id where m.removed=''0'' and f.removed = ''0'' and (f.id = '''||i_file_id ||''' )
and to_char(f.report_date,''' || v_format || ''')=''' ||
v_date || ''' and f.report_type=''' || i_report_type ||'''' ;
execute immediate v_sqlsmt
into v_result;
return v_result;
EXCEPTION
WHEN others THEN --捕捉处理
return -1;
end f_get_rec_fill_status_ex;
create or replace function F_GET_split_str(str_src in varchar2,p_index in number,split_str varchar2)
return varchar2 is
/**************************************************
获取截取字符串中的某一串
**************************************************/
v_result varchar2(500);
v_idx integer;
v_str_cought varchar2(500);
v_strs_rest varchar2(4000) := str_src;
v_idx_cnter integer := 0;
begin
loop
v_idx := instr(v_strs_rest,split_str);
if v_idx = 0 and v_idx_cnter = p_index
then v_result := v_strs_rest;
end if;
exit when v_idx = 0;
v_str_cought := substr(v_strs_rest,1,v_idx - 1);
v_strs_rest := substr(v_strs_rest,v_idx + 1);
if v_idx_cnter = p_index then
v_result := v_str_cought;
exit;
end if;
v_idx_cnter := v_idx_cnter + 1;
end loop;
return v_result;
end F_GET_split_str;
2.视图
|