zwwcn

Just another WordPress.com site

MySQL Procedure single loop example

DROP PROCEDURE IF EXISTS `create_string_field_options`;
DELIMITER //

CREATE PROCEDURE create_string_field_options()
BEGIN

DECLARE franchiseId bigint(20);
DECLARE no_more_rows BOOLEAN;
DECLARE franchise_cur CURSOR FOR
SELECT
OBJECT_ID
FROM OP_FRANCHISE
WHERE REC_STATUS="A" AND ORGANISATION_TYPE IN ("RF","RO");

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;

OPEN franchise_cur;

the_loop: LOOP

FETCH franchise_cur
INTO franchiseId;

IF no_more_rows THEN
CLOSE franchise_cur;
LEAVE the_loop;
END IF;

INSERT INTO OP_STRING_FIELD_OPTION VALUES(getNextObjectID(),'TypeName',franchiseId);

END LOOP the_loop;

END
//
DELIMITER ;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: