oracle11g 虚拟列,Oracle11新特性——虚拟列（二）

打算写一系列的文章介绍11g的新特性和变化。

Oracle11g增加了表的虚拟列，这个列的数据并没有存储在数据文件中，而是Oracle通过列数据的生成放到了数据字典中。

这篇介绍虚拟列的进一步的研究。

对于虚拟列又有一点新的研究。

虽然虚拟列不能参考其他的虚拟列，但是可以通过其他的方法来变相实现：

SQL> CREATE TABLE T_V_COL_P

2 (

3 ID NUMBER PRIMARY KEY,

4 NAME VARCHAR2(30),

5 V_NAME AS (LOWER(NAME)),

6 V_COL AS (LENGTH(V_NAME))

7 );

V_NAME AS (LOWER(NAME)),

*第5行出现错误:

ORA-54012:在列表达式中引用了虚拟列

由于虚拟列并不存储数据，而且数据的生成是在查询的时候，因此可以先建立表，然后将表的主键作为参数传递给虚拟列参考的函数：

SQL> CREATE OR REPLACE FUNCTION F_TEST_VIRTUAL (P_IN NUMBER) RETURN NUMBER DETERMINISTIC AS

2 BEGIN

3 FOR I IN (SELECT LENGTH(V_NAME) LEN FROM T_V_COL_P WHERE ID = P_IN) LOOP

4 RETURN I.LEN;

5 END LOOP;

6 END;

7 /

函数已创建。

SQL> INSERT INTO T_V_COL_P (ID, NAME) VALUES (1, 'TABLE');

已创建1行。

SQL> INSERT INTO T_V_COL_P (ID, NAME) VALUES (2, 'INDEX');

已创建1行。

SQL> SELECT * FROM T_V_COL_P;

ID NAME V_NAME V_COL

---------- ------------------------------ ------------------------------ ----------

1 TABLE table 5

2 INDEX index 5

通过这个变相的方法，就可以实现虚拟列参考其他的虚拟列。当前，由于虚拟列的数值本身就来自其他的实际列，因此虚拟列参考虚拟列的意义不大。

但是上面给出的方法还是很有意义的，可以利用这个方法实现很多的功能。

举个简单的例子，刚才建立的是主表，有一个子表引用主表：

SQL> CREATE TABLE T_V_COL_F

2 (

3 ID NUMBER,

4 FID NUMBER,

5 NAME VARCHAR2(30),

6 FOREIGN KEY (FID) REFERENCES T_V_COL_P

7 );

表已创建。

SQL> INSERT INTO T_V_COL_F SELECT 100000 + ROWNUM, 1, TABLE_NAME FROM DBA_TABLES;

已创建2493行。

SQL> INSERT INTO T_V_COL_F SELECT 200000 + ROWNUM, 2, INDEX_NAME FROM DBA_INDEXES;

已创建3945行。

如果想查询主表记录的同时查询参考当前主表ID的子表记录数：

SQL> SELECT ID, NAME, V_NAME, (SELECT COUNT(*) FROM T_V_COL_F WHERE FID = A.ID) NUM

2 FROM T_V_COL_P A;

ID NAME V_NAME NUM

---------- ------------------------------ ------------------------------ ----------

1 TABLE table 2493

2 INDEX index 3945

这是常规的写法，而使用虚拟列可以在一张表上实现这个功能：

SQL> CREATE OR REPLACE FUNCTION F_TEST_VIRTUAL (P_IN NUMBER) RETURN NUMBER DETERMINISTIC AS

2 BEGIN

3 FOR I IN (SELECT COUNT(*) NUM FROM T_V_COL_F WHERE FID = P_IN) LOOP

4 RETURN I.NUM;

5 END LOOP;

6 END;

7 /

函数已创建。

SQL> SELECT * FROM T_V_COL_P;

ID NAME V_NAME V_COL

---------- ------------------------------ ------------------------------ ----------

1 TABLE table 2493

2 INDEX index 3945

采用这种方法可以简化很多的问题，而且如果不访问虚拟列，并不会引发对子表的访问。

这种方法唯一需要注意一点，不要造成循环引用：

SQL> CREATE OR REPLACE FUNCTION F_TEST_VIRTUAL (P_IN NUMBER) RETURN NUMBER DETERMINISTIC AS

2 BEGIN

3 FOR I IN (SELECT V_COL FROM T_V_COL_P WHERE ID = P_IN) LOOP

4 RETURN I.V_COL;

5 END LOOP;

6 END;

7 /

函数已创建。

SQL> SELECT * FROM T_V_COL_P;

SELECT * FROM T_V_COL_P

*第1行出现错误:

ORA-00036:超过递归SQL级别的最大值50

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YANGTK.F_TEST_VIRTUAL", line 3

ORA-06512:在"YA