-- 大概这个样子 begin for x in(select id from a where name is null) loop update a set name = (select name from b where id= x.id); end loop; end; /
在oracle中如果不加限制条件,会对所有记录进行更新,容易造成你的其他记录值被更新为null,参考写法: update tmp a set a.djh =(select b.qsxz from tmp2 b where a.bsm=b.bsm) where a.bsm in (select bsm from tmp2) 注:外面的where很重要,否则容易造成数据损坏,自己可以试验一下
CREATE OR REPLACE procedure p_fill_data(as_log OUT VARCHAR2) --创建存储过程 IS CURSOR c_table_a IS SELECT * FROM table_a FOR UPDATE; --定义表a游标 ct c_table_a%ROWTYPE; BEGIN OPEN c_table_a; LOOP fetch c_table_a into ct; exit when c_table_a%notfound; IF ct.card IS NULL OR ct.card = '' THEN --如果为空 UPDATE table_a SET card = (SELECT card FROM table_b WHERE id = ct.id); --从表b更新数据 END LOOP; CLOSE c_table_a; as_log:='执行成功!'; COMMIT; --提交 EXCEPTION --异常 when others then as_log := sqlcode || '--' || sqlerrm; ROLLBACK; end p_fill_data;
begin
for x in(select id from a where name is null) loop
update a
set name = (select name from b where id= x.id);
end loop;
end;
/
update tmp a
set a.djh =(select b.qsxz from tmp2 b where a.bsm=b.bsm)
where a.bsm in (select bsm from tmp2)
注:外面的where很重要,否则容易造成数据损坏,自己可以试验一下
CREATE OR REPLACE procedure p_fill_data(as_log OUT VARCHAR2) --创建存储过程
IS
CURSOR c_table_a IS SELECT * FROM table_a FOR UPDATE; --定义表a游标
ct c_table_a%ROWTYPE;
BEGIN
OPEN c_table_a;
LOOP
fetch c_table_a into ct;
exit when c_table_a%notfound;
IF ct.card IS NULL OR ct.card = '' THEN --如果为空
UPDATE table_a SET card = (SELECT card FROM table_b WHERE id = ct.id); --从表b更新数据
END LOOP;
CLOSE c_table_a;
as_log:='执行成功!';
COMMIT; --提交
EXCEPTION --异常
when others then
as_log := sqlcode || '--' || sqlerrm;
ROLLBACK;
end p_fill_data;