题目

如下题 请经常写存储过程 懂数据库Oracle 或者你知道如何写都帮帮我
更新khexpinfo表的历史数据customer_id 的值,根据khexpInfo表的id与centerMaterialuseDetail表export_Uid关联,--centerMaterialuseDetail表的batchinfoUId与Batchinfo表的fuid关联,得到customer_id--用distinct关键字,如果结果记录数大于1则不需要处理,--否则就将khexinfo表的customer_id更新为所查出的customer_Id值
-- 先根据khexpinfo的id查询,若记录数大于1则不执行第2条更新语句,否则执行第2条语句更新语句select distinct b.customer_id from centermaterialusedetail c ,batchinfo b where c.export_uid='154461' and c.batchinfo_uid=b.fuid;
update khexpinfo set customer_id=(select distinct b.customer_id from centermaterialusedetail c ,batchinfo b where c.export_uid='154461' and c.batchinfo_uid=b.fuid) where id=154461;
我这样只能一条一条更新,谁会写歌存储过程 ,循环帮我更新,一条一条更新真的不现实啊

提问时间：2020-11-27

答案

create procedure test is
cursor custom_list is
select id from khexpinfo;
custom_dic custom_list%ROWTYPE;
my_custom_id in number(20);
begin
for custom_dic in custom_list loop
begin
select distinct b.customer_id into my_custom_id from centermaterialusedetail c ,batchinfo b where c.export_uid=custom_dic.id and c.batchinfo_uid=b.fuid;
update khexpinfo set customer_id=my_custom_id where id=custom_dic.id;
exception when others then
my_custom_id:=0;
end;
end loop;
commit;
end;