Problem
Create a package, DEPT_PKG, with the following items included in your package. Note: Only ONE create package and ONE create package body should be submitted for this answer. Do not submit answers as you are working to complete the package, only once the package is complete and successfully compiles.
A public procedure called NEW_DEPT to enter a new department row into the DEPARTMENTS table. The procedure should accept four parameters - one for each column in the DEPARTMENTS table. Your block will use these parameter values in your INSERT command's VALUES clause.
I. A public function called GET_AVG_SAL to select the average salary of all employees for a particular department ID from the EMPLOYEES table. The function should accept the department ID as a parameter and return the average salary of all employees in that department. Round this value to 2 decimal places. Add exception handling which will display to the screen - for any error that occurs - the exact error number and message that happened. Be sure to test this exception.
II. A private function called GET_DEPARTMENT_COUNT to retrieve the total number of employees (in the EMPLOYEES table) assigned to a specific department. The function should accept the department ID as a parameter and return the number of employees from the EMPLOYEES table in that department. If there are no employees in the given department, return 0.
III. A public procedure called DELETE_DEPARTMENT. The function should accept the department ID as a parameter, then test to see if there are employees in the given department by calling GET_DEPARTMENT_COUNT- pass the given department ID as the parameter when you call it. If no employees exist for that department, then delete that row from the department table. In other words, before deleting the row, call the private function GET_DEPARTMENT_COUNT for the department. If there are no employees in the department, delete the row from the DEPARTMENTS table. If there ARE employees for the department, display a message that says the department cannot be deleted because there are employees assigned to that department.