异步存储过程顾名思义就是对存储过程的处理实行异步执行,当我们需要执行一个存储过程时,不需要点击按钮,调用存储过程使它马上被执行,而是做某个操作,让他具备运行的条件,等某个时刻让系统自己去执行,在我们的项目中的实现过程是这样的:
为了灵活配置,建立表结构t_background_process和t_background_call,其中t_background_process中存储要调用的存储过程定义,这里的存储过程的定义有个要求,其参数必须是三个,相当于实现了一个存储过程的接口,所有书写的process都需要穿上这件外衣,以给统一调用提供一个统一的形式,每次需要启动一次个别存储过程时只需向t_background_call中添加一条记录,此表中有几个冗余的参数字段,用于存放调用的参数,其中包括t_background_process的主键关联和期望存储过程执行时间,
create table T_BACKGROUND_PROCESS ( PROCESS_ID NUMBER(10) not null, PROGRAM_NAME VARCHAR2(200) not null, PROCEDURE_DESC VARCHAR2(300) not null, PARA_NUM NUMBER(2), PRIORITY NUMBER(1) default 5 not null, FULL_CYCLE CHAR(1) default 'N' not null )
create table T_BACKGROUND_CALL ( CALL_ID NUMBER(10) not null, PROCESS_ID NUMBER(10) not null, EMP_ID NUMBER(10) not null, CALL_TIME DATE default SYSDATE not null, PLAN_TIME DATE default SYSDATE not null, START_TIME DATE, END_TIME DATE, CALL_STATUS NUMBER(1) not null, CALL_RESULT NUMBER(1), LOG_INFO VARCHAR2(4000), PARA_VALUE_1 VARCHAR2(200), PARA_VALUE_2 VARCHAR2(200), PARA_VALUE_3 VARCHAR2(200), PARA_VALUE_4 VARCHAR2(200), PARA_VALUE_5 VARCHAR2(200), PARA_VALUE_6 VARCHAR2(200), PARA_VALUE_7 VARCHAR2(200), PARA_VALUE_8 VARCHAR2(200), PARA_VALUE_9 VARCHAR2(200), PARA_VALUE_10 VARCHAR2(200) )
建立job,在任务里要做的工作是调用一个存储过程,比如:pkg_life_public_back_process.p_run(1,1);
P_RUN主要是查询出t_background_call里面需要执行的过程,更新调用情况,进行记录,
P_RUN_CALL 就是执行相应存储过程并记录执行情况,
procedure P_RUN ( i_priority_low in number, i_priority_high in number ) is
cursor cur_to_do is select a.process_id,b.call_id from t_background_process a,t_background_call b where a.process_id=b.process_id and b.call_status=0 and b.plan_time<=sysdate and a.priority >= i_priority_low and a.priority <= i_priority_high order by a.priority, b.plan_time
for update; -- commit in schedule block
m_process_id t_background_process.process_id%type;
m_call_id t_background_call.call_id%type;
m_no_data_found boolean;
begin loop open cur_to_do;
fetch cur_to_do into m_process_id,m_call_id;
m_no_data_found:=cur_to_do%notfound;
close cur_to_do; --调度schedule
if m_no_data_found then commit; --release exclusive lock on table t_background_call
exit; --退出 else --准备执行 update t_background_call set start_time = sysdate, call_status = 1 where call_id = m_call_id; commit; --执行程序 p_run_call(m_call_id); end if; end loop; end; --执行后台程序,同时作为测试接口 procedure P_RUN_CALL ( i_call_id number ) is m_program_name t_background_process.program_name%type; m_sql varchar2(1024); m_result t_background_call_result.result_id%type; m_log_info t_background_call.log_info%type; m_emp_id t_background_call.emp_id%type; begin --准备执行 update t_background_call set start_time = sysdate, call_status = 1 where call_id = i_call_id; commit; select bp.program_name,bc.emp_id into m_program_name,m_emp_id from t_background_call bc, t_background_process bp where bc.process_id = bp.process_id and bc.call_id = i_call_id; begin pkg_pub_app_context.P_SET_APP_USER_ID(m_emp_id); --执行后台运行程序 G_CALL_ID := i_call_id; m_sql := 'begin ' || m_program_name || '(:1, :2, :3); end;'; execute immediate m_sql using i_call_id, out m_result, out m_log_info; --正常执行退出 commit; exception when others then --异常执行退出 rollback; m_result := 1;--失败 m_log_info := sqlcode || ':' || sqlerrm;--错误信息 end; --结束执行 update t_background_call set end_time = sysdate, call_status = 2,--执行完成 call_result = m_result,--执行结果 log_info = substr(m_log_info, 1, 4000)--执行信息 where call_id = i_call_id; commit; exception when others then m_log_info := sqlcode||':'||sqlerrm; update t_background_call set end_time = sysdate, call_status = 2,--执行完成 call_result = 1,--失败 log_info = m_log_info--执行信息 where call_id = i_call_id; commit; end;
----业务数据手工生成异步处理
procedure P_SYNC_CHECK_DATA(
I_CALL_ID in number, --批处理调用ID(See T_BACKGROUD_CALL)
O_RESULT out number, --调用结果
O_LOG_INFO out varchar2 --执行时的一些日志信息 ) ;
procedure P_CHECK_DATA(YEA in number, ------- 开始年 --------
MON in number, ------- 开始月 --------
CODE in varchar, ------科目代码-------
AREA in varchar, -------circ机构------
) ;