Trying to write a simple procedure in mysql to calculate the bonus based on department id. The following is what i am doing. The problem is when i give my procedure a particular department id, it updates the salary for the entire table with the same salary value and not restrict itself to the provided dept id. Spent a lot of time on it but couldn't manage to figure out the issue.
create table employees(emp_id integer,dept_id int(4),emp_name varchar(10), salary float(11));
alter table employees add primary key (emp_id);
insert into employees values(1, 1,'A1',30);
insert into employees values(2, 2,'R1', 40);
insert into employees values(3, 3,'A2', 50);
insert into employees values(4, 4,'S1', 60);
insert into employees values(5, 1,'A3', 700);
delimiter $$
create procedure calculate_bonus(in in_dept_id int)
begin
declare done int default false;
declare emp_id integer;
declare dept_id int(4);
declare emp_name varchar(10);
declare new_salary float(11);
declare hike float(11);
declare c1 cursor for
select * from employees;
Declare continue handler for not found set done = TRUE;
open c1;
read_cursor: LOOP
fetch c1 into emp_id, dept_id, emp_name, new_salary;
if done then
leave read_cursor;
end if;
if(dept_id = in_dept_id) then
select case dept_id
when 1 then 10
when 2 then 20
when 3 then 30
else 40
end
into hike;
set new_salary = new_salary + (new_salary*hike/100);
select concat("salary",new_salary);
update employees
set salary = new_salary where dept_id = in_dept_id;
select concat("dept_id",dept_id, in_dept_id);
end if;
end LOOP read_cursor;
close c1;
end
$$
call calculate_bonus(3);
select * from employees;
The output I am getting is:
salary65
dept_id33
1 1 A1 65
2 2 R1 65
3 3 A2 65
4 4 S1 65
5 1 A2 65
DROP TABLE IF EXISTS T;
create table t(emp_id integer,dept_id int(4),emp_name varchar(10), salary float(11));
alter table t add primary key (emp_id);
insert into t values(1, 1,'A1',30);
insert into t values(2, 2,'R1', 40);
insert into t values(3, 3,'A2', 50);
insert into t values(4, 4,'S1', 60);
insert into t values(5, 1,'A3', 700);
drop procedure if exists p;
delimiter $$
create procedure p(in in_dept_id int)
begin
declare done int default false;
declare vemp_id integer;
declare vdept_id int(4);
declare vemp_name varchar(10);
declare vnew_salary float(11);
declare vhike float(11);
declare c1 cursor for select * from t where dept_id = in_dept_id;
Declare continue handler for not found set done = TRUE;
open c1;
read_cursor: LOOP
fetch c1 into vemp_id, vdept_id, vemp_name, vnew_salary;
if done then
leave read_cursor;
end if;
select case vdept_id
when 1 then 10
when 2 then 20
when 3 then 30
else 40
end
into vhike;
set vnew_salary = vnew_salary + (vnew_salary*vhike/100);
select concat("salary",vnew_salary);
update t
set salary = vnew_salary where dept_id = in_dept_id;
select concat("dept_id",vdept_id, in_dept_id);
end LOOP read_cursor;
close c1;
end $$
call p(3);
call p(1);
select * from t;
+--------+---------+----------+--------+
| emp_id | dept_id | emp_name | salary |
+--------+---------+----------+--------+
| 1 | 1 | A1 | 33 |
| 2 | 2 | R1 | 40 |
| 3 | 3 | A2 | 65 |
| 4 | 4 | S1 | 60 |
| 5 | 1 | A3 | 770 |
+--------+---------+----------+--------+
5 rows in set (0.00 sec)
Note I have uniquely named the declared variables, amended the cursor select to only select the dept_id I am interested in,removed the now redundant if statement and further qualified the update statement on employee id. The procedure (possibly) and the cursor (definitely) are unnecessary (unless you have been specifically told to do it this way) and the same result can be achieved by a single update statement for example
update t
set salary = salary + (salary * case dept_id
when 1 then 10
when 2 then 20
when 3 then 30
else 40
end / 100)
where dept_id = 3;
With a simple change to accept a parameter value instead of the hard coded 3 this would be all the code you would need in your procedure.