Create a Procedure that Deletes Employee Records from the Employee Table

Create a procedure that deletes employee records from the Employee table. Get the department name as an input parameter. Delete the employee records who belongs to that department.

Display the count of employee records that were deleted. If the respective department was not found, then raise "DeptNotFoundException" and print the message 'No Records found.'

Assume the Employee table has been already created and few records has been inserted.

Table: EMPLOYEE

COLUMN	DATATYPE	CONS.
EMPID	NUMBER(5)	PK
EMPNAME	VARCHAR2(25)	NOT NULL
SALARY	NUMBER(10,2)
DEPT	VARCHAR2(25)

EMP_ID	EMP_NAME	SALARY	DEPT
101	Tom	54000	MECH
102	William	43000	CSE
103	John	34560	MECH
104	Smith	56000	CSE
105	Steve	23450	IT

Functional Requirement: PROCEDURE DELETE_EMPLOYEE(v_dept IN EMPLOYEE.dept%TYPE)

Sample Output:

2 Employee record(s) got deleted.

(Hint: Data is case sensitive. Use '/' to terminate the PLSQL block)

here's a simple step-by-step explanation for the PL/SQL code:

Enable Output: SET SERVEROUTPUT ON; allows you to see output from the dbms_output.put_line calls.

Create Procedure: The CREATE OR REPLACE PROCEDURE DELETE_EMPLOYEE statement begins the creation of a procedure named DELETE_EMPLOYEE which takes v_dept as an input parameter.

Declare Exception: DeptNotFoundException is an exception that will be used to handle cases where no records are found.

Begin Block: The BEGIN block starts the procedure's main logic.

Delete Records: The DELETE FROM EMPLOYEE WHERE EMPLOYEE.DEPT = v_dept; statement deletes employee records where the department matches v_dept.

Check Deletion: The IF SQL%FOUND condition checks if any rows were deleted. If rows were deleted, it prints the number of deleted records.

Raise Exception: If no rows were deleted, the RAISE DeptNotFoundException; statement triggers the custom exception.

Exception Handling: The EXCEPTION block handles the DeptNotFoundException by printing "No Records found."

End Procedure: The END; statement marks the end of the procedure.

This procedure ensures that you get feedback on the operation, whether records were deleted or not.