Oracle 11g 虚拟列Word文件下载.docx

1、TABLE SUBPARTITONINDEX SUBPARTITIONLOB SUBPARTITIONCLUSTER） THEN6 RETURN 1;7 ELSE8 RETURN 0;9 END IF;10 END;11 /函数已创建。 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） GENER

2、ATED ALWAYS AS （LOWER（NAME） VIRTUAL, 7 TYPE VARCHAR2（30）, 8 V_TYPE AS （F_GETTYPE（TYPE）9 ）;表已创建。上面例子中，V_LENGTH、V_NAME和V_TYPE都是虚拟列，虚拟列的数值是通过真实列中的数据计算而来的。虚拟列的位置可以放在它参考的列的前面，也可以包括多个实际列的值，但是不能引用其他的虚拟列： 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 A

3、S （ID * 5）,*第 3 行出现错误:ORA-54012: 在列表达式中引用了虚拟列虚拟列的完整写法如上面例子中V_NAME列，包括列名、数据类型、GENERATED ALWAYS关键字、AS加列表达式和VIRTUAL关键字。其中GENERATED ALWAYS和VIRTUAL为可选关键字，主要用于描述虚拟列的特性，写与不写没有本质区别。而列的数据类型如果忽略，那么Oracle会根据AS后面的表达式最终结果的数据类型来确定虚拟列的数据类型。虚拟列可以使用Oracle自带的函数，也可以使用用户定义的函数，不过对于用户定义的函数要求必须声明函数的确定性： CREATE OR REPLACE

4、FUNCTION F_TEST RETURN NUMBER AS2 BEGIN3 RETURN 1;4 END;5 / CREATE TABLE T_VIRTUAL_COLUMN_DETER 2 （ID NUMBER, VID AS （F_TEST）;（ID NUMBER, VID AS （F_TEST）*第 2 行出现错误:ORA-54016: 指定了无效的列表达式虚拟列必须是对实际列进行操作后的结果，不能像上面这样直接写一个返回常数的函数，换句话说，表不能只包括虚拟列： CREATE OR REPLACE FUNCTION F_TEST（P_IN IN NUMBER） RETURN NUM

5、BER AS CREATE TABLE T_VIRTUAL_COLUMN_DETER3 ID NUMBER, 4 V_ID AS （F_TEST（ID）V_ID AS （F_TEST（ID）*第 4 行出现错误:ORA-30553: 函数不能确定现在错误信息显示，函数没有声明确定性： CREATE OR REPLACE FUNCTION F_TEST（P_IN IN NUMBER） RETURN NUMBER DETERMINISTIC ASOracle虽然在创建创建的时候会检查函数的确定性，在表建立之后，却可以将函数替换为非确定性函数： INSERT INTO T_VIRTUAL_COLUM

6、N_DETER （ID） VALUES （1）;已创建 1 行。 SELECT * FROM T_VIRTUAL_COLUMN_DETER;ID V_ID- -1 1 DROP FUNCTION F_TEST;函数已删除。SELECT * FROM T_VIRTUAL_COLUMN_DETER*第 1 行出现错误:ORA-00904: YANGTK.F_TEST: 标识符无效3 RETURN 2;1 2建立了虚拟列可以有效的减少数据的存储，简化查询语句中对列进行的处理，而且还可以利用虚拟列进行分区。不过虚拟列还会带来其他问题。首先包含了虚拟列的表在INSERT INTO语句中不能省略COLUM

7、N列表。由于虚拟列的值是由其他列的值计算得出的，且Oracle并不存储虚拟列的值，因此无论是INSERT还是UPDATE都不能对虚拟列进行修改： INSERT INTO T_VIRTUAL_COLUMN 2 SELECT ROWNUM ID, NULL, OWNER, OBJECT_NAME, NULL, OBJECT_TYPE, NULL 3 FROM DBA_OBJECTS;INSERT INTO T_VIRTUAL_COLUMNORA-00913: 值过多2 SELECT ROWNUM ID, NULL, OBJECT_NAME, NULL, OBJECT_TYPE, NULL ORA-

8、54013: 不允许对虚拟列执行 INSERT 操作 INSERT INTO T_VIRTUAL_COLUMN2 SELECT ROWNUM ID, OBJECT_NAME, OBJECT_TYPE ORA-00947: 没有足够的值 INSERT INTO T_VIRTUAL_COLUMN （ID, NAME, TYPE）已创建68587行。 COMMIT;提交完成。如果程序选择使用了一些工具来自动生成表的INSERT、UPDATE语句，那么遇到包含虚拟列的表就会报错。而且出于同样的原因，无法使用CREATE TABLE AS SELECT创建一个包含虚拟列的表。解决方法是CREATE TA

9、BLE AS SELECT结束后通过ALTER TABLE添加虚拟列。虚拟列还存在一个文件，当虚拟列的值一旦被实体化，那么虚拟列表达式发生变化会造成实体化结果与虚拟列不一致。简单的说就是虚拟列的结果是在查询的时候确定的，如果修改了虚拟列的表达式，下次执行查询时，虚拟列的值就会发生变化。但是一旦对虚拟列建立了索引，或者对包含虚拟列的表建立了物化视图，那么虚拟列的数值就被实际的存储下来，当虚拟列的表达式发生修改后，会导致索引或物化视图中已有的数据与目前虚拟列结果不一致。这个问题的解决方法只有删除索引并重建，或者将物化视图完全刷新。 CREATE INDEX IND_T_VIRTUAL_COLUMN

