PL/SQL provides many features for enhancing
the performance of code that performs many
separate queries or many separate transactions

In this article i am going to explain about
forall and bulkcollect.

Forall

The forall operator issues a series of dynamic or static DML commands,
replacing for loops.We just should specify the upper and lower
bounds for avoiding row by row fetching.

firt create one table,

create table EMP_TEMF
as select * from EMP;

Now we are going to delete first 20 records of Emp_Temf.

DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
Ratings NumList := NumList(1, 2, 3);
BEGIN
FORALL i IN Ratings.FIRST..Ratings.LAST
delete from EMP_TEMF
where Rating = Ratings(i);
COMMIT;
END;
/

for all only support data manipulation,not support Queries.

BulkCollect:

BulkCollect Supports Querying,bulk collect operates on sets of records in an
efficient manner.

See this below examble,

DECLARE
TYPE NumTab IS TABLE OF employees.employee_id%TYPE;
TYPE NameTab IS TABLE OF employees.last_name%TYPE;
enums NumTab;   — No need to initialize the collections.
names NameTab;  — Values will be filled in by the SELECT INTO.
PROCEDURE print_results IS
BEGIN
IF enums.COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE(‘No results! ‘);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Results: ‘);
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(‘  Employee #’ I I enums(i)  I I ‘: ‘ I I names(i));
END LOOP;
END IF;
END;
BEGIN
– Retrieve data for employees with Ids greater than 1000
SELECT employee_id, last_name
BULK COLLECT INTO enums, names FROM employees WHERE employee_id > 1000;
– The data has all been brought into memory by BULK COLLECT
– No need to FETCH each row from the result set
print_results ();
– Retrieve approximately 20% of all rows
SELECT employee_id, last_name

BULK COLLECT INTO enums, names FROM employees SAMPLE (20);
print_results();
END;
/

Please avoid cursor and try forall and bulkcollect for
good Performance.

Happy Coding.