[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
--//總之在具體開發中注意這個問題。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2149969/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 7.53 COLLECT
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解(二)PGA_AGGREGATE_LIMIT的作用OracleMIT
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解(三)PGA_AGGREGATE_LIMIT的大小設定OracleMIT
- 淺析Oracle(rownum)和Mysql(limit)分頁的區別OracleMySqlMIT
- Elasticsearch——mget及bulkElasticsearch
- sqlserver bulk insert報錯Cannot bulk load because the file could not be opened.SQLServer
- 用 Laravel Collect 收藏文章Laravel
- Go 語言中的 collect 使用Go
- BULK In-BIND與RECORD(轉)
- mongodb批量操作, bulk_write,MongoDB
- bulk forall 的測試(轉)
- 最新kali之bulk_extractor
- linux limit限制LinuxMIT
- 極限limitMIT
- Oracle12c叢集啟動時提示%CRS_LIMIT_OPENFILE%: invalid numberOracleMIT
- 【TUNE_ORACLE】PGA_AGGREGATE_LIMIT詳解(一)PGA_AGGREGATE_TARGET的限制OracleMIT
- How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)Database
- vue i18n-collect工具開發Vue
- set_time_limitMIT
- Mysql LIMIT的用法MySqlMIT
- How to open and close static streams in a USB bulk endpoint
- Script to Collect Log File Sync Diagnostic Information (lfsdiag.sql)ORMSQL
- MySQL中limit的用法MySqlMIT
- DMSQL LIMIT限定條件SQLMIT
- ElasticSearch7.4批量匯入_bulkElasticsearch
- 【elasticsearch】bulk api奇特的json格式的原因ElasticsearchAPIJSON
- Signac處理bulk ATAC-seq資料
- Java8之Stream-強大的collect操作Java
- SQL優化之limit 1SQL優化MIT
- Laravel 中 offset,limit 的使用LaravelMIT
- SQL之limit子句的使用SQLMIT
- mysql 使用技巧 分頁limitMySqlMIT
- sql中limit使用方法SQLMIT
- [20220223]Index ITL Limit.txtIndexMIT
- SAP WM高階之上架策略B (Bulk Storage)
- mysql分頁-limit offset分頁MySqlMIT
- db2 hadr_spool_limitDB2MIT
- MySQL LIMIT 和 ORDER BY 最佳化MySqlMIT
- JavaScript Promise Time Limit Function All In OneJavaScriptPromiseMITFunction