How do I write a pl/sql script that automatically deletes a record from the employee table if the employee age exceeds 60 years?

Question

How do I write a pl/sql script that automatically deletes a record from the employee table if the employee age exceeds 60 years?

Answer 1

From my point of view, if data model is correctly set, you can't do that - database won't allow it.

Why? Because employees aren't alone in the Universe; read: they don't exist only in the employee table. They were paid for their job, someone checked their attendance, they used certain equipment to do their job (and someone recorded it), etc.

All that means that the employee table is a master table, while all others (and probably many more) are its details. If everything is set as it should be, there are zillion foreign key constraints which would be violated if you tried to delete Scott once he celebrates his 60th birthday. So - nope, you wouldn't be able to delete his record from the employee table just like that.

---

Though, if we presume that it is some kind of a homework question, then: how is database supposed to know that Scott turned 60 just today? It isn't. You have to write a procedure which checks everyone's age and deletes the record if certain conditions are satisfied. For example:

create or replace procedure p_delete_60 is
begin
  delete from employee
    where months_between(trunc(sysdate), date_of_birth) / 12 >= 60;
end;

However, the way it is, you'd have to manually run that procedure every day. That's far from "automatic". So, why wouldn't you instruct the database to do it for you? How? Schedule a job. There are two packages you can use: DBMS_JOB and DBMS_SCHEDULE. For such a simple task, the first one would do:

declare
  l_job number;
begin
  dbms_job.submit(job       =>  l_job,
                  what      => 'p_delete_60;',
                  next_date =>  trunc(sysdate) + 1,
                  interval  => 'trunc(sysdate) + 1'
                 );
  commit;
end;
/

Check whether job exists:

SQL> select job, last_date, next_date, what from user_jobs;

       JOB LAST_DATE           NEXT_DATE           WHAT
---------- ------------------- ------------------- --------------------
       121                     04.09.2020 00:00:00 p_delete_60;

SQL>

Now, you just have to wait until midnight to see what happens. Maybe something (if someone turned 60 or - for the first procedure run - is already older than 60), maybe nothing (if everyone is younger than 60).