FORALL和BULK COLLECT

首页 > 代码库 > FORALL和BULK COLLECT

2024-09-23 16:14:27 215人阅读

参考：http://www.cnblogs.com/hellokitty1/p/4584333.html

用户在通过PLSQL编写程序时，PLSQL通常会在操作上进行交互，当用户通过PLSQL执行一条更新语句时，SQL会将更新后的数据返回给PLSQL，这样用户才可以在PLSQL之中取得更新后的数据，但是如果在PLSQL之中要进行大量的数据操作时，这种方式就会使程序执行性能大大降低。

例如：通过PLSQL执行多行数据更新

1 DECLARE
2        TYPE emp_array_type IS VARRAY(10) OF emp.empno%TYPE ;  --定义可变数组
3        
4        emp_array           emp_array_type := emp_array_type(7369 , 7499 , 7521 , 7566) ; --初始化可变数组
5 BEGIN
6        FOR x IN emp_array.first .. emp_array.last LOOP 
7            UPDATE lxemp SET sal = sal+1000 WHERE empno = emp_array(x) ; --更新数据
8        END LOOP ;
9 END ;

如果按照上面的做法，那么集合中有多少个元素就要执行多少次更新操作。

上面的这个程序一共向数据库发送了5次更新操作。假设要更新的内容很多，这样的方式很明显是浪费时间和性能。

最好的方式是一次性向数据库发出N多条的更新操作，采用批处理的方式。

FORALL是将所有需要更新的操作一次性发送给数据库。

1、FORALL语句：

1 FORALL index_name IN 
2     { lower_bound .. upper_bound
3     | INDICES OF collection_name [ BETWEEN lower_bound AND upper_bound ]
4     | VALUES OF index_collection
5     }
6  [ SAVE EXCEPTIONS ] dml_statement;

说明：
index_name : 一个无需声明的标识符，作为集合的下标使用
lower_bound  ..  upper_bound : 数字表达式，用来指定一组连续有效的索引数字下线和上限，该表达式只解析一次。
INDICES OF collection_name : 用于执行稀疏数组的实际下标。跳过没有赋值的元素，例如被DELETE的元素，NULL值也算。
BETWEEN lower_bound AND upper_bound : 指定一个范围之内的数据
VALUES OF index_collection : 把该集合中的值作为下标，且该集合值的类型只能是PLS_INTEGER或BINARY_INTEGER。
SAVE EXCEPTIONS : 可选关键字，表示即使一些DML语句失败，知道FORALL LOOP 执行完毕才抛出异常。可以使用SQL%BULK_EXCEPTION查看异常。
dml_statement : 静态语句，例如UPDATE 或 DELETE ，或者动态（EXECUTE IMMEDIATE）DML语句。

2、使用FORALL
示例1：使用FORALL批量插入、修改、删除数据

新建一张临时操作的表。

1 CREATE TABLE mylx ( 
2        mid NUMBER(10) , 
3        mname VARCHAR2 (20) ,
4        msal NUMBER(10) 
5 ) ;

 1 --insert
 2 DECLARE
 3     TYPE emp_table_type IS TABLE OF mylx%ROWTYPE ;   --定义一个嵌套表类型
 4     emp_table emp_table_type := emp_table_type() ;   --初始化嵌套表变量
 5 BEGIN
 6     FOR x IN 1 .. 5 LOOP     
 7       emp_table.extend() ;   --为emp_table嵌套变量扩充容量
 8       emp_table(x).mid :=  x ; 
 9       emp_table(x).mname := ‘name‘ || x ;
10       emp_table(x).msal :=  x * 10 ;
11     END LOOP ;
12     FORALL x IN emp_table.first .. emp_table.last 
13            INSERT INTO mylx VALUES emp_table(x) ;    --批量插入
14 END ;

1 --UPDATE
2 DECLARE
3        TYPE emp_array_type IS VARRAY(10) OF emp.empno%TYPE ;  --定义可变数组
4        emp_array           emp_array_type := emp_array_type(1 , 2 , 3 , 4 , 5) ; --初始化可变数组
5 BEGIN
6        FORALL x IN emp_array.first .. emp_array.last 
7               UPDATE mylx SET msal= 9000 WHERE mid = emp_array(x) ;
8 END ;

