标签搜索

[20180110]Oracle Bulk Collect and Limit

冰封一夏
2021-09-29 01:11:40 / 63 阅读 / 正在检测是否收录...

[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

--//总之在具体开发中注意这个问题。

7

评论

博主关闭了所有页面的评论