Cursor For-Loop

The 'Cursor For-Loop' is often used when you want to fetch and process all records of a selection. It might seem like the simplest solution, but it is not the optimal solution from a performance perspective. A more efficient approach is to use BULK COLLECT as a faster way to query data. If you expect to retrieve a relatively small number of rows (in the hundreds at most), then the performance of a 'Cursor For-Loop' will most likely meet your requirements. But for larger numbers, it is better to use BULK COLLECT instead. PL/SQL compiler automatically optimizes 'Cursor For-Loops' to execute similarly to BULK COLLECT if the loop body does not have any non-query DML (insert/update/delete) statements.

Rules

Instead of 'Cursor For-Loop':

• If it is expected to retrieve multiple rows of data and the upper limit does not exceed an acceptable value, then BULK COLLECT into a collection via an implicit cursor.
• If it is expected to retrieve multiple rows of data and the upper limit is not known, use BULK COLLECT with an explicit cursor and a FETCH statement that relies on a LIMIT clause. This ensures that you do not consume too much per-session memory.

Examples

Below is an example of where 'Cursor For-loop' is typically used.

PROCEDURE Cursor_For_Loop (
   company_             IN VARCHAR2,
   budget_process_id_   IN VARCHAR2,
   budget_structure_id_ IN VARCHAR2,
   template_            IN VARCHAR2 )
IS
   newrec_ budget_node_tab%ROWTYPE;
   CURSOR get_nodes_of_template IS
      SELECT   t.*
      FROM     budget_node_template n, budget_node_tab t
      WHERE    n.company              = company_
      AND      n.budget_process_id    = budget_process_id_
      AND      n.budget_structure_id  = budget_structure_id_
      AND      n.template             = template_
      AND      t.company              = n.company
      AND      t.budget_process_id    = n.budget_process_id
      AND      t.budget_structure_id  = n.budget_structure_id
      AND      t.budget_node_id       = n.node
      AND      t.record_type          = 'NODE';      
BEGIN
   FOR current_ancestor_node IN get_nodes_of_template LOOP
      newrec_.record_type  := '*'||current_ancestor_node.budget_node_id;
      newrec_.approver     := current_ancestor_node.approver;
      newrec_.responsible  := current_ancestor_node.responsible;
      Budget_Node_API.Check_Insert(newrec_);
   END LOOP;
END Cursor_For_Loop;

Solution

This solution shows the use of BULK COLLECT. The developer will have to write some additional lines of code, but performance will be enhanced.

PROCEDURE Cursor_Bulk_Collect (
   company_             IN VARCHAR2,
   budget_process_id_   IN VARCHAR2,
   budget_structure_id_ IN VARCHAR2,
   template_            IN VARCHAR2 )
IS
   newrec_ budget_node_tab%ROWTYPE;
   TYPE node_collection_type IS TABLE OF budget_node_tab%ROWTYPE;
   node_collection_    node_collection_type;
BEGIN 
   SELECT t.*
   BULK COLLECT
   INTO   node_collection_
   FROM   budget_node_template n, budget_node_tab t
   WHERE  n.company              = company_
   AND    n.budget_process_id    = budget_process_id_
   AND    n.budget_structure_id  = budget_structure_id_
   AND    n.template             = template_
   AND    t.company              = n.company
   AND    t.budget_process_id    = n.budget_process_id
   AND    t.budget_structure_id  = n.budget_structure_id
   AND    t.budget_node_id       = n.node
   AND    t.record_type          = 'NODE' ;
   FOR j IN 1..node_collection_.COUNT LOOP
      newrec_.record_type  := '*'||node_collection_(j).budget_node_id;
      newrec_.approver     := node_collection_(j).approver;
      newrec_.responsible  := node_collection_(j).responsible;
      Budget_Node_API.Check_Insert(newrec_);
   END LOOP;
END Cursor_Bulk_Collect;

BULK COLLECT helps retrieve multiple rows of data quickly. Rather than retrieving one row of data at a time into a record or a set of individual variables, BULK COLLECT lets you retrieve hundreds, thousands, even tens of thousands of rows with a single context switch to the SQL engine and deposit all that data into a collection. But that boost in performance results in an increase in the amount of per-session memory consumed by the collection populated by the query. In addition, each session connected to Oracle Database has its own per-session memory area. Therefore, a BULK COLLECT that fetches numerous rows can seriously affect memory management on the entire database server.

So when you need to fetch multiple rows of data and the number of rows fetched can be large or grow over time, you should use BULK COLLECT with the LIMIT clause.

The following example selects only 1000 records per fetch to reduce memory consumption while minimizing the context switch by bulk processing.

PROCEDURE Cursor_Bulk_Col_Limit (
   lu_name_    IN VARCHAR2,
   time_stamp_ IN DATE )
IS
   CURSOR get_lu_recs IS
      SELECT log_id
      FROM history_log_tab
      WHERE lu_name = lu_name_
      AND time_stamp < time_stamp_;
BEGIN
   OPEN get_lu_recs;
   LOOP
      FETCH get_lu_recs BULK COLLECT INTO log_ids_ LIMIT 1000; 
      EXIT WHEN log_ids.count < 1;
      FORALL i_ IN 1..log_ids.count
         DELETE
         FROM history_log_tab
         WHERE log_id = log_ids_(i_);
      FORALL i_ IN 1..log_ids.count
         DELETE 
         FROM history_log_attribute_tab 
         WHERE log_id = log_ids_(i_);
   END LOOP;
   CLOSE get_lu_recs;
END Cursor_Bulk_Col_Limit;