MySQL Nested Cursor Declaration Issue

Unlikes the Oracle PL/SQL which you can declare the CURSORs in the beginning of the program then execute the SELECT statement anywhere in the code body, MySQL needs to declare the CURSORs in the beginning of the program and applys the SELECT statement right behind the declaration. If you try to declare a Cursor in the middle of the codes, you will get -

You have an error in your sql syntax, check the manual that corresponds to your Mysql server version for right syntax to use .....

To avoid the problem, you can either issue a FUNCTION to handle the inside nested cursor statements or issue a nested BLOCK to declare the Cursor -

CREATE PROCEDURE FIND_ITEM()
BLOCK1: BEGIN
DECLARE done INT DEFAULT 0;

DECLARE XINVNO TEXT;

DECLARE cur CURSOR FOR
SELECT INVNO FROM TMP_SN5 WHERE TOT1=0 AND ITEMS='';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cur;
p_loop:LOOP
FETCH cur INTO XINVNO;
IF done=1 THEN
LEAVE p_loop;
END IF;

BLOCK2: BEGIN
DECLARE DIS_ITEM TEXT;
DECLARE XQTY INT;
DECLARE XITEM TEXT;
DECLARE done1 INT DEFAULT 0;
DECLARE cur2 CURSOR FOR
SELECT ITEM, QTYSHP FROM ARTRAN01 WHERE TRIM(INVNO)=TRIM(XINVNO);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1=1;
OPEN cur2;
SET DIS_ITEM='';
k_loop:LOOP
FETCH cur2 INTO XITEM, XQTY;
IF done1=1 THEN
LEAVE k_loop;
END IF;
SET DIS_ITEM = CONCAT(DIS_ITEM,  XITEM, ' (' , CONVERT(XQTY, CHAR), ') ') ; 
END LOOP k_loop;

CLOSE cur2;

IF DIS_ITEM<>'' THEN
UPDATE TMP_SN5 SET ITEMS=DIS_ITEM WHERE TRIM(INVNO)=TRIM(XINVNO);
END IF;
END BLOCK2;

END LOOP p_loop;
CLOSE cur;

END BLOCK1;