oracle存储过程实例

create or replace procedure hy_double_test is

begin

  DECLARE

l_cnt number;

temp_item_oid NUMBER := 0;

n_new_task_num NUMBER := 0 ;

--查询出RITEM_OID为空的记录

CURSOR c_table IS

select t.task_e_rprtaskid,t.ritem_e_boardid,t.errpos_e_noid,t.rpr_e_rprboardid

 from ecc_epm.query_reparedetail t

where t.RITEM_OID is null;

BEGIN

l_cnt := 0;

LOOP

FOR t1 IN c_table LOOP

l_cnt := l_cnt + 1;

--查询出RITEM_OID

select ritem.oid 

into temp_item_oid

from ecc_epm.ecc_eq_rprboarditem ritem

where ritem.e_rprboardid = t1.rpr_e_rprboardid 

and ritem.e_boardid = t1.ritem_e_boardid;

if temp_item_oid > 0 then

--判断RITEM_OID是否存在

SELECT COUNT(0)

INTO n_new_task_num

FROM ecc_epm.query_reparedetail q

    WHERE q.RITEM_OID = temp_item_oid;

--存在，删除记录

IF n_new_task_num >0 then

 

DELETE  FROM ecc_epm.query_reparedetail re_del

WHERE re_del.task_e_rprtaskid =t1.task_e_rprtaskid 

and re_del.ritem_e_boardid=t1.ritem_e_boardid

and re_del.RITEM_OID is null;

--t.task_e_rprtaskid,t.ritem_e_boardid,t.errpos_e_noid,t.rpr_e_rprboardid

--不存在，修改记录

ELSE

--修改更新时间最新的记录

UPDATE ecc_epm.query_reparedetail re_upd set re_upd.RITEM_OID = temp_item_oid 

where re_upd.task_e_rprtaskid =t1.task_e_rprtaskid 

and re_upd.ritem_e_boardid=t1.ritem_e_boardid

and re_upd.RITEM_OID is null

and re_upd.LAST_UPDATE_DATE = 

(select max(re_last.LAST_UPDATE_DATE) 

from ecc_epm.query_reparedetail re_last 

where re_last.task_e_rprtaskid =t1.task_e_rprtaskid 

and re_last.ritem_e_boardid=t1.ritem_e_boardid

and re_last.RITEM_OID is null);

--删除其他记录

DELETE  FROM ecc_epm.query_reparedetail re_del

WHERE re_del.task_e_rprtaskid =t1.task_e_rprtaskid 

and re_del.ritem_e_boardid=t1.ritem_e_boardid

and re_del.RITEM_OID is null;

END IF ;

END IF;

if (mod(l_cnt, 50) = 0) then

 commit;

end if;

    END LOOP;

    commit; 

end hy_double_test;