--案件登记中,剩余工作日D=举报投诉日期+5个工作日-当前日期） create or replace function f_ajdj_sygzr(f_BBE197 varchar2) return varchar2 is f_sygzr varchar2(100);--返回剩余工作日的值 v_date_from varchar2(19); v_date_to varchar2(19); begin if f_BBE197 is not null and f_length(f_BBE197) = 10 then begin v_date_from := to_char(to_date(f_ldbzjc_getdate(f_BBE197,5),'YYYY-MM-DD'),'yyyy-mm-d d'); v_date_to := to_char(trunc(sysdate,'dd'),'yyyy-mm-dd'); f_sygzr := f_ldbzjc_getworkdays(v_date_from,v_date_to); exception when others then f_sygzr := null; end; else f_sygzr :=null; end if; return f_sygzr; end f_ajdj_sygzr; /
v_date_to := to_char(trunc(sysdate,'dd'),'yyyy-mm-dd');
/**剩余工作日D=举报投诉日期+5个工作日-当前日期） *1) *2) 若D>2，不需要预警； 若1=<D<=2，显示黄灯；
*3) 若D<1，显示红灯*/ create or replace function f_ajdj_yj (f_BBE197 varchar2) return varchar2 is f_yjd varchar2(100);--预警灯 f_syrq varchar2(100);---剩余日期
begin if f_BBE197 is not null and f_length(f_BBE197) = 10 then f_syrq := f_ajdj_sygzr(f_BBE197); if (to_number(f_syrq)>2) then f_yjd :=null; elsif (to_number(f_syrq)>=1 and to_number(f_syrq)<=2) then f_yjd := '<img src="../images/yellow.gif" width="14" height="14" border="0">'; elsif (to_number(f_syrq)<1) then f_yjd := '<img src="../images/red.gif" width="14" height="14" border="0">'; end if; else f_yjd := null; end if; return f_yjd; end f_ajdj_yj; /
IF ELSIF END IF
TO_NUMBER()
--获取中文格式的经办日期（格式：XXXX年XX月XX日） --若最近修改日期存在，则取最近修改日期，否则取创建日期 --pi_ajsxh(案件顺序号)，pi_wslx(文书类型，即BE09表中对应的文书名称) create or replace function f_ldbzjc_get_jbrq_cn (pi_ajsxh IN VARCHAR2,pi_wslx IN VARCHAR2) return VARCHAR2 is v_date v_ajsxh v_wslx sql_str BEGIN v_ajsxh := pi_ajsxh; v_wslx := pi_wslx; BEGIN IF pi_ajsxh is not Null Then BEGIN sql_str:= 'select (CASE WHEN BAE005 is not null THEN substr(BAE005,1,4) ELSE substr(BAE003,1,4) END) ||''年''|| (CASE WHEN BAE005 is not null THEN substr(BAE005,6,2) ELSE substr(BAE003,6,2) END) ||''月''|| VARCHAR2(50); VARCHAR2(50); VARCHAR2(50); VARCHAR2(500);
(CASE WHEN BAE005 is not null THEN substr(BAE005,9,2) ELSE substr(BAE003,9,2) END) ||''日'' from BE09 where BBA031 = :ajsxh and BBD067 = :wslx'; EXECUTE IMMEDIATE sql_str into v_date using v_ajsxh,v_wslx; EXCEPTION WHEN no_Data_found THEN v_date := Null; END; Else v_date := Null; END IF; END; RETURN v_date; END f_ldbzjc_get_jbrq_cn; /
EXECUTE
IMMEDIATE
EXCEPTION
WHEN
no_Data_found THEN
execute immediate 执行动态语句。 执行动态语句。
eg: create or replace procedure proc( t_name in varchar2 ) IS num_record NUMBER; begin execute immediate 'insert into ' || t_name || ' values (1, ''A'',to_date(''2000-11-26'',''yyyy-mm-dd''),1, ''a'')'; commit;--显示提交
--e
