数据库的复,view,index,sequence

 

ORACLE SQL DAY08

Top

1. 创建一张表account_90，表结构与account一致，没有数据
2. account_90表中包含所有的90后客户
3. 通过演示理解什么是视图
4. 每个客户选择了哪些资费标准
5. 视图的维护
6. 视图中的with check option约束
7. 视图中的with read only约束
8. 创建唯一性索引
9. 创建联合索引
10. 创建函数索引
11. 序列号的应用场景和实现

1 创建一张表account_90，表结构与account一致，没有数据

1.1 问题

创建一张与account表结构一样的表account_90，表中不包含数据。

1.2 方案

在create table中可以嵌套SELECT语句，即子查询。若子查询的返回记录数为0，新建的表就只有结构。1 = 2是永假式，任何表都不会返回记录。

 

1. where 1 = 2

1.3 实现

代码实现：

 

1. create tableaccount_90
2. as
3. select * from account
4. where 1 = 2 ;

隐藏

2 account_90表中包含所有的90后客户

2.1 问题

account_90表中包含所有的90后客户。

2.2 方案

在insert中可以嵌套SELECT语句，即子查询。出生日期是90后的客户通过子查询实现。

 

1. select * from account
2. whereto_char(birthdate,'yyyy') between 1990 and 1999;

2.3 实现

代码实现：

 

1. insert intoaccount_90
2. select * from account
3. whereto_char(birthdate,'yyyy') between 1990 and 1999;

隐藏

2.4 扩展

3 通过演示理解什么是视图

3.1 问题

理解什么是视图，通过实验证明之。

3.2 方案

表是数据库对象,视图是另一种。它们既有相似之处,也有很大区别。通过演示，帮助同学们理解什么是视图？

3.3 实现

创建一张表，插入记录，提交。

 

1. drop table testpurge;
2. create tabletest(
3. c1number,
4. c2number);
6. insert into testvalues (1,1);
7. insert into testvalues (1,2);
8. insert into testvalues (2,2);
9. commit;

用DDL语句创建一张视图，我们可以像操作表那样操作视图。

 

1. create or replace viewtest_v1
2. as
3. select * from test
4. wherec1 = 1;
6. desctest_v1
7. NameNull? Type
8. ----------------------------------------- -------- --------
9. C1 NUMBER
10. C2 NUMBER
12. select * fromtest_v1;
13. C1 C2
14. ---------- ----------
15. 1 1
16. 1 2

向视图test_v1中插入一条记录（1,3），查看test_v1和test中的变化

 

1. insert intotest_v1values (1,3);
2. select * fromtest_v1;
3. C1 C2
4. ---------- ----------
5. 1 1
6. 1 2
7. 1 3
8. select * fromtest;
9. SQL> select * fromtest;
11. C1 C2
12. ---------- ----------
13. 1 1
14. 1 2
15. 2 2
16. 1 3

向表test中插入一条记录（1,4），查看test_v1和test中的变化：

 

1. insert into testvalues (1,4);
2. select * fromtest_v1;
3. C1 C2
4. ---------- ----------
5. 1 1
6. 1 2
7. 1 3
8. 1 4
9. select * fromtest;
10. C1 C2
11. ---------- ----------
12. 1 1
13. 1 2
14. 2 2
15. 1 3
16. 1 4

向视图test_v1中插入一条记录（2,3），查看test_v1和test中的变化

 

1. insert intotest_v1values (2,3);
2. select * fromtest_v1;
3. C1 C2
4. ---------- ----------
5. 1 1
6. 1 2
7. 1 3
8. 1 4
9. select * fromtest;
10. C1 C2
11. ---------- ----------
12. 1 1
13. 1 2
14. 2 2
15. 1 3
16. 1 4
17. 2 3

通过上述演示，我们发现视图类似于windows中的快捷方式。结论：视图就是一条SELECT语句，不占用单独的存储空间，从视图中查询实际是执行视图对应的SELECT语句。

 

1. selectview_name,text fromuser_views
2. whereview_name = 'TEST_V1';
3. VIEW_NAME     TEXT
4. ---------- ---------------------------
5. TEST_V1     select "C1","C2" from test
6.     wherec1 = 1

3.4 扩展

4 每个客户选择了哪些资费标准

4.1 问题

创建一张视图，包含客户名称account表的real_name，unix服务器ip地址service表的unix_host, 资费编号cost表的id，资费信息描述cost表的descr。结果集中值包含开通了远程登录业务的客户。

图 - 1

4.2 方案

写创建视图语句的核心是写SELECT语句，视图中包含的列来自account表、service表、cost表，即三张表的连接操作。

 

1. from account a join service s
2. ona.id = s.account_id
3. join cost c
4. ons.cost_id = c.id;

每多一张表会多一个join on操作。

