没有环境,就手写一个给你吧,如果有问题,远程给你调试吧,看符合你的需求不 create or replace procedure testInsert(c_return out varchar2) as v_order_code varchar2(50); v_item_name varchar2(50); v_item_price varchar2(50); v_flag number; v_insert number; v_update number; cursor c_order_info is select order_code, item_name, item_price FROM order_info; begin v_insert:=0; v_update:=0; open c_order_info; loop fetch c_order_info into v_order_code,v_item_name,v_item_price; exit when c_order_info%notfound; select count(1) into v_flag from order_info_all where order_code=v_order_code; if v_flag>=1 then update order_info_all set item_name=v_item_name,item_price=v_item_price where order_code=v_order_code; v_insert:=v_insert+1; else insert into order_info_all(order_code,item_name,item_price) values(v_order_code,v_item_name,v_item_price); v_update:=v_update+1; end if; end loop; close c_order_info; c_return:='新增 '||v_insert||' 条,更新'||v_update||'条。'; commit; exception when others then rollback; c_return:=sqlerrm; end;
create or replace procedure testInsert(c_return out varchar2)
as
v_order_code varchar2(50);
v_item_name varchar2(50);
v_item_price varchar2(50);
v_flag number;
v_insert number;
v_update number;
cursor c_order_info is select order_code, item_name, item_price FROM order_info;
begin
v_insert:=0;
v_update:=0;
open c_order_info;
loop
fetch c_order_info into v_order_code,v_item_name,v_item_price;
exit when c_order_info%notfound;
select count(1) into v_flag from order_info_all where order_code=v_order_code;
if v_flag>=1 then
update order_info_all set item_name=v_item_name,item_price=v_item_price where order_code=v_order_code;
v_insert:=v_insert+1;
else
insert into order_info_all(order_code,item_name,item_price) values(v_order_code,v_item_name,v_item_price);
v_update:=v_update+1;
end if;
end loop;
close c_order_info;
c_return:='新增 '||v_insert||' 条,更新'||v_update||'条。';
commit;
exception when others then
rollback;
c_return:=sqlerrm;
end;