Insert Employee details into Employee table

Create a PL/SQL Procedure to insert employee details into Employee table. Before inserting, check whether the employee age is eligible or not. Employee age should be 18 or greater. Values are passed as argument to the procedure. If age valid, insert employee record into table and print the message "Age valid - Record inserted", else print the message "Age invalid - Record not inserted" by raising an exception.

Table: EMPLOYEE

COLUMN	DATATYPE	CONS.
EMPID	NUMBER(5)	PK
EMPNAME	VARCHAR2(25)	NOT NULL
AGE	NUMBER(3)

 
Sample Input 1:
CHECK_AGE_ELIGIBILITY(103, 'Robert', 24);
              
Sample Output 1:
Age valid - Record inserted
              
Sample Input 2:
CHECK_AGE_ELIGIBILITY(104,'Riya', 4);
              
Sample Output 2:
Age invalid - Record not inserted
            

(Hint: Data is case sensitive. Use '/' to terminate the PLSQL block)

Let's break down the procedure step-by-step in a simple understandable way:

Procedure Declaration:

• The procedure is named check_age_eligibility.
• It accepts three input parameters: v_id, v_name, and v_age.
• These parameters correspond to the employee's ID, name, and age, respectively.

Parameter Types:

• The types of these parameters (employee.empid%type, employee.empname%type, employee.age%type) are derived from the employee table columns. This ensures the parameters match the data types of the respective table columns.

Exception Declaration:

• An exception named Not_ValidException is declared. This will be used to handle cases where the age is not valid (i.e., 18 or younger).

Begin Block:

• The main logic of the procedure starts here.

Age Check:

• The procedure checks if the input age (v_age) is greater than 18.
• If v_age is greater than 18, it proceeds to insert the employee details into the employee table.

Insert Statement:

• The INSERT INTO employee VALUES(v_id, v_name, v_age); statement adds the new employee record to the table.
• A message is displayed using DBMS_OUTPUT.PUT_LINE('Age valid - Record inserted'); indicating that the age is valid and the record has been inserted successfully.

Exception Handling:

• If the age is not greater than 18, the procedure raises the Not_ValidException. In the EXCEPTION block, when the Not_ValidException is caught, a message is displayed using DBMS_OUTPUT.PUT_LINE('Age invalid - Record not inserted'); indicating that the age is invalid and the record was not inserted.

End Block:

• The procedure ends here.

This procedure checks an employee's age before inserting their details into the employee table. If the age is greater than 18, the details are inserted, and a confirmation message is displayed. If the age is 18 or less, an exception is raised, and a message indicating the invalid age is displayed.