4.3 实现

代码实现：

 

1. create or replace viewaccount_cost_v
2. as
3. selecta.real_name,s.unix_host,c.id,c.descr
4. from account a join service s
5. ona.id = s.account_id
6. join cost c
7. ons.cost_id = c.id;

隐藏

4.4 扩展

所有的客户都在结果集中

图 - 2

代码实现如下:

 

1. create or replace viewaccount_cost_v
2. as
3. selecta.real_name,s.unix_host,c.id,c.descr
4. from account a left join service s
5. ona.id = s.account_id
6. left join cost c
7. ons.cost_id = c.id;
9. create or replace viewaccount_cost_v
10. selecta.real_name,t.unix_host,t.descr
11. from account a left join
12. (selects.account_id,s.unix_host,c.descr
13. from service s join cost c
14. ons.cost_id = c.id) t
15. ona.id = t.account_id
17. create or replace viewaccount_cost_v
18. as
19. selecta.real_name,s.unix_host,c.id,c.descr
20. from account a left join service s
21. ona.id = s.account_id
22. left join cost c
23. ons.cost_id = c.id;
25. create or replace viewaccount_cost_v
26. as
27. selectreal_name,c.name
28. from (select *
29. from account a left join service s
30. ona.id=s.account_id) aa left join cost c
31. onaa.cost_id =c.id

隐藏

5 视图的维护

5.1 问题

若将源表删除,基于源表的视图会发生怎样的变化?

5.2 方案

视图是一个依赖表的数据库对象，查询视图最终都要通过查询源表实现。如果源表的结构发生变化，对视图的操作就有可能出问题。查看视图的状态是帮助我们发现视图是否可用的方法。

5.3 实现

代码实现如下：

视图test_v1基于表test,此时它的状态为valid:

 

1. selectview_name,text fromuser_views
2. whereview_name = 'TEST_V1';
3. VIEW_NAME     TEXT
4. ---------- ---------------------------
5. TEST_V1     select "C1","C2" from test
6.     wherec1 = 1
8. columnobject_name format a15
9. columnobject_type format a10
10. selectobject_name,object_type,status fromuser_objects
11. whereobject_name = 'TEST_V1';
12. OBJECT_NAMEOBJECT_TYP STATUS
13. --------------- ---------- ------
14. TEST_V1 VIEW VALID

将test表删除,检查视图test_v1的状态:

 

1. drop table testpurge;
2. desctest_v1
3. ERROR:
4. ORA-24372: invalid object for describe
5. 提示：视图test_v1是无效的数据库对象
7. select * fromtest_v1;
8. ERROR at line 1:
9. ORA-04063: view "JSD1302.TEST_V1" has errors
10. 提示：视图test_v1有错误
12. selectobject_name,object_type,status fromuser_objects
13. whereobject_name = 'TEST_V1';
14. OBJECT_NAMEOBJECT_TYP STATUS
15. --------------- ---------- -------
16. TEST_V1 VIEW INVALID
17. 提示：test_v1的状态转变为无效INVALID。
19. alter viewtest_v1compile;
20. Warning: View altered with compilationerrors.
21. 提示：在不做任何修改的情况下，重新编译视图仍旧出错。
23. column text format a40
24. column name format a10
25. set linesize 200
26. select * fromuser_errors;
27. NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTEMESSAGE_NUMBER
28. ------- ------ -- -- -- ------------------------------------- ----- -
29. TEST_V1 VIEW 1 0 0 ORA-00942:table or view does not exist ERROR 0
30. 提示：编译出错的原因是test表不存在。
32. 创建test表以解决表不存在问题。
33. create tabletest(c1number,c2number);
34. selectobject_name,object_type,status fromuser_objects
35. whereobject_name = 'TEST_V1';
36. OBJECT_NAMEOBJECT_TYP STATUS
37. --------------- ---------- -------
38. TEST_V1 VIEW INVALID
39. select * fromtest_v1;
40. no rows selected
41. OBJECT_NAMEOBJECT_TYP STATUS
42. --------------- ---------- -------
43. TEST_V1 VIEW VALID
44. 原因:当drop源表,所有依赖源表的视图都将变为invalid。即视图本身存在，因为源表的丢失已经不能对视图正常操作了；当重新定义源表后，再次查询视图，系统先会对其编译：alterview_namecompile;若编译成功，可以正常操作视图。
45. 结论：对源表进行DDL操作后，需要检查数据库对象的状态是否有invalid，若有，需要改正。

5.4 扩展

6 视图中的with check option约束

6.1 问题

通过视图test_v1可以插入（2,3）,但从视图中不能查询到该记录，这样的情况不符合逻辑，怎样避免？

6.2 方案

可以对视图定义约束，with check option约束就是用来解决能对视图进行DML操作却不能SELECT的问题。

 

