PL/SQL procedure, %TYPE

Question

I have this task. I did it but e need to use specific ID (tmp_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 118;) or its giving me an error. How I can tell "check every ID".

Write a PL/SQL procedure to update the salary of an employee, provided as a parameter, by 5% if the salary exceeds the mid range of the salary against this job and update up to mid range if the salary is less than the mid range of the salary.

DECLARE
  emp_min_salary NUMBER(6,0);
  emp_max_salary NUMBER(6,0);
  emp_mid_salary NUMBER(6,2);
  tmp_salary     EMPLOYEES.SALARY%TYPE;
  tmp_emp_id     EMPLOYEES.EMPLOYEE_ID%TYPE := 118;
  tmp_emp_name   EMPLOYEES.FIRST_NAME%TYPE;
BEGIN

  SELECT min_salary,
         max_salary
  INTO emp_min_salary,
       emp_max_salary
  FROM JOBS
  WHERE JOB_ID = (SELECT JOB_ID
                 FROM EMPLOYEES
                 WHERE EMPLOYEE_ID = tmp_emp_id);
 
  -- calculate mid-range
  emp_mid_salary := (emp_min_salary + emp_max_salary) / 2;
  -- get salary of the given employee
  SELECT salary,first_name
  INTO tmp_salary,tmp_emp_name
  FROM employees
  WHERE employee_id = tmp_emp_id;
 
  -- update salary 
  
  IF tmp_salary < emp_mid_salary THEN
    UPDATE employees
    SET salary = emp_mid_salary
    WHERE employee_id = tmp_emp_id;
  ELSE
    UPDATE employees
    SET salary = salary + salary * 5 /100
    WHERE employee_id = tmp_emp_id;
  END IF;
   --display message
  IF tmp_salary > emp_mid_salary THEN
    DBMS_OUTPUT.PUT_LINE('The employee '||tmp_emp_name||' ID ' || TO_CHAR(tmp_emp_id) ||
                         ' works in salary ' || TO_CHAR(tmp_salary) ||
                         ' which is higher than mid-range of salary ' || TO_CHAR(emp_mid_salary));
  ELSIF tmp_salary < emp_mid_salary THEN
    DBMS_OUTPUT.PUT_LINE('The employee '||tmp_emp_name||' ID ' || TO_CHAR(tmp_emp_id) ||
                         ' works in salary ' || TO_CHAR(tmp_salary) ||
                         ' which is lower than mid-range of salary ' || TO_CHAR(emp_mid_salary));
 
  ELSE
    DBMS_OUTPUT.PUT_LINE('The employee '||tmp_emp_name||' ID ' || TO_CHAR(tmp_emp_id) ||
                         ' works in salary ' || TO_CHAR(tmp_salary) ||
                         ' which is equal to the mid-range of salary ' || TO_CHAR(emp_mid_salary));
  END IF;
END;
/

Answer 1

Personally I voted the solution of @littlefoot, since he answered properly about the original question of the NO_DATA_FOUND and also shrinked the code in 1 single merge statement as I would too.

