DB2 programming skills-correct use of cursors

Today we will mainly talk about the use of the cursor in the tips of DB2 programming. If you are interested in the use of the cursor in the tips of DB2 programming, you can click to view the following articles. The following is a detailed description of the main content of the article. I hope you will have a better understanding of it after browsing.

Note: commit and rollback

When using a cursor, pay special attention that if the with hold option is not added, the cursor will be closed during Commit and Rollback. There are many things to note about Commit and Rollback. Be careful

Two Methods for defining a cursor

One is

 
 

  
  
declare continue handler for not found  
  
  
begin  
  
  
set v_notfound = 1;  
  
  
end;  
  
  
declare cursor1 cursor with hold for select market_code from tb_market_code for update;  
  
  
open cursor1;  
  
  
set v_notfound=0;  
  
  
fetch cursor1 into v_market_code;  
  
  
while v_notfound=0 Do  
  
  
--work  
  
  
set v_notfound=0;  
  
  
fetch cursor1 into v_market_code;  
  
  
end while;  
  
  
close cursor1;  
 
 

This method is complex but flexible. In particular, you can use the with hold option. This method can only be used if the cursor is not closed due to a commit or rollback in a loop.

The other is

 
 

  
  
pcursor1: for loopcs1 as cousor1 cursor as  
  
  
select market_code as market_code  
  
  
from tb_market_code  
  
  
for update  
  
  
do  
  
  
end for;  
 
 

The advantage of this method is that it is relatively simple and does not need or allow) use open, fetch, close.

However, the with hold option cannot be used. If you want to use commit in a cursor loop, rollback cannot use this method. If you do not have the commit or rollback requirements, we recommend this method (it seems that there is a problem with the For method ).

How to modify the current record of a cursor

 
 

  
  
update tb_market_code set market_code='0' where current of cursor1; 
 
 

However, be sure to define cursor1 as a modifiable cursor.

 
 

  
  
declare cursor1 cursor for select market_code from tb_market_code  
  
  
for update;  
 
 

For update cannot be used together with group by, DISTINCT, order by, for read only, UNION, EXCEPT for union all.

The above content is a description of the usage of the cursor in the tips of the DB2 compiler. I hope it will help you in this regard.