Bulk Collect:-
One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.
Example:-
DECLARE
TYPE OBJECT_REC IS TABLE OF ALL_OBJECTS%ROWTYPE;
OBJECT_REC1 OBJECT_REC;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO OBJECT_REC1;
FOR I IN 1..OBJECT_REC1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(OBJECT_REC1(I).OBJECT_ID||CHR(9)||OBJECT_REC1(I).OBJECT_NAME);
END LOOP;
END;
Note:-
Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks. This gives you the benefits of bulk binds, without hogging all the server memory. The following code shows how to chunk through the data in a large table.
Example:-
DECLARE
TYPE OBJECT_REC IS TABLE OF ALL_OBJECTS%ROWTYPE;
OBJECT_REC1 OBJECT_REC;
CURSOR C1 IS SELECT * FROM ALL_OBJECTS;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO OBJECT_REC1 LIMIT 1000;
FOR I IN 1..OBJECT_REC1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(OBJECT_REC1(I).OBJECT_ID||CHR(9)||OBJECT_REC1(I).OBJECT_NAME);
END LOOP;
EXIT WHEN OBJECT_REC1.COUNT=0;
END LOOP;
END;
FORALL:-
The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.
The FORALL statement is not a loop; it is a declarative statement to the PL/SQL engine: “Generate all the DML statements that would have been executed one row at a time, and send them all across to the SQL engine with one context switch.”
INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly.
Example:-
CREATE OR REPLACE PROCEDURE XXC05_FORALL_EXAMPLE
IS
TYPE L_FORALL_TAB IS TABLE OF XXC05_ALL_OBJECTS%ROWTYPE;
L_FORALL L_FORALL_TAB;
CURSOR C1
IS
SELECT *
FROM ALL_OBJECTS;
BEGIN
OPEN C1;
LOOP
FETCH C1 BULK COLLECT INTO L_FORALL LIMIT 10000;
DBMS_OUTPUT.PUT_LINE(L_FORALL.COUNT||CHR(9)||'ROWS');
FORALL I IN L_FORALL.FIRST..L_FORALL.COUNT
INSERT INTO XXC05_ALL_OBJECTS VALUES L_FORALL(I);
EXIT WHEN L_FORALL.COUNT=0;
END LOOP;
COMMIT;
CLOSE C1;
END XXC05_FORALL_EXAMPLE;
