批量更新的陷阱?

记录数与EXIT WHEN %NOTFOUND;的位置关系.

测试表和过程如下:

drop table test_log
/

create table test_log
(
message varchar2(50)
)
/

drop table test_1
/

create table test_1
as
select object_id ,object_name
from dba_objects
/
insert into test_1 select * from test_1
/
insert into test_1 select * from test_1
/
commit
/

-- 记录数有227760条

drop table test_2
/
create table test_2
as
select object_id ,object_name
from dba_objects
where 1=2
/

CREATE OR REPLACE PROCEDURE sp_gd_cs_test
IS
TYPE t_id IS TABLE OF test_1.object_id%TYPE;

TYPE t_name IS TABLE OF test_1.object_name%TYPE;

v_id t_id;
v_name t_name;
v_count NUMBER (10);
v_i NUMBER (10);

CURSOR cur_t
IS
SELECT *
FROM test_1;
BEGIN
DELETE test_log;

DELETE test_2;

COMMIT;
v_i := 1;

OPEN cur_t;

LOOP
FETCH cur_t
BULK COLLECT INTO v_id,
v_name LIMIT 10000;

v_count := cur_t%ROWCOUNT;

INSERT INTO test_log
VALUES ( '第'
|| v_i
|| '次:记录条数:'
|| v_count
);

FORALL i IN 1 .. v_id.COUNT
INSERT INTO test_2
VALUES (v_id (i),
v_name (i)
);
v_i := v_i
+ 1;
EXIT WHEN cur_t%NOTFOUND;
END LOOP;

CLOSE cur_t;
END;
/

SQL>exec sp_gd_cs_test;

SQL> select count(*) from test_2
2 /

COUNT(*)
----------
227760

这是正确的.

把 EXIT WHEN cur_t%NOTFOUND;放在Fetch后面,再执行一次:

SQL>exec sp_gd_cs_test;

SQL> select count(*) from test_2
2 /

COUNT(*)
----------
220000

结果把最后7760条记录忽略了.

查看文档中......

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21992/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6906/viewspace-21992/