PL/SQL procedure to categorize employees

Q. Create a table Employee and write a PL/SQL  procedure to categorize employees such as 'Junior', 'Senior', 'Worker'.

Answer:

Create Table Employee:

CREATE TABLE EMPLOYEE ( "Employee No " Varchar2 (4), "Employee Name" Varchar2 (30), Designation Varchar2 (10), Category Character (1), "Basic Salary " Number (4) )


Create table employee_gross:

CREATE TABLE employee_gross ( "Employee No " Varchar2 (4), "Gross Salary " Number (4) )


Create Procedure gross:

CREATE OR REPLACE PROCEDURE gross AS
CURSOR c1 IS
SELECT *
FROM employee;

emp_record employee%rowtype;
da number(20,2);
hra number(20,2);
gross number(20,2);

BEGIN
DELETE
FROM employee_gross;

FOR emp_record IN c1 LOOP da:=emp_record."Basic Salary "*35/100;

hra:=emp_record."Basic Salary "*15/100;

IF emp_record.Category='j'
AND hra>250 THEN hra:=250;

elsif emp_record.Category='s'
AND hra>1000 THEN hra:=1000;

elsif emp_record.Category='w'
AND hra>30000 THEN hra:=30000;

ELSE hra:=0;
END IF;
gross:=emp_record."Basic Salary "+da+hra;

INSERT INTO employee_gross
VALUES(emp_record."Employee No ", gross);

END LOOP;
END;
/


Output:

categorize employee
▲