Aug 23, 2020 1:03 pm
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
Jun 01, 2021 11:02 am
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; /