BULK COLLECT AND BULK BINDING

Basic example of BULK COLLECT

SET SERVEROUTPUT ON; DECLARE
    TYPE l_emplnotab IS
        TABLE OF per_all_people_f.employee_number%TYPE;
    TYPE l_emplnametab IS
        TABLE OF per_all_people_f.full_name%TYPE;
    l_empnoarr     l_emplnotab;
    l_empnamearr   l_emplnametab;
    CURSOR c1 IS SELECT
                     employee_number,
                     full_name
                 FROM
                     per_all_people_f
                 WHERE
                     ROWNUM <= 10;
BEGIN OPEN c1; LOOP
    FETCH c1 BULK COLLECT INTO
        l_empnoarr,
        l_empnamearr;
    CLOSE c1;
    FOR i IN l_empnoarr.first..l_empnoarr.last LOOP
        dbms_output.put_line('employee number: '
                               || l_empnoarr(i)
                               || ' and employee name is :'
                               || l_empnamearr(i) );
    END LOOP;
    end;

Output of the above block:

 

employee number: 158204 and employee name is :Baumann, Albert J

employee number: 197145 and employee name is :Stanton, Dennis R.

employee number: 197942 and employee name is :Walker, Heseelyah Malina (Malina)

employee number: 198606 and employee name is :Adiga, Suresh

employee number:  and employee name is :Srinivasan, S.

employee number: 198615 and employee name is :Uppin, Vijay

employee number: 198613 and employee name is :Corcoran, Daniel

employee number: 160379 and employee name is :Stebbeds, Tracy D.

employee number: 188314 and employee name is :Marcial, Lisa Betty

employee number: 189610 and employee name is :Phillips, Dawn Nichole

 

BULK BIND EXAMPLE:

 

SET SERVEROUTPUT ON;
DECLARE
    TYPE l_empnotab IS
        TABLE OF test1.a%TYPE INDEX BY PLS_INTEGER;
    l_emparr   l_empnotab;
    l_size     NUMBER;
    k          NUMBER;
BEGIN
    l_size := 1000;
    FOR j IN 1..1000 LOOP
        l_emparr(j) := j;
    END LOOP;
--execute IMMEDIATE 'truncate table test1';
    FORALL i IN l_emparr.first..l_emparr.last
        INSERT INTO test1 VALUES ( l_emparr(i) );
    COMMIT;
END;