自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

Oracle數(shù)據(jù)庫(kù)的BULK COLLECT用法之批量增刪改

作者：jojo52013145
數(shù)據(jù)庫(kù) Oracle
本文我們主要介紹了Oracle數(shù)據(jù)庫(kù)的bulk collect用法之批量增刪改的相關(guān)操作，希望能夠?qū)δ兴斋@！

Oracle數(shù)據(jù)庫(kù)的BULK COLLECT用法之批量增刪改的相關(guān)知識(shí)是本文我們主要要介紹的內(nèi)容，FORALL語(yǔ)句的一個(gè)關(guān)鍵性改進(jìn)，它可以大大簡(jiǎn)化代碼，并且對(duì)于那些要在PL/SQL程序中更新很多行數(shù)據(jù)的程序來(lái)說(shuō)，它可顯著提高其性能。

用FORALL來(lái)增強(qiáng)DML的處理能力

Oracle為Oracle8i中的PL/SQL引入了兩個(gè)新的數(shù)據(jù)操縱語(yǔ)言（DML）語(yǔ)句：BULK COLLECT和FORALL。這兩個(gè)語(yǔ)句在PL/SQL內(nèi)部進(jìn)行一種數(shù)組處理；BULK COLLECT提供對(duì)數(shù)據(jù)的高速檢索，F(xiàn)ORALL可大大改進(jìn)INSERT、UPDATE和DELETE操作的性能。Oracle數(shù)據(jù)庫(kù)使用這些語(yǔ)句大大減少了。

PL/SQL與SQL語(yǔ)句執(zhí)行引擎的環(huán)境切換次數(shù)，從而使其性能有了顯著提高。使用BULK COLLECT，你可以將多個(gè)行引入一個(gè)或多個(gè)集合中，而不是單獨(dú)變量或記錄中。下面這個(gè)BULK COLLECT的實(shí)例是將標(biāo)題中包含有"PL/SQL"的所有書(shū)籍檢索出來(lái)并置于記錄的一個(gè)關(guān)聯(lián)數(shù)組中，它們都位于通向該數(shù)據(jù)庫(kù)的單一通道中。

 

  1. DECLARE  
  2. TYPE books_aat  
  3. IS TABLE OF book%ROWTYPE  
  4. INDEX BY PLS_INTEGER;  
  5. books books_aat;  
  6. BEGIN  
  7. SELECT *  
  8. BULK COLLECT INTO book  
  9. FROM books  
  10. WHERE title LIKE '%PL/SQL%';  
  11. ...  
  12. END; 

 

類似地，F(xiàn)ORALL將數(shù)據(jù)從一個(gè)PL/SQL集合傳送給指定的使用集合的表。下面的代碼實(shí)例給出一個(gè)過(guò)程，即接收書(shū)籍信息的一個(gè)嵌套表，并將該集合（綁定數(shù)組）的全部?jī)?nèi)容插入該書(shū)籍表中。注意，這個(gè)例子還利用了Oracle9i的FORALL的增強(qiáng)功能，可以將一條記錄直接插入到表中。BULK COLLECT和FORALL都非常有用，它們不僅提高了性能，而且還簡(jiǎn)化了為PL/SQL中的SQL操作所編寫(xiě)的代碼。下面的多行FORALL INSERT相當(dāng)清楚地說(shuō)明了為什么PL/SQL被認(rèn)為是Oracle數(shù)據(jù)庫(kù)的***編程語(yǔ)言。

 

  1. CREATE TYPE books_nt  
  2. IS TABLE OF book%ROWTYPE;  
  3. /  
  4. CREATE OR REPLACE PROCEDURE add_books (  
  5. books_in IN books_nt)  
  6. IS  
  7. BEGIN  
  8. FORALL book_index  
  9. IN books_in.FIRST .. books_in.LAST  
  10. INSERT INTO book  
  11. VALUES books_in(book_index);  
  12. ...  
  13. END; 

 

