'How to use multiple cursor with dynamic insertion
I have a stored procedure were I have defined two cursor, I want to do an insertion to label_entry in such a way that for each value of label_cursor there are 50 insertions using incident_cursor.
CREATE PROCEDURE create_label_entry()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE inc_id VARCHAR(100);
DECLARE counter, row_count INT;
DECLARE label_cursor CURSOR FOR select sys_id from label ;
DECLARE incident_cursor CURSOR FOR select sys_id from task where sys_class_name = 'incident' ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET counter = 1;
SET row_count = 50;
OPEN incident_cursor;
OPEN label_cursor;
read_loop: LOOP
FETCH incident_cursor INTO inc_id;
FETCH label_cursor INTO label_id;
IF done THEN
LEAVE read_loop;
END IF;
label1: LOOP
SET counter = counter + 1;
INSERT INTO label_entry ( `read`,`sys_mod_count`,`notify_onchange`,`label`,`sys_updated_on`,`title`,`table_key`,`sys_id`,`sys_updated_by`,`sys_created_on`,`id_type`,`table`,`sys_created_by`)
VALUES ('yes',0,0,label_id,CURRENT_TIMESTAMP,'Incident - Script',inc_id,REPLACE(UUID(), '-', ''),'admin',CURRENT_TIMESTAMP,'Incident','incident','admin') ;
IF counter <= row_count THEN ITERATE label1;
ELSE SET counter = 1;
END IF;
LEAVE label1;
END LOOP label1;
END LOOP;
CLOSE incident_cursor;
CLOSE label_cursor;
END; //
I tried using multiple cursor but unable to hold the control on their iteration
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
