Oracle数据库的BULKCOLLECT用法之批量增删改

Oracle数据库的BULK COLLECT用法之批量增删改的相关知识是本文我们主要要介绍的内容，FORALL语句的一个关键性改进，它可以大大简化代码，并且对于那些要在PL/SQL程序中更新很多行数据的程序来说，它可显著提高其性能。

成都创新互联-专业网站定制、快速模板网站建设、高性价比静乐网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式静乐网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖静乐地区。费用合理售后完善，10多年实体公司更值得信赖。

用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"的所有书籍检索出来并置于记录的一个关联数组中，它们都位于通向该数据库的单一通道中。

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

类似地，FORALL将数据从一个PL/SQL集合传送给指定的使用集合的表。下面的代码实例给出一个过程，即接收书籍信息的一个嵌套表，并将该集合（绑定数组）的全部内容插入该书籍表中。注意，这个例子还利用了Oracle9i的FORALL的增强功能，可以将一条记录直接插入到表中。BULK COLLECT和FORALL都非常有用，它们不仅提高了性能，而且还简化了为PL/SQL中的SQL操作所编写的代码。下面的多行FORALL INSERT相当清楚地说明了为什么PL/SQL被认为是Oracle数据库的***编程语言。

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

不过在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会非常有用。该语句的语法结构为：

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

VALUES OF用于一种不同的情况：绑定数组可以是稀疏数组，也可以不是，但我只想使用该数组中元素的一个子集。那么我就可以使用VALUES OF来指向我希望在DML操作中使用的值。该语句的语法结构为：

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

不用FOR循环而改用FORALL

假定我需要编写一个程序，对合格员工（由comp_analysis.is_eligible函数确定）加薪，编写关于不符合加薪条件的员工的报告并写入employee_history表。我在一个非常大的公司工作；我们的员工非常非常多。对于一位PL/SQL开发人员来说，这并不是一项十分困难的工作。我甚至不需要使用BULKCOLLECT或FORALL就可以完成这项工作，如清单1所示，我使用一个CURSORFOR循环和单独的INSERT及UPDATE语句。这样的代码简洁明了；不幸地是，我花了10分钟来运行此代码，我的"老式"方法要运行30分钟或更长时间。

清单 1:

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

好在我公司的数据库升级到了Oracle9i，而且更幸运的是，在最近的Oracle研讨会上（以及Oracle技术网站提供的非常不错的演示中）我了解到了批量处理方法。所以我决定使用集合与批量处理方法重新编写程序。写好的程序如清单2所示。

清单 2:

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

扫一眼清单1 和清单2 就会清楚地认识到：改用集合和批量处理方法将增加代码量和复杂性。但是，如果你需要大幅度提升性能，这还是值得的。下面，我们不看这些代码，我们来看一看当使用FORALL时，用什么来处理CURSORFOR循环内的条件逻辑。

定义集合类型与集合

在清单2中，声明段的***部分（第6行至第11行）定义了几种不同的集合类型，与我将从员工表检索出的列相对应。我更喜欢基于employee% ROWTYPE来声明一个集合类型，但是FORALL还不支持对某些记录集合的操作，在这样的记录中，我将引用个别字段。所以，我还必须为员工ID、薪金和雇用日期分别声明其各自的集合。

接下来为每一列声明所需的集合（第13行至第21行）。首先定义与所查询列相对应的集合（第13行至第15行）：

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

然后我需要一个新的集合，用于存放已被批准加薪的员工的ID（第17行）：approved_employee_ids employee_aat;

***，我再为每一列声明一个集合（第19行至第21行），用于记录没有加薪资格的员工：

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

关于Oracle数据库的bulk collect用法之批量增删改的相关操作就介绍到这里了，希望本次的介绍能够对您有所收获！

当前标题：Oracle数据库的BULKCOLLECT用法之批量增删改
本文URL：http://www.shufengxianlan.com/qtweb/news19/500919.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

App设计知识

同城分类信息

彭州做网站　　 　Google外贸推广　　 　成都做网站　　 　百度推广公司　　 　成都建网站　　 　成都网站制作　　 　云服务器　　 　上海济桢教育　　 　重庆做网站　　 　复盛机电设备安装　　 　成都网站推广　　 　和正源建设　　 　app网站建设　　 　高端网站建设　　 　大英网站建设　　 　成都微信开发　　 　成都网站优化　　 　高端定制网站设计　　 　成都微信二次开发公司　　 　雷运建站