不過(guò)在Oracle數(shù)據(jù)庫(kù)10g之前，以FORAll方式使用集合有一個(gè)重要的限制：該數(shù)據(jù)庫(kù)從IN范圍子句中的***行到***一行，依次讀取集合的內(nèi)容。如果在該范圍內(nèi)遇到一個(gè)未定義的行，Oracle數(shù)據(jù)庫(kù)將引發(fā)ORA-22160異常事件：ORA-22160: element at index [N] does notexist，對(duì)于FORALL的簡(jiǎn)單應(yīng)用，這一規(guī)則不會(huì)引起任何麻煩。但是，如果想盡可能地充分利用FORALL，那么要求任意FORALL驅(qū)動(dòng)數(shù)組都要依次填充可能會(huì)增加程序的復(fù)雜性并降低性能。

在Oracle數(shù)據(jù)庫(kù)10g中，PL/SQL現(xiàn)在在FORALL語(yǔ)句中提供了兩個(gè)新子句：INDICES OF與VALUES OF，它們使你能夠仔細(xì)選擇驅(qū)動(dòng)數(shù)組中該由擴(kuò)展DML語(yǔ)句來(lái)處理的行。

當(dāng)綁定數(shù)組為稀疏數(shù)組或者包含有間隙時(shí)，INDICES OF會(huì)非常有用。該語(yǔ)句的語(yǔ)法結(jié)構(gòu)為：

 

  1. FORALL indx IN INDICES  
  2. OF sparse_collection  
  3. INSERT INTO my_table  
  4. VALUES sparse_collection (indx); 

 

VALUES OF用于一種不同的情況：綁定數(shù)組可以是稀疏數(shù)組，也可以不是，但我只想使用該數(shù)組中元素的一個(gè)子集。那么我就可以使用VALUES OF來(lái)指向我希望在DML操作中使用的值。該語(yǔ)句的語(yǔ)法結(jié)構(gòu)為：

 

  1. FORALL indx IN VALUES OF pointer_array  
  2. INSERT INTO my_table  
  3. VALUES binding_array (indx); 

 

不用FOR循環(huán)而改用FORALL

假定我需要編寫(xiě)一個(gè)程序，對(duì)合格員工（由comp_analysis.is_eligible函數(shù)確定）加薪，編寫(xiě)關(guān)于不符合加薪條件的員工的報(bào)告并寫(xiě)入employee_history表。我在一個(gè)非常大的公司工作；我們的員工非常非常多。對(duì)于一位PL/SQL開(kāi)發(fā)人員來(lái)說(shuō)，這并不是一項(xiàng)十分困難的工作。我甚至不需要使用BULKCOLLECT或FORALL就可以完成這項(xiàng)工作，如清單1所示，我使用一個(gè)CURSORFOR循環(huán)和單獨(dú)的INSERT及UPDATE語(yǔ)句。這樣的代碼簡(jiǎn)潔明了；不幸地是，我花了10分鐘來(lái)運(yùn)行此代碼，我的"老式"方法要運(yùn)行30分鐘或更長(zhǎng)時(shí)間。

清單 1:

 

  1. CREATE OR REPLACE PROCEDUREgive_raises_in_department (  
  2. dept_in IN employee.department_id%TYPE  
  3. , newsal IN employee.salary%TYPE  
  4. )  
  5. IS  
  6. CURSOR emp_cur  
  7. IS  
  8. SELECT employee_id, salary, hire_date  
  9. FROM employee  
  10. WHERE department_id = dept_in;  
  11. BEGIN  
  12. FOR emp_rec IN emp_cur  
  13. LOOP  
  14. IF comp_analysis.is_eligible (emp_rec.employee_id)  
  15. THEN  
  16. UPDATE employee  
  17. SET salary = newsal 
  18. WHERE employee_id =emp_rec.employee_id;  
  19. ELSE  
  20. INSERT INTO employee_history  
  21. (employee_id, salary  
  22. , hire_date, activity  
  23. )  
  24. VALUES (emp_rec.employee_id,emp_rec.salary  
  25. , emp_rec.hire_date,'RAISE DENIED'  
  26. );  
  27. END IF;  
  28. END LOOP;  
  29. END give_raises_in_department; 

 

