PLSQL存储过程优化-优快云博客

本文链接：https://blog.youkuaiyun.com/mush_me/article/details/84156407

mark一下


create or replace procedure closemt is
--定义了两个变量备用	
		l_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; 
			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 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;