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.