好在我公司的數(shù)據(jù)庫(kù)升級(jí)到了Oracle9i，而且更幸運(yùn)的是，在最近的Oracle研討會(huì)上（以及Oracle技術(shù)網(wǎng)站提供的非常不錯(cuò)的演示中）我了解到了批量處理方法。所以我決定使用集合與批量處理方法重新編寫(xiě)程序。寫(xiě)好的程序如清單2所示。

清單 2:

  1. CREATE OR REPLACE PROCEDUREgive_raises_in_department (  
  2. dept_in IN employee.department_id%TYPE  
  3. ,newsal IN employee.salary%TYPE  
  4.  )  
  5. IS  
  6. TYPE employee_aat IS TABLE OF employee.employee_id%TYPE  
  7. INDEX BY PLS_INTEGER;  
  8. TYPE salary_aat IS TABLE OF employee.salary%TYPE  
  9. INDEX BY PLS_INTEGER;  
  10. TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE  
  11. INDEX BY PLS_INTEGER;  
  12. employee_ids employee_aat;  
  13. salaries salary_aat;  
  14. hire_dates hire_date_aat;  
  15. approved_employee_ids employee_aat;  
  16. denied_employee_ids employee_aat;  
  17. denied_salaries salary_aat;  
  18. denied_hire_dates hire_date_aat;  
  19. PROCEDURE retrieve_employee_info  
  20. IS  
  21. BEGIN  
  22. SELECT employee_id, salary, hire_date  
  23. BULK COLLECT INTO employee_ids, salaries, hire_dates  
  24. FROM employee  
  25. WHERE department_id = dept_in;  
  26. END;  
  27. PROCEDURE partition_by_eligibility  
  28. IS  
  29. BEGIN  
  30. FOR indx IN employee_ids.FIRST .. employee_ids.LAST  
  31. LOOP  
  32. IF comp_analysis.is_eligible (employee_ids (indx))  
  33. THEN  
  34. approved_employee_ids (indx) :=employee_ids (indx);  
  35. ELSE  
  36. denied_employee_ids (indx) :=employee_ids (indx);  
  37. denied_salaries (indx) :=salaries (indx);  
  38. denied_hire_dates (indx) :=hire_dates (indx);  
  39. END IF;  
  40. END LOOP;  
  41. END;  
  42. PROCEDURE add_to_history  
  43. IS  
  44. BEGIN  
  45. FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST  
  46. INSERT INTO employee_history  
  47. (employee_id  
  48. , salary  
  49. , hire_date, activity  
  50. )  
  51. VALUES (denied_employee_ids(indx)  
  52. , denied_salaries (indx)  
  53. , denied_hire_dates(indx), 'RAISE DENIED'  
  54.  );  
  55. END;  
  56. PROCEDURE give_the_raise  
  57. IS  
  58. BEGIN  
  59. FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST  
  60. UPDATE employee  
  61. SET salary = newsal 
  62. WHERE employee_id =approved_employee_ids (indx);  
  63. END;  
  64. BEGIN  
  65. retrieve_employee_info;  
  66. partition_by_eligibility;  
  67. add_to_history;  
  68. give_the_raise;  
  69. END give_raises_in_department; 

 

掃一眼清單1 和清單2 就會(huì)清楚地認(rèn)識(shí)到：改用集合和批量處理方法將增加代碼量和復(fù)雜性。但是，如果你需要大幅度提升性能，這還是值得的。下面，我們不看這些代碼，我們來(lái)看一看當(dāng)使用FORALL時(shí)，用什么來(lái)處理CURSORFOR循環(huán)內(nèi)的條件邏輯。

定義集合類型與集合

