sql – 引用另一个表的Oracle虚拟列

select * from TABLE1:
 COL_TAB1
|    1   |
|    2   |
|    3   |

另一个表TABLE2与COL_TAB2并引用第一列COL_TAB1

select * from TABLE2:
 COL_TAB2  |  COL_TAB1
| val1     |     1
| val2     |     2
| val2     |     3

是否可以在table1上添加虚拟列,以便获得与以下查询相同的结果：

select 
    t1.COL_TAB1,(select t2.COL_TAB2 from TABLE2 t2 where t2.COL_TAB1 = t1.COL_TAB1)
from TABLE1 t1

我试过了 ：

alter table TABLE1 add (
    SOME_COL_NAME as (select t2.COL_TAB2 from TABLE2 t2 where t2.COL_TAB1 = COL_TAB1)
)

但它给了我ORA-00936：缺少表情

解决方法

The expression used in the virtual column definition has the following
restrictions:

• It cannot refer to another virtual column by name.
• It can only refer to columns defined in the same table.
• If it refers to a
  deterministic user-defined function,it cannot be used as a
  partitioning key column.
• The output of the expression must be a
  scalar value. It cannot return an Oracle supplied datatype,a
  user-defined type,or LOB or LONG RAW.

正如@JoeStefanelli所说,做你想做的最好的选择是create a view.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!