I want use two cursor declared in begining of procedure
These are the tables
people.
________________
| code | name |
| 1 | Carlos |
| 2 | Charles |
| 3 | Mary |
| 4 | Jhon |
| 5 | Jen |
-----------------
classes.
________________
| code | desc |
| 1 | Java |
| 2 | PHP |
| 3 | Perl |
| 4 | Python |
| 5 | Javascript |
-------------------
DECLARE R CURSOR FOR SELECT C.CODE FROM people;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
DECLARE classes CURSOR FOR SELECT DISTINCT a.CODE FROM classes;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
To give two loops, one inside the other........
But I am retrieve this message:
Cursor declaration after handler declaration.............
DECLARE v_cdclass INT;
DELCARE v_codperson INT;
DROP TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1
(
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
cdfunc INT(11),
class INT
);
OPEN R;
loop0: LOOP
FETCH R INTO v_codperson;
IF done2 THEN
LEAVE loop0;
END IF;
BLOCO2: BEGIN
OPEN classes;
loop1: LOOP
FETCH classes INTO v_cdclass ;
IF done1 THEN
LEAVE loop1;
END IF;
INSERT INTO temp1
( cdfunc, class )
VALUES
( v_codperson , v_cdclass);
END LOOP loop1;
CLOSE classes;
END BLOCO2;
END LOOP ;
CLOSE R;
SELECT * FROM temp1;
Try:
...
DECLARE R CURSOR FOR SELECT C.CODE FROM TABLE1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
...
BLOCO2: BEGIN
DECLARE classes CURSOR FOR SELECT DISTINCT a.CODE FROM TABLE2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
...