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,'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.视图
create or replace view v_table_Metadata_hw as select r."TABLE_NAME",r."COLUMN_NAME",r."COMMENTS",r."JAVACOL",r."DATA_TYPE",r."IBATIS_COL",r."DATA_DEFAULT",r.column_name || ' as ' || r.javaCol as SEL_COL,'$(''#' || javaCol || ''').val(rowData.' || column_name || ');' AS fill_Detail_selected,'{field:''' || column_name ||''',title:''' ||COMMENTS || ''',width:100},' as fill_option,'<th>' || r.COMMENTS || ':</th><td><input id=''' || r.JAVACOL || '''/></td>' as fill_Detail_form,'<th>' || r.COMMENTS || ':</th><td><input name=''' || r.column_name || '''/></td>' as fill_query_form,'private '|| case DATA_TYPE when 'NVARCHAR2' then 'String' when 'CHAR' then 'String' when 'VARCHAR2' then 'String' when 'DATE' then 'Date' when 'NUMBER' then 'Double' when 'LONG' then 'Long' else 'Object' end ||' '||javacol||'; //' || comments as javaAttr,lower(substr(REPLACE(INITCAP(r.table_name),'_',''),1)) || substr(REPLACE(INITCAP(r.table_name),2) || '.set'|| upper(substr(javacol,1)) ||substr(javacol,2) || case DATA_TYPE when 'NVARCHAR2' then '(request.getParameter("'||javacol ||'"));' when 'CHAR' then '(request.getParameter("'||javacol ||'"));' when 'VARCHAR2' then '(request.getParameter("'||javacol ||'"));' when 'DATE' then '(new Date())' when 'NUMBER' then '(Double.parseDouble(request.getParameter("'||javacol ||'")));' when 'LONG' then '(Long.parseLong(request.getParameter("'||javacol ||'")));' else '' end as setter,column_name || ',' as INSERT1,'#{'||javacol||',jdbcType='||Ibatis_Col||'}'||',' AS INSERT2,column_name || '=#{' ||javacol||',' AS UPDATE1 from (select t.table_name,t.column_name,tt.comments,lower(substr(REPLACE(INITCAP(t.column_name),1)) || substr(REPLACE(INITCAP(t.column_name),2) as javaCol,DATA_TYPE,decode(DATA_TYPE,'DATE','NUMBER','DOUBLE','LONG','INTEGER','TIMESTAMP','CHAR','VARCHAR') as Ibatis_Col,data_default from all_tab_columns t inner join USER_COL_COMMENTS tt on t.table_name = tt.table_name and t.column_name = tt.column_name) r;
