Im getting a couple of errors within my following function:
CREATE OR REPLACE FUNCTION department_salary
(emp_dept VARCHAR2, salary NUMBER)
RETURN NUMBER AS
dept_sal NUMBER := salary;
BEGIN
IF emp_dept = 'MIS' THEN dept_sal := (dept_sal * .10) + dept_sal;
ELSEIF emp_dept = 'SALES' THEN dept_sal := (dept_sal * .15) + dept_sal;
ELSEIF emp_dept = 'HR' THEN dept_sal := (dept_sal * .20) + dept_sal;
ELSE dept_sal := -1;
END IF;
RETURN dept_sal;
END;
/
The errors that I get are the following:
LINE/COL ERROR
-------- ----------------------------------------------------------------
7/10 PLS-00103: Encountered the symbol "EMP_DEPT" when expecting one
of the following:
:= . ( @ % ;
8/10 PLS-00103: Encountered the symbol "EMP_DEPT" when expecting one
of the following:
:= . ( @ % ;
14/4 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if
The problem is the syntax of the IF statement. In particular, the keyword is ELSIF not ELSEIF (no e). If you change that, the function will compile
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION department_salary
2 (emp_dept VARCHAR2, salary NUMBER)
3 RETURN NUMBER
4 AS
5 dept_sal NUMBER := salary;
6 BEGIN
7 IF emp_dept = 'MIS' THEN dept_sal := (dept_sal * .10) + dept_sal;
8 ELSIF emp_dept = 'SALES' THEN dept_sal := (dept_sal * .15) + dept_sal;
9 ELSIF emp_dept = 'HR' THEN dept_sal := (dept_sal * .20) + dept_sal;
10 ELSE dept_sal := -1;
11 END IF;
12 RETURN dept_sal;
13* END;
14 /
Function created.
If you are writing code like this, however, it is generally clearer to use a CASE statement
SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE FUNCTION department_salary
2 (emp_dept VARCHAR2, salary NUMBER)
3 RETURN NUMBER
4 AS
5 dept_sal NUMBER := salary;
6 BEGIN
7 CASE emp_dept
8 WHEN 'MIS' THEN dept_sal := (dept_sal * .10) + dept_sal;
9 WHEN 'SALES' THEN dept_sal := (dept_sal * .15) + dept_sal;
10 WHEN 'HR' THEN dept_sal := (dept_sal * .20) + dept_sal;
11 ELSE dept_sal := -1;
12 END CASE;
13 RETURN dept_sal;
14* END;
15 /
Function created.