Oracle SQL - computed column with function-CodePudding

Is it possible and is it good solution create computed column where use function to calculate value?

eg.

table_1

id   type  date_from    date_to       TOTAL_CURRENT_YEAR (computed column)
-------------------------------------------------------------------
1    A     01.01.2022   01.05.2022    if type = A call function to calculate TOTAL_CURRENT_YEAR  => Select f_calculate_A(date_from,nvl(check_out,sysdate)) from dual)
2    B     01.01.2022   01.05.2022    if type = B call function to calculate TOTAL_CURRENT_YEAR  => Select f_calculate_B(date_from,nvl(check_out,sysdate)) from dual)

How define TOTAL_CURRENT_YEAR (computed column)?

Alter table table_1 add  TOTAL_CURRENT_YEAR.........

CodePudding user response：

is it good solution create computed column where use function to calculate value?

If the function is deterministic (i.e. if you give it the same input values then it will ALWAYS return the same output) then you can use a virtual column.

Since SYSDATE is not a deterministic function, you cannot use it in a virtual column and if you try you will get the exception:

ORA-54002: only pure functions can be specified in a virtual column expression

If you were to move NVL(check_out, SYSDATE) from the column definition to inside your function then the function would not be deterministic (because it relies on SYSDATE and the return value now and the return value in 1 second time would be different).

Therefore, it is not a good solution to use a virtual column.

Note: there are hacks you can use such as wrapping a call to SYSDATE in a deterministic function to trick the compiler into accepting it; but that is a hack and may cause unexpected behaviour if the SQL engine caches the output from the function, which it expects to be deterministic and so could reasonably expect to be able to keep using a cached value, and then does reuse the cached value rather than recalculating from SYSDATE and then your clock will stick for a while and then jump forwards as the cache is updated.

Instead, you should create a view and compute the values in the view.

CodePudding user response：

You should check Oracle' s virtual column.

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

Because, you didn' t prepare recreatable data, I can only give link :).

Also, if you want to index virtual column, you can.