bulk of the 用法_BULK COLLECT子句使用方法(转)

Using the BULK COLLECT Clause

The keywords BULK COLLECT tell the SQL engine to bulk-bind output

collections

before returning them to the PL/SQL engine. You can use these

keywords in the

SELECT INTO, FETCH INTO, and RETURNING INTO clauses. Here is

the syntax:

... BULK COLLECT INTO collection_name[, collection_name]

...

The SQL engine bulk-binds all collections referenced in the INTO list.

The

corresponding columns can store scalar or composite values including

objects. In

the following example, the SQL engine loads the entire empno and

ename database

columns into nested tables before returning the tables to the

PL/SQL engine:

DECLARE

TYPE NumTab IS TABLE OF emp.empno%TYPE;

TYPE

NameTab IS TABLE OF emp.ename%TYPE;

enums NumTab; -- no need to

initialize

names NameTab;

BEGIN

SELECT empno, ename BULK COLLECT INTO

enums, names FROM emp;

...

END;

In the next example, the SQL engine

loads all the values in an object column into a

nested table before returning

the table to the PL/SQL engine:

CREATE TYPE Coords AS OBJECT (x NUMBER, y

NUMBER);

CREATE TABLE grid (num NUMBER, loc Coords);

INSERT INTO grid

VALUES(10, Coords(1,2));

INSERT INTO grid VALUES(20,

Coords(3,4));

DECLARE

TYPE CoordsTab IS TABLE OF Coords;

pairs

CoordsTab;

BEGIN

SELECT loc BULK COLLECT INTO pairs FROM grid;

-- now

pairs contains (1,2) and (3,4)

END;

Using the BULK COLLECT Clause

4-38

PL/SQL User’s Guide and Reference

The SQL engine initializes and extends

collections for you. (However, it cannot

extend varrays beyond their maximum

size.) Then, starting at index 1, it inserts

elements consecutively and

overwrites any pre-existent elements.

The SQL engine bulk-binds entire

database columns. So, if a table has 50,000 rows,

the engine loads 50,000

column values into the target collection. However, you can

use the

pseudocolumn ROWNUM to limit the number of rows processed. In the

following

example, you limit the number of rows to 100:

DECLARE

TYPE SalList IS

TABLE OF emp.sal%TYPE;

sals SalList;[@more@]