实战BULK COLLECT(成批聚合类型)和数组集合type类型is table of

?

例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_typ e?类型的名字

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"? 的员工工资

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? 把它们打印出来

(总结 ):

1. 实验时把set?serveroutput?on? 打开

2. 以上的例子的目的主要是为了提高性能. 这里的性能主要指的是速度 . 如果综合的从整个 OLTP 或者 OLAP 系统的话就是另外一回事了,不以这里讨论 .

3. 速度指的是批量插入,更新,删除?,为什么会提高速度呢 ??

4. 提取到的数据都在内存中进行处理,因为在内存处理比较快,这是常识 .

5. 附以下几个例子大家自己看吧.

例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 元素所作用的行数