function based Virtual Column

Learn how to create a function based virtual column with example program.

Below is the function that calculate net salary of a employee.

CREATE OR REPLACE FUNCTION 
UDF_GET_EMPTOTAL_SALARY ( P_SALARY  NUMBER, P_BONUS  NUMBER)
   RETURN NUMBER
DETERMINISTIC
IS
BEGIN
   RETURN P_SALARY * 12 + P_BONUS;
END;

Below is the table that uses a function as expression for virtual column creation

CREATE TABLE EMPLOYEE_TBL
(
  EMP_ID    NUMBER,
  FIRST_NAME      VARCHAR2(50),
  LAST_NAME  VARCHAR2(50),
  SALARY    NUMBER(10,2),
  BONUS            NUMBER(10,2),
  TOTAL_SALARY    NUMBER(10,2) GENERATED ALWAYS AS 
                 (UDF_GET_EMPTOTAL_SALARY(SALARY,BONUS)) VIRTUAL
);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1348, 'VISWANATH', 'ANNANGI', 50000.00, 3500.00);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1349, 'VINITH', 'PAITHARI', 30000.00, 2500.00);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1350, 'RAVI', 'KANNEGUNDLA', 45000.00, 3500.00);

insert into EMPLOYEE_TBL
  (EMP_ID, FIRST_NAME, LAST_NAME, SALARY, BONUS)
values
  (1351, 'SUDHARMA', 'THUDUMULADENNA', 150000.00, 5000.00);

Note:
function is used as a part of table definition.
if function will be dropped then this will make the table inaccessible.

 DROP FUNCTION UDF_GET_EMPTOTAL_SALARY;

SELECT * FROM  EMPLOYEE_TBL;

Output:

ORA-00904: "[SCHEEMA_NAME]"."UDF_GET_EMPTOTAL_SALARY": invalid identifier

Again to access the table recreate the UDF_GET_EMPTOTAL_SALARY function.

Virtual column index creation

CREATE INDEX IDX_TOTAL_SALARY
ON EMPLOYEE_TBL(TOTAL_SALARY);

Virtual columns verification

SELECT column_name,
        data_type, 
        data_length, 
        data_default, 
        virtual_column
 FROM user_tab_cols
 WHERE table_name = 'EMPLOYEE_TBL';

Output

The value “YES” for the column “virtual_column” tells us that this is a virtual column. Another optional keyword “VIRTUAL” can also be added to make it syntactically complete.

index type verification on virtual columns

SELECT index_name, 
       index_type 
FROM user_indexes
WHERE table_name = 'EMPLOYEE_TBL';

Output

Adding a virtual column to the table

ALTER TABLE EMPLOYEE_TBL ADD (TOTAL_SALARY1 AS (SALARY * 12 + bonus));

you can create a new virtual column using ALTER-ADD statement

Modifying the virtual column

ALTER TABLE EMPLOYEE_TBL MODIFY (TOTAL_SALARY1 AS (UDF_GET_EMPTOTAL_SALARY(SALARY,BONUS)));

you can modiify the existing virtual colum based on ALTER-MODIFY statement.