Virtual Columns in Oracle Database 11g

When queried, virtual columns appear to be normal table columns, but
their values are derived rather than being stored on disc. The syntax
for defining a virtual column is listed below.
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS
and VIRTUAL
keywords are provided for clarity only.
The script below creates and populates an employees table with two
levels of commission. It includes two virtual columns to display the
commission-based salary. The first uses the most abbreviated syntax
while the second uses the most verbose form.
CREATE TABLE employees (
id NUMBER,
first_name VARCHAR2(10),
last_name VARCHAR2(10),
salary NUMBER(9,2),
comm1 NUMBER(3),
comm2 NUMBER(3),
salary1 AS (ROUND(salary*(1+comm1/100),2)),
salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
CONSTRAINT employees_pk PRIMARY KEY (id)
);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (1, 'JOHN', 'DOE', 100, 5, 10);
INSERT INTO employees (id, first_name, last_name, salary, comm1, comm2)
VALUES (2, 'JAYNE', 'DOE', 200, 10, 20);
COMMIT;
Querying the table shows the inserted data plus the derived commission-based salaries.
SELECT * from employees;
ID FIRST_NAME LAST_NAME SALARY COMM1 COMM2 SALARY1 SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 JOHN DOE 100 5 10 105 110
2 JAYNE DOE 200 10 20 220 240
2 rows selected.
SQL>
The expression used to generate the virtual column is listed in the DATA_DEFAULT
column of the [DBA|ALL|USER]_TAB_COLUMNS
views.
COLUMN data_default FORMAT A50
SELECT column_name, data_default
from user_tab_columns