PL/SQL_使用复合数据类型5（批量绑定）

批量绑定

批量绑定是Oracle9i新增加的特征，是指执行单次SQL操作能传递所有集合元素的数据。当在SELECT，INSERT,UPDATE,DELETE语句上处理批量数据时，通过批量绑定，可以极大地加快数据处理速度，提高应用程序的性能。
首先建立示例表DEMO：

CREATE TABLE tbl_demo(  ID NUMBER(6) PRIMARY KEY,  NAME VARCHAR2(10));

1、不使用批量绑定
在Oracle9i之前，当使用VALUES子句将数据插入到数据库表时，每次只能插入一条数据。如果要插入5000行数据，那么就需要调用5000次INSERT语句。因此，为了将多个集合元素的数据插入到数据库表，就必须要使用循环方式来完成。

DECLARE  TYPE id_table_type IS TABLE OF NUMBER(6)  INDEX BY BINARY_INTEGER;  TYPE name_table_type IS TABLE OF VARCHAR2(10)  INDEX BY BINARY_INTEGER;  id_table id_table_type;  name_table name_table_type;  start_time NUMBER;  end_time NUMBER;BEGIN  FOR i IN 1..5000 LOOP    id_table(i) := i;    name_table(i) := 'Name' || TO_CHAR(i);  END LOOP;  start_time := dbms_utility.get_time;  FOR i IN 1..id_table.COUNT LOOP    INSERT INTO tbl_demo(ID,NAME) VALUEs(id_table(i),name_table(i));  END LOOP;  COMMIT;  end_time := dbms_utility.get_time;  dbms_output.put_line('总计时间（秒）：' || to_char((end_time-start_time)/100));END;

2、使用批量绑定
在Oracle9i之中，当使用VALUES子句为数据库表插入数据时，通过使用批量绑定特征，只需要执行一条INSERT语句就可以插入5000行数据。

DECLARE  TYPE id_table_type IS TABLE OF NUMBER(6)  INDEX BY BINARY_INTEGER;  TYPE name_table_type IS TABLE OF VARCHAR2(10)  INDEX BY BINARY_INTEGER;  id_table id_table_type;  name_table name_table_type;  start_time NUMBER;  end_time NUMBER;BEGIN  FOR i IN 1..5000 LOOP    id_table(i) := i;    name_table(i) := 'Name' || TO_CHAR(i);  END LOOP;  start_time := dbms_utility.get_time;  Forall i IN 1..id_table.COUNT    INSERT INTO tbl_demo(ID,NAME) VALUEs(id_table(i),name_table(i));  COMMIT;  end_time := dbms_utility.get_time;  dbms_output.put_line('总计时间（秒）：' || to_char((end_time-start_time)/100));END;

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

FORALL语句

当要在PL/SQL应用程序中执行批量INSERT、UPDATE和DELETE操作时，可以使用FORALL语句。在Oracle9i之中，当使用FORALL语句时，必须具有连续的元素；而从Oracle 10g开始，通过使用新增加的INDICES OF和VALUES OF子句，可以使用不连续的集合元素。注意，FOR语句是循环语句，但FORALL语句却不是循环语句。从Oracle 10g开始，FORALL语句有三种执行语法。
语法一：

FORALL index IN lower_bound..upper_bound  sql_statement;

语法二：

FORALL index IN INDICES OF collection  [BETWEEN lower_bound..upper_bound]  sql_statement;

语法三：

FORALL index IN VALUES OF index_collection  sql_statement;

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

DECLARE  TYPE id_table_type IS TABLE OF NUMBER(6)  INDEX BY BINARY_INTEGER;  TYPE name_table_type IS TABLE OF VARCHAR2(10)  INDEX BY BINARY_INTEGER;  id_table id_table_type;  name_table name_table_type;BEGIN  FOR i IN 1..10 LOOP    id_table(i) := i;    name_table(i) := 'Name' || TO_CHAR(i);  END LOOP;  Forall i IN 1..id_table.COUNT    INSERT INTO tbl_demo(ID,NAME) VALUEs(id_table(i),name_table(i));END;

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

DECLARE  TYPE id_table_type IS TABLE OF NUMBER(6)  INDEX BY BINARY_INTEGER;  TYPE name_table_type IS TABLE OF VARCHAR2(10)  INDEX BY BINARY_INTEGER;  id_table id_table_type;  name_table name_table_type;BEGIN  FOR i IN 1..10 LOOP    id_table(i) := i;    name_table(i) := 'Name' || TO_CHAR(i);  END LOOP;  Forall i IN 1..id_table.COUNT    UPDATE tbl_demo SET NAME = name_table(i) WHERE ID = id_table(i);END;

