oracle存储过程记录
mark一下

create or replace procedure closemt isl_domino_path varchar2(20);l_process_instance_id varchar2(30);cursor mycur is select t1.domino_path,t1.process_instance_id from engine.t_engine_process_instance t1 left join ts_org t2 on(t1.drafter_dept_id=t2.org_id) where t1.status=2002 and t2.whole_org_id like '/1010/10157/%' and t1.drafter_id=30265 and t1.draft_time<TO_DATE('20120101','yyyymmdd');; begin open mycur; loopfetch mycur into l_domino_path,l_process_instance_id;exit when mycur %notfound;update engine.t_engine_process_instance t set t.status=2008 where t.domino_path=l_domino_path;update engine.t_engine_process_workitem t set t.status=2001 where t.domino_path=l_domino_path;update engine.t_unified_workitem t set t.status=2001 where t.domino_path=l_domino_path;update engine.t_engine_activity_instance t set t.status=2001 where t.process_instanceid=l_process_instance_id;end loop;close mycur;end closemt;

调用

call closemt();

此内容可以优化成带参数的

create or replace procedure close4(begin_date in varchar2) is      l_domino_path varchar2(50);    l_process_instance_id varchar2(50);    cursor mycur is select t1.domino_path,t1.process_instance_id from engine.t_engine_process_instance t1 left join ts_org t2 on(t1.drafter_dept_id=t2.org_id) where t1.status=2002 and t2.whole_org_id like '/1010/10157/%'  and t1.draft_time<TO_DATE(begin_date,'yyyymmdd');   begin     open mycur;       loop        fetch mycur into l_domino_path,l_process_instance_id;        exit when mycur %notfound;        update engine.t_engine_process_instance t set t.status=2008 where t.domino_path=l_domino_path;        update engine.t_engine_process_workitem t set t.status=2001 where t.domino_path=l_domino_path;        update engine.t_unified_workitem t set t.status=2001 where t.domino_path=l_domino_path;        update engine.t_engine_activity_instance t set t.status=2001 where t.process_instanceid=l_process_instance_id;      end loop;    close mycur;end close4;

调用

call close4('20110701');

我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html