Oracle11新特性——虛擬列（二）

打算寫一系列的文章介紹11g的新特性和變化。

Oracle11g增加了表的虛擬列，這個列的資料並沒有儲存在資料檔案中，而是Oracle透過列資料的生成放到了資料字典中。

這篇介紹虛擬列的進一步的研究。

Oracle11新特性——虛擬列：http://yangtingkun.itpub.net/post/468/409211

對於虛擬列又有一點新的研究。

雖然虛擬列不能參考其他的虛擬列，但是可以透過其他的方法來變相實現：

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