数据库的复,view,index,sequence-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_15469/article/details/82547556

ORACLE SQL DAY08

Top

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

1.1 问题

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

1.2 方案

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

where 1 = 2

1.3 实现

代码实现：

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

隐藏

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

2.1 问题

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

2.2 方案

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

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

2.3 实现

代码实现：

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

隐藏

2.4 扩展

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

3.1 问题

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

3.2 方案

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

3.3 实现

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

drop table testpurge;
create tabletest(
c1number,
c2number);
insert into testvalues (1,1);
insert into testvalues (1,2);
insert into testvalues (2,2);
commit;

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

create or replace viewtest_v1
as
select * from test
wherec1 = 1;
desctest_v1
NameNull? Type
----------------------------------------- -------- --------
C1 NUMBER
C2 NUMBER
select * fromtest_v1;
C1 C2
---------- ----------
1 1
1 2

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

insert intotest_v1values (1,3);
select * fromtest_v1;
C1 C2
---------- ----------
1 1
1 2
1 3
select * fromtest;
SQL> select * fromtest;
C1 C2
---------- ----------
1 1
1 2
2 2
1 3

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

insert into testvalues (1,4);
select * fromtest_v1;
C1 C2
---------- ----------
1 1
1 2
1 3
1 4
select * fromtest;
C1 C2
---------- ----------
1 1
1 2
2 2
1 3
1 4

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

insert intotest_v1values (2,3);
select * fromtest_v1;
C1 C2
---------- ----------
1 1
1 2
1 3
1 4
select * fromtest;
C1 C2
---------- ----------
1 1
1 2
2 2
1 3
1 4
2 3

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

selectview_name,text fromuser_views
whereview_name = 'TEST_V1';
VIEW_NAME TEXT
---------- ---------------------------
TEST_V1 select "C1","C2" from test
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表，即三张表的连接操作。

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

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

4.3 实现

代码实现：

create or replace viewaccount_cost_v
as
selecta.real_name,s.unix_host,c.id,c.descr
from account a join service s
ona.id = s.account_id
join cost c
ons.cost_id = c.id;

隐藏

4.4 扩展

所有的客户都在结果集中

图 - 2

代码实现如下:

create or replace viewaccount_cost_v
as
selecta.real_name,s.unix_host,c.id,c.descr
from account a left join service s
ona.id = s.account_id
left join cost c
ons.cost_id = c.id;
create or replace viewaccount_cost_v
selecta.real_name,t.unix_host,t.descr
from account a left join
(selects.account_id,s.unix_host,c.descr
from service s join cost c
ons.cost_id = c.id) t
ona.id = t.account_id
create or replace viewaccount_cost_v
as
selecta.real_name,s.unix_host,c.id,c.descr
from account a left join service s
ona.id = s.account_id
left join cost c
ons.cost_id = c.id;
create or replace viewaccount_cost_v
as
selectreal_name,c.name
from (select *
from account a left join service s
ona.id=s.account_id) aa left join cost c
onaa.cost_id =c.id

隐藏

5 视图的维护

5.1 问题

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

5.2 方案

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

5.3 实现

代码实现如下：

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

selectview_name,text fromuser_views
whereview_name = 'TEST_V1';
VIEW_NAME TEXT
---------- ---------------------------
TEST_V1 select "C1","C2" from test
wherec1 = 1
columnobject_name format a15
columnobject_type format a10
selectobject_name,object_type,status fromuser_objects
whereobject_name = 'TEST_V1';
OBJECT_NAMEOBJECT_TYP STATUS
--------------- ---------- ------
TEST_V1 VIEW VALID

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

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