How to return the salary class for an employee given the employee’s EmployeeID value as an IN parameter

Question

Trying to classify each employee by class based on Salary. When calling the function I get no output.

Data: Class A: <= 10,000

Class B: > 10,000 and <= 20,000

Class C: > 20,000

CREATE OR REPLACE PROCEDURE DisplaySalary2(p_EmployeeID
IN CHAR) IS
v_Salary NUMBER(10,2);

BEGIN
SELECT Salary INTO v_Salary FROM Employee
WHERE EmployeeID = '88777';
IF v_Salary <= 10000 THEN
     DBMS_OUTPUT.PUT_LINE ('Class A');
 ELSIF v_Salary > 10000 AND v_Salary <= 20000 THEN
DBMS_OUTPUT.PUT_LINE ('Class B');
ELSE
DBMS_OUTPUT.PUT_LINE ('Class C');
END IF;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('Employee not found.'); 
END;
/


/*Calling DisplaySalary 2 */
DECLARE
v_SalaryOutput CHAR (10);
BEGIN
DisplaySalary2(v_SalaryOutput);
DBMS_OUTPUT.PUT_LINE(v_SalaryOutput);
END;

/

Answer 1

You have written a plsql procedure and say that you are calling a function. If you display everything inside the procedure, then it would not be required to use it as a function by returning values.

Also note the comments to understand how to use the parameter p_employeeId.You seem to have hard-coded the values.

CREATE OR REPLACE PROCEDURE DisplaySalary2(p_EmployeeID
IN CHAR) IS

v_Salary NUMBER(10,2);

BEGIN
SELECT Salary INTO v_Salary FROM Employees
WHERE EmployeeID = '88777'; -- You should use - WHERE EmployeeID = p_EmployeeID;
IF v_Salary <= 10000 THEN
     DBMS_OUTPUT.PUT_LINE ('Class A');
ELSIF v_Salary > 10000 AND v_Salary <= 20000 THEN
DBMS_OUTPUT.PUT_LINE ('Class B');
ELSE
DBMS_OUTPUT.PUT_LINE ('Class C');
END IF;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('Employee not found.'); 
END;
/

And while calling the procedure, since you don't have any OUT parameters, simply call it like this.

SET SERVEROUTPUT ON
DECLARE
BEGIN
DisplaySalary2('88777');
END;

/

If you want to convert the block to a plsql function , you must use create or replace function ..return returntype syntax explained in this link.

Create function