oracle学习之bulk collect用法
来源:互联网 发布:mac 桌面下方出现白条 编辑:程序博客网 时间:2024/06/06 12:48
Oracle8i中首次引入了Bulk Collect特性,该特性可以让我们在PL/SQL中能使用批查询,批查询在某些情况下能显著提高查询效率。
</pre><pre name="code" class="sql">--在select into语句中使用bulk collectDECLARETYPE SalList IS TABLE OF emp.sal%TYPE;sals SalList;BEGIN-- Limit the number of rows to 100.SELECT sal BULK COLLECT INTO sals FROM empWHERE ROWNUM <= 100;-- Retrieve 10% (approximately) of the rows in the table.SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;END;/--在fetch into中使用bulk collectDECLARETYPE DeptRecTab IS TABLE OF dept%ROWTYPE;dept_recs DeptRecTab;CURSOR c1 ISSELECT deptno, dname, loc FROM dept WHERE deptno > 10;BEGINOPEN c1;FETCH c1 BULK COLLECT INTO dept_recs;END;/--在returning into中使用bulk collectCREATE TABLE emp2 AS SELECT * FROM employees;DECLARETYPE NumList IS TABLE OF employees.employee_id%TYPE;enums NumList;TYPE NameList IS TABLE OF employees.last_name%TYPE;names NameList;BEGINDELETE FROM emp2 WHERE department_id = 30RETURNING employee_id, last_name BULK COLLECT INTO enums, names;dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');FOR i IN enums.FIRST .. enums.LASTLOOPdbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));END LOOP;END;/DROP TABLE emp2;
通过上面的测试和分析,我们可以看到Bulk
Collect批查询在某种程度上可以提高查询效率,它首先将所需数据读入内存,然后再统计分析,这样就可以提高查询效率。但是,如果Oracle数据库的内存较小,Shared
Pool Size不足以保存Bulk Collect批查询结果,那么该方法需要将Bulk Collect的集合结果保存在磁盘上,在这种情况下,Bulk
Collect方法的效率反而不如其他两种方法,有兴趣的读者可以进一步测试。
另外,除了Bulk Collect批查询外,我们还可以使用FORALL语句来实现批插入、删除和更新,这在大批量数据操作时可以显著提高执行效率。
实战BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer
实验环境
操作系统: linux 5.4
数据库: ORACLE 11.2.0.1.0 启用归档
日期:2010-09-04
实验目标
使用BULK COLLECT(成批聚合类型)和数组集合type类型is table of 表%rowtype index by binary_integer
实验步骤
例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" 的员工工资
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元素所作用的行数
- oracle学习之bulk collect用法
- oracle学习之bulk collect用法
- oracle学习之bulk collect用法
- oracle学习之bulk collect用法
- oracle学习之bulk collect用法
- oracle学习之bulk collect用法
- oracle学习之bulk collect用法
- 【转】oracle学习之bulk collect用法
- oracle学习之bulk collect用法
- Oracle学习之BULK COLLECT用法
- oracle学习之bulk collect用法(转)
- oracle bulk collect用法
- Oracle 之 bulk collect 的用法
- Oracle 之 bulk collect 的用法
- Oracle 之 bulk collect 的用法
- Oracle Bulk Collect 学习
- oracle中bulk collect用法
- oracle学习总结三(bulk collect用法)
- 第5周项目多文件组织
- python高级函数map/reduce
- 第五周——多文件组织多各类——三角形类
- String to Integer (atoi)--LeetCode
- Fukushima Nuclear Blast - UVa 11978 求圆和多边形面积的并
- oracle学习之bulk collect用法
- 第五周 阅读程序(2)
- 生成PowerDesigner16.5设计图大图片的方法
- Linux常用Terminal命令与快捷键
- 更新php的版本
- 4月风怒(2)
- python编写dijkstra算法
- แทงบอลออนไลน์ผ่านเว็บคนไทย
- LeetCode | Path Sum II