Welcome

PL SQL Tutorial

PL SQL BULK COLLECT INTO Clause

     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

	      	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;