Getting Syntax Error in MySQL Stored Procedure while trying nested Cursors

Question

I have a MySQL Stored Procedure Script in which I am trying to implement a nested cursors . Following is my code

DELIMITER //
DROP PROCEDURE IF EXISTS PopulateStaleEntityTable//
DELETE FROM bucket_stale;
LOAD DATA LOCAL INFILE '/home/centos/mysql1.txt' INTO TABLE bucket_stale;
CREATE PROCEDURE PopulateStaleEntityTable()
   BEGIN
    DECLARE finished INTEGER DEFAULT 0;
        DECLARE finished_stale INTEGER DEFAULT 0;
    DECLARE product_offer_id_var bigint(20) DEFAULT 0;
    DECLARE product_offer_group_id_var bigint(20) DEFAULT 0;
    DECLARE batch_count INTEGER DEFAULT 0;
        DECLARE max_batch_count INTEGER DEFAULT 100;
        DECLARE bucket_id_var bigint(10) DEFAULT 0;
        DECLARE stale_cursor CURSOR FOR 
        SELECT staleid FROM bucket_stale;
        DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished_stale = 1;
        OPEN stale_cursor;
        insert_stale : LOOP
        FETCH stale_cursor INTO bucket_id_var;
    SELECT bucket_id_var; 
    BEGIN
    DECLARE product_cursor CURSOR FOR 
        SELECT product_offer_id FROM product where bucket_id='1086';
        DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
    OPEN product_cursor;
        insert_entity: LOOP
        START TRANSACTION;
        FETCH product_cursor INTO product_offer_id_var;
        SELECT product_offer_group_id INTO product_offer_group_id_var FROM product_offer where id=product_offer_id_var;
        INSERT IGNORE INTO stale_cached_entity (entity_type,entity_id,cache_action,created,field_change) VALUES          ('PRODUCT_OFFER_GROUP',product_offer_group_id_var,  'UPDATE',now(),'DEFAULT_SUPC_LIST1'); 
            SET batch_count=batch_count+1;
            IF batch_count > max_batch_count THEN 
            COMMIT;
            SET batch_count=0;
        END IF;
        IF finished = 1 THEN 
        LEAVE insert_entity;
    END IF;
    END LOOP insert_entity
    END;
        IF finished_stale = 1 THEN 
        LEAVE insert_stale;
        END IF;   
    END LOOP insert_stale;
        CLOSE stale_cursor;
   END//
DELIMITER ;

When i try and run this script it gives the following error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END;
        IF finished_stale = 1 THEN 
   LEAVE insert_stale;
        END IF; ' at line 38

I tried to figure out whats wrong but i couldn't . What am I missing ?

This much SQL suggests you're trying to perform application-level operations inside the database. Why does this need to be done internally? — tadman
– tadman, Commented Oct 26, 2017 at 15:17

Bill Karwin · Accepted Answer · 2017-10-26 15:25:12Z

1

The problem lies in this line:

END LOOP insert_entity

If you review the example of cursor code at https://dev.mysql.com/doc/refman/5.7/en/cursors.html you see that END LOOP does not need the label, and it needs a semicolon statement terminator.

Like this:

END LOOP;

I agree with @tadman's comment. I understand some developers who have had experience with Oracle or Microsoft have an expectation of using stored procedures, but frankly, MySQL's stored procedure support sucks so bad you will be happier and more productive writing some simple Python scripts (or any other language).

answered Oct 26, 2017 at 15:25

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting Syntax Error in MySQL Stored Procedure while trying nested Cursors

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related