PL/SQL批处理语句:BULK COLLECT与FORALL

我不是女神ヾ 2022-06-15 12:10 140阅读 0赞

[![2013041017163410.png][]][2013041017163410.png]

FORALL和BULK COLLECT可以让PL/SQL引擎把多个上下文却换压缩成一个，这使得在PL/SQL中的要处理多行记录的SQL语句执行的花费时间骤降

下面详解这爷俩  
  
**㈠ 通过BULK COLLECT 加速查询  
  
⑴ BULK COLLECT 的用法**  
  
采用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 employees.salary%TYPE;  
sals sallist;  
BEGIN  
SELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;  
\--接下来使用集合中的数据  
END;  
/

**② 在fetch into中使用bulk collect**

复制代码 代码如下:

DECLARE  
TYPE deptrectab IS TABLE OF departments%ROWTYPE;  
dept\_recs deptrectab;  
CURSOR cur IS SELECT department\_id,department\_name FROM departments where department\_id>10;  
BEGIN  
OPEN cur;  
FETCH cur BULK COLLECT INTO dept\_recs;  
\--接下来使用集合中的数据  
END;  
/

**③ 在returning into中使用bulk collect**

复制代码 代码如下:

CREATE TABLE emp AS SELECT \* FROM employees;

DECLARE  
TYPE numlist IS TABLE OF employees.employee\_id%TYPE;  
enums numlist;  
TYPE namelist IS TABLE OF employees.last\_name%TYPE;  
names namelist;  
BEGIN  
DELETE emp 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;  
/

deleted6rows:  
employee\#114:Raphaely  
employee\#115:Khoo  
employee\#116:Baida  
employee\#117:Tobias  
employee\#118:Himuro  
employee\#119:Colmenares

EATE TABLE emp AS SELECT \* FROM employees;DECLARE TYPE numlist IS TABLE OF employees.employee\_id%TYPE; enums numlist; TYPE namelist IS TABLE OF employees.last\_name%TYPE; names namelist;BEGIN DELETE emp 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;/deleted6rows:employee\#114:Raphaelyemployee\#115:Khooemployee\#116:Baidaemployee\#117:Tobiasemployee\#118:Himuroemployee\#119:Colmenares

**⑵ 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引擎

[2013041017163410.png]: http://files.jb51.net/file_images/article/201304/2013041017163410.png