批量更新的陷阱?

記錄數與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/，如需轉載，請註明出處，否則將追究法律責任。