creating table exp_emp

------------------------------- creating table exp_emp

CREATE TABLE exp_emp(
    idd NUMBER(4) primary key,
    ename VARCHAR2(50),
    sal NUMBER(10),
    h_date DATE
);



------------------------------  add employees information

DECLARE
CURSOR emp_add IS
        SELECT *
        FROM employees;

        emp_rec emp_add%ROWTYPE;



BEGIN


OPEN emp_add;
FETCH emp_add INTO emp_rec;

WHILE emp_add%found
LOOP

INSERT INTO exp_emp
VALUES (emp_rec.employee_id,CONCAT(emp_rec.first_name,emp_rec.last_name),emp_rec.salary,ADD_MONTHS(emp_rec.hire_date,-300));



FETCH emp_add INTO emp_rec;
END LOOP;

CLOSE emp_add;



END;



------------------------------------------------------- show employees information

DECLARE
CURSOR emp_show IS
        SELECT *
        FROM exp_emp;

        emp_rec emp_show%ROWTYPE;



BEGIN


OPEN emp_show;
FETCH emp_show INTO emp_rec;

WHILE emp_show%found
LOOP


DBMS_OUTPUT.put_line('EMP ID: '||emp_rec.idd);
DBMS_OUTPUT.put_line('First Name: '||emp_rec.ename);
DBMS_OUTPUT.put_line('Salary : '||emp_rec.sal);
DBMS_OUTPUT.put_line('Length of service : '||ROUND(MONTHS_BETWEEN(SYSDATE,emp_rec.h_date)/12)||' Years');
DBMS_OUTPUT.put_line('===============================');



FETCH emp_show INTO emp_rec;
END LOOP;

CLOSE emp_show;



END;