Create a package, DEPT_PKG, with the following:

 

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. Use the parameter values in your INSERT command.A public procedure called UPD_DEPTMGR to update the manager for a specific department ID in the DEPARTMENTS table. The procedure should provide two parameters: the department  ID, and a new manager ID.  Add exception handling to account for an invalid department ID.

 

 

• A public function called GET_DEPARTMENT_COUNT to retrieve the total number of employees assigned to a specific department. The function should accept the department ID as a parameter and return the number of employees in that department. Add error handling to account for an invalid department ID.
• Write an anonymous block that invokes the procedure NEW_DEPT to add a new department to the DEPARTMENTS table with dept ID 88, department name of “IT”.  You may choose any value for location_id that is valid.  Add an exception handler that will display the current error message that raised the exception.

 

 

• Execute the UPD_DEPTMGR procedure and change the manager number of the new department you just added.  Query the DEPARTMENTS table to view your changes.

 

• Write an anonymous block which uses an explicit cursor to process all departments.  Retrieve the department name and call the GET_DEPT_COUNT function from your package to return the total count by department.  Simply call DBMS_OUTPUT.PUT_LINE and print the department name and number of employees for all departments.

 

 

• Write a SELECT statement to display all department information for all departments using a SELECT statement which also invokes the GET_DEPT_COUNT function to return the total employee count for each department.

 

 

Query the code from your package, DEPT_PKG, (both parts) from the data dictionary.Create a trigger which will always use the next value of the sequence, bb_prodid_seq, as the idproduct column when a new record is inserted into the BB_PRODUCT table.  Test  your trigger.