PL/SQL批量绑定

批量绑定是使用BULK COLLECT子句和FORALL语句来完成。其中，BULK COLLECT子句用于取得批量数据，该子句只能用于SELECT语句、FETCH语句和DML返回子句中；而FORALL语句只适用于执行批量DML操作。

 

当要在PL/SQL应用程序中执行批量INSERT、UPDATE和DELETE操作时，可以使用FORALL。Oracle 10g中，FORALL语句有三种执行语法(9i中只能使用第一种)：

 

1. FORALL index IN lower_bound.. upper_bound sql_statement;

 

2. FORALL index IN INDICES OF collection[BETWEEN lower_bound. AND.  upper_bound]  sql_statement(INDICES OF子句用于跳过NULL集合元素);

 

3. FORALL index IN VALUES OF index_collection sql_statement(VALUES OF子句用于从其他集合变量中取得集合下标index的值);

 

 

在INSERT语句上使用批量绑定：

当使用批量绑定为数据库表插入数据时，首先要给集合元素赋值，然后使用FORALL语句执行批量绑定插入操作。

在UPDATE语句上使用批量绑定：

当使用批量绑定更新数据库数据时，首先要给集合元素赋值，然后使用FORALL语句执行批量绑定更新操作。

在DELETE语句上使用批量绑定：

当使用批量绑定删除数据库表数据时，首先要给集合元素赋值，然后使用FORALL语句执行批量绑定删除操作。

使用FORALL语句执行批量绑定时，既可以使用集合的所有元素，也可以使用集合的部分元素。

属性SQL%BULK_ROWCOUNT是专门为FORALL语句提供用于取得在执行批量绑定操作时第i个元素所作用的行。例：

 

DECLARE

        TYPE dno_table_type IS TABLE OF NUMBER(3);

        dno_table dno_table_type:=dno_table_type(10,20);

BEGIN

 

        FORALL i  IN 1..dno_table.COUNT

                 UPDATE emp SET sal=sal*1.1 WHERE deptno=dno_table(i);

        dbms_output.put_line('第2个元素更新的行数：'||SQL%BULK_ROWCOUNT(2));

END;

 

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

BULK COLLECT子句只适用于SELECT INTO语句，FETCH INTO语句和DML返回子句。通过使用该子句，可以将批量数据存放到PL/SQL集合变量中。

 

在SELECT INTO语句中使用BULK COLLECT子句：可以一次将SELECT语句的多行结果检索到集合变量中。例：

 

DECLARE

        TYPE ename_table_type IS TABLE OF emp%ROWTYPE

                INDEX BY BINSRY_INTEGER;

        ename_table ename_table_type;

BEGIN

        SELECT * BULK COLLECT INTO ename_table

                FROM emp WHERE deptno=&no;

        FOR i  IN 1..ename_table.COUNT LOOP

                 dbms_output.put('雇员姓名：'||emp_table(i)ename);

        END LOOP;

END;

 

在DML返回子句中使用BULK COLLECT子句：为取得DML操作所改变的数据，可以使用RETURING子句。为了取得DML所作用的多行数据，需要使用BULK COLLECT子句。例：

 

DECLARE

        TYPE ename_table_type IS TABLE OF emp.ename%TYPE;

        ename_table ename_table_type;

BEGIN

        DELETE FROM emp WHERE deptno=&no

                RETURING ename BULK COLLECT INTO ename_table;

        dbms_output.put('雇员名：');

        FOR i  IN 1..ename_table.COUNT LOOP

                 dbms_output.put(ename_table(i)||' ');

        END LOOP;

        dbms_output.new_line;

END;