Correct use of dynamic cursors in DB2 stored procedures

This article mainly introduces the correct use of dynamic cursors in DB2 stored procedures. I believe that if you have mastered the correct use of dynamic cursors in DB2 stored procedures, it will be of great help in future studies or work. The following is a detailed description of the main content of the article.

 
 

  
  
CREATE PROCEDURE data_wtptest( IN in_taskid_timestamp varchar(30),  
  
  
OUT o_err_no int,  
  
  
OUT o_err_msg varchar(1024))  
  
  
LANGUAGE SQL  
  
  
P1: BEGIN ATOMIC  
 
 

Declaration start

Temporary Variable Error variable

 
 

  
  
DECLARE SQLCODE integer default 0;  
  
  
DECLARE SQLStmt varchar(1024) default '';  
  
  
DECLARE r_code integer default 0;  
  
  
DECLARE state varchar(1024) default 'AAA'; 
 
 

Record the current work of the program

 
 

  
  
DECLARE at_end int DEFAULT 0;  
  
  
DECLARE t_destnetid int default 0;  
  
  
DECLARE t_recvid varchar(30) default '';  
  
  
DECLARE SP_Name varchar(50) default 'data_integrate'; 
 
 

Declare the cursor value in the DB2 Stored Procedure

Declare Dynamic Cursor storage variables

 
 

  
  
DECLARE stmt1 STATEMENT;  
  
  
DECLARE c1 CURSOR FOR stmt1; 
 
 

Declarative error handling

 
 

  
  
DECLARE EXIT HANDLER FOR SQLEXCEPTION  
  
  
begin  
  
  
set r_code=SQLCODE;  
  
  
set o_err_no=1; 
 
 

Set o_err_msg = 'process [' | state | '] error,' | 'error code SQLCODE: [' | CHAR (r_code) | '].';

 
 

  
  
insert into fcc_sp_log(object,name,value) values(SP_Name,in_taskid_timestamp,o_err_msg);  
  
  
end;  
  
  
DECLARE continue HANDLER for not found  
  
  
begin  
  
  
set at_end = 1; 
 
 

Set state = 'found 0 rows of records or has reached the end of the record .';

End;

Statement ended

SET state = '[add] Number of statistics in a separate test ';

 
 

  
  
SET SQLStmt='SELECT count(*) FROM wtp_pre_download where task_timestamp = ?';  
  
  
PREPARE stmt1 FROM SQLStmt;  
  
  
OPEN c1 USING in_taskid_timestamp;  
  
  
FETCH c1 INTO t_destnetid;  
  
  
CLOSE c1; 
 
 

SET state = '[add] query details ';

 
 

  
  
SET at_end = 0;  
  
  
SET SQLStmt='SELECT recv_userid FROM wtp_pre_download where task_timestamp = ?';  
  
  
PREPARE c1 FROM SQLStmt;  
  
  
OPEN c1 USING in_taskid_timestamp;  
  
  
SET state='[add]************';  
  
  
FETCH c1 INTO t_recvid;  
  
  
SET state='[add]============';  
  
  
insert into fcc_sp_log(object,name,value) values(SP_Name,in_taskid_timestamp,t_recvid);  
  
  
END p1 
 
 

The above content is an introduction to the use of dynamic cursors In the DB2 stored procedures. I hope you will gain some benefits.