Virtual Columns in Oracle Database 11g Release 1

Virtual Columns in Oracle Database 11g Release 1

/************ test ***************/

create table tb_test

(

sup number,

vat number,

amt as (sup + vat)

)

-- Add/modify columns 

alter table tb_test add ch1 varchar2(1);

alter table tb_test add ch2 varchar2(1);

alter table tb_test add ch12 varchar2(2) as (ch1||ch2)

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;

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>

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)
SALARY2                        ROUND("SALARY"*(1+"COMM2"/100),2)

8 rows selected.

SQL>

출처

https://oracle-base.com/articles/11g/virtual-columns-11gr1

http://blog.ngelmaum.org/entry/research-note-oracle-virtual-column