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.