MySQL Forums
Forum List  »  Stored Procedures

PROCEDURE DOESNT WORK
Posted by: Serhii Nuzhnyi
Date: April 27, 2018 02:05PM

I have such a mysql procedure. It shows no error but do nothing. Cant figure out why.
DROP PROCEDURE IF EXISTS `replbnd`;
delimiter //
CREATE PROCEDURE replbnd()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE done2 INT DEFAULT FALSE;
DECLARE ido INT;
DECLARE brand VARCHAR(255) DEFAULT '';
DECLARE captiono VARCHAR(255) DEFAULT '';
DECLARE brands VARCHAR(955) DEFAULT '';
DECLARE hyphen VARCHAR(1) DEFAULT '-';
DECLARE plus VARCHAR(1) DEFAULT '+';
DECLARE comma VARCHAR(1) DEFAULT ',';
DECLARE space VARCHAR(1) DEFAULT ' ';
DECLARE upperc, camel, element VARCHAR(255);

BLOCK1: BEGIN
DECLARE cur1 CURSOR FOR SELECT caption FROM db.brand_content;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur1;
iterator1: LOOP
FETCH cur1 INTO brand;
IF done THEN
LEAVE iterator1;
END IF;
SET brand = REPLACE(brand, plus, comma);
SET brand = REPLACE(brand, space, comma);
SET brand = REPLACE(brand, hyphen, comma);
SET brand = LCASE(brand);
SET brands = CONCAT(brand, ',', brands);
END LOOP iterator1;
CLOSE cur1;
END BLOCK1;

BLOCK2: BEGIN
DECLARE cur2 CURSOR FOR SELECT id, caption FROM db.catalog_items_content;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
OPEN cur2;
iterator2: LOOP
IF done2 THEN
LEAVE iterator2;
END IF;
FETCH cur2 INTO ido, captiono;

BLOCK3: BEGIN
iterator3 : LOOP
IF LENGTH(TRIM(brands)) = 0 OR brands IS NULL THEN
LEAVE iterator3;
END IF;
SET element = SUBSTRING_INDEX(brands, ',', 1);
SET brands = SUBSTRING(brands, LENGTH(element)+2);
SET captiono = REPLACE(captiono, element, '');
SET camel = CONCAT(UCASE(LEFT(element, 1)), LCASE(SUBSTRING(element, 2)));
SET captiono = REPLACE(captiono, camel, '');
SET upperc = UCASE(element);
SET captiono = REPLACE(captiono, upperc, '');
SET captiono = TRIM(TRAILING space FROM captiono);
SET captiono = TRIM(TRAILING hyphen FROM captiono);
SET captiono = TRIM(TRAILING comma FROM captiono);
SET captiono = TRIM(TRAILING space FROM captiono);
UPDATE db.catalog_items_content SET caption = @captiono WHERE id= @ido;
ITERATE iterator3;
END LOOP iterator3;
END BLOCK3;

END LOOP iterator2;
CLOSE cur2;
END BLOCK2;

END//
CALL replbnd();

Options: ReplyQuote


Subject
Views
Written By
Posted
PROCEDURE DOESNT WORK
1211
April 27, 2018 02:05PM
471
April 27, 2018 02:13PM


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.