ITLife365 生活 互动学习空间 - 互动学习空间

oracle中bulk collect的使用
通过bulk collect减少loop处理的开销
 
采用bulk collect可以将查询结果一次性地加载到collections中。
而不是通过cursor一条一条地处理。
可以在select into,fetch into,returning into语句使用bulk collect。
注意在使用bulk collect时，所有的into变量都必须是collections.

1.实验时把set serveroutput on 打开
2.下面例子的目的主要是为了提高性能.这里的性能主要指的是速度.如果综合的从整个OLTP或者OLAP系统的话就是另外一回事了,不以这里讨论.
3.速度指的是批量插入,更新,删除 ,为什么会提高速度呢?
4.提取到的数据都在内存中进行处理,因为在内存处理比较快,这是常识.
5.附以下几个例子大家自己看吧.

 例1:批量 查询部门号为"10" 号的并把它们打印出来.

DECLARE
TYPE emp_table_type IS TABLE OF my_emp%ROWTYPE INDEX BY BINARY_INTEGER;
v_emp_table emp_table_type;

BEGIN
SELECT * BULK COLLECT INTO v_emp_table FROM my_emp WHERE deptno=&deptno;

FOR i IN 1..v_emp_table.COUNT LOOP
dbms_output.put_line('EMPLOYEE_INFO:'||v_emp_table(i).ename||','||v_emp_table(i).job||
','||v_emp_table(i).hiredate);

END LOOP;
END;

说明部分:
1.DECLARE 说明以下你要声明的部分
2.Type 声明是类型 emp_table_type 类型的名字
3.IS TABLE OF 指定是一个集合的表的数组类型,简单的来说就是一个可以存储一列多行的数据类型,my_emp指出在哪个表上(存在的表)%ROWTYPE指在表上的行的数据类型.
4.INDEX BY BINARY_INTEGER 指索引组织类型
5.v_emp_table定义一个变量来存储集合数据类型
6.BULK COLLECT INTO 指是一个成批聚合类型,简单的来说,它可以存储一个多行多列存储类型,into后面指定从哪里来,
7.v_emp_table.COUNT用来v_emp_table里面的数量
8.(i)表示下标号

例2:批量更新部门号为"10" 的员工工资sql
DECLARE

TYPE ename_table_type IS TABLE OF my_emp.ename%TYPE;
TYPE sal_table_type IS TABLE OF my_emp.sal%TYPE;
v_ename_table ename_table_type;
v_sal_table sal_table_type;

BEGIN

UPDATE my_emp SET sal=sal*1.20 WHERE deptno=&deptno RETURNING ename,sal BULK COLLECT INTO v_ename_table,v_sal_table;

FOR i IN 1..v_ename_table.COUNT LOOP

dbms_output.put_line('EMPLOYEE_INFO:'||v_ename_table(i)||','||v_sal_table(i));

END LOOP;
END;

说明部分:
1.%TYPE和my_emp.ename数据类型一样
v_ename_table ename_table_type;
2. v_sal_table sal_table_type; 定义变量来存储它们.
3. RETURNING 用来指定要返回的部分,BULK COLLECT INTO 成批聚合类型
4.用for 把它们打印出来

例3：批量插入


CREATE TABLE my_emp(empno number(4),ename varchar2(15))
/

DECLARE
   TYPE empno_table_type IS TABLE OF number(4) INDEX BY BINARY_INTEGER;
   TYPE ename_table_type IS TABLE OF varchar2(20) INDEX BY BINARY_INTEGER;
   v_empno_table empno_table_type;
   v_ename_table ename_table_type;

BEGIN
   FOR i IN 1..1000 LOOP
   v_empno_table(i):=i+2000;
   v_ename_table(i):='NAME'||to_char(i);

END LOOP;

FORALL i IN 1..v_empno_table.COUNT INSERT INTO my_emp VALUES(v_empno_table(i),v_ename_table(i));

END;
/


例4:批量更新

CREATE TABLE my_emp(empno number(4),ename varchar2(15))

/

DECLARE
   TYPE empno_table_type IS TABLE OF my_emp.empno%TYPE INDEX BY BINARY_INTEGER;
   TYPE ename_table_type IS TABLE OF my_emp.ename%TYPE INDEX BY BINARY_INTEGER;

   v_empno_table empno_table_type;
   v_ename_table ename_table_type;

BEGIN
   FOR i IN 1..1000 LOOP
   v_empno_table(i):=i+2000;
   v_ename_table(i):='CN'||to_char(i);

END LOOP;

FORALL i IN 1..v_empno_table.COUNT UPDATE my_emp SET ename=v_ename_table(i) WHERE empno=v_empno_table(i);
END;

/

例5：批量删除

DECLARE

TYPE empno_table_type IS TABLE OF my_emp.empno%TYPE INDEX BY BINARY_INTEGER;

v_empno_table empno_table_type;

BEGIN
   FOR i IN 1..8 LOOP
       v_empno_table(i):=i+2000;
END LOOP;
FORALL i IN 1..3 DELETE FROM my_emp WHERE empno=v_empno_table(i);

END;

/

例6：SQL%BULK_ROWCOUNT属性

DECLARE
TYPE deptno_table_type IS TABLE OF number(2);
v_deptno_table deptno_table_type:=deptno_table_type(10,20,30);

BEGIN

FORALL i IN 1.. v_deptno_table.COUNT UPDATE emp2 SET sal=sal*0.5 WHERE deptno=v_deptno_table(i);

dbms_output.put_line('DEPARTMENT_10:'||SQL%BULK_ROWCOUNT(1)||' rows');
dbms_output.put_line('DEPARTMENT_10:'||SQL%BULK_ROWCOUNT(1)||' rows');
dbms_output.put_line('DEPARTMENT_20:'||SQL%BULK_ROWCOUNT(2)||' rows');
dbms_output.put_line('DEPARTMENT_30:'||SQL%BULK_ROWCOUNT(3)||' rows');

END;
/

SQL%BULK_ROWCOUNT(i)表示FORALL语句第i元素所作用的行数