oracle批量处理(bulk collect)

采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时，所有的into变量都必须是collections. ### Oracle批量处理(Bulk Collect) #### 一、概述 在Oracle数据库中，`BULK COLLECT` 是一种高效的数据检索机制，它允许一次性从数据库中提取多行数据，并将其存储在一个PL/SQL集合中。与传统的游标逐行处理相比，这种机制能够显著提高应用程序的性能，因为它减少了往返数据库的次数以及上下文切换的数量。 #### 二、基本概念 - **BULK COLLECT**：用于将查询结果一次性加载到集合中。 - **集合（Collections）**：在PL/SQL中用于存储多个相同类型的值的数据结构，如`VARRAY`或`TABLE`。 - **上下文切换**：当执行程序在数据库和应用程序之间切换时发生的性能开销。 #### 三、使用场景 - **SELECT INTO**：可以使用`BULK COLLECT`来一次性获取查询结果。 - **FETCH INTO**：结合游标使用`BULK COLLECT`一次性获取多行数据。 - **RETURNING INTO**：在`DELETE`或`UPDATE`语句后，使用`BULK COLLECT`来获取受影响行的信息。 #### 四、示例 ##### 1. 在SELECT INTO语句中使用BULK COLLECT ```plsql DECLARE TYPE sal_list IS TABLE OF emp.sal%TYPE; sals sal_list; BEGIN -- 限制结果集大小为100行 SELECT sal BULK COLLECT INTO sals FROM emp WHERE rownum <= 100; -- 获取表中大约10%的行 SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10; END; / ``` 这个例子展示了如何使用`BULK COLLECT`一次性加载最多100行的薪水数据，以及通过采样获取大约10%的数据。 ##### 2. 在FETCH INTO中使用BULK COLLECT ```plsql DECLARE TYPE dept_rec_tab IS TABLE OF dept%ROWTYPE; dept_recs dept_rec_tab; CURSOR c1 IS SELECT deptno, dname, loc FROM dept WHERE deptno > 10; BEGIN OPEN c1; FETCH c1 BULK COLLECT INTO dept_recs; END; / ``` 这里展示了如何使用`BULK COLLECT`与游标结合，一次性获取所有符合条件的部门记录。 ##### 3. 在RETURNING INTO中使用BULK COLLECT ```plsql CREATE TABLE emp2 AS SELECT * FROM employees; DECLARE TYPE num_list IS TABLE OF employees.employee_id%TYPE; enums num_list; TYPE name_list IS TABLE OF employees.last_name%TYPE; names name_list; BEGIN DELETE FROM emp2 WHERE department_id = 30 RETURNING employee_id, last_name BULK COLLECT INTO enums, names; dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:'); FOR i IN enums.FIRST .. enums.LAST LOOP dbms_output.put_line('Employee # ' || enums(i) || ': ' || names(i)); END LOOP; END; / ``` 这个例子展示了如何在`DELETE`语句后使用`BULK COLLECT`获取被删除员工的ID和姓名。 #### 五、性能优化技巧 - **减少循环处理的开销**：通过使用`BULK COLLECT`，可以减少循环处理数据时的开销，因为不再需要逐行处理数据。 - **使用LIMIT**：在某些情况下，可能需要限制`BULK COLLECT`获取的结果集大小，这可以通过在`SELECT`语句中使用`WHERE`子句或`SAMPLE`子句实现。 - **利用10g的新特性**：Oracle 10g增强了`FORALL`语句的功能，使得可以更灵活地批量处理数据。 #### 六、总结 通过使用`BULK COLLECT`，可以极大地提高应用程序处理大量数据时的性能。了解并熟练掌握其用法，能够在开发高性能的应用程序时发挥重要作用。