Virtual columns are used, e.g. display calculated columns.
The big advantage that Virtual Columns offer is that they can not be physically stored, indexed, and constraints can be created on a Virtual Column.
An application would be e.g. the salary of an employee. For example, the employee receives a monthly salary of €2,000 and monthly grants worth €200.
Now we have the possibility to save 2200 € in one column. However, we can no longer understand how much he really deserves and how much € grant.
Another option would be to save the 2 values separately. In the query, one would have to add the values but each time again to get his actual salary + subsidies.
The 3rd possibility is to create 3 columns. One for the gross salary, one for the grants and one for the sum. But what if the salary changes? The total column would always have to be reworked manually and will lead to errors in the long run. That’s why there are virtual columns. These can dynamically calculate the value.
Virtual columns are added to a table as follows:
1 2 3 | Alter Table Employee Add Summ as (nvl(income),0) + nvl(grants,0)); |
These can now be easily queried by Select:
1 2 3 | Select Summ from Employee |
For an update, all columns except for the virtual columns must be specified! A shortened update statement is no longer possible.
