I new at using PL/SQL and I want the following: I have this table on Oracle SQLcl
create table Child (
id varchar not null,
name varchar not null,
gender varchar not null,
YearOfBirth number(4) not null,
YearsOfAge number(4) null,
CONSTRAINT Pk primary key (id)
);
And I want a PL/SQL (preferred anonymous) that update field of "YearsOfAge" by minusing 2020 from the "YearOfBirth" field. I could do that but my problem is that the table won't be updated until I insert the PL/SQL block again. So whenever I insert a new row, I have to insert my PL/SQL block again. I want to get the table updated whenever I insert/update a row, without a need to insert this block following a new row.
To be clearer, I just want to insert SL/SQL block one time after creating the table, then get the table's "YearsOfAge" updated whenever I insert/update/delete a row. So when I write "select * from Child;" I need to see the "YearsOfAge" with the new value that computed from subtracting 2020 from "YearOf Birth". My current PL/SQL is below:
begin
IF INSERTING THEN
update Child set YearsOfAge = 2020 - YearOfBirth;
ELSIF DELETEING THEN
update Child set YearsOfAge = 2020 - YearOfBirth;
ELSE
update Child set YearsOfAge = 2020 - YearOfBirth;
END IF;
END;
/
If you really need to store the age this way, some options are virtual columns, views, and triggers.
With a virtual column, Oracle will automatically perform the calculation on the fly.
SQL> create table Child
2 (
3 id number not null,
4 name varchar2(10) not null,
5 gender varchar2(10) not null,
6 YearOfBirth number(4) not null,
7 YearsOfAge number generated always as (2020 - yearOfBirth) null,
8 constraint pk_child primary key (id)
9 );
Table created.
SQL> insert into child(id, name, gender, yearOfBirth) values(1, 'A', 'female' , 1990);
1 row created.
SQL> insert into child(id, name, gender, yearOfBirth) values(2, 'B', 'male' , 2000);
1 row created.
SQL> insert into child(id, name, gender, yearOfBirth) values(3, 'C', 'non-binary', 2010);
1 row created.
SQL> select * from child;
ID NAME GENDER YEAROFBIRTH YEARSOFAGE
---------- ---------- ---------- ----------- ----------
1 A female 1990 30
2 B male 2000 20
3 C non-binary 2010 10
One downside of virtual columns is that they cannot use functions like SYSDATE, so the year has to be hard-coded. With a view, the expression can reference SYSDATE and will always be up-to-date:
create or replace view child_view as
select id, name, gender, yearOfBirth, extract(year from sysdate) - yearOfBirth yearsOfAge
from child;
You can also use a trigger to create the value when a row is inserted or updated:
create or replace trigger child_trg
before update or insert on child
for each row
begin
if updating('YEAROFBIRTH') or inserting then
:new.yearsOfAge := extract(year from sysdate) - :new.yearOfBirth;
end if;
end;
/
But in practice, triggers are a pain to maintain. Which leads to the question: why do you want to store this information in the first place?
Good database design should minimize the amount of redundant data. There are always exceptions, but you should have a good reason for those exceptions, like an especially complicated calculation that you don't want others to get wrong, you can't create a PL/SQL function because of an unusual security constraint, etc. Calculating something as trivial as the age may cause more problems than it solves.