The task is to get the maximum salary for employees holding the same position from a department. The position and department number are parameters of the function. here is the code

CREATE OR REPLACE FUNCTION getMaxSalary
(jobId in employees.job_id%TYPE,
 dep_id in employees.department_id%TYPE
)
RETURN NUMBER IS
  result NUMBER := 0;
BEGIN 
    SELECT MAX(salary)INTO result
    FROM employees
    WHERE job_id = jobId AND department_id = dep_id;
    IF result IS NULL THEN
      RAISE_APPLICATION_ERROR(-20201, 'This is not a valid department');
    END IF;
    RETURN result;
END;

ACCEPT x CHAR PROMPT 'Please enter Job ID:'
DECLARE
  jobId employees.job_id%TYPE;
  maxSalary NUMBER;
BEGIN
  jobId:= '&x';
  maxSalary:= getMaxSalary(jobId);
    DBMS_OUTPUT.PUT_LINE('The max salary for job id: '|| maxSalary);
END;

CodePudding user response：

ACCEPT works in SQL*Plus. You tagged the question with plsqldevelper tag which is related to Allround Automations GUI tool. What do you really use?

Anyway: try to add a slash to terminate PL/SQL block, here:

    ...
    RETURN result;
END;
/                            --> here

ACCEPT x CHAR PROMPT 'Please enter Job ID:'
DECLARE
...