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;