Bulk collect in Oracle PL/SQL

Bulk collect:

   v Bulk collect is about reducing the context switching and improving the performance. Before going to bulk collect we need to know context switching.

Context switching:

   v Whenever we try to execute the PL/SQL program and execute it, The PL/SQL runtime engine starts preceding it line by line.

   v This engine processes all the PL/SQL statements by itself but it passes all the SQL statements which are coded into the PL/SQL block to the run time engine.

   v Those SQL statements will then get processed separately by the SQL engine, once it processing them the SQL engine then returns the results back to the PL/SQL engine, So that a combined result can be produced by the later, this to and hopping of control is called context switching.

   Disadvantage in context switching:

v The higher the hopping of control the greater will be overhead, which in turn will degrade the performance.

Bulk collect clause:

v Bulk collect clause reduces multiple control hopping by collecting all the SQL statement calls from PL/SQL program and sending them to SQL engine in just one go and vice versa.

Advantages of using Bulk collect clause:

v Bulk collect clause reduces or compresses multiple switches into a single context switch.

v It increases the performance and efficiency of a PL/SQL program.

Bulk data processing:

   v The process of fetching batches of data from PL/SQL runtime engine to SQL runtime engine and vice versa is called bulk data processing.

Bulk processing statement:

Bulk processing statement has two statements,

        1.    Bulk collect

        2.     FORALL

Bulk collect clause used with,

        1.    SELECT INTO

        2.    INSERT INTO

        3.    RETURNING - INTO

Example:

Bulk collect in SELECT INTO statement:

1. DECLARE

      Type e_ename is table of varchar2(50);

      empname e_ename;

   BEGIN

      SELECT ename

      BULK COLLECT INTO empname

        FROM emp;

       

       for i in 1..empname.count

       loop

        DBMS_output.put_line(i || ' - ' || empname (i));

       End loop;

   END;

Output:

Share: