SQL Query to DELETE Rows From the Employee Table and Raise Exception

Last Post

Posts: 17

Rahul Updhyay

Topic starter

Aug 23, 2020 1:03 pm

(@rahul)

Active Member

Joined: 3 years ago

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...

Remove-Employee-records.png

This topic was modified 2 years ago by KV Kumar

1 Reply

Posts: 18

jeevan 123

Jun 01, 2021 11:02 am

(@jeevan303500)

Active Member

Joined: 2 years ago

Let us consider the employee table for our scenario. It has five columns like EMPID, EmpName, Salary, and Dept

Employee:

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;

/