PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the result data to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL language features known collectively as bulk SQL. The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches, rather than one at a time. The BULK COLLECT clause brings back batches of results from SQL. If the DML statement affects four or more database rows, the use of bulk SQL can improve performance considerably.

The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:

1. in-bind When a PL/SQL variable or host variable is stored in the database by an INSERT or UPDATE statement.
2. out-bind When a database value is assigned to a PL/SQL variable or a host variable by the RETURNING clause of an INSERT, UPDATE, or DELETE statement.
3. define When a database value is assigned to a PL/SQL variable or a host variable by a SELECT or FETCH statement.

Bulk SQL uses PL/SQL collections, such as varrays or nested tables, to pass large amounts of data back and forth in a single operation. This process is known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. Queries can pass back any number of results, without requiring a FETCH statement for each row.

To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.

To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.