oracle创建复杂的视图

来源：互联网发布：三国群英传7mac版资源编辑：程序博客网时间：2024/05/20 19:17

分组视图

例1：

CREATE OR REPLACE VIEW dept_tot as

select a.dname dept,sum(b.sal) total_sal from dept a,emp b

where a.deptno=b.deptno group by a.dname

例2：

CRATE OR REPLACE ITEMTOT AS

SELECT PERSION,SUM(AMOUNT) ITEMTOT

FROM LEDGER

WHERE ACTIONDATE BETWEEN

TO_DATE(‘01-MAR-1901’,’DD-MON-YYYY’) and

TO_DATE(‘31-MAR-1901’,’DD-MON-YYYY’)

And ACTION IN ( ‘BOUGHT’,’RAID’)

GROUP BY PERSION;

§7.2.2 合计视图

例：

CREATE VIEW empvi AS

SELECT DEPTNO,SUM(SAL),SUM(COMM)

FROM EMP GROUP BY DEPTNO;

§7.2.3 组合视图

例：

CREATE OR REPLACE VIEW BYITEM AS

SELECT L.persion persion.item,

Amount,

100*amount/itemtotal bypersion, 100*amount/total bytotal

from ledgerl L ,iteamtotal I, total

where L.persion=I.persion

and actiondate between

to_date(‘01-MAR-1901’,’DD-MON-YYYY’) and

TO_date(‘31-MAR-1901’,’DD-MON-YYYY’)

And ACTION IN(‘BOUGHT’,’PAID’);

§7.3 家族树

ORACLE 提供了一个有趣的功能 connect by 子句，它可以对具有家族树结构的分枝进行排序。它的用途有机构或公司的各层结构，财务的科目代码等。

要使用查询遍历，需要在将数据在基表中按照层次结构进行存储。比如一个组织机构就是这样的典型例子。

实现语句：

SELECT column

FROM table_name

START WITH column=value

CONNECT BY PRIOR 父主键=子外键

§7.3.1 排除单一体和分枝

例1：在ORACLE的EMP 表中，每一条记录都有一个唯一标识当前雇员的empno和标识这个雇员的经理的mgr列。如果mgr 为空，则该雇员是该机构的最顶级。现在要列出每个雇员的层次结构(从顶到底)：

select lpad(' ',4*(level-1))||ename name ,empno,mgr from emp

start with mgr is null

connect by prior empno=mgr;

NAME EMPNO MGR

-------------------- --------- ---------

KING 7839

JONES 7566 7839

SCOTT 7788 7566

ADAMS 7876 7788

FORD 7902 7566

SMITH 7369 7902

BLAKE 7698 7839

ALLEN 7499 7698

WARD 7521 7698

MARTIN 7654 7698

TURNER 7844 7698

JAMES 7900 7698

CLARK 7782 7839

MILLER 7934 7782

14 rows selected.

SQL>

从查询结果中可以看出，由于JONES、BLAKE、CLARK的上司是KING，所以JONES等MGR(经理编号)=KING的empno号，即KING的直接下级是JONES、BLAKE、CLARK，因为他们的MGR与KING的EMPNO一样。

§7.3.2 遍历至根

例2：现在要从某个雇员往他的上级列出该雇员的层次结构(从顶到底)：

SQL> col ename for a30

SQL> l

select lpad(' ',4*(level-1))||ename ename,mgr,empno from emp

start with mgr=7788

connect by prior mgr= empno

SQL> /

ENAME MGR EMPNO

------------------------------ ----- ---------

ADAMS 7788 7876

SCOTT 7566 7788

JONES 7839 7566

KING 7839

例3：现在要列出所有雇员的层次结构(从顶到底)：

select lpad(' ',4*(level-1))||ename name ,empno,mgr from emp

start with mgr is not null

connect by empno=prior mgr

NAME EMPNO MGR

-------------------- --------- -----

SMITH 7369 7902

FORD 7902 7566

JONES 7566 7839

KING 7839

ALLEN 7499 7698

BLAKE 7698 7839

KING 7839

WARD 7521 7698

BLAKE 7698 7839

KING 7839

JONES 7566 7839

KING 7839

MARTIN 7654 7698

BLAKE 7698 7839

KING 7839

BLAKE 7698 7839

KING 7839

CLARK 7782 7839

KING 7839

SCOTT 7788 7566

JONES 7566 7839

KING 7839

TURNER 7844 7698

BLAKE 7698 7839

KING 7839

ADAMS 7876 7788

SCOTT 7788 7566

JONES 7566 7839

KING 7839

JAMES 7900 7698

BLAKE 7698 7839

KING 7839

FORD 7902 7566

JONES 7566 7839

KING 7839

MILLER 7934 7782

CLARK 7782 7839

KING 7839

38 rows selected.

§7.4 在from 中使用视图

在SELECT 语句中，当对一个表或视图查询时，语句要求视图是必须存在的。在ORACLE7.2版以后。当该视图不存在时，我们可以在 FROM子句后写上该视图即可。

详细例子如下：

1.假设有下面视图：

CREATE OR REPLACE VIEW TOTAL AS

Select SUM(amount) TOTAL

From ledger

Where actiondate between

To_date(‘01-MAR-1901’,’DD-MON-YYYY’) and

To_date(‘31-MAR-1901’,’DD-MON-YYYY’)

And action in(‘BOUGHT’,’PAID’);

2.我们可以像下面来使用视图( FROM 之后的TOTAL是视图)：

SELECT PERSON,AMOUNT,100*AMOUNT/TOTAL

From LEDGER,TOTAL

Where actiondate between

To_date(‘01-MAR-1901’,’DD-MON-YYYY’) and

To_date(‘31-MAR-1901’,’DD-MON-YYYY’)

And action in(‘BOUGHT’,’PAID’);

3.如果不建立 TOTAL视图，也可以使用下面语句来完成同样的工作：

SELECT PERSON,AMOUNT,100*AMOUNT/TOTAL

From LEDGER,

( select SUM(Amount) TOTAL

from Ledger

Where actiondate between

To_date(‘01-MAR-1901’,’DD-MON-YYYY’) and

To_date(‘31-MAR-1901’,’DD-MON-YYYY’)

And action in(‘BOUGHT’,’PAID’)

)

Where actiondate between

To_date(‘01-MAR-1901’,’DD-MON-YYYY’) and

To_date(‘31-MAR-1901’,’DD-MON-YYYY’)

And action in(‘BOUGHT’,’PAID’);