declare
v_oid raw(32);
v_id varchar2(50);
v_materialcode varchar2(50);
v_lastrevision number(4);
v_row number(4) := 0;
cursor emp_cursor is
select superid
from dqplm.yt_test
where superid in (select plm_m_id from plm_psm_itemmaster_revision)
and superid like 'M%'; -- where superid like 'B1320E-181100A%';
begin
open emp_cursor;
fetch emp_cursor
into v_id;
loop
exit when emp_cursor%NOTFOUND;
--Dbms_Output.put_line(v_id);
select count(*)
into v_row
from plm_psm_itemmaster_revision m
where PLM_M_ID = v_id
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_m_class = 'TIPART';
if v_row = 1 then
select plm_r_oid, m.plm_r_revision
into v_oid, v_lastrevision
from plm_psm_itemmaster_revision m
where PLM_M_ID = v_id
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_m_class = 'TIPART';
select subid
into v_materialcode
from dqplm.yt_test WT
where WT.SUPERID = v_id;
/*Dbms_Output.put_line(v_oid);
Dbms_Output.put_line(v_materialcode);
Dbms_Output.put_line(v_lastrevision);*/
update plm_cus_tipart
set plm_materialcode = v_materialcode
where plm_revisionoid = v_oid
and plm_iteration = v_lastrevision;
else
Dbms_Output.put_line(v_id);
exit;
end if;
fetch emp_cursor
into v_id;
end loop;
close emp_cursor;
--commit;
end;从一张表里读取一个字段,并更新到另一张表里相同的字段,条件类似t1.a = t2.a + 其他条件
v_oid raw(32);
v_id varchar2(50);
v_materialcode varchar2(50);
v_lastrevision number(4);
v_row number(4) := 0;
cursor emp_cursor is
select superid
from dqplm.yt_test
where superid in (select plm_m_id from plm_psm_itemmaster_revision)
and superid like 'M%'; -- where superid like 'B1320E-181100A%';
begin
open emp_cursor;
fetch emp_cursor
into v_id;
loop
exit when emp_cursor%NOTFOUND;
--Dbms_Output.put_line(v_id);
select count(*)
into v_row
from plm_psm_itemmaster_revision m
where PLM_M_ID = v_id
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_m_class = 'TIPART';
if v_row = 1 then
select plm_r_oid, m.plm_r_revision
into v_oid, v_lastrevision
from plm_psm_itemmaster_revision m
where PLM_M_ID = v_id
and m.plm_m_lastrevision = m.plm_r_revision
and m.plm_m_class = 'TIPART';
select subid
into v_materialcode
from dqplm.yt_test WT
where WT.SUPERID = v_id;
/*Dbms_Output.put_line(v_oid);
Dbms_Output.put_line(v_materialcode);
Dbms_Output.put_line(v_lastrevision);*/
update plm_cus_tipart
set plm_materialcode = v_materialcode
where plm_revisionoid = v_oid
and plm_iteration = v_lastrevision;
else
Dbms_Output.put_line(v_id);
exit;
end if;
fetch emp_cursor
into v_id;
end loop;
close emp_cursor;
--commit;
end;从一张表里读取一个字段,并更新到另一张表里相同的字段,条件类似t1.a = t2.a + 其他条件
v_Row Number(4) := 0;
Begin
For x In (Select Plm_m_Id As Superid
From Plm_Psm_Itemmaster_Revision
Where Plm_m_Id Like 'M%') Loop
Select Count(*)
Into v_Row
From Plm_Psm_Itemmaster_Revision m
Where Plm_m_Id = x.Superid
And m.Plm_m_Lastrevision = m.Plm_r_Revision
And m.Plm_m_Class = 'TIPART';
If v_Row = 1 Then
Update Plm_Cus_Tipart n
Set Plm_Materialcode = (Select Subid
From Dqplm.Yt_Test Wt
Where Wt.Superid = x.Superid)
Where Exists (Select '1'
From Plm_Psm_Itemmaster_Revision m
Where Plm_m_Id = x.Superid
And m.Plm_m_Lastrevision = m.Plm_r_Revision
And m.Plm_m_Class = 'TIPART'
And Plm_r_Oid = n.Plm_Revisionoid
And Plm_r_Revision = n.Plm_Iteration);
Else
Dbms_Output.Put_Line(v_Id);
Exit;
End If;
End Loop;
End;