Question

oracle存储过程循环执行SQL语句

declare
sql_tem Varchar2(4000);
a number ;
b number ;
i number;
begin
a := 1;
b := 5;
for i in 1..3 loop
sql_tem := 'insert into A2 (ID,NAME) (select ID,NAME from A1 WHERE ROWNUM between :1 and :2)';

EXECUTE IMMEDIATE sql_tem USING a,b;
commit;
a := a + b;
b := b + 5;
end loop;
end;
这段代码不好用，需要做表A1 1-5行的数据插入到A2，第二次6-10行的数据插入到A2，一次类推，原因出现在a和b的增量问题，a,b值不变，循环3次是可以的。求高手帮我看看哪里出了问题

Answer 1

实现方式错了，批量移动数据应该使用Cursor，而不是像分页那样每次都查询。
每次都查询可能会导致重复数据。
正确方式应该是打开一个Cursor，循环Cursor来插入，使用计数器来控制每次COMMIT的行数：
declare
TYPE R_CURSOR IS REF CURSOR;
i number;
a1_cursor R_CURSOR;
a1_row A1%ROWTYPE;
begin
open a1_cursor FOR
select ID, NAME from A1;
i := 0;
loop
fetch a1_cursor
into a1_row;
exit when a1_cursor%notfound;
INSERT INTO A2 VALUES a1_row;
i := i + 1;
if i >= 5 then
commit;
i := 0;
end if;
end loop;
close a1_cursor;
commit;
end;

Answer 2

declare
sql_tem Varchar2(4000);
a number;
b number;
i number;
begin
a := 1;
for i in 1 .. 3 loop
b := a + 4;
sql_tem := 'insert into A2 (ID,NAME) (select ID,NAME from A1 WHERE ROWNUM between :1 and :2)';

EXECUTE IMMEDIATE sql_tem
USING a, b;
commit;
a := a + 5;
end loop;
end;

试试上面的代码看一下能不能满意你的要求先呗。。。

追问

还是不行，效果还是一样的只是插入了1-5，后面都没有，只要a,b变化了 ，就不执行了

追答

改成下面的试一下，原因是 Oracle的伪列rownum不支持>,>=,=,between...and
declare
sql_tem Varchar2(4000);
a number;
b number;
i number;
begin
a := 1;
for i in 1 .. 3 loop
b := a + 4;
sql_tem := 'insert into A2 (ID, NAME) (select ID,NAME from (select a.*, rownum as RN from B2B_BC_TXN_ORDER_log a where rownum = :2)';

EXECUTE IMMEDIATE sql_tem
USING b, a;
commit;
a := a + 5;
end loop;
end;

Answer 3

a := a + 5;

b := b + 5;

原来的a := a + b; 有问题
b := b + 5;

Answer 4

commit;
a := a + 5;
b := b + 5;
end loop;

改成这样即可。
for i in 1..8 loop
a=1 b=5
a=6 b=10
a=11 b=15
a=16 b=20
a=21 b=25
a=26 b=30
a=31 b=35
a=36 b=40