Check if rows were selected PLSQL

Question

Well I have a procedure were I add two the salary of two employees. I get the salary with the id of the employee. If the employee was not found I should set the salary to 0. This is my code

    PROCEDURE private_salaries(
    sal_res OUT NUMBER,
    emp_1 NUMBER,
    emp_2 NUMBER)
IS
  sal_1 NUMBER;
  sal_2 NUMBER;
BEGIN
  SELECT salary INTO sal_1 FROM employees WHERE employee_id = emp_1;
  SELECT salary INTO sal_2 FROM employees WHERE employee_id = emp_2;
END;

Answer 1

The procedure can be simplified as below,

create or replace PROCEDURE private_salaries(
    emp_1 NUMBER,
    emp_2 NUMBER,
    sal_res out NUMBER)
is 
begin 

select sal1 + sal2 into sal_res
  from
  (
  select 
  nvl((SELECT salary  FROM employees WHERE employee_id = emp_1),0) sal1,
  nvl((SELECT salary FROM employees WHERE employee_id = emp_2),0) sal2
  from dual);

end;

Test Cases :

• When both of them exists - Returns sum of the salary
• When either one doesn't exists - Returns salary of one that exists
• When none of them exists - Returns zero.

Recommendation - Use function instead of procedure and you may use them in SQL,for procedure a PL/SQL block is required though.