CODEWITHSUNDEEP
mysql
Talen Kylon
Talen Kylon

Reputation: 1968

Stored Procedure/Cursor Logic

I've designed a stored procedure that uses two cursors to update a total_sal column in department based on employee's that belong to that department.

I am not getting the correct results and I believe I may have a wrong idea on the flow of a stored procedure and cursor.

The logic I am trying to use to accomplish this:

1- open Cursor to iterate through department

2- at each row in department get the department number

3- open Cursor to iterate through employee

4- at each employee, if employee dept number is equal to the department number from the dept curser, we add their salary to a total_sum

5- after employee table has been exhausted we finally update the department's total_salary with the total_sum

6 - restart at step 2

Here is the stored procedure:

CREATE PROCEDURE updateSalary()
BEGIN
  DECLARE emp_sal, eDno, dDno INT;  
  DECLARE total_sum INT DEFAULT 0;
  DECLARE finished INT DEFAULT 0;
  DECLARE dep_cursor CURSOR FOR SELECT Dno FROM Department;
  DECLARE emp_cursor CURSOR FOR SELECT Dno, Salary FROM Employee;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

  open dep_cursor;
  department_loop: LOOP
    FETCH dep_cursor INTO dDno;
    IF finished = 1 THEN
      LEAVE department_loop;
    END IF;

    open emp_cursor;

    employee_loop: LOOP
      FETCH emp_cursor INTO eDno, emp_sal;
      IF eDno = dDno THEN
        SET total_sum = total_sum + emp_sal;
      END IF;
      IF finished = 1 THEN
        update department SET total_sal = total_sum WHERE department.dno = dDno;
        LEAVE employee_loop;
      END IF;
      SET total_sum = 0;
    END LOOP employee_loop;
    close emp_cursor;
  END LOOP department_loop;
  CLOSE dep_cursor;
END;
/

Here is the SQLFiddle I've been working with, http://sqlfiddle.com/#!2/b9cc2f/1/1

I'm not very familiar with debugging MySQL, if this were Java I would just throw in some print statements to see what's going on.

Upvotes: 0

Views: 140

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 23992

I see some logical errors in your stored procedure. It is due to improper handling of cursor finished fetch state.

As you mentioned it is an assignment on cursors, I suggest you some steps to follow that will perform desired action.

  1. Declare a cursor on employee with dno and sum of salary grouped by dno.
  2. Open the same cursor and loop through
  3. If not finished then, update deparment in a single statement with values fetched from above emp cursor. use dno fetched in where clause.
  4. If finished then Close emp cursor
  5. End procedure
  6. Call procedure
  7. Select all from department
    -- resultset shows: all happies

Upvotes: 1

Related Questions