create table A
(
a_id number primary key,
a_name varchar2(20),
a_colName varchar2(50)
);
create table B
(
b_id number primary key,
b_colName varchar2(50)
);
create table C
(
c_id number primary key,
c_name varchar(20)
);
create sequence seq_A;
create sequence seq_B;
create sequence seq_C;insert into B values(seq_B.Nextval,'colName1');
insert into B values(seq_B.Nextval,'colName2');
insert into B values(seq_B.Nextval,'colName3');insert into C values(seq_C.Nextval,'Name1');
insert into C values(seq_C.Nextval,'Name2');
insert into C values(seq_C.Nextval,'Name3');insert into A(a_id,a_name) select seq_A.Nextval,c.c_name from C c
select * from A;
--显示结果
-- A_ID A_NAME A_COLNAME
-- 1 Name1
-- 2 Name2
-- 3 Name3 --问题 怎样才能把 A_COLNAME 列填满 要求值是从 B 表 b_colName 列而来(考虑性能)--我是这样做的 ,我希望有一种能批量的方法 (oracle 10g)
declare
b_colname varchar2(15);
cursor mycur is select b.b_colname from B b ;
begin
open mycur;
loop
fetch mycur into b_colname;
--update A表
exit when mycur%notfound;
dbms_output.put_line(b_colname );
end loop;
close mycur;
end;
或者用forall
cursor cur_b is select b_id,b_colname from B ;
begin
for t_cur_b in cur_b loop
update A
set a_colName = t_cur_b.b_colname
where a_id = t_cur_b.b_id;
end loop;
end;
如果要用过程的话
不知道你b表有多大
考虑使用
fetch .. bulk collect into [pl/sql表]
然后使用forall命令来批量执行
cursor cur_b is select b_id,b_colname from B ;
begin
for t_cur_b in cur_b loop
update A
set a_colName = t_cur_b.b_colname
where a_id = t_cur_b.b_id;
end loop;
end;
试了不行 你查b_id干嘛?
set a_colName = (select b_colname from B b where b.b_id = a.a_id);
update A a set a_colName = (select b_colname from B b where b.b_id = a.a_id);
我可没说A表和B表有关系 他们是每外键关系的
算了,给你个例子参考下
针对你要的批量
DECLARE
TYPE tmp IS TABLE OF b%rowTYPE INDEX BY binary_integer;
b_tmp tmp;
cursor mycur is select * from B b ;
begin
open mycur;
loop
fetch mycur bulk collect into b_tmp limit 10000;
exit WHEN mycur%notfound;
forall i IN 1..b_tmp.count
update A set a_col=b_tmp(i).b_col where aid=b_tmp(i).bid;
commit;
end loop;
close mycur;
END;
#15楼update a set A_COLNAME=select b_colname from B难道还能这么写?select b_colname from B 返回的可是n行 肯定错