Important note: Staring in Oracle 11g release 2, we see the new bulk operator!
When inserting rows in PL/SQL, developers often place the insert statement inside a FOR loop.
Typically, each block performs a logical action in he program.
A block has the following structure: DECLARE /* Declarative section: variables, types, and local subprograms.
This is achieved by the FORALL statement passing an entire PL/SQL table to the SQL engine in a single step.
Internal tests show that this process results in substantial performance increases over traditional methods. This example, assume that a Pro*C program is calling a PL/SQL function for a bulk insert. PROCEDURE customer_insert (p_cust_key IN customer.cust_key%TYPE, p_current_state IN current_state_array, p_anno_user_category IN anno_user_category_array, p_anno_name IN anno_name_array, p_color IN color_array) To properly perform the bulk insert you must get the number of items passed in by the Pro*C program.
By giving your developers the ability to create temporary tables, you can radically improve the speed of certain types of Oracle SQL statements.
The major nuance is that the form of the statement in PL/SQL only works if the result of the query contains a single tuple.