通过bulk collect减少loop处理的开销，使用Bulk Collect提高Oracle查询效率
Oracle8i中首次引入了Bulk Collect特性，该特性可以让我们在PL/SQL中能使用批查询，批查询在某些情况下能显著提高查询效率。
采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时，所有的into变量都必须是collections.
【在select into语句中使用bulk collect】
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
-- Limit the number of rows to 100.
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;
END;
/
【在fetch into中使用bulk collect】
DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
/
【在returning into中使用bulk collect】
CREATE TABLE emp2 AS SELECT * FROM emp;
DECLARE
TYPE NumList IS TABLE OF emp.empno%TYPE;
enums NumList;
TYPE NameList IS TABLE OF emp.ename%TYPE;
names NameList;
BEGIN
DELETE FROM emp2 WHERE deptno = 30
RETURNING empno, ename 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('Emp #' || enums(i) || ': ' || names(i));
END LOOP;
END;
/
DROP TABLE emp2;
通过上面的测试和分析，我们可以看到Bulk
Collect批查询在某种程度上可以提高查询效率，它首先将所需数据读入内存，然后再统计分析，这样就可以提高查询效率。但是，如果Oracle数据库的内存较小，Shared
Pool Size不足以保存Bulk Collect批查询结果，那么该方法需要将Bulk Collect的集合结果保存在磁盘上，在这种情况下，Bulk
Collect方法的效率反而不如其他两种方法，有兴趣的读者可以进一步测试。
另外，除了Bulk Collect批查询外，我们还可以使用FORALL语句来实现批插入、删除和更新，这在大批量数据操作时可以显著提高执行效率。
⑵ BULK COLLECT 对大数据DELETE UPDATE的优化
这里举DELETE就可以了，UPDATE同理
举个案例：
需要在一个1亿行的大表中，删除1千万行数据
需求是在对数据库其他应用影响最小的情况下，以最快的速度完成
如果业务无法停止的话，可以参考下列思路：
根据ROWID分片、再利用Rowid排序、批量处理、回表删除
在业务无法停止的时候，选择这种方式，的确是最好的
一般可以控制在每一万行以内提交一次，不会对回滚段造成太大压力
我在做大DML时，通常选择一两千行一提交
选择业务低峰时做，对应用也不至于有太大影响
代码如下：
DECLARE
--按rowid排序的cursor
--删除条件是oo=xx，这个需根据实际情况来定
CURSOR mycursor IS SELECT rowid FROM t WHERE OO=XX ORDER BY rowid;
TYPE rowid_table_type IS TABLE OF rowid index by pls_integer;
v_rowid rowid_table_type;
BEGIN
OPEN mycursor;
LOOP
FETCH mycursor BULK COLLECT INTO v_rowid LIMIT 5000;--5000行提交一次
EXIT WHEN v_rowid.count=0;
FORALL i IN v_rowid.FIRST..v_rowid.LAST
DELETE t WHERE rowid=v_rowid(i);
COMMIT;
END LOOP;
CLOSE mycursor;
END;
/
⑶ 限制BULK COLLECT 提取的记录数
语法：
FETCH cursor BULK COLLECT INTO ...[LIMIT rows]；
其中，rows可以是常量，变量或者求值的结果是整数的表达式
假设你需要查询并处理1W行数据，你可以用BULK COLLECT一次取出所有行，然后填充到一个非常大的集合中
可是，这种方法会消耗该会话的大量PGA，APP可能会因为PGA换页而导致性能下降
这时，LIMIT子句就非常有用，它可以帮助我们控制程序用多大内存来处理数据
DECLARE
CURSOR allrows_cur IS SELECT * FROM employees;
TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp employee_aat;
BEGIN
OPEN allrows_cur;
LOOP
FETCH allrows_cur BULK FETCH INTO v_emp LIMIT 100;
/*通过扫描集合对数据进行处理*/
FOR i IN 1 .. v_emp.count
LOOP
upgrade_employee_status(v_emp(i).employee_id);
END LOOP;
EXIT WHEN allrows_cur%NOTFOUND;
END LOOP;
CLOSE allrows_cur;
END;
/
⑷ 批量提取多列
需求：
提取transportation表中的油耗小于 20公里/RMB的交通具体的全部信息
DECLARE
--声明集合类型
TYPE vehtab IS TABLE OF transportation%ROWTYPE;
--初始化一个这个类型的集合
gas_quzzlers vehtab;
BEGIN
SELECT * BULK COLLECT INTO gas_quzzlers FROM transportation WHERE mileage < 20;
...
⑸ 对批量操作使用RETURNING子句
有了returning子句后，我们可以轻松地确定刚刚完成的DML操作的结果，无须再做额外的查询工作
例子请见BULK COLLECT 的用法的第三小点
㈡ 通过FORALL 加速DML
FORALL告诉PL/SQL引擎要先把一个或多个集合的所有成员都绑定到SQL语句中，然后再把语句发送给SQL引擎
⑴ 语法
------------------------------------------------------------------------------------------------
在Oracle 10g中，新增加了一个聚合函数--collect:
Takes a column of any type and creates a nested table of the input type out of the rows selected
SQL> create or replace type varchar2_app as table of varchar2(2000);
/
Type created.
SQL> select object_type,CAST(COLLECT(object_name) AS varchar2_app) AS object_name
from user_objects
group by object_type;
OBJECT_TYPE OBJECT_NAME
------------- ---------------------------------------------------------------------
FUNCTION VARCHAR2_APP('STR2VARLIST', 'GET_NUMBER_CHINESE', 'FORMAT_STRING')
PROCEDURE VARCHAR2_APP('WHO_CALL_ME')
TABLE VARCHAR2_APP('TOMS_SOURCE_SQL', 'TOMS_REGION_DEF', 'TOMS_TOOLS_DEF')
TYPE VARCHAR2_APP('VARCHAR2_APP', 'SYSTPAlfegzdFTrC2VSJl8f5cbA==')
Example
CAST('123.4567' AS NUMBER(10,2)) returns the value 123.46.
通过上面的描述,我们就可以知道CAST可以将一种类型转换为另外一种类型。
比如,将字符串类型转换为NUMBER(10,2)类型，而不仅仅是限于使用用to_number、to_char（）以及to_date（）类型，上述表Table 3-1代表CAST是否能够用于该类型的相互转换。
例子调用：
ChenZw> SELECT CAST('123.4567' AS NUMBER(10,2)) AS NUM FROM DUAL;
http://www.cnblogs.com/fangwenyu/archive/2009/12/31/1636902.html
http://blog.csdn.net/ceclar123/article/details/12394967