I just want to add an alternate version in case you "need" to log salary changes and/or add more business logic for each specific case.

    DECLARE
      emp_mid_salary NUMBER(6,2);
      tmp_salary     EMPLOYEES.SALARY%TYPE;
      new_salary     EMPLOYEES.SALARY%TYPE;
      tmp_emp_id     EMPLOYEES.EMPLOYEE_ID%TYPE := 118;
      tmp_emp_name   EMPLOYEES.FIRST_NAME%TYPE;
    BEGIN
      --single query for extract all data
      SELECT (min_salary + max_salary) / 2, salary, first_name
      INTO emp_mid_salary, tmp_salary, tmp_emp_name
      FROM JOBS J
      JOIN EMPLOYEES E
        ON J.JOB_ID = E.JOB_ID
      WHERE E.EMPLOYEE_ID = tmp_emp_id
      ;
      --business logic
      IF tmp_salary > emp_mid_salary THEN
        --calcs
        new_salary := tmp_salary + tmp_salary * 5 /100;
        --output
        DBMS_OUTPUT.PUT_LINE('The employee '||tmp_emp_name||' ID ' || TO_CHAR(tmp_emp_id) ||
                             ' works in salary ' || TO_CHAR(tmp_salary) ||
                             ' which is higher than mid-range of salary ' || TO_CHAR(emp_mid_salary));
      ELSIF tmp_salary < emp_mid_salary THEN
        --calcs
        new_salary := emp_mid_salary;
        --output
        DBMS_OUTPUT.PUT_LINE('The employee '||tmp_emp_name||' ID ' || TO_CHAR(tmp_emp_id) ||
                             ' works in salary ' || TO_CHAR(tmp_salary) ||
                             ' which is lower than mid-range of salary ' || TO_CHAR(emp_mid_salary));
     
      ELSE
        --calcs
        new_salary := tmp_salary + tmp_salary * 5 /100;
        --output
        DBMS_OUTPUT.PUT_LINE('The employee '||tmp_emp_name||' ID ' || TO_CHAR(tmp_emp_id) ||
                             ' works in salary ' || TO_CHAR(tmp_salary) ||
                             ' which is equal to the mid-range of salary ' || TO_CHAR(emp_mid_salary));
      END IF;
      BEGIN
          --one single final update
          UPDATE employees
          SET salary = new_salary
          WHERE employee_id = tmp_emp_id;
      EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('Failed to update salary to employee with ID ' || TO_CHAR(tmp_emp_id));
            --handle/log exception
      END;
    EXCEPTION 
        WHEN NO_DATA_FOUND THEN 
            DBMS_OUTPUT.PUT_LINE('Could not find a JOB related to the employee with ID ' || TO_CHAR(tmp_emp_id));
        WHEN OTHERS THEN
            --handle/log exception, in the rare case something went wrong with the calcs?
    END;
    /

Answer 2

NO_DATA_FOUND is returned by one of SELECT statements you used; can't tell which one, I don't have your data.

However, that can be simplified. Here's an example which shows how you might do that.

Sample data is based on Scott's EMP table.

Mid-salaries (using the same algorithm you used):

SQL>   SELECT job, (MIN (sal) + MAX (sal)) / 2 midsal
  2      FROM emp
  3  GROUP BY job;

JOB           MIDSAL
--------- ----------
CLERK           1050      --> I'll be using CLERKS for demonstration
SALESMAN        1425
PRESIDENT       5000
MANAGER       2712,5
ANALYST         3000

Clerks:

SQL>   SELECT *
  2      FROM employees
  3     WHERE job = 'CLERK'
  4  ORDER BY ename;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7876 ADAMS      CLERK           1100
      7900 JAMES      CLERK            950
      7934 MILLER     CLERK           1300  --> salary is higher than mid-salary
      7369 SMITH      CLERK            800  --> salary is lower than mid-salary

Procedure: it uses MERGE as it does everything in the same step, no need for additional commands.

SQL> CREATE OR REPLACE PROCEDURE p_sal (par_empno IN employees.empno%TYPE)
  2  IS
  3  BEGIN
  4     MERGE INTO employees e
  5          USING (  SELECT job, (MIN (sal) + MAX (sal)) / 2 midsal
  6                     FROM emp
  7                 GROUP BY job) x
  8             ON (e.job = x.job)
  9     WHEN MATCHED
 10     THEN
 11        UPDATE SET
 12           e.sal =
 13              CASE WHEN e.sal > x.midsal THEN e.sal * 1.05 ELSE x.midsal END
 14                WHERE e.empno = par_empno;
 15  END;
 16  /

Procedure created.

Testing:

SQL> EXEC p_sal(7369);

PL/SQL procedure successfully completed.

SQL> EXEC p_sal(7934);

PL/SQL procedure successfully completed.

SQL>   SELECT *
  2      FROM employees
  3     WHERE job = 'CLERK'
  4  ORDER BY ename;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7876 ADAMS      CLERK           1100
      7900 JAMES      CLERK            950
      7934 MILLER     CLERK           1365  --> 5% raise
      7369 SMITH      CLERK           1050  --> set to mid-salary

SQL>