sqlplus之 內(nèi)聯(lián)視圖

內(nèi)聯(lián)視圖

在select語(yǔ)句里的內(nèi)聯(lián)視圖(in-line?view），即 SELECT? *? FROM? (

scott@ORCL>select?*?from?dept;

????DEPTNO?DNAME??????????LOC
----------?--------------?-------------
????????10?ACCOUNTING?????NEW?YORK
????????20?RESEARCH???????DALLAS
????????30?SALES??????????CHICAGO
????????40?OPERATIONS?????BOSTON

scott@ORCL>select?*?from?emp;

?????EMPNO?ENAME??????JOB??????????????MGR?HIREDATE??????????????SAL???????COMM		DEPTNO
----------?----------?---------?----------?--------------?----------?----------		----------
??????7369?SMITH??????CLERK???????????7902?17-12月-80????????????800			20
??????7499?ALLEN??????SALESMAN????????7698?20-2月?-81???????????1600????????300		30
??????7521?WARD???????SALESMAN????????7698?22-2月?-81???????????1250????????500		30
??????7566?JONES??????MANAGER?????????7839?02-4月?-81???????????2975			20
??????7654?MARTIN?????SALESMAN????????7698?28-9月?-81???????????1250???????1400		30
??????7698?BLAKE??????MANAGER?????????7839?01-5月?-81???????????2850			30
??????7782?CLARK??????MANAGER?????????7839?09-6月?-81???????????2450			10
??????7788?SCOTT??????ANALYST?????????7566?19-4月?-87???????????3000			20
??????7839?KING???????PRESIDENT????????????17-11月-81???????????5000			10
??????7844?TURNER?????SALESMAN????????7698?08-9月?-81???????????1500??????????0		30
??????7876?ADAMS??????CLERK???????????7788?23-5月?-87???????????1100			20
??????7900?JAMES??????CLERK???????????7698?03-12月-81????????????950			30
??????7902?FORD???????ANALYST?????????7566?03-12月-81???????????3000			20
??????7934?MILLER?????CLERK???????????7782?23-1月?-82???????????1300			10

已選擇14行。

scott@ORCL>select?*?from
??2??(select?deptno,count(*)?emp_count?from?emp?group?by?deptno)??emp,dept
??3??where?dept.deptno=emp.deptno;

????DEPTNO??EMP_COUNT?????DEPTNO?DNAME??????????LOC
----------?----------?----------?--------------?-------------
????????10??????????3?????????10?ACCOUNTING?????NEW?YORK
????????20??????????5?????????20?RESEARCH???????DALLAS
????????30??????????6?????????30?SALES??????????CHICAGO

其中，(?select?deptno,count(*)?emp_count?from?emp?group?by?deptno?) 就是內(nèi)聯(lián)視圖(in-line?view）

在DML語(yǔ)句里的內(nèi)聯(lián)視圖(in-line?view），insert?into?(

insert?into?(select?object_id,object_name,object_type?from?xxx?where?object_id<1000)?values(1001,'testbyhao','testtype');

普通視圖

scott@ORCL>create?view?emp7934
??2??as
??3??select?empno,ename,sal?from?emp?where?empno=7934;

視圖已創(chuàng)建。

普通視圖本質(zhì)上還是內(nèi)聯(lián)視圖(in-line?view)，因?yàn)樵趫?zhí)行包含普通視圖的SQL語(yǔ)句時(shí)，普通視圖都會(huì)最終轉(zhuǎn)化為內(nèi)聯(lián)視圖(in-line?view)

在select語(yǔ)句里的普通視圖，例如:

scott@ORCL>select?*?from?emp7934;

?????EMPNO?ENAME?????????????SAL
----------?----------?----------
??????7934?MILLER???????????1300

最終轉(zhuǎn)化為:

scott@ORCL>select?*?from?(select?empno,ename,sal?from?emp?where?empno=7934);

?????EMPNO?ENAME?????????????SAL
----------?----------?----------
??????7934?MILLER???????????1300

在DML語(yǔ)句里的普通視圖，例如:

scott@ORCL>insert?into?emp7934?values(111,'Test',3500);
已創(chuàng)建?1?行。

最終轉(zhuǎn)化為:

scott@ORCL>insert?into?(?select?empno,ename,sal?from?emp?where?empno=7934)?values(222,'Test2',4000);
已創(chuàng)建?1?行。