Databases Reference
In-Depth Information
The following is a simple example that shows how to create a virtual column named
TOTAL_AMOUNT
, which
is calculated as the total value of a sale by taking the product of the
AMOUNT_SOLD
and
QUANTITY_SOLD
columns:
SQL> create table sales
2 (prod_id number(6) not null,
3 cust_id number not null,
4 time_id date not null,
5 channel_id char(1) not null,
6 quantity_sold number(3) not null,
7 amount_sold number(10,2) not null,
8* total_amount AS (quantity_sold * amount_sold))
SQL> /
Table created.
SQL>
This example used the shorter syntax for defining a virtual column. The full syntax for creating a
virtual column is as follows:
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
So, for example, you can use both the following types of syntax to generate a virtual column:
salary as (ROUND(salary*(1+commission/100),2))
salary NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+commission)/100),2))
The
GENERATED ALWAYS
clause means that the column value is generated at runtime based on the
values of the columns it is based on. A virtual column can also be derived from a constant instead of
table's columns. The column can include SQL or user-defined PL/SQL functions.
Once you create a virtual column, you can use it as any regular column. Virtual columns reduce the
need for using triggers. You can create an index on a virtual column just as you'd for a regular column.
The index you create will be a function-based index; in fact, you can add a virtual column and index it as
an alternative to creating a function-based index on one or more columns in a table.
SQL> create index test_virtual_indx1 on sales(total_amount);
Index created.
SQL>
SQL> select a.index_name,a.index_type,
2 b.column_expression
3 from user_indexes a
4 inner join user_ind_expressions b
5 on a.index_name=b.index_name
6* where a.index_name='TEST_VIRTUAL_INDX1'
SQL> /
INDEX_NAME INDEX_TYPE COLUMN_EXPRESSION
-------------------- -------------------- -------------------------------
TEST_VIRTUAL_INDX1 FUNCTION-BASED NORMAL "QUANTITY_SOLD"*"AMOUNT_SOLD"
SQL>
