有两个表 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;
解决方案 »
- 请问:有关在10g版本中,Oarcle的官方文档明确指出增量备份只有0和1两种级别?
- oracle查询问题,望指教
- Oracle存储过程 符号转换
- 怎样用代码实现ORACLE客户端NET8服务连接的功能?
- 关于oracle数据库的连接问题。
- 菜鸟请教:如何利用ide工具修改orcale8某个表的字段名?
- 求助一个简单的问题:将数据写到oracle数据库里,命令该怎么写?
- Oracle 9i 中的 ORA-27040问题
- 高分请教一个小问题
- 初学者的问题
- ASP.NET调用Oracle存储过程问题
- 详细资料 ORA-12505: TNS: 监听程序当前无法识别连接描述符中所给出的 SID (DBD ERROR: OCIServerAttach)(在线等)
--哥們,你這是重復帖唉
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;
/
这行有的时候会找不到数据,,,请问应该怎么改啊
--聲明個變量,取記錄倏數,如果存在,則執行,不存在,不執行
declare
v_item_id number;
v_frame_id number;
CURSOR cur_cursor
is
select exam_result_item_id from the_d_exam_result_item;
v_count int:=0;
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 count(*) from the_d_exam_result_frame where exam_result_item_id =:1'
into v_count
using v_item_id;
if v_count<>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 if;
end loop;
commit;--只能放到loop外層
end;
/
create or replace procedure zhuzhusex7984_upd(
v_rows int:=1000
)
as
v_sql varchar(4000) := 'update the_d_exam_result_item
set exam_result_frame_id=(select exam_result_frame_id
from the_d_exam_result_frame b
where the_d_exam_result_item.exam_result_item_id=b.exam_result_item_id
and the_d_exam_result_item.exam_result_frame_id<>b.exam_result_frame_id)
where rownum<:1 and exists(select 1 from the_d_exam_result_frame c where
the_d_exam_result_item.exam_result_item_id=c.exam_result_item_id
and the_d_exam_result_item.exam_result_frame_id<>c.exam_result_frame_id)';
begin
execute immediate v_sql
using v_rows;
commit;
end;
/
--执行批次更新
declare
v_rows int := 0;
v_sql varchar2(4000) :='select count(1) from the_d_exam_result_item a,the_d_exam_result_frame b
where a.exam_result_item_id=b.exam_result_item_id
and a.exam_result_frame_id<>b.exam_result_frame_id';
begin
loop
execute immediate v_sql
into v_rows;
exit when v_rows=0;
zhuzhusex7984_upd;
--zhuzhusex7984_upd(10000);--如果需要根据你记录的多少传入每次更新的记录数量就可以了
end loop;
end;
/
--執行下面這句,看下有多少倏記錄
select count(1) from the_d_exam_result_item a,the_d_exam_result_frame b
where a.exam_result_item_id=b.exam_result_item_id
and a.exam_result_frame_id<>b.exam_result_frame_id;