create or replace procedure moveA2B
as
var_cur sys_refcursor;
var_rec A%ROWTYPE;
begin
open var_cur for select a1,a2,a3,a4 from A;
loop
fetch var_cur into var_rec;
exit when var_cur%NOTFOUND;
insert into B(b1,b2,b3) values (var_rec.a1,var_rec.a2,var_rec.a4);
insert into B(b1,b2,b3) values (var_rec.a1,var_rec.a3,var_rec.a4);
end loop;
close var_cur;
dbms_output.put_line('done!');
exception when others then
raise;
end;
open mycursor for '
select a1,a2,a3,a4 from TABLE1
';
loop
fetch mycursor into col1, col2, col3, col4;--把游标中的数据存入变量,可以有多个变量
if mycursortest%found then --如果找到数据,找不到数据为notfound
insert into TABLE2
(b1,b2,b3
) values
(col1,col2,col4
);
insert into TABLE2
(b1,b2,b3
) values
(col1,col3,col4
);
else
dbms_output.put_line
(
'处理完毕'
)
;
exit;--退出循环
end if;
end loop;
close mycursortest;--关闭游标