Forum

SQL Query to DELETE...
 
Share:
Notifications
Clear all

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


Posts: 17
Topic starter
(@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...

1 Reply
Posts: 18
(@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;

/
Reply

Self Learning Video Tutorials - Software Course

Leave a reply

Author Name

Author Email

Title *

Preview 0 Revisions Saved
Share: