PL/SQL block to create user-defined exception 'exp_check'

Q. Write a PL/SQL block to create a user-defined exception titled 'exp_check'. Raise the user-defined exception 'exp_check' to achieve the following tasks.

i) Select the employee name (e_name ) and hire_date of all employees into a cursor.

ii) Calculate the experience of all the employees in years, and insert the e_name and experience of each employee into temp table.

Iii) Checkout If any employee has experience less than 2 years.

Answer:

Create a table 'emp':

CREATE TABLE emp ( enpno varchar(4), empname varchar(30), designation varchar2(10), category char(1), basicsalary number(4), joined date )


emp table

DECLARE exp_check exception;
CURSOR c1 IS
SELECT empname,
       joined
FROM emp;
BEGIN
FOR i IN c1 LOOP if(trunc(months_between(sysdate,i.joined)/12)<2) THEN RAISE exp_check;
ELSE
INSERT INTO tempp2
VALUES(i.empname,
       trunc(months_between(sysdate,i.joined)/12));
END IF;
END LOOP;
exception WHEN exp_check THEN dbms_output.put_line('experiance is less then 2 years not allowed');
WHEN others THEN dbms_output.put_line('un identified error occured');
END;
/


The above code will check if employee has a experience more than 2 years.

Output:

exp check exception
▲