Dear Tom,
we would like to ask you for your opinion about our idea regarding columns with dependent values in a database table definiton.
With the proliferation of PL/SQL we register a paradigma switch in relocation of "business logic" code from application in the database. In order to follow this trend, we propose the possibility of creating table columns defined by an SQL-query instead of storing physical data, similarly as known from views.
Consider the following example: We have two tables:
CREATE TABLE Customer (ID_Cust NUMBER(9), Name VARCHAR(20), Surname VARCHAR(20), Formatted_Name VARCHAR(41), Sum_Orders NUMBER(9));
CREATE TABLE Order (ID_Order NUMBER(9), ID_Cust NUMBER(9), Price NUMBER(6));
Now, we want Customer.Formatted_Name to be selectable as
"Name Surname" directly from the table without the need to concatenate the two columns later in the application. Here is our proposed DDL construct for laying off the functionality into the database:
ALTER TABLE Customer ADD DEPENDENT_VALUE Formatted_Name_DP
AS SELECT Name || ' ' || Surname
FROM THIS;
(With "THIS" we mean the table Customer and the corresponding row, for simplicity)
The next example is to have the sum of all money purchased by the customer in the table as a "shortcut":
ALTER TABLE Customer ADD DEPENDENT_VALUE Sum_Orders_DP
AS SELECT SUM(Price)
FROM Order, THIS
WHERE Order.ID_Cust = THIS.ID_Cust;
Of course it is possible to handle this problem with an additional view or triggers, but in our proposed solution
(1) there is no need for additional DB objects (a view)
(2) only the column definition, not the resulting data, must be stored - an advantage especially in the example with Formatted_Name
We would like to hear from you what do you think about this concept and whether it is worth to implement it in the DBMS (for example in 12g ;-). Thanks!
Peter, database analyst
Daniel, DBA
Well, for the first example - 11g does just that, here is a nifty example (showing that foreign keys work with this)
Starting in Oracle Database 11g Release 1 with the introduction of virtual columns ¿ we have the ability to define referential integrity based on functions of columns ¿ not just on real columns themselves.
SQL> create table data
2 ( the_key varchar2(10)
3 primary key,
4 status_code
5 as (substr( the_key, 5, 2 ))
6 references lookup
7 );
Table created.
SQL> insert into data (the_key) values ( '1234AA7890' );
1 row created.
SQL> insert into data (the_key) values ( '1234XX7890' );
insert into data (the_key) values ( '1234XX7890' )
*
ERROR at line 1:
ORA-02291: integrity constraint (OPS$TKYTE.SYS_C009730) violated - parent key not found
The virtual column is simply meta-data, it does not store the data redundantly, it does not require the trigger to maintain the value. And ¿ it can now be used to enforce the integrity constraint ¿ further increasing the times when declarative integrity may be used in your applications.
the other one - not so, you cannot do that on a table, you'll need a VIEW for that. Even if you wrote a user defined function, you would not be able to use it - since it is not deterministic (only deterministic functions can be used and yours definitely is not)
ops$tkyte%ORA11GR1> !oerr ora 54002
54002, 0000, "only pure functions can be specified in a virtual column expression"
// *Cause: Column expression contained a function whose evaluation is
// non-deterministic.
// *Action: Rewrite column expression to reference only pure functions.