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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

theGhost_k8Database ConsultantCommented:
0
mkiredjianCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.