想从 1.首先从test表里面取出一个字段name
2.student表里面根据test表已经取出的name与 student表里面的name1匹配，最后取出student表里面的id
3.再根据test表里面的字段name 与 student表里面的name1相同 把 student表的id插入test表里面的oid
procedure df_id is
v_subg3efid number;
cursor cur is (select name
from test t);
begin
for cur_result in cur loop
begin
select id
into v_subid
from student
where rownum = 1
and name1 = cur_result.name;
update test t
set t.oid = v_subid
where t.name1 = cur_result.name;
end;
end loop;
commit ;
end df_id;
为什么这样写的存储过程会没有任何效果？
------解决方案--------------------
第二条记录报异常了应该。
- SQL code
CREATE OR PROCEDURE DF_ID IS V_SUBG3EFID NUMBER; CURSOR CUR IS( SELECT NAME FROM TEST T);BEGIN FOR CUR_RESULT IN CUR LOOP BEGIN SELECT ID INTO V_SUBID FROM STUDENT WHERE ROWNUM = 1 AND NAME1 = CUR_RESULT.NAME; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; UPDATE TEST T SET T.OID = V_SUBID WHERE T.NAME1 = CUR_RESULT.NAME; END LOOP; COMMIT;END DF_ID;
------解决方案--------------------
- SQL code
--或者CREATE OR PROCEDURE DF_ID IS V_SUBG3EFID NUMBER; CURSOR CUR IS( SELECT NAME FROM TEST T);BEGIN FOR CUR_RESULT IN CUR LOOP BEGIN SELECT ID INTO V_SUBID FROM STUDENT WHERE ROWNUM = 1 AND NAME1 = CUR_RESULT.NAME; EXCEPTION WHEN NO_DATA_FOUND THEN GO TO LABLE1; END; UPDATE TEST T SET T.OID = V_SUBID WHERE T.NAME1 = CUR_RESULT.NAME; <<LABLE1>> END LOOP; COMMIT;END DF_ID;