pension.sql

set serveroutput on;

create table employee(empid number primary key,
    empname varchar(30),
    join_date date,
    relieving_date date,
    salary number(10,2));

insert into employee values
    (1001,'Ramu',date '2000-01-21', date '2008-04-21',6000.00);
insert into employee values
    (1002,'Raju',date '2005-01-21', date '2018-04-21',8000.00);
insert into employee values
    (1003,'Ravi',date '2006-01-21', date '2022-04-21',10000.00);
insert into employee values
    (1004,'Rajesh',date '1992-01-21', date '2010-04-21',15000.00);
insert into employee values
    (1005,'Abhi',date '1998-01-21', date '2020-04-21',18000.00);

declare
    pension number(10,2);
    CURSOR services_ is 
        select FLOOR(months_between(relieving_date,join_date)/12) 
        as service_years, empid, salary,empname from employee;
begin
    dbms_output.put_line('>>>Service Details<<<');
    for row in services_ loop
            dbms_output.put_line(row.empname||
            ' ['||row.empid||'] had '||
            row.service_years||' years of service');
    end loop;

    dbms_output.put_line('>>>Pension Details<<<');
    for row in services_ loop
            pension := row.service_years * row.salary/100;
            dbms_output.put_line(row.empname||' ['||row.empid||
            '] can have a pension of '
            ||pension||' rupees');
    end loop;
end;
/

drop table employee;