源码-Oracle数据库管理-第七章-视图-Part 2

内联视图（Inline View）：一种临时视图，不会存储到数据字典中；当一个Select语句的From子句中又使用了另一个Select语句时，可以说这个查询使用了内联视图。

PS：视图用的还不够熟练，需要多多练习和学习。

--代码 7.11 内联视图使用最简单的例子
 SELECT d.deptno, d.dname, emp_cnt.tot
    FROM dept d,
     (SELECT deptno, COUNT(*) tot
      FROM emp
      GROUP BY deptno) emp_cnt
    WHERE d.deptno = emp_cnt.deptno;

 
--代码 7.12 使用内联视图获取部门最高薪资级别
SELECT dept.deptno, dept.dname, NVL (MAX (emp_grade.grade), 0)
    FROM dept,
         (SELECT emp.ename, emp.job, emp.deptno,
                 (SELECT grade
                    FROM salgrade
                   WHERE emp.sal BETWEEN salgrade.losal AND salgrade.hisal) grade
            FROM emp) emp_grade
   WHERE dept.deptno = emp_grade.deptno(+)
GROUP BY dept.deptno, dept.dname
ORDER BY NVL (MAX (emp_grade.grade), 0) DESC;
 

/*
Grade/LOSAL/HISAL
1	700	1200
2	1201	1400
3	1401	2000
4	2001	3000
5	3001	9999
*/
select * from salgrade; 



 
 
--代码 7.13 内联视图的执行示意图
 SELECT d.deptno, d.dname, emp_cnt.tot             --顶层查询
            FROM dept d,
             (SELECT deptno, COUNT(*) tot                           --外层内联查询
              FROM 
               (SELECT * FROM emp WHERE comm IS NOT NULL)     --最内层内联查询
              GROUP BY deptno) emp_cnt
            WHERE d.deptno = emp_cnt.deptno;

 
 
--代码7.14 内联视图聚合运算（这部分没搞懂是怎么回事）
SELECT dept.dname, emp_up.empno, emp_up.ename, emp_up.job, dept_avg.avg_sal
FROM   (SELECT empno, ename, job, deptno
        FROM   emp
        START  WITH empno = 7369
        CONNECT BY empno = PRIOR mgr) emp_up,
       (SELECT emp.deptno, AVG(emp.sal) avg_sal
        FROM   emp
        GROUP  BY deptno) dept_avg,
       dept
WHERE  emp_up.deptno = dept_avg.deptno AND emp_up.deptno = dept.deptno;
 
--reference
with  x  as   
    ( select  'aa'  chr  from  dual 
    union  all   
    select  'bb'  chr  from  dual) 
    select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other  from  x  connect  by  level <= 3
        order by chr; 
        

--不使用内联视图时的SQL语句
UPDATE emp o
   SET o.comm = o.sal * 0.2
 WHERE o.comm IS NULL AND o.deptno IN (SELECT deptno
                                         FROM (SELECT   deptno, COUNT (*) tot
                                                   FROM (SELECT *
                                                           FROM emp
                                                          WHERE comm IS NULL)
                                               GROUP BY deptno)
                                        WHERE tot >= 2);
                                        
rollback;
                                        
 --代码7.15 使用内联视图时的UPDATE语句
 UPDATE (SELECT o.comm,o.sal
          FROM emp o
         WHERE o.comm IS NULL
           AND o.deptno IN (SELECT deptno
                              FROM (SELECT   deptno, COUNT (*) tot
                                        FROM (SELECT *
                                                FROM emp
                                               WHERE comm IS NULL)
                                    GROUP BY deptno)
                             WHERE tot >= 2)) empcomm
   SET empcomm.comm = empcomm.sal * 0.2;
   
   rollback;
                                       
--使用内联视图进行删除
DELETE FROM (SELECT o.comm,o.sal
              FROM emp o
             WHERE o.comm IS NULL
               AND o.deptno IN (SELECT deptno
                                  FROM (SELECT   deptno, COUNT (*) tot
                                            FROM (SELECT *
                                                    FROM emp
                                                   WHERE comm IS NULL)
                                        GROUP BY deptno)
                                WHERE tot > 1)) empcomm


--使用Oracle Hint避免ORA-01779异常 (没搞懂！)
UPDATE (SELECT  /*+BYPASS_UJVC*/              --使用Oracle提示跳过键检查
               a.col2 col2a, b.col2 col2b
          FROM test1 a, test2 b
         WHERE a.col1 = b.col1)
   SET col2a = col2b;