mysql bulk collect_Oracle 批量绑定forall bulk collect用法

采用bulk collect 可以将查询结果一次性的加载到collections中，而不是通过CURSOR一条一条地处理，可以在select into,fetchinto,

采用bulk collect 可以将查询结果一次性的加载到collections中，，而不是通过CURSOR一条一条地处理，可以在select into,fetchinto,returning into语句中使用bulk collect.

注意在使用bulk collect时，所有的into变量都必须是collections.

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

DECLARE

TYPE SalList IS TABLE OF emp.sal%TYPE;

sals SalList;

BEGIn

SELECT sal BULK COLLECT INTO sals FROM emp WHERE ROWNUM<=100;

SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;

----在fetch into 中使用bulk collect

DECLARE

TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;

dept_recs DeptRecTab;

CURSOR c1 IS

SELECT deptno,dname,loc FROM dept WHERE deptno>10;

BEGIN

OPEN c1;

FETCH c1 BULK COLLECT INTO dept_recs;

END;

/

----在returning into 中使用bulk collect

CREATE TABLE emp2 AS SELECT * FROM employees;

DECLARE

TYPE NumList IS TABLE OF employees.employee_id%TYPE;

enums NumList;

TYPE NameList IS TABLE OF employees.last_name%TYPE;

names NameList;

BEGIN

DELETE FROM emp2 WHERE department_id=30;

RETURNING employee_id,last_name BULK COLLECT INTO enums,names;

DBMS_OUTPUT.PUT_LINE('Deleted' ||SQL%ROWCOUTN||'rows;');

FOR i IN enms.FIRST..enums.LAST

LOOP

DBMS_OUTPUT.PUT_LINE('Employee #'||enums(i)||';'||names(i));

END LOOP;

END;

/

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 the BULK　COLLECT clause,一般用来提高查询(SELECT)的性能

本文原创发布php中文网，转载请注明出处，感谢您的尊重！