10、_VNAME ON T_VIRTUAL_COLUMN（V_NAME）;索引已创建。 ALTER TABLE T_VIRTUAL_COLUMN MODIFY V_NAME AS （UPPER（NAME）;ALTER TABLE T_VIRTUAL_COLUMN MODIFY V_NAME AS （UPPER（NAME）ORA-54022: 无法更改虚拟列表达式, 因为在列上定义了索引一旦建立了索引，Oracle会禁止虚拟列发生修改，但是前面提到了，Oracle并不禁止虚拟列参考的函数的修改： CREATE INDEX IND_T_VIRTUAL_COLUMN_VTYPE ON T_VIRTUAL

11、_COLUMN（V_TYPE）; SET AUTOT ON EXP SELECT COUNT（*） FROM T_VIRTUAL_COLUMN WHERE V_TYPE = 1;COUNT（*）-6914执行计划-Plan hash value: 4264298180-| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time | 0 | SELECT STATEMENT | | 1 | 42 | 53 （33）| 00:00:01 | 1 | SORT AGGREGATE | | 1 | 42 | | |* 2 | INDEX FAS

12、T FULL SCAN| IND_T_VIRTUAL_COLUMN_VTYPE | 34294 | 1406K| 53 （33）| 00:Predicate Information （identified by operation id）:-2 - filter（V_TYPE=1）, 6 RETURN -1; SELECT /*+ FULL（A） */ COUNT（*） FROM T_VIRTUAL_COLUMN A WHERE V_TYPE = 1; 3215935171-| 0 | SELECT STATEMENT | | 1 | 42 | 154 （12）| 00:02 |* 2 | T

13、ABLE ACCESS FULL| T_VIRTUAL_COLUMN | 34294 | 1406K| 154 （12）| 00: DROP INDEX IND_T_VIRTUAL_COLUMN_VTYPE;索引已删除。更正一下上个邮件的一些问题。如果是在OLTP的库，而且表列较多，或被用来进行HASH运算的列是long或lob类的列时，应放弃第一卦邮件中使用的hash校验，转而使用时间戳，并使用oracle的乐观锁定。例如原表为：create table dept（ deptno number（2）, dname varchar2（14）, loc varchar2（13）;加入校验列las

14、t_mod： loc varchar2（13）, last_mod timestamp with time zone default systimestamp not null, constraint dept_pk primary key（deptno） ）;插入数据：insert into dept （deptno, dname, loc） select deptno, dname, loc from scott.dept;rows mit;这是一个简单的实现，如果你想保护数据库表不出现丢失更新问题，应对每个要保护的表增加一列。这一列一般是NUMBER或DATE/TIMESTAMP列，通常

15、通过表上的一个行触发器来维护。每次修改行时，这个触发器要负责递增NUMBER列中的值，或者更新DATE/TIMESTAMP列。如果应用要实现乐观并发控制，只需要保存这个附加列的值，而不需要保存其他列的所有“前”映像。应用只需验证请求更新那一刻，数据库中这一列的值与最初读出的值是否匹配。如果两个值相等，就说明这一行未被更新过。以上代码会重建DEPT表，但是将有一个附加的LAST_MOD列，这个列使用TIMESTAMP WITH TIME ZONE数据类型（Oracle9i 及以上版本中才有这个数据类型）。我们将这个列定义为NOT NULL，以保证这个列必须填有数据，其默认值是当前的系统时间。这个

16、TIMESTAMP数据类型在Oracle中精度最高，通常可以精确到微秒（百万分之一秒）。如果应用要考虑到用户的思考时间，这种TIMESTAMP级的精度实在是绰绰有余，而且数据库获取一行后，人看到这一行，然后修改，再向数据库发回更新，一般不太可能在不到1秒钟的片刻时间内执行整个过程。两个人在同样短的时间内（不到1秒钟）读取和修改同一行的几率实在太小了。接下来，需要一种方法来维护这个值。我们有两种选择：可以由应用维护这一列，更新记录时将LAST_MOD列的值设置为SYSTIMESTAMP；也可以由触发器/存储过程来维护。如果让应用维护LAST_MOD，这比基于触发器的方法表现更好，因为触发器会代表

17、Oracle对修改增加额外的处理。不过这并不是说：无论什么情况，你都要依赖所有应用在表中经过修改的所有位置上一致地维护LAST_MOD。所以，如果要由各个应用负责维护这个字段，就需要一致地验证LAST_MOD列未被修改，并把LAST_MOD列设置为当前的SYSTIMESTAMP。例如，如果应用查询DEPTNO=10这一行：variable deptno numbervariable dname varchar2（14）variable loc varchar2（13）variable last_mod varchar2（50）begin :deptno := 10; select dname, loc,