[20180110]Oracle Bulk Collect and Limit

[20180110]Oracle's Bulk Collect and Limit.txt

--//連結提到
--//如果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

--//總之在具體開發中注意這個問題。