oracle11g 虚拟列,Oracle11G新特性——虚拟列

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

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

看一个简单的虚拟列的例子：

SQL> CREATE OR REPLACE FUNCTION F_GETTYPE(P_TYPE IN VARCHAR2) RETURN NUMBER

2 DETERMINISTIC AS

3 BEGIN

4 IF P_TYPE IN ('TABLE', 'INDEX', 'LOB', 'TABLE PARTITION', 'INDEX PARTITION', 'LOB PARTITION',

5 'TABLE SUBPARTITON', 'INDEX SUBPARTITION', 'LOB SUBPARTITION', 'CLUSTER') THEN

6 RETURN 1;

7 ELSE

8 RETURN 0;

9 END IF;

10 END;

11 /

函数已创建。

SQL> CREATE TABLE T_VIRTUAL_COLUMN

2 (

3 ID NUMBER PRIMARY KEY,

4 V_LENGTH AS (CEIL(LENGTH(TO_CHAR(ID)) / 2) 1 LENGTH(NAME) LENGTH(TYPE)),

5 NAME VARCHAR2(30),

6 V_NAME CHAR(50) GENERATED ALWAYS AS (LOWER(NAME)) VIRTUAL,

7 TYPE VARCHAR2(30),

8 V_TYPE AS (F_GETTYPE(TYPE))

9 );

表已创建。

上面例子中，V_LENGTH、V_NAME和V_TYPE都是虚拟列，虚拟列的数值是通过真实列中的数据计算而来的。

虚拟列的位置可以放在它参考的列的前面，也可以包括多个实际列的值，但是不能引用其他的虚拟列：

SQL> CREATE TABLE T_VIRTUAL_COLUMN_ERR

2 (ID NUMBER,

3 V_ID1 AS (ID * 5),

4 V_ID2 AS (V_ID1 45)

5 );

V_ID1 AS (ID * 5),

*第3行出现错误:

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

虚拟列的完整写法如上面例子中V_NAME列，包括列名、数据类型、GENERATED ALWAYS关键字、AS加列表达式和VIRTUAL关键字。其中GENERATED ALWAYS和VIRTUAL为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。而列的数据类型如果忽略，那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型。

虚拟列可以使用Oracle自带的函数，也可以使用用户定义的函数，不过对于用户定义的函数要求必须声明函数的确定性：

SQL> CREATE OR REPLACE FUNCTION F_TEST RETURN NUMBER AS

2 BEGIN

3 RETURN 1;

4 END;

5 /

函数已创建。

SQL> CREATE TABLE T_VIRTUAL_COLUMN_DETER

2 (ID NUMBER, VID AS (F_TEST));

(ID NUMBER, VID AS (F_TEST))

*第2行出现错误:

ORA-54016:指定了无效的列表达式

虚拟列必须是对实际列进行操作后的结果，不能像上面这样直接写一个返回常数的函数，换句话说，表不能只包括虚拟列：

SQL> CREATE OR REPLACE FUNCTION F_TEST(P_IN IN NUMBER) RETURN NUMBER AS

2 BEGIN

3 RETURN 1;

4 END;

5 /

函数已创建。

SQL> CREATE TABLE T_VIRTUAL_COLUMN_DETER

2 (

3 ID NUMBER,

4 V_ID AS (F_TEST(ID))

5 );

V_ID AS (F_TEST(ID))

*第4行出现错误:

ORA-30553:函数不能确定

现在错误信息显示，函数没有声明确定性：

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

2 BEGIN

3 RETURN 1;

4 END;

5 /

函数已创建。

SQL> CREATE TABLE T_VIRTUAL_COLUMN_DETER

2 (

3 ID NUMBER,

4 V_ID AS (F_TEST(ID))

5 );

表已创建。

Oracle虽然在创建创建的时候会检查函数的确定性，在表建立之后，却可以将函数替换为非确定性函数：

SQL> INSERT INTO T_VIRTUAL_COLUMN_DETER (ID) VALUES (1);

已创建1行。

SQL> SELECT * FROM T_VIRTUAL_COLUMN_DETER;

ID V_ID

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

1 1

SQL> DROP FUNCTION F_TEST;

函数已删除。

SQL> SELECT * FROM T_VIRTUAL_COLUMN_DETER;

SELECT * FROM T_VIRTUAL_COLUMN_DETER

*第1行出现错误:

ORA-00904: "YANGTK"."F_TEST":

标识符无效