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.
CLICK HERE to find out more related problems solutions.