Update table inside a loop in a procedure pl/sql

So i want to hire a new manager for a department. This procedure has 2 parameters, the department name i want to change, and the new id for manager (taken from employee’s id). So to change it, i need to update all employee that has the old manager id, and change their manager id into the new one. This is my code so far, the problem is that it updated all of the employees’ manager, so the whole database is updated. I must use procedure, not function. Any idea? Thanks.

CREATE OR REPLACE PROCEDURE update_manager(v_deptname IN departments.department_name%TYPE,v_empid IN employees.employee_id%TYPE) IS
v_deptid departments.department_id%type;
BEGIN
SELECT department_id INTO v_deptid FROM departments WHERE department_name=v_deptname;
    FOR i IN (SELECT * FROM employees)
    LOOP
        IF i.department_id=v_deptid THEN
            UPDATE employees
            SET manager_id=v_empid
            WHERE i.department_id=v_deptid;
        END IF;
    END LOOP;
END;
/
BEGIN
update_manager('Marketing',100);
END;
/

Answer

If you must create a PL/SQL procedure then you can do

CREATE OR REPLACE PROCEDURE update_manager(v_deptname IN departments.department_name%TYPE,v_empid IN employees.employee_id%TYPE) IS
  v_deptid departments.department_id%type;
BEGIN
  SELECT department_id INTO v_deptid FROM departments WHERE department_name=v_deptname;
  UPDATE employees
  SET    manager_id=v_empid
  WHERE  department_id=v_deptid;
END;
/

The problem with your code that’s causing “it updated all of the employees’ manager” is that your update statement:

UPDATE employees
SET manager_id=v_empid
WHERE i.department_id=v_deptid;

Your filter here is comparing i.department_id, this is the variable that’s coming from your FOR i IN (SELECT * FROM employees), NOT from the update statement. You’ve already confirmed that i.department_id=v_deptid because you are calling this in a loop with an if statement checking it.

It is not efficient at all to get all rows in employees, loop the results, checking each row if it matches a condition and then firing off an update statement (even if your update statement is filtering against the correct row.