Update employee's salary using cursor and subquery pl/sql

Question

I need to display employee's position, employee's name, and the average salary from 'sales' department, after they are being updated. The condition of update are...if the employee is also a manager, then increase salary by 30% (salary+(salary*30%)), but if the employee's job is a 'sales representative' then increase salary by 50%.

The example output: 
Manager John Russell 27817 
Manager Karen Partners27167 
Sales Jonathon Taylor 22517

This is my code so far

declare
  cursor dept_detail is 
      select employee_id,first_name,salary,job_id
        from employees
        where department_id=(select department_id from departments where department_name='Sales');

TYPE record_type IS RECORD (
        dept_id employees.employee_id%TYPE,
      dept_name employees.first_name%TYPE,
      dept_sal employees.salary%TYPE,
      dept_job employees.job_id%TYPE);
      
      dept_record record_type; 

begin
   OPEN dept_detail;
    LOOP
        FETCH dept_detail INTO dept_record;
        EXIT WHEN dept_detail%NOTFOUND;
    dbms_output.put_line (dept_record.dept_name||' '||dept_record.avg(dept_sal));
    if dept_record.dept_job=15 or dept_record.dept_job=16 then
    update employees set salary = salary+(salary*.50);
    elsif dept_record.dept_id=(select distinct(manager_id) from employees) then
    update employees set salary = salary+(salary*.30);
    end if;
  end loop;
end;
/

The problem is oracle does not allow a subquery in if condition, and i also cannot display average salary. And also, how do you give a condition to dbms_output.put_line for the 'manager' or 'sales' display

Bob Jarvis - Reinstate Monica Bob Jarvis - Reinstate Monica · Accepted Answer · 2020-10-15T02:24:41

I'd add "manager" and "salesman" flags to the cursor, and compute the departmental average salary in the cursor as well - then you can use those in your code. I'd also get rid of the stand-alone cursor and the data structure and just use an in-line cursor - easier to code and easier to understand:

begin
   FOR aRow IN (select e.employee_id,
                       e.first_name,
                       e.salary,
                       e.job_id,
                       CASE
                         WHEN sd.DEPARTMENT_ID IS NOT NULL THEN 'Y'
                         ELSE 'N'
                       END AS SALESMAN_FLAG,
                       CASE
                         WHEN m.MANAGER_ID IS NOT NULL THEN 'Y'
                         ELSE 'N'
                       END AS MANAGER_FLAG,
                       as.DEPARTMENT_NAME
                    from employees e
                    LEFT OUTER JOIN DEPARTMENTS sd
                      WHERE sd.DEPARTMENT_ID = e.DEPARTMENT_ID AND
                            sd.DEPARTMENT_NAME = 'Sales'
                    LEFT OUTER JOIN (SELECT DISTINCT MANAGER_ID
                                       FROM EMPLOYEES) m
                      WHERE m.MANAGER_ID = e.EMPLOYEE_ID
                    INNER JOIN (SELECT DEPARTMENT_ID,
                                       DEPARTMENT_NAME,
                                       AVG(SALARY) AS DEPT_AVG_SAL
                                  FROM EMPLOYEES
                                  GROUP BY DEPARTMENT_ID,
                                           DEPARTMENT_NAME)) as
                      ON as.DEPARTMENT_ID = e.DEPARTMENT_ID
   LOOP
     dbms_output.put_line (aRow.DEPARTMENT_NAME || ' ' ||
                           aRow.DEPT_AVG_SAL);

     if aRow.SALESMAN_FLAG = 'Y' THEN
       update employees
         set salary = salary * 1.50;
     elsif aRow.MANAGER_FLAG = 'Y' THEN
       update employees
         set salary = salary * 1.30;
     end if;
   end loop;
end;

In your original code you were using the employees first name as the department name - that didn't really look right to me so I grabbed the DEPARTMENT_NAME field from DEPARTMENT while computing the department average salary.

Update employee's salary using cursor and subquery pl/sql

1 Answers