3、在DELETE语句上使用批量绑定
当使用批量绑定删除数据库表的数据时，首先需要为集合元素赋值，然后才使用FORALL语句执行批量绑定删除相应数据。

DECLARE  TYPE id_table_type IS TABLE OF NUMBER(6)  INDEX BY BINARY_INTEGER;  TYPE name_table_type IS TABLE OF VARCHAR2(10)  INDEX BY BINARY_INTEGER;  id_table id_table_type;  name_table name_table_type;BEGIN  FOR i IN 1..10 LOOP    id_table(i) := i;    name_table(i) := 'Name' || TO_CHAR(i);  END LOOP;  Forall i IN 1..id_table.COUNT    DELETE FROM tbl_demo WHERE ID = id_table(i);END;

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

DECLARE  TYPE id_table_type IS TABLE OF NUMBER(6)  INDEX BY BINARY_INTEGER;  TYPE name_table_type IS TABLE OF VARCHAR2(10)  INDEX BY BINARY_INTEGER;  id_table id_table_type;  name_table name_table_type;BEGIN  FOR i IN 1..10 LOOP    id_table(i) := 11-i;    name_table(i) := 'Name' || TO_CHAR(11-i);  END LOOP;  Forall i IN 8..id_table.COUNT    INSERT INTO tbl_demo(ID,NAME) VALUEs(id_table(i),name_table(i));END;

5、在FORALL语句上使用INDICES OF子句
INDICES OF子句是Oracle 10g新增加的特征，该子句用于跳过NULL集合元素。

DECLARE  TYPE id_table_type IS TABLE OF NUMBER(6);  id_table id_table_type;BEGIN  id_table := id_table_type(1,NULL,3,NULL,5);  Forall i IN INDICES OF id_table    DELETE FROM tbl_demo WHERE ID = id_table(i);END;

6、在FORALL语句上使用VALUES OF子句
VALUES OF子句是Oracle 10g新增加的特征，该子句用于从其他集合中取得集合下标（index）的值。
首先执行以下语句建立tbl_newdemo表：

CREATE TABLE tbl_newdemo AS SELECT * FROM tbl_demo WHERE 1 = 0;DECLARE  TYPE id_table_type IS TABLE OF tbl_demo.id%TYPE;  TYPE name_table_type IS TABLE OF tbl_demo.name%TYPE;  TYPE index_pointer_type IS TABLE OF PLS_INTEGER;  id_table id_table_type;  name_table name_table_type;  index_pointer index_pointer_type;BEGIN  SELECT ID,NAME BULK COLLECT INTO id_table,name_table  FROM tbl_demo;  index_pointer := index_pointer_type(6,8,10);  FORALL i IN VALUES OF index_pointer    INSERT INTO tbl_newdemo(ID,NAME) VALUES(id_table(i),name_table(i));END;

7、使用SQL%BULK_ROWCOUNT属性
属性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子句

BULK COLLECT子句用于取得批量数据，它只适用于SELECT INTO语句，FETCH INTO语句和DML返回子句。
语法如下：

... BULK COLLECT INTO collection_name[,collection_name] ...

1、在SELECT INTO语句中使用BULK COLLECT子句
在Oracle9i之前，当编写SELECT INTO语句时，该语句必须返回一行数据，并且只能返回一行数据，否则会触发PL/SQL例外。从Oracle9i开始，通过在SELECT INTO语句中使用BULK COLLECT子句，可以一次将SELECT语句的鑫行结果检索到集合变量中。

DECLARE  TYPE emp_table_type IS TABLE OF emp%ROWTYPE  INDEX BY BINARY_INTEGER;  emp_table emp_table_type;BEGIN  SELECT * BULK COLLECT INTO emp_table  FROM emp;  FOR i IN 1..emp_table.COUNT LOOP    dbms_output.put_line('员工姓名：' || emp_table(i).ename);  END LOOP;END;

2、在DML的返回子句中使用BULK COLLECT子句
执行DML操作时会改变数据库数据。为了取得DML操作所改变的数据，可以使用RETURNING子句。为了取得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 = &deptno  RETURNING 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;