MySQL Forums
Forum List  »  Stored Procedures

Passing value beteen declared variables within cursor loop
Posted by: Michael Ayres
Date: March 15, 2016 04:26PM

I am having trouble with manipulating declared local variables within a stored procedure's cursor fetch loop.

--- pseudo code ----

I have Declare v_subcat_id INT (11)
Declare var2 as ...

Declare mycursor cursor as
select ,,, subcat_id from table1
While bla bla
FETCH mycursor INTO .., v_subcat_id
if v_subcat_id IS NULL THEN
var2 = v_subcat_id

....

UPDATE TABLE1 SET somefield = var2, otherfield=v_subcat_id;

--- error does not seem to pass local cursor var values to table update

end while


//////////////////

actual code

CREATE PROCEDURE updatecore1()
BEGIN
DECLARE v_nist_id INT(11);
DECLARE v_refdesc_fk INT(11);
DECLARE v_subcat_id INT(11);
DECLARE v_subcatdesc VARCHAR (101);
DECLARE v_prev_id INT (11);
DECLARE v_comments VARCHAR(101);
DECLARE update_count INT DEFAULT 0;
DECLARE row_not_found TINYINT DEFAULT FALSE;

DECLARE nist_core CURSOR FOR
SELECT ID, subcategory, subcat_id
FROM csf_core;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET row_not_found = TRUE;

OPEN nist_core;
WHILE row_not_found = FALSE DO
FETCH nist_core INTO v_nist_id, v_subcatdesc, v_subcat_id;
IF v_subcat_id IS NOT NULL THEN
v_prev_id = v_subcat_id;
ELSEIF v_subcat_id IS NULL THEN
v_subcat_id = v_prev_id;
UPDATE csf_core
SET subcat_id = v_subcat_id;
END IF;
SET update_count = update_count + 1;
END WHILE;
CLOSE nist_core;
SELECT CONCAT(update_count, ' Rows updated');

END//

DELIMITER ;

Michael Ayres
Senior Technical Project Manager
Cyber Security Coordination Center

Michael Ayres, MS, PMP, ACP, CISSP
University of California Office of the President (contractor)
1111 Franklin Street #7109 Oakland, Ca.94607
Michael.Ayres@ucop.edu | (510) 987-9459 | (415) 999-2049 Mobile

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.