Common DB2 cycle usage

DB2 cycles are widely used in DB2 databases. The following describes four common DB2 cycle usage methods. We hope this will help you learn about DB2 loops.

1. Use the obtained cursor to cycle in the stored procedure:

 
 

  
  
DECLARE CUR_FEESET cursor with return to caller (
  
  
SELECT
  
  
--
  
  
FROM table
  
  
WHERE condition
  
  
);
  
  
OPEN CUR_FEESET; -- get the cursor
  
  
-- Obtain the number of cursor records
  
  
SELECT
  
  
Count (CIF_CSTNO)
  
  
Into v_count
  
  
FROM CB_CSTINF A, CB_CSTBSNINF B
  
  
Where a. CIF_STT <> '3' and a. CIF_CSTNO = B. CBI_CSTNO ;--
  
  
FETCH CUR_FEESET INTO V_CSTNO, V_FEECODE, V_CSTLEVEL, V_FEEMODE ;--
  
  
WHILE V_COUNT> 0 DO
  
  
........
  
  
FETCH CUR_FEESET INTO V_CSTNO, V_FEECODE, V_CSTLEVEL, V_FEEMODE ;--
  
  
End while ;--
  
  
 
 
 

2. Another standard db2 cycle format (leave and iterate usage ):

 
 

  
  
SET V_COUNT = LENGTH (V_VALIDAUTHCOMBOS );--
  
  
 
  
  
SET V_INDEX = 0 ;--
  
  
 
  
  
AUTHLOOP:
  
  
LOOP
  
  
IF V_INDEX> = V_COUNT THEN
  
  
Leave authloop; -- equivalent to break
  
  
End if ;--
  
  
....
  
  
SET V_INDEXV_INDEX = V_INDEX + 1 ;--
  
  
.......
  
  
IF condition THEN
  
  
Iterate authloop; -- equivalent to continue
  
  
End if ;--
  
  
End loop ;--
 
 

3. Loop V_TEMP = 'cb1001 | CB1002 | CB1003 | ') of the truncated string '):

 
 

  
  
SET V_LENGTH = LENGTH (V_TEMP );
  
  
WHILE V_LENGTH> 0 DO
  
  
SET V_POS = POSSTR (V_TEMP, '| ');
  
  
SET V_CURRENT_BSN = SUBSTR (V_TEMP, 1, V_POS-1 );
  
  
SET V_TEMP = SUBSTR (V_TEMP, V_POS + 1 );
  
  
SET V_LENGTH = LENGTH (V_TEMP );
  
  
-- The last field is not truncated.
  
  
SET V_BSNTYPE = V_CURRENT_BSN;
  
  
End while;
 
 

4. You do not need to open the cursor for a cursor loop ):

 
 

  
  
DROP PROCEDURE TESTFOR;  
  
  
    CREATE PROCEDURE TESTFOR()  
  
  
    LANGUAGE SQL  
  
  
    BEGIN  
  
  
    DECLARE V_TEMP1 VARCHAR(2);  
  
  
    DECLARE V_TEMP2 VARCHAR(70);  
  
  
    FOR V1 AS CURSOR1 CURSOR FOR  
  
  
SELECT STUDENT_ID AS TEMP1,STUDENT_NAME AS TEMP2 FROM STUDENT  
  
  
    DO   
  
  
DELETE FROM STUDENT WHERE STUDENT_ID = TEMP1;  
  
  
SET V_TEMP1 = TEMP1;  
  
  
SET V_TEMP2 = TEMP2;  
  
  
    END FOR;  
  
  
    COMMIT;  
  
  
    END; 
 
 

Implementation of creating a database in DB2

DB2 Directory view description

Create a table in DB2 -- a table with an auto-incrementing Column

DB2 rename a table and view table information

Implementation of DB2 column-to-row