UPDATE T1 SET T1.C=(SELECT T2.A FROM T2 WHERE T2.B=T1.C)没建表,大概是这样。如果关联不到的,第一张表的C列会更新为空,如果想不为空,可以WHERE加EXISTS条件,或者SET中用NVL函数
可以用SQL语句解决的不必用游标。
已经解决了 -- Created on 2017/8/2 by 李维俊 declare -- Local variables here i integer; begin -- Test statements here declare cursor cur_cc is select *from test_yzz ; row_ez11 cur_cc%rowtype ; v_a varchar2 (20); v_aa varchar2 (20); v_aaa varchar2 (20); v_count number; masg varchar2(100); n_1 number; n_2 number; n_3 number; begin for row_ez11 in cur_cc loop begin select count(1) into n_1 from test_yzz2 where B=row_ez11.B; if n_1 > 0 then select A into v_a from test_yzz2 where B=row_ez11.B; update test_yzz set B=v_a where B=row_ez11.B; end if;
select count(1) into n_2 from test_yzz2 where B=row_ez11.C; if n_2 > 0 then select A into v_aa from test_yzz2 where B=row_ez11.C; update test_yzz set C=v_aa where C=row_ez11.C; end if;
select count(1) into n_3 from test_yzz2 where B=row_ez11.D; if n_3 > 0 then select A into v_aaa from test_yzz2 where B=row_ez11.D; update test_yzz set D=v_aaa where D=row_ez11.D; end if; exception when others then null; end;
end loop; exception when others then masg:=sqlerrm; dbms_output.put_line(masg); end;end;
UPDATE T1 SET T1.C=(SELECT T2.A FROM T2 WHERE T2.B=T1.C)没建表,大概是这样。如果关联不到的,第一张表的C列会更新为空,如果想不为空,可以WHERE加EXISTS条件,或者SET中用NVL函数
可以用SQL语句解决的不必用游标。
-- Created on 2017/8/2 by 李维俊
declare
-- Local variables here
i integer;
begin
-- Test statements here
declare
cursor cur_cc is select *from test_yzz ;
row_ez11 cur_cc%rowtype ;
v_a varchar2 (20);
v_aa varchar2 (20);
v_aaa varchar2 (20);
v_count number;
masg varchar2(100);
n_1 number;
n_2 number;
n_3 number;
begin
for row_ez11 in cur_cc loop
begin
select count(1) into n_1 from test_yzz2 where B=row_ez11.B;
if n_1 > 0 then
select A into v_a from test_yzz2 where B=row_ez11.B;
update test_yzz set B=v_a where B=row_ez11.B;
end if;
select count(1) into n_2 from test_yzz2 where B=row_ez11.C;
if n_2 > 0 then
select A into v_aa from test_yzz2 where B=row_ez11.C;
update test_yzz set C=v_aa where C=row_ez11.C;
end if;
select count(1) into n_3 from test_yzz2 where B=row_ez11.D;
if n_3 > 0 then
select A into v_aaa from test_yzz2 where B=row_ez11.D;
update test_yzz set D=v_aaa where D=row_ez11.D;
end if;
exception when others then
null;
end;
end loop;
exception when others then
masg:=sqlerrm;
dbms_output.put_line(masg);
end;end;