SQL Query to DELETE Rows From the Employee Table and Raise Exception
Remove Employee records - Procedures
Create a procedure that deletes rows from the Employee table. It should accept 1 parameter, department name; only delete the employee records belonging to that department. Display how many employees were deleted else raise "DeptNotFoundException" a...
Let us consider the employee table for our scenario. It has five columns like EMPID, EmpName, Salary, and Dept
Column_Name DataType Constraints EMPID NUMBER(10) PK EmpName VARCHAR2(30) NOT NULL Salary NUMBER(15,2) Dept VARCHAR2(30) EmpID EmpName Salary Dept 101 JOHN 54000 MECH 102 TOM 43000 CSE 103 WILLIAM 34560 MECH 104 STEVE 56000 CSE 105 SMITH 23450 IT
Now we can write a procedure as below in order to get the expected output.
set serveroutput on; create or replace procedure Delete_Emp (DeptName in EMPLOYEE.dept%type) is deptnotfound EXCEPTION; begin delete from EMPLOYEE where dept=DeptName; if (sql%found) then dbms_output.put_line (sql%rowcount || 'Employee record(s) got deleted.'); else raise DeptNoFound; end if; EXCEPTION when DeptNoFound then dbms_output.put_line('No Records Exists'); end delete_EMPLOYEE; /