namesjj namesjj -3 years ago 213
SQL Question

PL/SQL procedure to compare 3 employees' salary, then swap the lowest and highest salary

I'm looking to have a procedure that takes in 3 employee_id's as parameters then compares them, the lowest and highest will be swapped while the middle one is left alone. I'm not sure if I'm approaching this correctly, I keep getting errors on oracle apex in the if else section. I've read maybe implementing a case statement could be easier?
--Note: this is a homework question
What I have so far:

create or replace procedure myproc(empid in number, empid in number, empid in number) is
originalsalary1 employees.salary%type;
originalsalary2 employees.salary%type;
originalsalary3 employees.salary%type;
newsalary employees.salary%type;
begin
select salary INTO originalsalary1 from employees
where employee_id = empid;
select salary INTO originalsalary2 from employees
where employee_id = empid;
select salary INTO originalsalary3 from employees
where employee_id = empid;
if originalsalary1 <= originalsalary2 and originalsalary3 then
newsalary := originalsalary1 * 1.1;
elseif originalsalary2 <= originalsalary1 and originalsalary3 then
newsalary := originalsalary2 * 1.1;
elseif originalsalary3 <= originalsalary1 and originalsalary2 then
newsalary := originalsalary3 * 1.1;
end if;
end;
/

Answer Source
CREATE OR REPLACE PROCEDURE PR_PREP(EMPID1 IN INT, EMPID2 IN INT , EMPID3 IN 
INT)
 IS
 MAX_SAL_EMP_ID INT;
 MIN_SAL_EMP_ID INT;
 MAX_SAL EMPLOYEES.SALARY%TYPE;
 MIN_SAL EMPLOYEES.SALARY%TYPE;
 BEGIN
   SELECT EMP_ID , SALARY INTO MAX_SAL_EMP_ID , MAX_SAL FROM EMPLOYEES WHERE 
    EMP_ID IN (EMPID1, EMPID2, EMPID3)
    AND SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES WHERE EMP_ID IN (EMPID1, 
   EMPID2, EMPID3));

   SELECT EMP_ID , SALARY INTO MIN_SAL_EMP_ID , MIN_SAL FROM EMPLOYEES WHERE 
    EMP_ID IN (EMPID1, EMPID2, EMPID3)
    AND SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES WHERE EMP_ID IN (EMPID1, 
    EMPID2, EMPID3));

    UPDATE EMPLOYEES SET SALARY = MAX_SAL WHERE EMP_ID = MIN_SAL_EMP_ID;
    UPDATE EMPLOYEES SET SALARY = MIN_SAL WHERE EMP_ID = MAX_SAL_EMP_ID;
    COMMIT;
   END;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download