创建(带参数):
create or replace procedure saveMaterial( obj in varchar) as
cursor ivv is select * from user_sjlr_sub i where i.shtinsid = obj and not exists (select 1 from user_material_sub b where i.itemcode = b.itemcode and i.invorgid = b.invorgid);
t_row ivv%rowtype;
t_maxnum number;
begin
select max(keyid) into t_maxnum from user_material_sub where shtinsid = 12259;
dbms_output.put_line('maxnum='||t_maxnum);
for t_row in ivv loop
t_maxnum := t_maxnum+1;
dbms_output.put_line('maxnum='||t_maxnum);
insert into user_material_sub a
(a.shtinsid,
a.invorgid,
a.itemcode,
a.itemdesc,
a.wlzt,
a.ywbz,
a.price,
a.keyid)
values
(
'12259',
t_row.invorgid,
t_row.itemcode,
t_row.itemdesc,
t_row.wlzt,
t_row.ywbz,
t_row.price,
t_maxnum
);
end loop;
commit;
end;
调用:
begin
saveMaterial('6655737');
end;