有两个表 the_d_exam_result_item (exam_result_item_id,exam_result_frame_id)
the_d_exam_result_frame(exam_result_item_id,exam_result_frame_id)
其中the_d_exam_result_item 中的exam_result_frame_id表没有数据
我想根据表the_d_exam_result_frame的数据,插入到the_d_exam_result_item中
请问下面的代码应该怎么改
declare
v_item_id number;
v_frame_id number;
CURSOR cur_cursor
is
select exam_result_item_id from the_d_exam_result_item;
begin
open cur_cursor;
loop
fetch cur_cursor into v_item_id;
begin
--if ( select count(1) from the_d_exam_result_frame where exam_result_item_id = v_frame_id ) >0 then
select exam_result_frame_id into v_frame_id from the_d_exam_result_frame where exam_result_item_id = v_frame_id;
--end if;
update the_d_exam_result_item set exam_result_id= v_frame_id where EXAM_RESULT_ITEM_ID = v_item_id;
commit;
end;
exit when cur_cursor%notfound;
end loop;
end;
the_d_exam_result_frame(exam_result_item_id,exam_result_frame_id)
其中the_d_exam_result_item 中的exam_result_frame_id表没有数据
我想根据表the_d_exam_result_frame的数据,插入到the_d_exam_result_item中
请问下面的代码应该怎么改
declare
v_item_id number;
v_frame_id number;
CURSOR cur_cursor
is
select exam_result_item_id from the_d_exam_result_item;
begin
open cur_cursor;
loop
fetch cur_cursor into v_item_id;
begin
--if ( select count(1) from the_d_exam_result_frame where exam_result_item_id = v_frame_id ) >0 then
select exam_result_frame_id into v_frame_id from the_d_exam_result_frame where exam_result_item_id = v_frame_id;
--end if;
update the_d_exam_result_item set exam_result_id= v_frame_id where EXAM_RESULT_ITEM_ID = v_item_id;
commit;
end;
exit when cur_cursor%notfound;
end loop;
end;
declare
v_item_id number;
v_frame_id number;
CURSOR cur_cursor
is
select exam_result_item_id from the_d_exam_result_item;
begin
open cur_cursor;
loop
fetch cur_cursor into v_item_id; exit when cur_cursor%notfound;
--begin
--if ( select count(1) from the_d_exam_result_frame where exam_result_item_id = v_frame_id ) >0 then
execute immediate 'select exam_result_frame_id from the_d_exam_result_frame where exam_result_item_id =:1'
into v_frame_id
using v_item_id;
--end if;
--exam_result_id改為exam_result_frame_id,因你提供的表結構是這個列
update the_d_exam_result_item set exam_result_frame_id= v_frame_id where EXAM_RESULT_ITEM_ID = v_item_id;
--commit;
--end;
end loop;
commit;--只能放到loop外層
end;
/
v_item_id number;
v_frame_id number;
CURSOR cur_cursor
is
select exam_result_item_id from the_d_exam_result_item;
begin
open cur_cursor;
loop
fetch cur_cursor into v_item_id; exit when cur_cursor%notfound;
--begin
--if ( select count(1) from the_d_exam_result_frame where exam_result_item_id = v_frame_id ) >0 then
execute immediate 'select exam_result_frame_id from the_d_exam_result_frame where exam_result_item_id =:1'
into v_frame_id
using v_item_id;
--end if;
--exam_result_id改為exam_result_frame_id,因你提供的表結構是這個列
update the_d_exam_result_item set exam_result_frame_id= v_frame_id where EXAM_RESULT_ITEM_ID = v_item_id;
--commit;
--end;
end loop;
commit;--只能放到loop外層
end;
/