批量绑定

2010年9月14日 12:46:37

批量绑定

    批量绑定是Oracle9i新增加的特征，是指执行单次sql操作能传递所有集合元素的数据。当在select，insert，update，delete语句上处理批量数据时，通过批量绑定，可以极大地加快数据处理速度，提高应用程序的性能。
    批量绑定是通过forall和bulk collect子句来完成的。Bulk collect子句用于取得批量数据，该子句只能用于select语句、fetch语句和DML返回子句中；而forall语句只适用于执行批量的DML操作。

--以下进行 比较批量绑定用否的区别
CREATE TABLE demo(
id NUMBER(6) PRIMARY KEY,name VARCHAR2(10));

--不使用批量绑定
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(10);
  end_time NUMBER(10);
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 demo VALUES(id_table(i),name_table(i));
  END LOOP;
  end_time:=dbms_utility.get_time;
  dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100));
END;
总计时间(秒):.29

--使用批量绑定
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(10);
  end_time NUMBER(10);
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 demo VALUES(id_table(i),name_table(i));
  end_time:=dbms_utility.get_time;
  dbms_output.put_line('总计时间(秒):'||to_char((end_time-start_time)/100));
END;
总计时间(秒):.06

1、INSERT语句使用批量绑定
见使用批量绑定

2、update语句使用批量绑定
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..5 LOOP
    id_table(i):=i;
    name_table(i):='N'||to_char(i);
  END LOOP;
  FORALL i IN 1..id_table.COUNT
    UPDATE demo SET name=name_table(i) where id=id_table(i);
END;


3、DELETE语句批量绑定的使用
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;
BEGIN
  FOR i IN 1..3 LOOP
    id_table(i):=i;
  END LOOP;
  FORALL i IN 1..id_table.COUNT
    DELETE demo where id=id_table(i);
END;

4、在forall语句中使用部分集合元素
DECLARE
  TYPE id_table_type IS TABLE OF NUMBER(6)
    INDEX BY BINARY_INTEGER;
  id_table id_table_type;
BEGIN
  FOR i IN 1..10 LOOP
    id_table(i):=i;
  END LOOP;
  FORALL i IN 8..10 --只取部分元素
    INSERT INTO demo(id) VALUES(id_table(i));
END;

5、INDICES OF子句的使用，该子句用于跳过null集合元素
DECLARE
  TYPE id_table_type IS TABLE OF NUMBER(6);
  id_table id_table_type;
BEGIN
  id_table:=id_table_type(8,9,null);
  FORALL i IN INDICES OF id_table
    DELETE FROM demo WHERE id=id_table(i);
END;

6、values of 用于从其他集合变量中取得集合下标（index）的值。首先创建表demo_new（与demo表结构一样）。
  从demo表中复制id=8,9,10这三行数据
DECLARE
  TYPE id_table_type IS TABLE OF demo.id%TYPE;
  TYPE name_table_type IS TABLE OF demo.name%TYPE;
  id_table id_table_type;
  name_table name_table_type;
  TYPE index_pointer_type IS TABLE OF PLS_INTEGER;
  index_pointer index_pointer_type;
BEGIN
  SELECT * BULK COLLECT INTO id_table,name_table FROM demo;
  index_pointer:=index_pointer_type(1,2,3); --这个值是行数，第1,2,3,行
  FORALL i IN VALUES OF index_pointer
    INSERT INTO demo_new VALUES(id_table(i),name_table(i));
END;

select id from demo_new;
8
9
10

7、使用sql%bulk_rowcount属性
  专门为forall语句提供的，用于取得在执行批量绑定操作时第i个元素所作用的函数
DECLARE
  TYPE id_table_type IS TABLE OF NUMBER(3);
  id_table id_table_type:=id_table_type(8,9,10);
BEGIN
  FORALL i IN 1..id_table.COUNT
    UPDATE demo SET name=id_table(i) where id=id_table(i);
    dbms_output.put_line('第2个元素更新的行数'||sql%bulk_rowcount(2));
END;
 第2个元素更新的行数1
执行之前
select * from demo
        id
1        8        
2        9        
3        10

执行之后
        id       name
1        8        8
2        9        9
3        10       10       

8、bulk collect子句
该子句用于取得批量数据，它只适用于select into，fetch into，和dml返回子句。

--select into 语句使用bulk collect
在oracle9i之前，编写select into语句时，该语句必须返回一行数据，并且只能返回一行数据，否则会触发pl/sql例外。从oracle9i开始，通过在select into语句中使用bulk collect子句，可以一次从selct语句的多行结果检索到集合变量中。例子可见上述的6。

--返回语句使用bulk collect
DECLARE
  TYPE name_table_type IS TABLE OF demo.name%TYPE;
  name_table name_table_type;
BEGIN
  DELETE FROM demo WHERE id=&id
    RETURNING name BULK COLLECT INTO name_table;
  dbms_output.put('名称:');
  FOR i IN 1..name_table.COUNT LOOP
    dbms_output.put(name_table(i)||' ');
  END LOOP;
    dbms_output.new_line;
END;