oracle10g系统管理之视图

<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">建立简单视图</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">简单视图是基于单个表建立的，不包含任何函数、表达式和分组数据的试图。可以在简单视图上执行</span><span lang="EN-US"><span style="font-family: Times New Roman;">SELECT UPDATE DELETE INSERT</span></span><span style="">操作。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE VIEW emp_vu AS</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span lang="EN-US"><span style="font-family: Times New Roman;">SELECT empno,empname,sal,job,deptno FROM</span></span><span style="">　</span><span lang="EN-US"><span style="font-family: Times New Roman;">emp;</span></span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">2.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">建立视图并定义</span><span lang="EN-US"><span style="font-family: Times New Roman;">CHECK</span></span><span style="">约束</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE VIEW emp_vu10 AS </span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT * FROM emp WHERE DEPTNO=10</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">WITH CHECK OPTION CONSTRAINT chk_vu10;</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">3.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">建立只读视图</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE VIEW emp_vu10 AS </span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT * FROM emp WHERE DEPTNO=10</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">WITH READ ONLY;</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">4.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">建立复杂视图</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style="font-size: small;"><span style="">复杂视图是指包含函数，表达式或分组数据的视图，使用复杂视图的目的是为了简化查询操作。复杂视图上制定</span><span lang="EN-US"><span style="font-family: Times New Roman;">DML</span></span><span style="">操作必须符合特定条件。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE VIEW job_vu AS </span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT job,avg(sal),avgsal,sum(sal),sumsal,max(sal) maxsal,min(sal) minsal</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">FROM emp GROUP BY job;</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">5.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style=""><span style="font-size: small;">建立连接视图</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span style=""><span style="font-size: small;">连接视图是指基于多个表所建立的视图，使用连接视图的主要目的是为了简化连接查询。</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">CREATE VIEW dept_emp_vu AS</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">SELECT a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">FROM dept a,emp b</span></span></p>
<p class="MsoNormal" style="margin: 0cm 0cm 0pt 21pt;"><span lang="EN-US"><span style="font-size: small; font-family: Times New Roman;">WHERE a.deptno=b.deptno AND a.deptno=20;</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">6.</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">在视图上制定</span><span lang="EN-US"><span style="font-family: Times New Roman;">DML</span></span><span style="">操作的原则</span></span></p>
<p class="MsoNormal" style=""><span style="" lang="EN-US"><span style=""><span style="font-family: Times New Roman;"><span style="font-size: small;">1)</span><span style='font: 7pt "Times New Roman";'> </span></span></span></span><span style="font-size: small;"><span style="">如果试图包含</span><span lang="EN-US"><span style="font-family: Times New Roman;">GROUP BY</span></span><span style="">子句，分组函数和</span><span lang="EN-US"><span style="font-family: Times New Roman;">DISTINCT</span></span><span style="">关键字，则不能在该视图执行任何</span><span lang="EN-US"><span style="font-family: Times New Roman;">DML</span></span><span style="">操作。</span></span></p>
<p class="MsoNormal" style=""><span style="font-family: Times New Roman;"><span style="" lang="EN-US"><span style=""><span style="font-size: small;">2)</span><span style='font: 7pt "Times New Roman";'> </span></span></span><span lang="EN-US"><span style="font-size: small;">UPDATE</span></span></span><span style=""><span style="font-size: small;">操作原则：不能更新基于函数或表达式所定义的列，不能更新伪列</span></span></p>
<p class="MsoNormal" style=""><span style="font-family: Times New Roman;"><span style="" lang="EN-US"><span style=""><span style="font-size: small;">3)</span><span style='font: 7pt "Times New Roman";'> </span></span></span><span lang="EN-US"><span style="font-size: small;">INSERT</span></span></span><span style="font-size: small;"><span style="">操作原则：如果视图不包含表的</span><span lang="EN-US"><span style="font-family: Times New Roman;">NOT NULL</span></span><span style="">列，则不能通过视图增加数据。</span></span></p>