12视图.docx
《12视图.docx》由会员分享，可在线阅读，更多相关《12视图.docx（22页珍藏版）》请在冰豆网上搜索。
12视图
六、TOP-N
八、数据字典与动态性能视图
第一节什么是视图
一、创建视图：
创建视图的主法是：
CREATE[ORREPLACE]VIEW视图名AS查询语句。
例如，如下语句就创建了一个视图：
sid=41pid=17>createviewydsp_v1asselectnvl（spbh,'待定'）spbh,sum（nvl（jg,0））sumfromydspgroupbyspbh;
视图已创建。
视图的名字是YDSP_V1。
哪么，它有什么用呢？
很简单，如果以后你再想查看SPBH（商品编号）对JG（价格）列进行分组求和的结果，不必再使用“selectnvl（spbh,'待定'）spbh,sum（nvl（jg,0））sumfromydspgroupbyspbh;”，直接SELECT视图即可：
sid=41pid=17>select*fromydsp_v1;
SPBHSUM
--------------
10143500
待定0
1026000
2019800
2029800
这样比你每次输入分组查询简单一些。
我们可以将常用的比较复杂的查询操作像上面这样，建立为视图。
这样每次查询视图就可看到结果，比输入复杂查询语句更省事。
创建视图命令中的[ORREPLACE]的意义，REPLACE有替换的意思。
它的主要作用是修改视图的定义，也就是修改视图内的SQL语句。
比如说上面的YDSP_V2，我想为YDSP_V2中增加一个COUNT（*），命令如下：
createorreplaceviewydsp_v1asselectnvl（spbh,'待定'）spbh,sum（nvl（jg,0））,count（*）CTsumfromydspgroupbyspbh;
这个命令非常简单，我不再试了。
这条命令的意思是把YDSP_V1再创建一次。
用新的YDSP_V1替代老的YDSP_V1，也就是重建视图。
视图在创建后，它的结构、定义等信息不能修改，要想改变视图的定义信息，只能使用CREATEORREPLACE，用新的定义重建视图。
在创建视图时，对这种使用各种函数，或运算表达式的列，一定要起别名，如没有别名视图创建就会失败，例如：
sid=41pid=17>createviewydsp_v1asselectnvl（spbh,'待定'）,sum（nvl（jg,0））SUMfromydspgroupbyspbh;
createviewydsp_v1asselectnvl（spbh,'待定'）,sum（nvl（jg,0））SUMfromydspgroupbyspbh
*
第1行出现错误:
ORA-00998:
必须使用列别名命名此表达式
我把NVL（）函数后的别名去掉了，创建失败。
ORACLE报出的错误，就是说“我在这里缺少列的别名”。
为什么一定要为列起别名，因为一个创建好的视图，就像一个表一样，就像我们刚开始所举的例子，它有两个列：
SPBH和SUM。
用户可以通过selectspbh,sumfromydsp_v1来显示这两个列中的数据。
像nvl（spbh,'待定'）这样的表达式，如果你没有指定别名的话，ORACLE将无法确定在视图中，它的列名是什么。
因此，在创建视图时，对于这种复杂的表达式，一定要为它起别名。
当使用SELECT查询一个视图时，和查询表是一某一样的，我们就可以把视图当表一样去查询。
SELECT语句中，所有适用于表特性，都同样适用性视图。
比如我们可以增长条件：
sid=41pid=17>select*fromydsp_v1wherespbh<>'待定';
SPBHSUM
--------------
10143500
1026000
2019800
2029800
显示YDSP_V1视图中，SPBH（商品编号）列不等于“待定”的行。
在使用这个命令时，我们完全可以把YDSP_V1当作一个表，它有5行，两列，列名分别是SPBH和SUM。
视图数据的来源，被称为视图的“基表”。
视图的基表可以有多个。
如果我根据连接命令创建一个视图，那么这个视图中的数据可能来自于多个表，这样它就有了多个基表。
基表中的数据变化时，视图也会跟着发生变化。
例如，在YDSP表中我再插入一行：
sid=41pid=17>insertintoydspvalues（'020-0003','201','2008-06-05',9800,'测试中'）;
已创建1行。
sid=41pid=17>commit;
提交完成。
我又为201号商品增加了一行。
下面我再显示一下视图：
sid=41pid=17>select*fromydsp_v1;
SPBHSUM
--------------
10143500
待定0
1026000
20119600
2029800
201号商的销售总额已经是19600了。
也就是说，基表发生了什么变化，不需要任何操作，视图马上就可以反应出来。
这就是视图，我们先不深入的讨论它，仅从外观看，是看不出它和表有什么区别的。
在SELECT时，你根据看不出来你SELECT的是一个表，还是一个视图。
其实很多时候，我们都在SELECT视图，但是我们都以为在SELECT表。
我们还可以以视图为基表，再创建视图，比如上面的例子，我想把显示结果中的SPBH（商品编号），换成商品的型号，这需要和SP表等值连接起来显示：
sid=41pid=17>selectxh,sumfromydsp_v1a,spbwherea.spbh=b.spbh;
XHSUM
-------------------------
至尊型43500
平民型6000
商务型19600
个人型9800
连接的命令还是稍有点长的，下面我根据此命令，再创建一个视图：
sid=41pid=17>createviewydsp_sp_v1asselectxh,sumfromydsp_v1a,spbwherea.spbh=b.spbh;
视图已创建。
视图的名字是YDSP_SP_V1，名字是根据需要随变定的，如果觉得我这个复杂，可以换成简单的，主要是我想当我看到这个名字时，就能知道这个视图涉及两个表YDSP和SP。
好了，下面我们显示一下这个视图，它的输出结果，将和上面的那条连接SELECT语句一样：
sid=41pid=17>select*fromydsp_sp_v1;
XHSUM
-------------------------
至尊型43500
平民型6000
商务型19600
个人型9800
这样，以后无论哪种型号的商又被定购，如果我想查看种型号的定购总额。
直接查看这个视图就行了。
这个视图的基表有SP表和YDSP_V1视图，而YDSP_V1的基表是YDSP表。
也就是说一个视图可以作为其它视图的基表。
再强调一下，在进行SELECT操作时，我们可以把视图当作表一样去用，我们干脆暂时称视图为视图表。
我们可以认为视图表的数据，来自于基表。
那么，既然视图是表了，我基于这个表再创建其它视图当然是没有问题的。
二、视图的作用
最明显的作用，就是我们上面所验示的那样，是为了简化查询操作。
一个视图，可能代表一串非常复杂的查询操作。
还有，视图可以限制数据的访问，例如：
sid=41pid=17>createviewydsp_v2asselectgkbh,spbh,jgfromydsp;
视图已创建。
我选择YDSP（已定商品）表中的三列GKBH、SPBH和JG来创建视图。
视图创建后，我只告诉用户，如果想要查看已定商品的信息，就查看YDSP_V2。
不告诉他还有一个YDSP表。
这样，用户只能通过YDSP_V2看到YDSP的GKBH、SPBH和JG这三个列。
为了更保险，以后我们还可以通过改变数据库权限，让用户只能查看到YDSP_V2视图，而无法查看YDSP表。
这样，可以进一步达到隐藏一些数据的目的。
三、视图的本来面目
我刚才一直强调，我们可以把视图当作一种表。
在ORACLE中，称视图为逻辑表。
逻辑二字还是说明了视图不是真正的表。
因为视图内根本就没有数据。
视图的全部家当，也就是你创建视图时的SELECT语句。
没错，视图中只保存这条SELECT语句。
比如YDSP_V2视图，它的创建时SELECT语句是“selectgkbh,spbh,jgfromydsp”，YDSP_V2视图中，只保存这条语句。
当你查询YDSP_V2中的数据时，比如用户发出
Select*fromydsp_v2wherejg>=10000后：
第一步ORACLE会如上图这样，先把YDSP_V2视图展开。
展开的结果，如上图，创建视图的SQL语句，被融进了用户所发出的语句。
原语句是“Select*fromydsp_v2wherejg>=10000”，FROM后的视图YDSP_V2，被替换为了创建视图的SQL语句，整体语句变成：
Select*from（Selectgkbh,spbh,jgfromydsp）wherejg>=10000。
FROM后原本是表名，现在被换成了子查询。
这个子查询就是视图。
最后，一步，ORACE执行最终的转化过的SQL语句：
Select*from（Selectgkbh,spbh,jgfromydsp）wherejg>=10000。
这时，这条语句中，已经没有视图了，只有一个子查询。
如果还有视图，再进行一次转换，直到没有视图。
根据情况，ORACLE有时还会把子查询外的条件挪动子查询中。
ORACLE内部有一个段程序，叫作语句优化器，专门负责这个工作。
这条语句，最终的转换、优化后，将变成这样：
Selectgkbh,spbh,jgfromydspwherejg>=10000。
这就是视图的执行过程。
视图中并没有数据，它只是一条SQL语句，ORACLE会把它看作一条子查询。
在执行时，ORACLE会将视图的SQL语句，和你查询视图的语句进行融合。
根据融合难易程序的不同，ORACLE把视图分为两类：
简单视图和复杂视图。
下面我们来看一下视图的分类：
四、视图的分类
ORACLE把视图分为两类，简单视图与复杂视图，如下表：
特性
简单视图
复杂视图
表的数目
一个
多个
是否包含函数
否
是
是否分组
否
是
我们上面的例子中的YDSP_V2就是一个简单视图，而YDSP_SP_V1肯定就是一个复杂视图了。
通常，我们可以对简单视图使用DML命令，而复杂视图则不能使用DML命令，只能查询。
不过从9i开始，对于基于多表的连接创建的视图，虽然也是复杂视图，但是也可以对它进行DML操作了。
下面我们来看一下对视图进行DML时的情况。
第二节视图与DML
一、视图与DML
视图只是一条SQL语句，在查询视图时，ORACLE会把查询语句与视图内的SQL语句互相融合。
如果是对执行DML操作，ORACLE照样会将视图的SQL语句与DML命令相融合，例如，我们更新视图YDSP_2：
sid=41pid=17>updateydsp_v2setjg=jg+100wherespbh='202';
已更新1行。
这条语句的执行步骤和上面的查询差不多，ORACLE先将视图YDSP_V2换为它的SQL：
update（Selectgkbh,spbh,jgfromydsp）setjg=jg+100wherespbh='202';
对于这样的DML语句，不应该陌生，在第七讲“操纵数据”中我们讲过。
也就是说，对视图的更新，ORACLE会将它转化为其基表的更新。
因为视图中又没有数据，数据都在基表中，更新视图，最终还是要更新基表。
在对视图进行DML时，ORACLE要把你的DML转化成对基表的DML。
如果转化操作ORACLE无法完成，对视图的DML就会失败。
例如：
我实验一个包含GROUPBY、组函数的视图。
视图的创建SQL语句如下：
createorreplaceviewydsp_v1asselectnvl（spbh,'待定'）spbh,sum（nvl（jg,0））sumfromydspgroupbyspbh;
查询它的结果如下：
sid=41pid=17>select*fromydsp_v1;
SPBHSUM
--------------
10143500
待定0
1026000
20119600
2029800
如上图，左边是基表，右边是上面例子中的视图。
视图中的一行，都是由基表中的若干行合并而成的，
如果我向视图中插入一行的话：
比如上图，我向视图中插入一个新行，那么这个新行，应该对应基表的中多少行呢？
或者说，此视图中的新行应该是由基表中的多少行合并而成的呢？
ORACLE无法确认此点，因此，插入操作是不可能成功的。
因此，在我们对视图作DML时，要考虑一下ORACLE是否能把此DML转化成针对基表的DML。
如果不行的话，对视图的DML操作就会失败。
比如：
sid=41pid=17>insertintoydsp_v1values（'203',10000）;
insertintoydsp_v1values（'203',10000）
*
第1行出现错误:
ORA-01733:
此处不允许虚拟列
虚拟列，就是SUM（JG）列。
它是由计算而得来的，不是原来基表中的列。
对于上面例子中的视图，不但不能进行插入，其他的操作都是不可以的。
凡是这样带有要把基表中多行合为一行的视图，改变视图中的一行，ORACLE都无法确定这一行对应基表中几行，这样的视图，都无法进行任何DML操作。
会对多行进行合并的，也就只有分组、组函数和取消重复行DISTINCT选项。
DISTINCT是取消重复行，其实就是将多个重复的行，合为一个，在视图中的一行，也有可能对应基表的中的若干行。
除了这种情况之外，其他类型的视图，至少都可以进行某种DML操作。
例如，当非空列不在视图中时，不能向视图插入行。
但可以更新或删除。
这一点是为什么？
考虑一下！
其实非常简单，如果表有A、B、C三列，其中C列有非空约束。
视图甲包含表的A、B两列。
向视图中插入行，其实就是向表插入行，但只对表的A、B两列赋值，因为C列不包含在视图中，因此向视图中插入行，不会对表C的赋值。
而C列是非空的。
这就会引起违返非空约束的错误。
不过此进对视图的更新和删除是可以的。
通过这一点，又为我们提个醒，把我上面说过的话，进一步完善一下：
“在我们对视图作DML时，要考虑一下ORACLE是否能把此DML转化成针对基表的DML。
如果不行的话，对视图的DML操作就会失败”。
再加一句，“还要考虑对视图的DML在转化为对基表的DML后，是否会违反基表的约束，如果有违反，对视图的DML同样会失败”。
对什么样的视图可以进行什么样的DML，是很灵活的，我们掌握总的规则就行，这总的规则就是我们刚才所说的：
“在我们对视图作DML时，要考虑一下ORACLE是否能把此DML转化成针对基表的DML。
如果不行的话，对视图的DML操作就会失败。
还要考虑对视图的DML在转化为对基表的DML后，是否会违反基表的约束，如果有违反，对视图的DML同样会失败”
最后还有一点，就是ORACLE中有两点硬性规则，视图中如果包含这两点，在对视图进行DML时，也会有所限制。
所谓硬性规则，也就是ORACLE的中规定，不需要原因。
这两点硬性规定是：
1．当视图中含有ROWNUM时，视图不能进行任何的DML。
有关这个ROWNUM我们马上就要说到了。
2．视图中含有运算表达式时，对运算表达式的列，不能更新。
不能插入新行，但可以删除行，例如：
如下创建视图：
sid=41pid=17>createorreplaceviewydsp_v3asselectspbh,jg+10JGfromydsp;
视图已创建。
可以如下删除行：
sid=41pid=17>deleteydsp_v3;
已删除8行。
删除后，基表中的数据都没有了：
sid=41pid=17>select*fromydsp;
未选定行
sid=41pid=17>rollback;
回退已完成。
不能插入新行：
sid=41pid=17>insertintoydsp_v3values（'203',NULL）;
insertintoydsp_v3values（'203',NULL）
*
第1行出现错误:
ORA-01733:
此处不允许虚拟列
不能更新带有运算表达式的表，也叫虚拟列。
sid=41pid=17>updateydsp_v3setjg=1000wherespbh='101';
updateydsp_v3setjg=1000wherespbh='101'
*
第1行出现错误:
ORA-01733:
此处不允许虚拟列
但可以更新其他不包含运算表达式的列：
sid=41pid=17>updateydsp_v3setspbh='100'wherespbh='101';
已更新3行。
sid=41pid=17>rollback;
回退已完成。
二、只读视图
有些视图并不希望用户通过它更改基表中的行，可以把这些视图建为只读视图：
命令如下：
sid=41pid=17>createorreplaceviewydsp_v3asselectspbh,jgfromydspwithreadonly;
视图已创建。
在创建视图命令的最后，加上WITHREADONLY，就是将视图创建为只读视图。
这样，无论对视图进行什么DML操作，都不能成功：
先试一下更新：
sid=41pid=17>updateydsp_v3setjg=1000wherespbh='101';
updateydsp_v3setjg=1000wherespbh='101'
*
第1行出现错误:
ORA-01733:
此处不允许虚拟列
再试一下插入：
sid=41pid=17>insertintoydsp_v3values（'203',1000）;
insertintoydsp_v3values（'203',1000）
*
第1行出现错误:
ORA-01733:
此处不允许虚拟列
最后，删除：
sid=41pid=17>deleteydsp_v3;
deleteydsp_v3
*
第1行出现错误:
ORA-01752:
不能从没有一个键值保存表的视图中删除
三、WITHCHECKOPTION
还记得这个选项吗，在第七章有过讲述。
如果视图中有条件，此选项保证了你只能在视图的条件之内，对视图进行DML。
例如我创建如下视图：
sid=41pid=17>createorreplaceviewydsp_v3asselectspbh,jgfromydspwherejg>=10000withcheckoption;
视图已创建。
视图中的条件是JG>=10000，因此，我插入一个JG小于10000的行，将会报出错误：
sid=41pid=17>insertintoydsp_v3values（'203',1000）;
insertintoydsp_v3values（'203',1000）
*
第1行出现错误:
ORA-01402:
视图WITHCHECKOPTIDNwhere子句违规
而插入一个大于10000的行，可以成功插入：
sid=41pid=17>insertintoydsp_v3values（'203',100000）;
已创建1行。
其他的更新、删除我就不再试了，只要DML的结果满足JB大于等10000，DML就可以正常进行。
四、删除视图
没有用的视图可以删掉，命令非常简单：
sid=41pid=17>dropviewydsp_v3;
视图已删除。
ORACLE中DROP命令专门用来删除各种对象。
DROPTABLE是删除表。
DROPVIEW是删除视图，我们以回，还会用到其他的删除对象命令。
第三节TOP-N
在讲述TOP-N之前，我们先来描述两个相关的概念：
一、什么是内建视图
讲到这里再描述什么是内建视图就非常的简单了，内建视图就是子查询。
例如下面的语句中：
Select*from（Selectgkbh,spbh,jgfromydspwherejb>=10000）;
括号中的子查询就是内建视图。
二、ROWNUM伪列
内建视图很简单，但是ROWNUM就是一个复杂的问题了。
它很容易使人迷惑，很多人使用不好它。
它被称为伪列。
它也是表中的一列，它是一个序号，我们可以如下显示它：
sid=41pid=17>selectrownum,spbh,jgfromydsp;
ROWNUMSPBHJG
------------------------
110114500
210114500
310114500
42029800
51026000
62019800
7
82019800
9203100000
已选择9行。
但是，除非指明要显示它，否则它不会被显示出来。
因为它是伪列吗。
看上去它和列一模一样，但实际上，它并不存在。
既然它并不存在，它是怎么被显示出来的呢？
非常简单，就像上面这个例子：
显示出来第一行时，ORACLE为ROWNUM赋个值1
ROWNUM+NULLSPBHJG
-------------------------
110114500
显示第二行时，ROWNUM值为2：
ROWNUM+NULLSPBHJG
-------------------------
110114500
210114500
显示第三行时，ROWNUM值为3。
等等。
也就是说，ROWNUM是在输出行时，临时加上去的。
输出的第一行，ROWNUM值就是1。
输出的第二行，ROWNUM值就是2，等等，依次类推。
根据上面我们所讲的，可以推论出来，表中每一行的ROWNUM的值，将不固定。
例如，YDSP表中SPBH为‘202’的行，这样显示时，他排在第四行，因此，他的ROWNUM就是4：
sid=41pid=17>selectrownum,spbh,jgfromydsp;
ROWNUMSPBHJG
------------------------
110114500
210114500
310114500
42029800
51026000
62019800
7
820
升级会员 