PL SQL Tutorial
BULK COLLECT: These are SELECT statements that retreive multiple rows with a single fetch, there by improving the speed of data retreival.
Context Switch : PL/SQL code may include PL/SQL Code or SQL Code, PL/SQL Code run by PL/SQL Engine, SQL Code run by SQL Engine, When Pl/SQL sees SQL code, it passes SQL Statement to SQL Engine, SQL Engine executes the statement and returns the information back to the PL/SQL Engine. This transfer of control is called as Context Switch
FORALL
Syntax:select * BULK COLLECT INTO collection_name_list from [[table][view]];
PL SQL BULK COLLECT INTO clause can be used in SELECT INTO, FETCH INTO, or
RETURNING INTO clause of.
DELETE Statement
INSERT Statement
UPDATE Statement
EXECUTE IMMEDIATE Statement
Display table data using BULK Collect INTO Clause
DECLARE TYPE EMP_COPY IS TABLE OF EMPLOYEES%ROWTYPE; L_EMPS EMP_COPY; BEGIN SELECT * BULK COLLECT INTO L_EMPS FROM EMPLOYEES; --Associate Array methods FIRST and LAST methods are used to fetch records, because it can sparse FOR IDX IN L_EMPS.FIRST..L_EMPS.LAST LOOP DBMS_OUTPUT.PUT_LINE(L_EMPS(IDX).EMPLOYEE_ID || ' '|| L_EMPS(IDX).FIRST_NAME); END LOOP; END;
ADS