CODEWITHSUNDEEP
sqloracle-databaseplsqlforallbulk-collect
Plant More Trees
Plant More Trees

Reputation: 65

In PL SQL I want to refactor this for loop with inserts, by using BULK COLLECT and FORALL

For performance reasons, I want to rewrite the following to use BULK COLLECT and FORALL, rather than doing the inserts in a loop. The problem I'm running in to, is that empID must be generated on each iteration of the loop, or I need to do something similar with BULK COLLECT to create a collection to use FORALL on.

...

FOR i in 1 .. numberOfEmployeesToAdd
LOOP

    BEGIN

    empID := EMPLOYEE_SEQ.NEXTVAL;

    INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) 
        VALUES (empID, 'firstNameTest', 'lastNameTest');

    INSERT INTO EMPLOYEE_DEPT_ASSOC (ID, DEPT_ID, EMP_ID)
        VALUES (EMPLOYEE_DEPT_ASSOC_SEQ.NEXTVAL, '247', empID);

    INSERT INTO SKILLSET (ID, EMP_ID, SKILL_ID)
        VALUES (SKILLSET_ASSOC.NEXTVAL, empID, '702');

    END;

END LOOP;

The examples of BULK COLLECT and FORALL seem to mostly consist of creating a cursor where you do select * from [some table] and then fetch that cursor and do a BULK COLLECT. But, I need to somehow dynamically assign values within a cursor using the next contiguous 'numberOfEmployeesToAdd' number of IDs and then do a FORALL to do the inserts.

Upvotes: 0

Views: 751

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Wont this help you? If you have a nested table with your set of data, you can join it to the SELECT

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) 
   SELECT EMPLOYEE_SEQ.NEXTVAL, 'firstNameTest', 'lastNameTest'
     FROM DUAL
   CONNECT BY LEVEL <= numberOfEmployeesToAdd;


INSERT INTO EMPLOYEE_DEPT_ASSOC (ID, DEPT_ID, EMP_ID)
    SELECT EMPLOYEE_DEPT_ASSOC_SEQ.NEXTVAL, '247', ID
    FROM EMPLOYEE;

INSERT INTO SKILLSET (ID, EMP_ID, SKILL_ID)
    SELECT SKILLSET_ASSOC.NEXTVAL, ID, '702'
    FROM EMPLOYEE;

Upvotes: 0

Related Questions