1. whereos_usernamelike 'h%'

6.3 实现

在创建视图时增加with check option约束，该约束要求通过视图插入的记录必须符合where条件。

 

1. create or replace viewtest_ck
2. as
3. select * from test
4. wherec1 = 1 with checkoption;
6. insert intotest_ckvalues (2,3);
7. ERROR at line 1:
8. ORA-01402: view WITH CHECK OPTIONwhere-clause violation

隐藏

6.4 扩展

7 视图中的with read only约束

7.1 问题

对于简单视图来说，如何实现不允许DML操作？

7.2 方案

在创建视图时增加with read only约束，该约束能够实现只读。

7.3 实现

在创建视图时增加with read only约束，该约束要求对视图只能查询，不能做DML操作。

 

1. create or replace viewtest_ro
2. as
3. select * from test
4. wherec1 = 1 with readonly;
6. insert intotest_rovalues (1,5);
7. ERROR at line 1:
8. ORA-01733: virtual column not allowed here

隐藏

7.4 扩展

8 创建唯一性索引

8.1 问题

为表中的列创建唯一性索引。

8.2 方案

oracle提供了一种索引形式是唯一性索引，语法是：

 

1. create unique index indname
2. ontabname (colname);

要求该列的取值必须唯一。

8.3 实现

代码实现:

 

1. create tabletest(
2. c1 number constrainttest_c1_pk primarykey);
3. insert into testvalues (1);
4. insert into testvalues (1);
5. ERROR at line 1:
6. ORA-00001: uniqueconstraint (JSD1302.TEST_C1_PK) violated
7. 第二条insert语句违反了唯一性约束
9. alter table test drop primarykey;
10. create unique indextest_c1_uniidx
11. ontest(c1);
12. insert into testvalues (1);
13. insert into testvalues (1);
14. ERROR at line 1:
15. ORA-00001: uniqueconstraint (JSD1302.TEST_C1_UNIIDX) violated
16. 注意：唯一性约束的名字是唯一性索引的名字
17. 结论：唯一性约束是通过唯一性索引实现的，二者是等价的。

隐藏

8.4 扩展

9 创建联合索引

9.1 问题

为表中的多列创建索引。

9.2 方案

oracle提供了一种索引形式是多列索引，在on关键字后的（）里可以跟多列。如果有两列经常在一起查询，适合建多列索引。

9.3 实现

代码实现

 

1. create tabletest(
2. c1 number constrainttest_c1_pk primarykey,
3. c2number,
4. c3number);
6. create indextest_c2_c3_idx
7. ontest(c2,c3);

隐藏

9.4 扩展

10 创建函数索引

10.1 问题

oracle为什么提供函数索引？怎样创建函数索引？

10.2 方案

若在c2列上创建普通索引，where round(c2) = 10是用不了该索引的，oracle仍然会用全表扫描的方式查询数据，要想提高查询效率，必须使用函数索引。

 

1. ontabname (funame(colname,..))

隐藏

10.3 实现

代码实现

 

1. create tabletest(
2. c1 number constrainttest_c1_pk primarykey,
3. c2number,
4. c3number);
6. create indextest_c2_funidx
7. ontest(round(c2));

隐藏

10.4 扩展

11 序列号的应用场景和实现

11.1 问题

oracle为什么提供sequence？怎样创建sequence？

11.2 方案

表中的PK/UK列要求取值一定要唯一，在程序执行尤其是并发执行时，怎样保证每次插入的值是唯一的。oracle提供了自己的解决方案：使用sequence。

 

1. create sequenceseq_name
2. [increment by 1|integer]
3. [start withinteger]
4. [maxvalueinteger|nomaxvalue]
5. [minvalueinteger|nominvalue]
6. [cycle|nocycle]
7. [cache 20|integer|nocache]

隐藏

11.3 实现

代码实现

 

1. drop table testpurge;
2. create tabletest(
3. c1 number constrainttest_c1_pk primarykey);
5. drop sequences_test_c1;
6. create sequences_test_c1
7. start with 1302001;
9. insert into testvalues (s_test_c1.nextval);
10. insert into testvalues (s_test_c1.nextval);
11. commit;
12. insert into testvalues (s_test_c1.nextval);
13. rollback;
14. insert into testvalues (s_test_c1.nextval);
16. select * fromtest;
17. C1
18. ----------
19. 1302001
20. 1302002
21. 1302004
22. selects_test_c1.currval fromdual;
23. CURRVAL
24. ----------
25. 1302004
27. selectsequence_name,cache_size,last_number
28. fromuser_sequences;
29. SEQUENCE_NAMECACHE_SIZELAST_NUMBER
30. ------------------------------ ---------- -----------
31. S_TEST_C1 20 1302021

隐藏

11.4 扩展