1 --delete
2 DECLARE
3     TYPE emp_array_type IS VARRAY(10) OF emp.empno%TYPE ;  --定义可变数组
4     emp_array           emp_array_type := emp_array_type(1 , 2 ) ; --初始化可变数组
5 BEGIN
6     FORALL x IN emp_array.first .. emp_array.last 
7            DELETE FROM mylx WHERE mid = emp_array(x) ;
8 END ;

--语法1：

FORALL 下标变量(只能当作下标被引用) IN 下限..上限

sql 语句; --只允许一条 sql 语句

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

示例2：在FORALL 中使用 INDICES OF子句

 1 --INDICES OF
 2 DECLARE
 3           TYPE num_insex_type IS TABLE OF mylx%ROWTYPE INDEX BY PLS_INTEGER ;
 4           num_index num_insex_type  ;
 5          
 6 BEGIN
 7           FOR x IN 1 .. 5 LOOP      
 8             num_index(x).mid := x ;
 9             num_index(x).mname := ‘zhang‘ || x  ;
10             num_index(x).msal := x*100 ;
11           END LOOP ;
12           
13           num_index.delete(2) ;
14           num_index.delete(4) ;
15           
16           FORALL x IN INDICES OF num_index 
17                  INSERT INTO mylx VALUES num_index(x) ;
18 END ;

在使用INDICES OF 时，如果集合中某个值为NULL 或者被DELETE了，那么它就会跳过那个值继续执行，知道执行完毕。

--语法2：

FORALL 下标变量 IN INDICES OF(跳过没有赋值的元素，例如被 DELETE 的元素，NULL 也算值) 集合

[BETWEEN 下限 AND 上限]

sql 语句;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

示例3 ：在FORALL 中使用 VALUES OF 子句

 1 --VALUES OF
 2 DECLARE
 3    TYPE px_index_type  IS TABLE OF PLS_INTEGER   ;    -- 创建一个嵌套表类型 （集合值的类型只能是 PLS_INTEGER 或 BINARY_INTEGER）
 4    px_index  px_index_type ;
 5    TYPE mupx_index_type IS TABLE OF  mylx%ROWTYPE INDEX BY BINARY_INTEGER ; -- 创建一个嵌套表类型 
 6    mupx_index mupx_index_type ;
 7 BEGIN
 8        px_index := px_index_type (1 ,  3 , 5 ) ;     --实例化 px_index_type
 9     
10     FOR x IN 1 .. 5 LOOP      
11             mupx_index(x).mid := x ;    --实例化 mupx_index_type
12             mupx_index(x).mname := ‘jie‘ || x  ;
13             mupx_index(x).msal := x*100 ;
14     END LOOP ;
15     
16     
17     FORALL x IN VALUES  OF px_index 
18         UPDATE mylx SET msal = 8888 WHERE mid = mupx_index(x).mid ;
19 END ;

使用的VALUES OF 子句的时候 ，  px_index_type 集合的值是作为下标使用的。mupx_index_type 是根据  px_index_type 这个集合的值进行操作的 。

--语法3：

FORALL 下标变量 IN VALUES OF 集合(把该集合中的值当作下标，且该集合值的类型只能是 PLS_INTEGER BINARY_INTEGER)

sql 语句;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

2、FORALL注意事项：

　　a、FORALL语句执行体，必须是一个单独的DML语句，比如INSERT 、 UPDATE 、 DELETE。

　　b、不要显示定义index_row，他被PLSQL引擎隐式的定义为PLS_INTEGER类型，并且它的作用域仅仅是FORALL。

　　c、这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。

　　d、lower_bound和upper_bound之间是按照步进 1 来递增的。

　　e、在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。

　　f、在sql_statement中使用的集合，下标不能使用表达式。

4、BULK COLLECT介绍

BULK COLLECT子句会批量检索结果，即一次性将结果集绑定到一个集合变量中。并从sql引擎发送到PLSQL。通常可以在SELECT INOT 、 FETCH INTO 以及 RETURNING INTO 中使用BULK COLLECT。

5、BULK COLLECT 的使用

5.1 在SELECT INTO 中使用BULK COLLECT

实例：

 1 DECLARE
 2   TYPE emp_table_type IS TABLE OF mylx%ROWTYPE ;
 3   emp_table emp_table_type ;
 4 BEGIN
 5       SELECT * BULK COLLECT INTO emp_table FROM mylx ;  --在SELECT INTO 中使用BULK COLLECT 
 6     
 7     FOR x IN emp_table.first .. emp_table.last LOOP
 8         dbms_output.put_line (‘编号 : ‘ || emp_table(x).mid || ‘ ， 姓名 ： ‘ || emp_table(x).mname || ‘ , 工资 :‘ || emp_table(x).msal) ;
 9     END LOOP ;
