ORACLE11G vritual_column

SQL> spool virtual_columns.log

SQL> CREATE TABLE employees (
  2    id          NUMBER,
  3    first_name  VARCHAR2(10),
  4    last_name   VARCHAR2(10),
  5    salary      NUMBER(9,2),
  6    column1       NUMBER(3),
  7    column2       NUMBER(3),
  8    salary1     AS (ROUND(salary*(1+column1/100),2)),
  9    salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+column2/100),2)) VIRTUAL,
 10    CONSTRAINT employees_pk PRIMARY KEY (id)
 11    );
 
SQL> insert into employees(id,first_name,last_name,salary,column1,column2)
  2*    values(1,'zhang','li',1000,5,10);

1 row created.

SQL> INSERT INTO employees (id, first_name, last_name, salary, column1, column2)
  2  VALUES (2, 'hbjx', 'zl', 2000, 10, 20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from employees;

        ID FIRST_NAME LAST_NAME      SALARY    COLUMN1    COLUMN2    SALARY1    SALARY2
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
         1 zhang      li               1000          5         10       1050       1100
         2 hbjx       zl               2000         10         20       2200       2400

SQL> desc employees;
 Name                                                              Null?    Type
 ------------------------------------------------------- --------
 ID                                                                NOT NULL NUMBER
 FIRST_NAME                                                                 VARCHAR2(10)
 LAST_NAME                                                                  VARCHAR2(10)
 SALARY                                                                     NUMBER(9,2)
 COLUMN1                                                                    NUMBER(3)
 COLUMN2                                                                    NUMBER(3)
 SALARY1                                                                    NUMBER
 SALARY2                                                                    NUMBER

SQL> col column_name for a30
SQL> col data_type for a40
SQL> select column_name,data_type from user_tab_columns where table_name like 'EMPLOYEES';

COLUMN_NAME                    DATA_TYPE
------------------------------ ----------------------------------------
ID                             NUMBER
FIRST_NAME                     VARCHAR2
LAST_NAME                      VARCHAR2
SALARY                         NUMBER
COLUMN1                        NUMBER
COLUMN2                        NUMBER
SALARY1                        NUMBER
SALARY2                        NUMBER

8 rows selected.

SQL> col data_default for a40
SQL> l
  1* select column_name,data_type from user_tab_columns where table_name like 'EMPLOYEES'
SQL> c /type/default
  1* select column_name,data_default from user_tab_columns where table_name like 'EMPLOYEES'
SQL> /

COLUMN_NAME                    DATA_DEFAULT
------------------------------ ----------------------------------------
ID
FIRST_NAME
LAST_NAME
SALARY
COLUMN1
COLUMN2
SALARY1                        ROUND("SALARY"*(1+"COLUMN1"/100),2)
SALARY2                        ROUND("SALARY"*(1+"COLUMN2"/100),2)

8 rows selected.