Oracle 11g之虚拟列

以下内容摘自OTN。

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

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.