10 END ;

5.2在FETCH INTO 中使用BULK COLLECT

在游标中可以使用BLUK COLLECT一次取出一个数据集合，比用游标单条取数据效率高，尤其是在网络不大好的情况下。

语法：

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];

在使用BULK COLLECT子句时，对于集合类型会自动对其进行初始化以及扩展。因此如果使用BULK COLLECT子句操作集合，则无需对集合进行初始化以及扩展。由于BULK COLLECT的批量特性，如果数据量较大，而集合在此时又自动扩展，为避免过大的数据集造成性能下降，因此可以使用LIMIT子句来限制一次提取的数据量。LIMIT子句只允许出现在FETCH操作语句的批量中。

 1 DECLARE
 2     CURSOR mylx_cur  -- 创建游标
 3     IS
 4     SELECT * FROM mylx ;
 5     
 6     TYPE mylx_table_type IS TABLE OF mylx%ROWTYPE ;       -- 创建一个嵌套表类型
 7     mylx_table mylx_table_type ;
 8 BEGIN
 9     OPEN mylx_cur ;
10     FETCH mylx_cur BULK COLLECT INTO mylx_table  ;        -- 在FETCH INTO 中使用 BULK COLLECT 
11             
12      FOR x IN mylx_table.first .. mylx_table.last  LOOP   -- 遍历所有雇员 
13       dbms_output.put_line (‘编号 : ‘ || mylx_table(x).mid || ‘ ， 姓名 ： ‘ || mylx_table(x).mname || ‘ , 工资 :‘ || mylx_table(x).msal) ; 
14      END LOOP ;
15     /*
16      FORALL x IN mylx_table.first .. mylx_table.last     --批量修改所有雇员的工资
17             UPDATE mylx SET msal = 10000 WHERE mid = mylx_table(x).mid ;*/
18     CLOSE mylx_cur ;
19 END ;

5.3 在RETURNING INTO 中使用 BULK COLLECT

BULK COLLECT除了与SELECT，FETCH进行批量绑定之外，还可以与INSERT，DELETE，UPDATE语句结合使用。当与这几个DML语句结合时，需要使用RETURNING子句来实现批量绑定。

 1 DECLARE
 2     TYPE mylx_table_type IS TABLE OF mylx%ROWTYPE ;
 3     mylx_table           mylx_table_type ;
 4 BEGIN
 5     DELETE FROM mylx WHERE msal = 10000  -- 删除mylx表 工资为10000 的员工
 6     RETURNING mid , mname , msal         -- 使用RETURNING 返回 这几个列
 7     BULK COLLECT INTO mylx_table ;       -- 把返回的数据INTO到mylx_table
 8     
 9     IF mylx_table.COUNT  > 0 THEN 
10        FOR x IN mylx_table.first .. mylx_table.last LOOP 
11          dbms_output.put_line (‘编号 : ‘ || mylx_table(x).mid || ‘ ， 姓名 ： ‘ || mylx_table(x).mname || ‘ , 工资 :‘ || mylx_table(x).msal ||  ‘被删除‘) ; 
12        END LOOP ;
13     END IF ;
14 END ;

6 、 BULK COLLECT的注意事项

7、 BULK COLLECT 和 FORALL 总合应用

 1 DECLARE
 2    CURSOR mylx_cur 
 3    IS 
 4    SELECT * FROM mylx ;
 5    
 6    TYPE mylx_table_type IS TABLE OF mylx%ROWTYPE ;
 7    mylx_table  mylx_table_type ;
 8 BEGIN
 9    OPEN mylx_cur ;
10    FETCH mylx_cur BULK COLLECT INTO mylx_table ; -- 取出全部的数据到集合中
11    
12    FORALL x IN mylx_table.first .. mylx_table.last          -- 使用FORALL 进行批量删除
13           DELETE FROM mylx WHERE mid = mylx_table(x).mid ;
14    CLOSE mylx_cur ;
15 END ;

FORALL和BULK COLLECT

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > FORALL和BULK COLLECT

FORALL和BULK COLLECT

看完仍有疑问？有类似问题直接问程序猿