Two pointer loops in the DB2 Stored Procedure

I believe everyone knows about the DB2 stored procedure. The following describes two pointer loops in the DB2 stored procedure. I hope this will help you learn about the DB2 stored procedure.

Pointer loops in DB2 stored procedures:

There are two ways for DB2 stored procedures: for Loop and while LOOP, for example:

For Loop:

 
 

  
  
for c1 as select deliveryid,deliverycode from delivery where status=40 for read only do  
  
  
 
  
  
select sum(qty) into dQty from deliverydetail where deliveryid=c1.deliveryid;  
  
  
 
  
  
......  
  
  
 
  
  
end for;  
 
 

While loop:

 
 

  
  
declare c1 cursor for select deliveryid,deliverycode from delivery where status=40 for read only ;  
  
  
 
  
  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET at_end_c1 = 1;  
  
  
 
  
  
set at_end_c1=0;  
  
  
open c1 ;  
  
  
FETCH c1 INTO ndeliveryid ,sdeliverycode;  
  
  
WHILE ( at_end_c1 = 0) do  
  
  
     select sum(qty) into dQty from deliverydetail where deliveryid=ndeliveryid ;  
  
  
 
  
  
...  
  
  
 
  
  
set at_end_c1=0;  
  
  
    FETCH c1 INTO ndeliveryid ,sdeliverycode;  
  
  
END while ;  
  
  
close c1;  
 
 

Obviously, the for loop is much simpler than the while loop.

In addition, if you do not pay attention to the while loop, it is easy to see an endless loop.

We recommend that you use a for loop.

However, sometimes the while LOOP must be used.

If there is a transaction in the loop, for example, there is a transaction that requires independent transactions in the loop, if the execution is successful, the transaction will be committed. If the execution fails, the transaction will be rolled back, to continue the next loop, you can only use the while loop.

Because the current pointer is automatically closed when commit is used.

In the while LOOP, if you want to commit without closing the current pointer, add with hold when defining the pointer. The above pointer is defined:

 
 

  
  
declare c1 cursor with hold for select deliveryid,deliverycode from delivery where status=40 for read only ; 
 
 

There is no way to deal with the for loop.

To use rollback, You need to define the savepoint and then roll back to the specified rollback point.
 

Implementation of creating a database in DB2

DB2 Directory view description

DB2 database commands

DB2 mount command performance factors

Implementation of DB2 column-to-row