MySQL Store Procedure to Loop with Table Cursor and Update with UUID()

This is a sample MySQL store procedure where I loop in all rows in a table and then I update each row with a UUID().


DELIMITER //
CREATE PROCEDURE dwdata.puFCTableGUIDs()
BEGIN

/* -- Declare variables used just for cursor and loop control */
DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;

/* declare variables to hold table values */
DECLARE doc_id INT DEFAULT 0;
/* Lets create a cursor for building a list */
DECLARE CursorLoop CURSOR FOR
SELECT DWDOCID
FROM dwdata.pe_docs_mysql;

/* Declare 'handlers' for exceptions */
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;

/* 'open' the cursor and capture the number of rows returned */
/* (the 'select' gets invoked when the cursor is 'opened') */
OPEN CursorLoop;
select FOUND_ROWS() into num_rows;

/** Start the loop **/
the_loop: LOOP

FETCH CursorLoop
INTO doc_id;

/* Check the loop*/
IF no_more_rows THEN
CLOSE CursorLoop;
LEAVE the_loop;
END IF;

/* Update the table*/
update dwdata.pe_docs_mysql
set contact = UUID()
where dwdocid = doc_id;

END LOOP the_loop;
END;
//

,

No comments yet.

Leave a Reply

*