Update table inside a loop in a procedure pl/sql

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.