在清單2中，聲明段的***部分（第6行至第11行）定義了幾種不同的集合類型，與我將從員工表檢索出的列相對(duì)應(yīng)。我更喜歡基于employee% ROWTYPE來(lái)聲明一個(gè)集合類型，但是FORALL還不支持對(duì)某些記錄集合的操作，在這樣的記錄中，我將引用個(gè)別字段。所以，我還必須為員工ID、薪金和雇用日期分別聲明其各自的集合。

接下來(lái)為每一列聲明所需的集合（第13行至第21行）。首先定義與所查詢列相對(duì)應(yīng)的集合（第13行至第15行）：

 

  1. employee_ids employee_aat;  
  2. salaries salary_aat;  
  3. hire_dates hire_date_aat; 

 

然后我需要一個(gè)新的集合，用于存放已被批準(zhǔn)加薪的員工的ID（第17行）：approved_employee_ids employee_aat;

***，我再為每一列聲明一個(gè)集合（第19行至第21行），用于記錄沒(méi)有加薪資格的員工：

 

  1. denied_employee_ids employee_aat;  
  2. denied_salaries salary_aat;  
  3. denied_hire_dates hire_date_aat; 

 

關(guān)于Oracle數(shù)據(jù)庫(kù)的bulk collect用法之批量增刪改的相關(guān)操作就介紹到這里了，希望本次的介紹能夠?qū)δ兴斋@！

【編輯推薦】

  1. Oracle學(xué)習(xí)筆記之DECODE及常用窗口函數(shù)
  2. Oracle數(shù)據(jù)庫(kù)各類控制語(yǔ)句的使用詳細(xì)介紹
  3. Oracle數(shù)據(jù)庫(kù)日期范圍查詢的兩種實(shí)現(xiàn)方式
  4. Oracle數(shù)據(jù)庫(kù)只讀模式的CACHE BUFFERS CHAINS測(cè)試
  5. Oracle 10g數(shù)據(jù)庫(kù)中UNDO_RETENTION參數(shù)的使用詳解
責(zé)任編輯：趙鵬 來(lái)源： CSDN博客
OracleBULK COLLECTFORALL
相關(guān)推薦

2010-04-26 11:01:26

Oracle 10g

2019-11-07 15:39:36

數(shù)據(jù)庫(kù)MySQL文章

2012-04-12 09:23:15

達(dá)夢(mèng)數(shù)據(jù)庫(kù)

2011-08-12 11:04:47

Oracle數(shù)據(jù)庫(kù)增刪集合元素Java

2009-11-13 15:54:26

ADO.NET數(shù)據(jù)庫(kù)操

2011-09-02 10:37:15

Oraclesqlload用法

2011-08-03 13:11:10

Oracle數(shù)據(jù)庫(kù)序列

2021-10-20 09:04:21

Spring Beanscope數(shù)據(jù)庫(kù)

2011-07-27 08:56:32

Oracle數(shù)據(jù)庫(kù)綁定變量軟解析

2013-03-12 09:51:02

2011-08-03 13:28:08

Oracle數(shù)據(jù)庫(kù)數(shù)據(jù)庫(kù)控制文件

2024-06-06 08:34:33

2011-08-23 18:19:19

Oracle行轉(zhuǎn)列Join用法

2009-07-23 09:31:56

數(shù)據(jù)庫(kù)表連接方式

2011-08-16 16:37:40

Oracle數(shù)據(jù)庫(kù)樹(shù)形查詢根節(jié)點(diǎn)

2018-01-25 12:50:33

數(shù)據(jù)庫(kù)OracleROWNUM

2011-08-29 18:17:12

Oracle聯(lián)合查詢

2011-07-06 10:22:41

Oracle數(shù)據(jù)庫(kù)RAC組件

2010-04-23 09:23:44

Oracle 數(shù)據(jù)庫(kù)

2011-05-26 10:30:12

Oracle數(shù)據(jù)庫(kù)約束
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)