oracle批量查询插件,oracle批量查询.doc

oracle批量查询

2009-11-02?|?oracle批量绑定table类型变－－ forall bulk collect用法以及测试案例 采用bulk collect可以将查询结果一次性地加载到collections中。而不是通过cursor一条一条地处理。可以在select into,fetch into,returning into语句使用bulk collect。注意在使用bulk collect时，所有的into变量都必须是collections.

--在select into语句中使用bulk collect

DECLARETYPE 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;

--在fetch into中使用bulk collect

DECLARETYPE 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 collect

CREATE 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;oracle批量绑定forall bulk collect

批量绑定(Bulk binds)可以通过减少在PL/SQL和SQL引擎之间的上下文切换(context switches )提高了性能.

量绑定(Bulk binds)包括：

????? (i) Input collections, use the FORALL statement，用来改善DML(INSERT、UPDATE和DELETE) 操作的性能

????? ?(ii) Output collections, use BULK COLLECT clause，一般用来提高查询(SELECT)的性能

10g开始forall语句可以使用三种方式：

?????? i in low..up

?????? i in indices of collection? 取得集合元素下标的值

?????? i in values of collection?? 取得集合元素的值

?????? forall语句还可以使用部分集合元素

?????? sql%bulk_rowcount(i)表示forall语句第i元素所作用的行数

CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));

CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));

CREATE TABLE parts3 (pnum INTEGER, pname VARCHAR2(15));

CREATE TABLE parts4 (pnum INTEGER