CREATE OR REPLACE PACKAGE BODY Emp_pack
AS
FUNCTION NetSalary
(ID IN EMPLOYEES.EMPLOYEE_ID%TYPE)
RETURN NUMBER
IS
NetSal NUMBER(10,2);
BEGIN
SELECT SALARY + NVL(COMMISSION_PCT, 0) INTO NetSal
FROM EMPLOYEES WHERE EMPLOYEE_ID = ID;
RETURN(NetSal);
END NetSalary;
PROCEDURE Tax
(ID IN EMPLOYEES.EMPLOYEE_ID%TYPE, Tax OUT NUMBER)
IS
NetSal NUMBER(10,2);
BEGIN
NetSal := NetSalary(ID);
IF NetSal < 2000 THEN
Tax := NetSal * .02;
ELSIF NetSal <4000 THEN
Tax := NetSal * .04;
ELSE
Tax := NetSal * .1;
END IF;
END Tax;

PROCEDURE DeptSalary
(Deptname IN DEPARTMENTS.DEPARTMENT_NAME%TYPE,
Tolsal OUT NUMBER)
IS
Deptno EMPLOYEES.DEPARTMENT_ID%TYPE;
Salary EMPLOYEES.SALARY%TYPE;
Comm EMPLOYEES.COMMISSION_PCT%TYPE;
DepSalary NUMBER(10,2);
CURSOR cur_tr IS
SELECT DEPARTMENT_ID, SALARY, COMMISSION_PCT
FROM EMPLOYEES WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME = Deptname);
BEGIN
DepSalary := 0;
OPEN Cur_Tr;
LOOP
FETCH Cur_Tr INTO Deptno, Salary, Comm;
IF cur_tr%rowcount = 0 THEN
RAISE_APPLICATION_ERROR(-20020, 'THERE IS NO DATA');
END IF;
EXIT when cur_tr%notfound;
DepSalary := DepSalary + Salary + NVL(Comm, 0);
END LOOP;
CLOSE Cur_Tr;
Tolsal := DepSalary;
END DeptSalary;
END Emp_pack;