db2存储过程批量插入数据_DB2存储过程通过游标实现批量数据处理

CREATE procedure proc_change()

LANGUAGE SQL

BEGIN

DECLARE l_id INTEGER;

DECLARE l_detail_id INTEGER;

DECLARE l_gather_type VARCHAR(2);

DECLARE l_item_id VARCHAR(30);

DECLARE counts INTEGER;

SELECT count(*) INTO counts FROM ZM_GATHER_ITEM;

begin

DECLARE cur CURSOR FOR

SELECT ID, detail_id, gather_type, item_id FROM ZM_GATHER_ITEM;

OPEN CUR;

WHILE counts > 0 DO

FETCH CUR INTO l_id, l_detail_id, l_gather_type, l_item_id;

UPDATE ZM_GATHER_INFO SET GATHER_ITEM_ID = l_ID

WHERE DETAIL_ID = l_detail_id and index_type = l_gather_type AND ITEM_ID = l_item_id;

SET counts = counts - 1;

END WHILE;

END;

END;

SELECT * FROM ZM_GATHER_ITEM;

SELECT * FROM ZM_GATHER_INFO;

--DROP PROCEDURE proc_change;

CALL PROC_CHANGE();