How to create mysql nested loop in stored procedure

Question

I got the exception to create. Can anybody help me? How to write nested loop in stored procedure in Mysql the proper way and what is my wrong?

DELIMITER $$

`INVESTMENT_MATCH_POINT_CREATOR`(_percentage INT, _vat_tex INT)
BEGIN
    DECLARE _user_id INT; 
    DECLARE _package_id INT;


    DECLARE _left_investment INT;
    DECLARE _right_investment INT;

    DECLARE _left_point INT;
    DECLARE _right_point INT;

    DECLARE _left_carry_point INT;
    DECLARE _right_carry_point INT;

    DECLARE _get_point INT;

    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE _user_investment_table CURSOR FOR SELECT user_id,package_id,left_invesetment,right_investment DATA FROM user_investment_match;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN _user_investment_table;

    read_loop:LOOP

    FETCH _user_investment_table INTO _user_id,_package_id,_left_investment,_right_investment;

    IF done THEN 
    LEAVE read_loop;
    END IF;

        BEGIN
                DECLARE _match_point INT; 
                DECLARE done2 BOOLEAN DEFAULT FALSE;

                DECLARE _package_match_point_table CURSOR FOR SELECT match_point DATA FROM package_match_points WHERE package_id=_package_id ORDER BY match_point DESC;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;

                OPEN _package_match_point_table;
                read_loop2:LOOP

                FETCH _package_match_point_table INTO _match_point;

                    IF done2 THEN 
                    LEAVE read_loop2;
                    END IF;

                    /*if(_match_point=<_left_investment) and (_match_point=<_right_investment) then
                        set _left_point=abs(_left_investment-_match_point);
                        set _right_point=abs(_right_investment-_match_point);
                        set _get_point=((_match_point*_percentage)/100);
                    end if;*/

                END LOOP;
                CLOSE _package_match_point_table;
            END$$




    END LOOP;
    CLOSE _user_investment_table;

END$$

DELIMITER ;

I would wager that 99% of the time, if you're nesting cursors, you're doing something wrong. You should look into set based solutions instead of procedural ones. — Michael Fredrickson
– Michael Fredrickson, Commented Mar 6, 2012 at 19:49

yoel halb · Accepted Answer · 2012-03-06 20:13:14Z

1

You should use the specified delimiter only at the end of the procedure, however in your code you are using the $$ delimiter at the end of the inner block, (and in other words you are using the $$ delimiter which signals the end of the procedure before it is actually ended)

answered Mar 6, 2012 at 20:13

yoel halb

12.8k3 gold badges59 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

How to create mysql nested loop in stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related