Oracle資料庫的BULK COLLECT用法之批量增刪改<轉>
Oracle資料庫的BULK COLLECT用法之批量增刪改的相關知識是本文我們主要要介紹的內容,FORALL語句的一個關鍵性改進,它可以大大簡化程式碼,並且對於那些要在PL/SQL程式中更新很多行資料的程式來說,它可顯著提高其效能。
用FORALL來增強DML的處理能力
Oracle為Oracle8i中的PL/SQL引入了兩個新的資料操縱語言(DML)語句:BULK COLLECT和FORALL。這兩個語句在PL/SQL內部進行一種陣列處理;BULK COLLECT提供對資料的高速檢索,FORALL可大大改進INSERT、UPDATE和DELETE操作的效能。Oracle資料庫使用這些語句大大減少了。
PL/SQL與SQL語句執行引擎的環境切換次數,從而使其效能有了顯著提高。使用BULK COLLECT,你可以將多個行引入一個或多個集合中,而不是單獨變數或記錄中。下面這個BULK COLLECT的例項是將標題中包含有"PL/SQL"的所有書籍檢索出來並置於記錄的一個關聯陣列中,它們都位於通向該資料庫的單一通道中。
- DECLARE
- TYPE books_aat
- IS TABLE OF book%ROWTYPE
- INDEX BY PLS_INTEGER;
- books books_aat;
- BEGIN
- SELECT *
- BULK COLLECT INTO book
- FROM books
- WHERE title LIKE '%PL/SQL%';
- ...
- END;
類似地,FORALL將資料從一個PL/SQL集合傳送給指定的使用集合的表。下面的程式碼例項給出一個過程,即接收書籍資訊的一個巢狀表,並將該集合(繫結陣列)的全部內容插入該書籍表中。注意,這個例子還利用了Oracle9i的FORALL的增強功能,可以將一條記錄直接插入到表中。BULK COLLECT和FORALL都非常有用,它們不僅提高了效能,而且還簡化了為PL/SQL中的SQL操作所編寫的程式碼。下面的多行FORALL INSERT相當清楚地說明了為什麼PL/SQL被認為是Oracle資料庫的最佳程式語言。
- CREATE TYPE books_nt
- IS TABLE OF book%ROWTYPE;
- /
- CREATE OR REPLACE PROCEDURE add_books (
- books_in IN books_nt)
- IS
- BEGIN
- FORALL book_index
- IN books_in.FIRST .. books_in.LAST
- INSERT INTO book
- VALUES books_in(book_index);
- ...
- END;
不過在Oracle資料庫10g之前,以FORAll方式使用集合有一個重要的限制:該資料庫從IN範圍子句中的第一行到最後一行,依次讀取集合的內容。如果在該範圍內遇到一個未定義的行,Oracle資料庫將引發ORA-22160異常事件:ORA-22160: element at index [N] does notexist,對於FORALL的簡單應用,這一規則不會引起任何麻煩。但是,如果想盡可能地充分利用FORALL,那麼要求任意FORALL驅動陣列都要依次填充可能會增加程式的複雜性並降低效能。
在Oracle資料庫10g中,PL/SQL現在在FORALL語句中提供了兩個新子句:INDICES OF與VALUES OF,它們使你能夠仔細選擇驅動陣列中該由擴充套件DML語句來處理的行。
當繫結陣列為稀疏陣列或者包含有間隙時,INDICES OF會非常有用。該語句的語法結構為:
- FORALL indx IN INDICES
- OF sparse_collection
- INSERT INTO my_table
- VALUES sparse_collection (indx);
VALUES OF用於一種不同的情況:繫結陣列可以是稀疏陣列,也可以不是,但我只想使用該陣列中元素的一個子集。那麼我就可以使用VALUES OF來指向我希望在DML操作中使用的值。該語句的語法結構為:
- FORALL indx IN VALUES OF pointer_array
- INSERT INTO my_table
- VALUES binding_array (indx);
不用FOR迴圈而改用FORALL
假定我需要編寫一個程式,對合格員工(由comp_analysis.is_eligible函式確定)加薪,編寫關於不符合加薪條件的員工的報告並寫入employee_history表。我在一個非常大的公司工作;我們的員工非常非常多。對於一位PL/SQL開發人員來說,這並不是一項十分困難的工作。我甚至不需要使用BULKCOLLECT或FORALL就可以完成這項工作,如清單1所示,我使用一個CURSORFOR迴圈和單獨的INSERT及UPDATE語句。這樣的程式碼簡潔明瞭;不幸地是,我花了10分鐘來執行此程式碼,我的"老式"方法要執行30分鐘或更長時間。
清單 1:
- CREATE OR REPLACE PROCEDUREgive_raises_in_department (
- dept_in IN employee.department_id%TYPE
- , newsal IN employee.salary%TYPE
- )
- IS
- CURSOR emp_cur
- IS
- SELECT employee_id, salary, hire_date
- FROM employee
- WHERE department_id = dept_in;
- BEGIN
- FOR emp_rec IN emp_cur
- LOOP
- IF comp_analysis.is_eligible (emp_rec.employee_id)
- THEN
- UPDATE employee
- SET salary = newsal
- WHERE employee_id =emp_rec.employee_id;
- ELSE
- INSERT INTO employee_history
- (employee_id, salary
- , hire_date, activity
- )
- VALUES (emp_rec.employee_id,emp_rec.salary
- , emp_rec.hire_date,'RAISE DENIED'
- );
- END IF;
- END LOOP;
- END give_raises_in_department;
好在我公司的資料庫升級到了Oracle9i,而且更幸運的是,在最近的Oracle研討會上(以及Oracle技術網站提供的非常不錯的演示中)我瞭解到了批量處理方法。所以我決定使用集合與批量處理方法重新編寫程式。寫好的程式如清單2所示。
清單 2:
- CREATE OR REPLACE PROCEDUREgive_raises_in_department (
- dept_in IN employee.department_id%TYPE
- ,newsal IN employee.salary%TYPE
- )
- IS
- TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
- INDEX BY PLS_INTEGER;
- TYPE salary_aat IS TABLE OF employee.salary%TYPE
- INDEX BY PLS_INTEGER;
- TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE
- INDEX BY PLS_INTEGER;
- employee_ids employee_aat;
- salaries salary_aat;
- hire_dates hire_date_aat;
- approved_employee_ids employee_aat;
- denied_employee_ids employee_aat;
- denied_salaries salary_aat;
- denied_hire_dates hire_date_aat;
- PROCEDURE retrieve_employee_info
- IS
- BEGIN
- SELECT employee_id, salary, hire_date
- BULK COLLECT INTO employee_ids, salaries, hire_dates
- FROM employee
- WHERE department_id = dept_in;
- END;
- PROCEDURE partition_by_eligibility
- IS
- BEGIN
- FOR indx IN employee_ids.FIRST .. employee_ids.LAST
- LOOP
- IF comp_analysis.is_eligible (employee_ids (indx))
- THEN
- approved_employee_ids (indx) :=employee_ids (indx);
- ELSE
- denied_employee_ids (indx) :=employee_ids (indx);
- denied_salaries (indx) :=salaries (indx);
- denied_hire_dates (indx) :=hire_dates (indx);
- END IF;
- END LOOP;
- END;
- PROCEDURE add_to_history
- IS
- BEGIN
- FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST
- INSERT INTO employee_history
- (employee_id
- , salary
- , hire_date, activity
- )
- VALUES (denied_employee_ids(indx)
- , denied_salaries (indx)
- , denied_hire_dates(indx), 'RAISE DENIED'
- );
- END;
- PROCEDURE give_the_raise
- IS
- BEGIN
- FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST
- UPDATE employee
- SET salary = newsal
- WHERE employee_id =approved_employee_ids (indx);
- END;
- BEGIN
- retrieve_employee_info;
- partition_by_eligibility;
- add_to_history;
- give_the_raise;
- END give_raises_in_department;
掃一眼清單1 和清單2 就會清楚地認識到:改用集合和批量處理方法將增加程式碼量和複雜性。但是,如果你需要大幅度提升效能,這還是值得的。下面,我們不看這些程式碼,我們來看一看當使用FORALL時,用什麼來處理CURSORFOR迴圈內的條件邏輯。
定義集合型別與集合
在清單2中,宣告段的第一部分(第6行至第11行)定義了幾種不同的集合型別,與我將從員工表檢索出的列相對應。我更喜歡基於employee% ROWTYPE來宣告一個集合型別,但是FORALL還不支援對某些記錄集合的操作,在這樣的記錄中,我將引用個別欄位。所以,我還必須為員工ID、薪金和僱用日期分別宣告其各自的集合。
接下來為每一列宣告所需的集合(第13行至第21行)。首先定義與所查詢列相對應的集合(第13行至第15行):
- employee_ids employee_aat;
- salaries salary_aat;
- hire_dates hire_date_aat;
然後我需要一個新的集合,用於存放已被批准加薪的員工的ID(第17行):approved_employee_ids employee_aat;
最後,我再為每一列宣告一個集合(第19行至第21行),用於記錄沒有加薪資格的員工:
- denied_employee_ids employee_aat;
- denied_salaries salary_aat;
- denied_hire_dates hire_date_aat;
關於Oracle資料庫的bulk collect用法之批量增刪改的相關操作就介紹到這裡了,希望本次的介紹能夠對您有所收穫!
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/22392018/viewspace-708168/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle 的 bulk collect用法Oracle
- oracle中bulk collect into用法 (批次效率提取遊標資料)Oracle
- Oracle、mysql資料庫增、刪、改OracleMySql資料庫
- pl/sql中bulk collect的用法SQL
- 多行資料的批處理之bulk collect
- Flutter資料庫Sqflite之增刪改查Flutter資料庫
- 使用BULK COLLECT+FORALL加速批量提交
- iOS FMDB資料庫之增刪改查使用iOS資料庫
- 批量刪除Oracle資料庫的資料Oracle資料庫
- 基本的資料庫增刪改查資料庫
- 使用bulk collect實現cursor 批量fetch!
- 資料的增刪改
- go——beego的資料庫增刪改查Go資料庫
- 關於mongodb資料庫的增刪改查MongoDB資料庫
- 搭建頁面:資料庫的增刪改查資料庫
- ORM實操之資料的增刪改查ORM
- Oracle批量建立、刪除資料庫表Oracle資料庫
- mogoose 建立資料庫並增刪改查Go資料庫
- 資料庫操作增刪改查模糊查資料庫
- 使用Bulk Collect提高Oracle查詢效率Oracle
- 資料庫的簡介和MySQL增刪改查資料庫MySql
- mybatis實現MySQL資料庫的增刪改查MyBatisMySql資料庫
- 使用Forall 與bulk collect的快速複製表資料
- angualrJs對資料庫資料處理的增刪改查JS資料庫
- YII1 增、刪、改、查資料庫操作資料庫
- Python操作SQLServer資料庫增刪改查PythonSQLServer資料庫
- MongoDB 資料庫建立刪除、表(集合)建立刪除、資料增刪改查MongoDB資料庫
- pandas(10):資料增刪改
- MySQL資料庫 ---MySQL表的增刪改查(進階)MySql資料庫
- 用jsp實現資料庫的增刪改查JS資料庫
- 遊標+bulk collect into limit的不同方法查詢資料MIT
- MySql 表資料的增、刪、改、查MySql
- Golang原生sql操作Mysql資料庫增刪改查GolangMySql資料庫
- 連線資料庫並實現增、刪、改、查資料庫
- 一次遷移思考的記錄--bulk_collect的limit用法MIT
- mysql資料增刪改查操作MySql
- Oracle中大批量刪除資料的方法(轉自)Oracle
- easyui treegrid增、刪、改及批量儲存UI