ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle11新特性——虚拟列(二)

Oracle11新特性——虚拟列(二)

原创 Linux操作系统 作者:yangtingkun 时间:2007-11-03 00:00:00 0 删除 编辑

打算写一系列的文章介绍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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69488/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10488479