/******************************************************************************   
  3 examples below query rows and store results in a CURSOR
******************************************************************************/
truncate table output;
/
/*=======================================================================
  Declare a cursor
  Fetch multiple rows through the cursor
========================================================================*/
DROP PROCEDURE proc_4a;
/
CREATE PROCEDURE proc_4a(p_student_id INT)
BEGIN
    DECLARE v_student_id    int; 
    DECLARE v_fname         varchar(50); 
    DECLARE v_lname         varchar(50); 
    DECLARE more            int  DEFAULT 1;
    DECLARE student_cursor CURSOR FOR 
        SELECT student_id, fname, lname 
          FROM student
         WHERE student_id  <= p_student_id;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET more=0;
    OPEN student_cursor;
L1: LOOP
        FETCH student_cursor INTO v_student_id, v_fname, v_lname;
        IF more=0 THEN
            LEAVE L1;
        END IF;
        INSERT into output VALUES( concat(v_fname, ' ', v_lname) );  --or do something else
    END LOOP;
    CLOSE student_cursor;
END;    
/ 
CALL proc_4a(5);
/
SELECT * FROM output; 
/

/*=======================================================================
  Declare nested cursors
  Fetch multiple rows using the cursors
  Outer cursor loops through all instructors
  If instructor that we are looking for from input paramters
  Inner cursor loops through all students for that instructor. 
------------------------------------------------------------------*/
DROP PROCEDURE proc_4b;
/
CREATE PROCEDURE proc_4b(p_first varchar(20), p_last varchar(20))
BEGIN
-- ===============================================
-- Param: p_first varchar(20), p_last varchar(20)
-- ===============================================
    DECLARE done_inst  INT DEFAULT 0;
    DECLARE fi,li,ssn1 VARCHAR(20);
    DECLARE fs,ls      VARCHAR(20);
    DECLARE result     VARCHAR(5000) DEFAULT '';
    DECLARE cur1 CURSOR FOR 
        SELECT fname, lname, ssn  
          FROM instructor
         ORDER BY 2,1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_inst=1;

    OPEN cur1;
L1: LOOP                                          --loop thru the instructors
        FETCH cur1 INTO fi,li,ssn1;
        IF done_inst THEN
            LEAVE L1;
        END IF;
        SET result=concat(result,'&lt;b>&lt;u>Instructor: ',fi,' ',li,'&lt;/u>&lt;/b>&lt;br>');

        IF (fi=p_first AND li=p_last) THEN       --if the instructor from param
            BEGIN
                DECLARE done_stu INT DEFAULT 0;
                DECLARE cur2 CURSOR FOR 
                    SELECT distinct fname, lname
                      FROM student join class on ssn=stu_ssn
                       AND inst_ssn=ssn1        --ssn1 is a variable from cur1
                     ORDER BY 2,1;
                DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_stu=1;

                OPEN cur2;
L2:             LOOP                            --loop thru the students
                    FETCH cur2 INTO fs,ls;
                    IF done_stu THEN
                        LEAVE L2;
                    END IF;
                    SET result=concat(result,fs,' ',ls,'&lt;br>');
                END LOOP;
                CLOSE cur2;
            END;
        END IF;
    END LOOP;
    CLOSE cur1;

    SET @result = result;                  --make it a global variable
END;
/
CALL proc_4b('George','Pefanis');
/
SELECT @result;
/


/*=======================================================================
  Declare nested cursors
  Fetch multiple rows using the cursors
  Outer cursor loops through all instructors
  Inner cursor loops through all students for that instructor. 
========================================================================*/
TRUNCATE TABLE output;
/
DROP PROCEDURE proc_4c;
/
CREATE PROCEDURE proc_4c()
BEGIN
    DECLARE done_inst  INT DEFAULT false;
    DECLARE fi,li,ssn1 VARCHAR(20);
    DECLARE fs,ls      VARCHAR(20);
    DECLARE result     VARCHAR(5000) DEFAULT '';
    DECLARE cur1 CURSOR FOR 
        SELECT fname, lname, ssn  
          FROM instructor
         ORDER BY 2,1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_inst=true;

    OPEN cur1;
L1: LOOP                                          --loop thru the instructors
        FETCH cur1 INTO fi,li,ssn1;
        IF done_inst THEN
            LEAVE L1;
        END IF;
        SET result=concat('<b>Instructor: ',fi,' ',li,':</b> ');
        BEGIN
            DECLARE done_stu INT DEFAULT false;
            DECLARE cur2 CURSOR FOR 
                SELECT distinct fname, lname
                  FROM student join class on ssn=stu_ssn
                 WHERE inst_ssn=ssn1             --ssn1 is a variable from cur1
                 ORDER BY 2,1;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done_stu=true;
            SET done_stu=false;
            OPEN cur2;
L2:         LOOP                                 --loop thru the students
                FETCH cur2 INTO fs,ls;
                IF done_stu THEN
                    LEAVE L2;
                END IF;
                SET result=concat(result,ls,', ');
            END LOOP;
            CLOSE cur2;
        END;
        INSERT INTO output VALUES(result);
    END LOOP;
    CLOSE cur1;
END;
/
CALL proc_4c( );
/
SELECT * FROM output;