存储过程
想从 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;