mysqlsqlprocedurecursors

How to handle multiple cursors during a procedure?


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;   

Solution

  • 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;
    ...