如何用用PL/SQL实现两表数据比对插入功能？？？

Question

表CCIQTEST没对好，对好的如下：
表CCIQTEST内容
code name
111  天堂                                                                       
112  地狱                                                                       
113  四川                                                                       
114  深圳                                                                       
115  海南                                                                       
126  黑夜

Answer 1

表CCIQTEST内容
code    name
111    天堂                                                                       
112    地狱                                                                       
113    四川                                                                       
114    深圳                                                                       
115    海南                                                                       
126    黑夜                                                                       

Answer 2

改方法如下，出现
declare
v_code cciqtest.code%type;
v_name cciqtest.name%type;
v_c_code customsciqtest.c_code%type;
cursor mycursor is
select name from customsciqtest;
begin
open mycursor;
loop
fetch mycursor into v_name;
exit when mycursor%notfound;
if v_name  in
(select name from cciqtest) then
select code,name into v_code,v_name from cciqtest cc where cc.name=v_name;
update customsciqtest cciq set cciq.c_code=v_code where cciq.name=v_name;
commit;
end if;
end loop;
close mycursor;
end;ORA-06550: 第 13 行, 第 1 列：
PLS-00405: 此上下文中不支持子查询
ORA-06550: 第 12 行, 第 1 列：
PL/SQL: Statement ignored

Answer 3

直接用sql:
update CUSTOMSCIQTEST a set ciq_code=(select code from CCIQTEST b where a.name=b.name);

Answer 4

UPDATE CUSTOMSCIQTEST  SET CIQ_CODE=(SELECT DISTINCT CODE FROM CCIQTEST WHERE 
CUSTOMSCIQTEST.NAME = CCIQTEST.NAME);
COMMIT;

Answer 5

create procedure upate_pro
as
cursor t_sor is
select * from CCIQTEST;
begin
for v_sor in t_sor loop
update CUSTOMSCIQTEST  SET CIQ_CODE=v_sor.CODE where NAME=v_sor.name;
end loop;
end;
/

Answer 6

commit;在游标执行的循环中执行，是不行的，会关闭并销毁游标的。

Answer 7

更正一下你的无名程序段
declare
v_code cciqtest.code%type;
v_name cciqtest.name%type;
v_c_code customsciqtest.c_code%type;
cursor mycursor is
select name from customsciqtest;
begin
open mycursor;
loop
fetch mycursor into v_name;
exit when mycursor%notfound;
 begin
  select code,name into v_code,v_name from cciqtest cc where cc.name=v_name;
  update customsciqtest cciq set cciq.c_code=v_code where cciq.name=v_name;
 exception when no_data_found
    null;
 end;
end loop;
close mycursor;
commit;
end;