Oracle 11g之虚拟列


		Oracle 11g之虚拟列
 
Acme 的数据库包含了一个名为 SALES 的表，如前所示。该表的结构如下：
 
SALES_ID
NUMBER
CUST_ID
NUMBER
SALES_AMT
NUMBER
 
某些用户希望添加一个名为 SALE_CATEGORY 的列，以便根据销售量和当前客户来标识销售的类型：LOW、MEDIUM、HIGH 和 ULTRA。该列将帮助他们识别相应动作的记录，并将记录路由给相关人员以进行处理。以下是列值的逻辑：
 
如果 sale_amt 大于：
且 sale_amt 小于或等于：
则 sale_category 为
0
1000
LOW
10001
100000
MEDIUM
100001
1000000
HIGH
1000001
无限
ULTRA
 
尽管该列是重要的业务需求，但开发团队不希望更改代码来创建必要的逻辑。当然，您可以在表中添加一个名为 sale_category 的新列，然后编写一个触发器以使用上述逻辑填充该列 — 一个相当简单的操作。但是，由于与触发器代码的上下文切换，可能会导致性能问题。
在 Oracle Database 11g 中，您不需要编写任何触发器代码。您只需添加一个虚拟列。虚拟列为您提供了灵活性，可以添加传达商业意识的列，而不增加任何复杂性或性能影响。
以下是创建该表的方法：
 
SQL> create table sales
  2  (
  3    sales_id      number,
  4    cust_id       number,
  5    sales_amt     number,
  6    sale_category varchar2(6)
  7    generated always as
  8     (
  9       case
 10          when sales_amt <= 10000 then 'LOW'
 11          when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
 12          when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
 13          else 'ULTRA'
 14       end
 15     ) virtual
 16  );
 
注意 6-7 行；该列被指定为“generated always as”，这意味着，列值在运行时生成，而非作为表的一部分进行存储。该子句的后面是在详细的 CASE 语句中计算值的方法。最后，在第 15 行，指定了“virtual”以加强这是一个虚拟列的事实。现在，如果您插入一些记录：
 
SQL> insert into sales (sales_id, cust_id, sales_amt) values (1,1,100);
 
1 row created.
 
SQL> insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500);
 
1 row created.
 
SQL>insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from sales;
 
SALES_ID   CUST_ID SALES_AMT SALE_C
----------      ----------    ----------     ------
1          1        100       LOW
2          102      1500     LOW
3          102      100000   MEDIUM
 
3 rows selected.
 
虚拟列值都将照常填充。即使该列未存储，您也可以将其视为表的任何其他列，甚至可以在其上创建索引。
 
SQL> create index in_sales_cat on sales (sale_category);
 
Index created.
 
其结果将是一个基于函数的索引。
 
SQL> select index_type
  2 from user_indexes
  3 where index_name = 'IN_SALES_CAT';
 
INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL
 
SQL>  select column_expression
  2 from user_ind_expressions
  3 where index_name = 'IN_SALES_CAT';
 
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE  WHEN"SALES_AMT"<=10000 THEN 'LOW' WHEN ("SALES_AMT">10000AND "SALES_AMT"
<=100000) THEN CASE  WHEN"CUST_ID"<101 THEN 'LOW' WHEN ("CUST_ID">=101 AND"CUS
T_ID"<=200) THEN 'MEDIUM' ELSE 'MEDIUM' END  WHEN ("SALES_AMT">100000 AND"SALES
_AMT"<=1000000) THEN CASE  WHEN"CUST_ID"<101 THEN 'MEDIUM' WHEN ("CUST_ID">=101
 AND "CUST_ID"<=200) THEN'HIGH' ELSE 'ULTRA' END  ELSE 'ULTRA' END
 
您甚至可以在该列上分区，如本系列的分区一文中所述。但是，您不能为该列输入值。如果您尝试输入值，很快就会收到错误消息：
 
insert into sales values (5,100,300,'HIGH');
            *
ERROR at line 1:
ORA-54013: INSERToperation disallowed on virtual columns
 
----------------------------
Present  By  Dylan.