[20180110]Oracle's Bulk Collect and Limit.txt
--//链接提到https://www.databasejournal.com/features/oracle/oracles-bulk-collect-and-limit-can-be-a-problem.html
--//如果Bulk Collect使用Limit,可能导致处理数据丢失,应该引起开发足够重视,我不知道开发是否喜欢这样处理事务或者这样
--//写PL/sql语句.自己改写例子来说明问题.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table emp_test as select * from emp where 1=0;
Table created.
2.测试一:
$ cat limit.sql
DECLARE
TYPE empcoltyp IS TABLE OF emp%ROWTYPE;
emp_c empcoltyp;
CURSOR get_emp_data
IS
SELECT * FROM emp;
BEGIN
OPEN get_emp_data;
LOOP
FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
EXIT WHEN get_emp_data%NOTFOUND;
--exit when emp_c.count = 0;
FOR i IN 1 .. emp_c.COUNT
LOOP
DBMS_OUTPUT.put_line ( emp_c (i).empno || emp_c (i).ename || emp_c (i).sal);
insert into emp_test (empno, ename, sal) values (emp_c(i).empno, emp_c(i).ename, emp_c(i).sal);
END LOOP;
END LOOP;
commit;
END;
/
SCOTT@book> set serverout on
SCOTT@book> @ limit 9
old 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
new 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT 9;
7369SMITH800
7499ALLEN1600
7521WARD1250
7566JONES2975
7654MARTIN1250
7698BLAKE2850
7782CLARK2450
7788SCOTT3000
7839KING5000
7369SMITH800 =>再次输出一遍
7499ALLEN1600
7521WARD1250
7566JONES2975
7654MARTIN1250
7698BLAKE2850
7782CLARK2450
7788SCOTT3000
7839KING5000
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from emp_test;
COUNT(*)
----------
9
--//可以发现emp_test仅仅有9条记录。也就是漏处理了5条记录，而DBMS_OUTPUT.put_line的输出是18条（重复1次)
--//而使用参数7，正好整除,结果如下：
SCOTT@book> truncate table emp_test ;
Table truncated.
SCOTT@book> @ limit 7
old 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
new 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT 7;
7369SMITH800
7499ALLEN1600
7521WARD1250
7566JONES2975
7654MARTIN1250
7698BLAKE2850
7782CLARK2450
7788SCOTT3000
7839KING5000
7844TURNER1500
7876ADAMS1100
7900JAMES950
7902FORD3000
7934MILLER1300
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from emp_test;
COUNT(*)
----------
14
--//我自己还是不理解DBMS_OUTPUT.put_line会重复输出(limit 9)。
--//注：错误在我自己的设置，参考链接：http://blog.itpub.net/267265/viewspace-2150028/
SCOTT@book> truncate table emp_test ;
Table truncated.
SCOTT@book> @ limit 5
old 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
new 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT 5;
7369SMITH800
7499ALLEN1600
7521WARD1250
7566JONES2975
7654MARTIN1250
7698BLAKE2850
7782CLARK2450
7788SCOTT3000
7839KING5000
7844TURNER1500
PL/SQL procedure successfully completed.
SCOTT@book> select * from emp_test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH 800
7499 ALLEN 1600
7521 WARD 1250
7566 JONES 2975
7654 MARTIN 1250
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7844 TURNER 1500
10 rows selected.
--//这样是正确的!!而limit 9就存在问题(那位能解析)。如果换成
EXIT WHEN get_emp_data%NOTFOUND;
修改为
exit when emp_c.count = 0;
---//执行结果就是正确的。具体解析可以看链接。
3.测试二:
--//换成forall看看，错误应该也是一样的。
$ cat limit1.sql
/* Formatted on 2018/1/10 9:57:46 (QP5 v5.252.13127.32867) */
DECLARE
TYPE empcoltyp IS TABLE OF emp%ROWTYPE;
emp_c empcoltyp;
CURSOR get_emp_data
IS
SELECT * FROM emp;
BEGIN
OPEN get_emp_data;
LOOP
FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
EXIT WHEN get_emp_data%NOTFOUND;
--exit when emp_c.count = 0;
--FORALL i IN 1 .. emp_c.COUNT
-- DBMS_OUTPUT.put_line ( emp_c (i).empno || emp_c (i).ename || emp_c (i).sal);
--FORALL i IN 1 .. emp_c.COUNT
-- INSERT INTO emp_test (empno, ename, sal) VALUES (emp_c (i).empno, emp_c (i).ename, emp_c (i).sal);
FORALL i IN emp_c.first .. emp_c.last
INSERT INTO emp_test (empno, ename, sal) VALUES (emp_c (i).empno, emp_c (i).ename, emp_c (i).sal);
END LOOP;
COMMIT;
END;
/
--//forall不能执行DBMS_OUTPUT.put_line输出，仅仅能执行sql语句，好像仅仅1行语句。
--//测试:
SCOTT@book> truncate table emp_test ;
Table truncated.
SCOTT@book> @ limit1 9
old 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT &1;
new 13: FETCH get_emp_data BULK COLLECT INTO emp_c LIMIT 9;
PL/SQL procedure successfully completed.
SCOTT@book> select count(*) from emp_test;
COUNT(*)
----------
9
--//总之在具体开发中注意这个问题。
