Nested cursor in Mysql

How to implement nested cursor in a single stored procedure in Mysql. I want to write multiple cursor with in the loop of a cursor.
npglobalAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
mkiredjianConnect With a Mentor Commented:
OK I will give you an example

CREATE PROCEDURE good_nested_cursors1(  )
   READS SQL DATA
BEGIN

  DECLARE l_department_id INT;
  DECLARE l_employee_id   INT;
  DECLARE l_emp_count     INT DEFAULT 0 ;
  DECLARE l_done          INT DEFAULT  0;

  DECLARE dept_csr cursor  FOR
    SELECT department_id FROM departments;
  DECLARE emp_csr cursor  FOR
    SELECT employee_id FROM employees
     WHERE department_id=l_department_id;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;

  OPEN dept_csr;
  dept_loop: LOOP   -- Loop through departments
    FETCH dept_csr into l_department_id;

    IF l_done=1 THEN
       LEAVE dept_loop;
    END IF;

    OPEN emp_csr;
    SET l_emp_count=0;
    emp_loop: LOOP      -- Loop through employee in dept.
      FETCH emp_csr INTO l_employee_id;

      IF l_done=1 THEN
         LEAVE emp_loop;
      END IF;
      SET l_emp_count=l_emp_count+1;
    END LOOP;
    CLOSE emp_csr;
    SET l_done=0;

    SELECT CONCAT('Department ',l_department_id,' has ',
           l_emp_count,' employees');

  END LOOP dept_loop;
  CLOSE dept_csr;

END;
0
 
mkiredjianCommented:
If there is something you didn't get it ask me about it
0
 
npglobalAuthor Commented:
Dear Expert,
Thanks for your comments, shall test and revert on your siggestion
0
 
npglobalAuthor Commented:
thanks we could manage to proceed further
0
All Courses

From novice to tech pro — start learning today.