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.
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
WHERE table_name = 'EMPLOYEES';
COLUMN_NAME DATA_DEFAULT
------------------------------ --------------------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COMM1
COMM2
SALARY1 ROUND("SALARY"*(1+"COMM1"/100),2)