oracle偽列以及偽表

1、實(shí)用文案標(biāo)準(zhǔn)文檔oracle的偽列以及偽表oracle系統(tǒng)為了實(shí)現(xiàn)完整的關(guān)系數(shù)據(jù)庫(kù)功能，系統(tǒng)專(zhuān)門(mén)提供了一組成為偽列(P seudocolum n)的數(shù)據(jù)庫(kù)列，這些列不是在建立對(duì)象時(shí)由我們完成的，而是在我們建 立時(shí)由Oracle完成的。Oracle目前有以下偽列:一、偽列:CURRVAL AND NEXTVAL使用序列號(hào)的保留字LEVEL查詢(xún)數(shù)據(jù)所對(duì)應(yīng)的層級(jí)ROWID 記錄的唯一標(biāo)識(shí)ROWNUM 限制查詢(xún)結(jié)果集的數(shù)量二、偽表DUAL 表該表主要目的是為了保證在使用SELECT語(yǔ)句中的語(yǔ)句的完整性而提供的。般用于驗(yàn)證函數(shù)。例如:select sysdate ,to_char( sysdate ，y

2、yyy-mm-dd HH24:mm:ss' ) from dualOracle 偽列 RowID、什么是偽列RowID ？id，基于64位編碼的18個(gè)1、首先是一種數(shù)據(jù)類(lèi)型，唯一標(biāo)識(shí)一條記錄物理位置的一個(gè)字符顯示。2、未存儲(chǔ)在表中，可以從表中查詢(xún)，但不支持插入，更新，刪除它們的值。、RowID的用途1, 在開(kāi)發(fā)中使用頻率應(yīng)該是挺多的，特別在一些up date語(yǔ)句中使用更加頻繁。所以oracleERP中大部份的視圖都會(huì)加入rowid這個(gè)字段。在一些cursor定義時(shí)也少不了加入rowid。但往往我們?cè)陂_(kāi)發(fā)過(guò)程中,由于連接的表很多，再加上程序的復(fù)制，有時(shí)忽略了rowid 對(duì)應(yīng)的是那一個(gè)表中

3、rowid，所以有時(shí)過(guò)程出錯(cuò),往往花上很多時(shí)間去查錯(cuò)，最后查出來(lái)既然是up date時(shí)帶的rowid并非此表的rowid,所以在發(fā)現(xiàn)很多次的錯(cuò)誤時(shí)，重視r(shí)owid起來(lái)了，開(kāi)發(fā)中一定要注意rowid的匹配2，能以最快的方式訪(fǎng)問(wèn)表中的一行。3，能顯示表的行是如何存儲(chǔ)的。4，作為表中唯一標(biāo)識(shí)。三，RowID的組成rowid確定了每條記錄是在Oracle中的哪一個(gè)數(shù)據(jù)對(duì)象，數(shù)據(jù)文件、塊、行上。ROWID 的格式如下:數(shù)據(jù)對(duì)象編號(hào)文件編號(hào)塊編號(hào)行編號(hào)OOOOOOFFFRRR由 data_object_id# + rfile# + block# + row#組成，占用10個(gè)bytes的空間,32bit的

4、data_object_id#,10 bit的 rfile#,22bit的 block#,16 bit的 row#.所以每個(gè)表空間不能超過(guò)1023個(gè)數(shù)據(jù)文件。四、RowID的應(yīng)用1。準(zhǔn)備數(shù)據(jù):當(dāng)試圖對(duì)庫(kù)表中的某一列或幾列創(chuàng)建唯一索引時(shí),系統(tǒng)提示 ORA-01452:不能創(chuàng)建唯一索引，發(fā)現(xiàn)重復(fù)記錄。/*conn scott/tigerCreate table empa as select * from emp;（為以下演示）插入重復(fù)記錄-創(chuàng)建一個(gè)表 create table dup City Info asselect * from citylnfo-制造重復(fù)數(shù)據(jù)insert into dup

5、City Info select * from city Info2. 查找重復(fù)記錄的幾種方法:2.1.查找大量重復(fù)記錄方法1 :select id ,co unt (*) from dup City Infogroup by id havi ng count (*) > 1;方法2 :Select * From dupCityInfoWhere ROWID Not In (-每個(gè)組中最小的 ROWID（或者最大的 ROWID）Select Min (ROWID )From dup City InfoGroup By id）；22查找少量重復(fù)記錄select * from dup Cit

6、y Info awhere rowid <>(select max (rowid ) from dup City Infowhere id =a. id）；3. 刪除重復(fù)記錄的幾種方法:（1）.適用于有大量重復(fù)記錄的情況例上建有索引的時(shí)候，用以下語(yǔ)句效率會(huì)很高）：方法1 :Delete from dup CityInfoWhere id In (Select id From dupCityInfo)And ROWID Not In (Select Min (ROWID ) From)；Group Bydu pCityInfoid Having count (*)> 1Grou

7、p By id Having Count (*) > 1方法2 :Not In (Delete from dup CityI nfo Where ROWID-每個(gè)組中最小的 ROWIDGroup By id );Select Min (ROWID ) From dupCityInfo（2）.適用于有少量重復(fù)記錄的情況（注意，對(duì)于有大量重復(fù)記錄的情況，用以下語(yǔ)句效率會(huì)很低）：where rowid <>(Delete from dup City Info aselect max (rowid)from dup City Info where id =a. id );Oracle

8、 偽列 RowNum、概述:rownum 從1開(kāi)始;rownum 按照記錄插入時(shí)的順序給記錄排序，所以有order by 的子句時(shí)一定要注意啊!使用時(shí)rownum , order by 字段是否為主鍵有什么影響?子查詢(xún)中rownum rn ，而rn用到外查詢(xún)中到底是怎樣的序列?若id主鍵是按照從小到大的順序插入的，select語(yǔ)句沒(méi)有g(shù)roup by 和order by 的子句時(shí)，row num的順序和id順序基本一致。、實(shí)例:假設(shè)某個(gè)表t1(c1) 有 20 條記錄，如果用select rownum,c1 from t1 whererownum < 10,只要是用小于號(hào)，查出來(lái)的結(jié)果很

9、容易地與一般理解在概念上能達(dá)成一致,應(yīng)該不會(huì)有任何疑問(wèn)的?？扇绻?select row nu m,c1 from t1 where row num > 10 (如果寫(xiě)下這樣的查詢(xún)語(yǔ)句，這時(shí)候在您的頭腦中應(yīng)該是想得到表中后面10條記錄），你就會(huì)發(fā)現(xiàn)，顯示出來(lái)的結(jié)果要讓您失望了，那問(wèn)題是出在哪呢?因?yàn)镽OWNUM是對(duì)結(jié)果集加的一個(gè)偽列，即先查到結(jié)果集之后再加上去的一個(gè)列（強(qiáng)調(diào)：先要有結(jié)果集）。簡(jiǎn)單的說(shuō)rown um 是對(duì)符合條件結(jié)果的序列號(hào)。它總是從1開(kāi)始排起的。所以你選出的結(jié)果不可能沒(méi)有1，而有其他大于1的值。所以您沒(méi)辦法期望得到下面的結(jié)果集:11 aaaaaaaa12 bbbbbbb1

10、3 cccccccrownum >10沒(méi)有記錄，因?yàn)榈谝粭l不滿(mǎn)足去掉的話(huà)，第二條的ROWNUM 又成了 1，所以永遠(yuǎn)沒(méi)有滿(mǎn)足條件的記錄。或者可以這樣理解:ROWNUM 是一個(gè)序列，是oracle數(shù)據(jù)庫(kù)從數(shù)據(jù)文件或緩沖區(qū)中讀取數(shù)據(jù)的順序。它取得第一條記錄則rownum 值為1，第二條為2，依次類(lèi)推。如果你用>,>=,=,between.and這些條件，因?yàn)閺木彌_區(qū)或數(shù)據(jù)文件中得到的第一條記錄的rownum 為 1，則被刪除，接rownum 的!= 10,或著取下條，可是它的rown um 還是1，又被刪除，依次類(lèi)推，便沒(méi)有了數(shù)據(jù)。有了以上從不同方面建立起來(lái)的對(duì)rownum 的概

11、念，那我們可以來(lái)認(rèn)識(shí)使用 幾種現(xiàn)像1. select row nu m,c1 from t1 where rownum != 10為何是返回前 9條數(shù)據(jù)呢？它與 select rown um,c1 from table name whererown um < 10返回的結(jié)果集是一樣的呢?因?yàn)槭窃诓樵?xún)到結(jié)果集后，顯示完第9條記錄后，之后的記錄也都是者>=10,所以只顯示前面 9條記錄。也可以這樣理解，rownum 為9后的記錄的rownum為10,因條件為！=10，所以去掉，其后記錄補(bǔ)上，rownum 又是10，也去掉，如果下去 也就只會(huì)顯示前面 9條記錄了。2.為什么 rownum

12、 >1時(shí)查不到一條記錄，而 rownum >0 或 rownum >=1 卻總 顯示所有的記錄?因?yàn)閞ownum 是在查詢(xún)到的結(jié)果集后加上去的，它總是從1開(kāi)始。3.為什么 between 1 and 10 或者 between 0 and 10 能查到結(jié)果，而用 between 2 and 10 卻得不到結(jié)果?原因同上一樣，因?yàn)閞ownum 總是從1開(kāi)始。從上可以看出，任何時(shí)候想把rownum=1rownum = 1這條記錄拋棄是不對(duì)的，它在結(jié)果集中是不可或缺的，少了像空中樓閣一般不能存在，所以你的rown um 條件要包含到 1。但如果就是想要用rownum > 10

13、這種條件的話(huà)話(huà)就要用子查詢(xún)，把rownum 先生成, 后對(duì)他進(jìn)行查詢(xún)。select * from (selet rownum as rn ， t1.* from a where .) where rn >10般代碼中對(duì)結(jié)果集進(jìn)行分頁(yè)就是這么干的。另外：rowid與rownum 雖都被稱(chēng)為偽列，但它們的存在方式是不一樣的，rowid以說(shuō)是物理存在的，表示記錄在表空間中的唯一位置ID，在DB中唯一。只要記錄沒(méi)被搬動(dòng)過(guò)，rowid是不變的。rowid相對(duì)于表來(lái)說(shuō)又像表中的一般列，所以以rowid 為條件就 不會(huì)有rownum 那些情況發(fā)生。另外還要注意：rown um不能以任何基表的名稱(chēng)作為前

14、綴。對(duì)于rownum 來(lái)說(shuō)它是oracle系統(tǒng)順序分配為從查詢(xún)返回的行的編號(hào)，返回的第一行分配的是1，第二行是2，依此類(lèi)推，這個(gè)偽字段可以用于限制查詢(xún)返回的總行數(shù)，且rownum 不能以任何表的名稱(chēng)作為前綴。（1） row num對(duì)于等于某值的查詢(xún)條件如果希望找到學(xué)生表中第一條學(xué)生的信息,可以使用rownum=1 作為條件。但是想找到學(xué)生表中第二條學(xué)生的信息，使用rownum=2 結(jié)果查不到數(shù)據(jù)。因?yàn)閞ownum都是從1開(kāi)始，但是1以上的自然數(shù)在rownum 做等于判斷是時(shí)認(rèn)為都是false條件，所以無(wú)法查到rownum = n（n>1的自然數(shù)）。select rownu m,id, n

15、ame from stude nt where row num=1（可以用在限制返回記錄條數(shù)的地方，保證不出錯(cuò)，如：隱式游標(biāo)）（2） rownum對(duì)于大于某值的查詢(xún)條件如果想找到從第二行記錄以后的記錄，當(dāng)使用rownum>2 是查不出記錄的，原因是由于rownum 是一個(gè)總是從1開(kāi)始的偽列，Oracle認(rèn)為row num > n（n >1 的自然數(shù)）這種條件rownum 必依舊不成立，所以查不到記錄。查找第二行以后的記錄可使用以下的子查詢(xún)方法來(lái)解決。注意子查詢(xún)中的須要有別名，否則還是不會(huì)查出記錄來(lái)，這是因?yàn)閞ownum 不是某個(gè)表的列，如果不起別名的話(huà)，無(wú)法知道 rownum

16、 是子查詢(xún)的列還是主查詢(xún)的列。select * from(select row num no ,id, name from stude nt) where no>2;NO ID NAME3 200003 李三4 200004 趙四(3) rownum 對(duì)于小于某值的查詢(xún)條件rownum 對(duì)于rownum<n(n>1的自然數(shù))的條件認(rèn)為是成立的，所以可以找到記錄。select rownu m,id, name from stude nt where rownum <3;ROWNUM IDNAME1 200001 張一2 200002 王二查詢(xún)r(jià)ownum 在某區(qū)間的數(shù)據(jù)，

17、必須使用子查詢(xún)。 例如要查詢(xún)r(jià)ownum 在第二行到第三行之間的數(shù)據(jù)，包括第二行和第三行數(shù)據(jù)，那么我們只能寫(xiě)以下語(yǔ)句， 先讓它返回小于等于三的記錄行，然后在主查詢(xún)中判斷新的rownum 的別名列大于等于二的記錄行。但是這樣的操作會(huì)在大數(shù)據(jù)集中影響速度。select * from (select row num n o,id, name from stude nt where row num <=3where no >=2;NO ID NAME2 200002 王二3 200003 李三(4)rownum 和排序Oracle中的rownum 的是在取數(shù)據(jù)的時(shí)候產(chǎn)生的序號(hào)，所以想對(duì)指定

18、排序的數(shù)據(jù)去指定的rowmun 行數(shù)據(jù)就必須注意了。select rownum ,id, name from stude nt order by n ame;ROWNUM IDNAME3200003李三2200002王二1 200001張一4200004趙四可以看出，rownum 并不是按照name列來(lái)生成的序號(hào)。系統(tǒng)是按照記錄插入時(shí)的順序給記錄排的號(hào)，rowid也是順序分配的。為了解決這個(gè)問(wèn)題，必須使用子查詢(xún);select row num /* 主查詢(xún)中的 rownum, 與子查詢(xún)無(wú)關(guān) */ ,id, name from (select * from stude nt order by n

19、ame）；ROWNUM IDNAME1 200003李三2200002王二3200001張一4200004趙四這樣就成了按name排序，并且用rownum標(biāo)出正確序號(hào)（有小到大）筆者在工作中有一上百萬(wàn)條記錄的表，在jsp頁(yè)面中需對(duì)該表進(jìn)行分頁(yè)顯示，便考慮用rownum 來(lái)作，下面是具體方法 （每頁(yè)顯示20條）：“select * from tab name where row num <20 order by n ame"但卻發(fā)現(xiàn)oracle卻不能按自己的意愿來(lái)執(zhí)行，而是先隨便取20條記錄，然后再order by ，后經(jīng)咨詢(xún)oracle,說(shuō)rownum 確實(shí)就這樣，想用的話(huà)，只

20、能用子查詢(xún)來(lái)實(shí)現(xiàn)先排序，后rownum ，方法如下:"select * from (select * from tab name order by n ame) where rownum <20",但這樣一來(lái)，效率會(huì)低很多。后經(jīng)筆者試驗(yàn)，只需在order by的字段上加主鍵或索引即可讓oracle先按該字段排序,然后再rownum ;方法不變:"select * from tab name where row num <20 order by n ame"取得某列中第N大的行select colu mn_n ame fromselect ta

21、ble_ name.*,de nse_ra nk() over (order by colu mn desc) rankfrom table_ namewhere rank = &N假如要返回前5條記錄:select * from table name where row num <6;(或是 row num <= 5 或是 row num != 6)假如要返回第5-9條記錄:select * from table name where and rownum <10minus select * from table name where and rownum<5

22、 order by n ame選出結(jié)果后用name排序顯示結(jié)果。（先選再排序注意：只能用以上符號(hào)（、=、!=）。返回的是前9條記錄。不能用：>,>=,=,Betwee n.a nd。由于 row num是一個(gè)總是從1開(kāi)始的偽列，Oracle認(rèn)select * from table name where row num != 10;為這種條件不成立。另外，這個(gè)方法更快:select * from (select row num r,a from yourtable where rownum <= 20order by n ame where r > 10這樣取出第11-2

23、0條記錄!（先選再排序再選）要先排序再選則須用select嵌套：內(nèi)層排序外層選。rownum是隨著結(jié)果集生成的,旦生成，就不會(huì)變化了；同時(shí),生成的結(jié)果是依次遞加的,沒(méi)有1就永遠(yuǎn)不會(huì)有2!rownum 是在查詢(xún)集合產(chǎn)生的過(guò)程中產(chǎn)生的偽列，并且如果where 條件中存在 rownum條件的話(huà)，則：1 :假如判定條件是常量，則:只能rownum = 1, <= 大于1的自然數(shù)，=大于1的數(shù)是沒(méi)有結(jié)果的；大于一個(gè)數(shù)也是沒(méi)有結(jié)果的即 當(dāng)出現(xiàn)一個(gè) rownum 不滿(mǎn)足條件的時(shí)候則查詢(xún)結(jié)束this is stop key（一個(gè)不滿(mǎn)足,系統(tǒng)將該記錄過(guò)濾掉， 則下一條記錄的rownum 還是這個(gè)，所以后面

24、的就不再有滿(mǎn)足記錄,this is stop key ）；2:假如判定值不是常量，則:若條件是=var ,則只有當(dāng)var為1的時(shí)候才滿(mǎn)足條件，這個(gè)時(shí)候不存在 stop key ,必須進(jìn)行full scan ,對(duì)每個(gè)滿(mǎn)足其他 where 條件的數(shù)據(jù)進(jìn)行判定，選出一行后才能去選rownum=2 的行 以下摘自中國(guó)IT實(shí)驗(yàn)室1.在 oracle 中實(shí)現(xiàn) select top n由于oracle 不支持select top 語(yǔ)句，所以在 oracle 中經(jīng)常是用 order by 跟rownum 的組合來(lái)實(shí)現(xiàn)select top n的查詢(xún)。簡(jiǎn)單地說(shuō)，實(shí)現(xiàn)方法如下所示:select 列名1.列名n fr

25、om （select 列名1.列名n from 表名 order by 列名1.列名n ）where rownum<=n（扌由出記錄數(shù)）order by rownum ascF面舉個(gè)例子簡(jiǎn)單說(shuō)明一下。顧客表customer(id,name)有如下數(shù)據(jù):ID NAME01 first02 Second03 third04 forth05 fifth06 sixth07 seve nth08 eighth09 ninth10 last則按NAME的字母順抽出前三個(gè)顧客的 SQL語(yǔ)句如下所示: select * from(select * from customer order by n am

26、e)where row num <=3order by row num asc輸出結(jié)果為:ID NAME08 eighth05 fifth01 first序列可以保證多個(gè)用戶(hù)對(duì)同一張表進(jìn)行操作時(shí)生成唯一的整數(shù)，通常用來(lái)做表的主鍵。創(chuàng)建序列：create sequenee< 序歹U名字 >start with < 起始值 >in creme nt by< 增長(zhǎng)值 >MaxValue < 最大值 >NoMaxValue /沒(méi)有上限例如:create seque nee mySeqstart with 1 in creme nt 1刪除序列:dr

27、op seque nee <序列名字修改序列:alter seque nee序列名字start with起始值in creme nt by增長(zhǎng)值MaxValue最大值查看序列:使用下列視圖之一:Dba_SequencesAll_ Seque ncesUser_ Seque nces訪(fǎng)問(wèn)序列:CurVal返回序列的當(dāng)前值NextVal返回序列的下一個(gè)值例如：select mySeq.NextVal,city from postConnect by語(yǔ)句該語(yǔ)句結(jié)合偽列rownum或level可以產(chǎn)生一個(gè)結(jié)果集.1.基本用法:產(chǎn)生1100 之間的整數(shù)Select row numxh from d

28、ual connect by row num <= 100 ;Select level xh from dual connect by level <=100 ;2.高級(jí)用法2.1.產(chǎn)生所有漢字，漢字內(nèi)碼為:1996840869之間dualselect t.* from (select row numxh, nchr( rownum ) hz from connect by row num < 65535where t.xh between 19968 and 4086922查找某個(gè)漢字的內(nèi)碼使用CTE:with myCh in ese as(select t.* from

29、(select row num xh, nchr( row num ) hz from dualconnect by row num <65535wheret.xh between 19968 and40869select * frommyChinese where hz='東'-查找漢字'東'的內(nèi)碼2.3.拆分字符串with t as (select'中華人民共和國(guó)'sentence fromdual)select substr(sentence, rownum,1) from tfrom t)-order by NLSSORT(subs

30、tr(sentence, rownum ,1)，NLS_SORT=SCHINESE_STROKE_M' );-按 筆畫(huà)排序connect by rownum <=( selectlength(sentence)一、集合操作UNION由每個(gè)查詢(xún)選擇的所有不重復(fù)的行并集不包含重復(fù)值UNION ALL由每個(gè)查詢(xún)選擇的所有的行，包括所有重復(fù)的行完全并集包含重復(fù)值INTERSECT由每個(gè)查詢(xún)選擇的所有不重復(fù)的相交行交集MINUS在第一個(gè)查詢(xún)中，不在后面查詢(xún)中，并且結(jié)果行不重復(fù)差集所有的集合運(yùn)算與等號(hào)的優(yōu)先級(jí)相同，如果SQL語(yǔ)句包含多個(gè)集合運(yùn)算并且沒(méi)有圓括號(hào)明確地指定另一個(gè)順序，Oracle

31、服務(wù)器將以從左到右的順序計(jì)算。你應(yīng)該使用圓括號(hào)來(lái)明確地指定帶另外的集合運(yùn)算的INTERSECT （相交）運(yùn)算查詢(xún)中的賦值順序。Un io n all 效率一般比un io n高。1.1.union禾R union allUNION（聯(lián)合）運(yùn)算UNION運(yùn)算返回所有由任一查詢(xún)選擇的行。用UNION 運(yùn)算從多表返回所有行，但除去任 何重復(fù)的行。例:Sql代碼1. select e1.e mpno ,e1.e name,e1.mgr from emp e1 union select e2.e mpno ,e2.job,e2.sal2. from emp e2原則：SELECT語(yǔ)句一致。列的在例子中將

32、輸出(1)被選擇的列數(shù)和列的數(shù)據(jù)類(lèi)型必須是與所有用在查詢(xún)中的名子不必相冋。(2)聯(lián)合運(yùn)算在所有被選擇的列上進(jìn)行。(3)在做重復(fù)檢查的時(shí)候不忽略空(NULL)值。(4)IN運(yùn)算有比UNION運(yùn)算高的優(yōu)先級(jí)。在默認(rèn)情況下，輸出以 SELECT子句的第一列的升序排序。empno,en ame,mgr三歹U數(shù)據(jù)。全聯(lián)合(UNION ALL)運(yùn)算用全聯(lián)合運(yùn)算從多個(gè)查詢(xún)中返回所有行。原則:(1)和聯(lián)合不同，重復(fù)的行不被過(guò)濾，并且默認(rèn)情況下輸出不排序。不能使用DISTINCT關(guān)鍵字。(2) 使用:Select stateme nt union | union all Select stateme nt;1.

33、2.i ntersect交集操作相交運(yùn)算用相交運(yùn)算返回多個(gè)查詢(xún)中所有的公共行。無(wú)重復(fù)行。原則:(1).在查詢(xún)中被SELECT語(yǔ)句選擇的列數(shù)和數(shù)據(jù)類(lèi)型必須與在查詢(xún)中所使用的所有的SELTCT語(yǔ)句中的一樣，但列的名字不必一樣。相交的表的倒序排序不改變結(jié)果。.相交不忽略空值。.使用:Select stateme nt in tersect all Select stateme nt;1.3. min us 差集操作 相減運(yùn)算用相減運(yùn)算返回由第一個(gè)查詢(xún)返回的行，那些行不出現(xiàn)在第二個(gè)查詢(xún)中(第一個(gè)SELECT語(yǔ)句減第二個(gè) SELECT語(yǔ)句)。原則:在查詢(xún)中被SELECT語(yǔ)句選擇的列數(shù)和數(shù)據(jù)類(lèi)型必須與在查

34、詢(xún)中所使用的所有的SELTCT語(yǔ)句中的一樣，但列的名字不必一樣。對(duì)于MINUS 運(yùn)算，在 WHERE子句中所有的列都必須在 SELECT子句中。集合運(yùn)算的原則?在兩個(gè)SELECT列表中的表達(dá)式必須在數(shù)目上和數(shù)據(jù)類(lèi)型上相匹配 ?可以用圓括號(hào)改變執(zhí)行的順序 ?ORDER BY子句：-只能出現(xiàn)在語(yǔ)句的最后-從第一個(gè) SELECT語(yǔ)句接收列名、別名，或者位置記號(hào)注：？除了 UNION ALL，重復(fù)行自動(dòng)被清除?在結(jié)果中的列名是第一個(gè)查詢(xún)中出現(xiàn)的列名?除了 UNION ALL ，默認(rèn)情況下按升序順序輸出exists 和 not exists 的使用1.謂詞exists和in概述Exists用于只能用于子

35、查詢(xún)，可以替代 in，若匹配到結(jié)果，則退出內(nèi)部查詢(xún)，并將條件標(biāo)志為true，傳回全部結(jié)果資料.in不管匹配到匹配不到都全部匹配完畢使用exists可以將子查詢(xún)結(jié)果定為常量，不影響查詢(xún)效果，而且效率高。如查詢(xún)所有銷(xiāo)售部門(mén)員工的姓名，對(duì)比如下:IN is ofte n better if the results of the subquery are very smallWhen you write a query using the IN clause, you're telli ng the rule-based op timizer that you want the inner q

36、uery to drive the outer query.When you write EXISTS in a where clause, you're telli ng the op timizer that you want the outer query to be run first, using each value to fetch a value from the inner query.In many cases, EXISTS is better because it requires you to sp ecify a join con diti on.which

37、 can in voke an INDEX sca n. However, IN is ofte n better if the results of thesubquery are very small. You usually want to run the query that retur ns the smaller set of results first.2.1 n和exists使用原則:2.1.若子查詢(xún)結(jié)果集比較小，優(yōu)先使用in。2.2.若外層查詢(xún)比子查詢(xún)小，優(yōu)先使用exists。因?yàn)槿粲胕n，則oracle會(huì)優(yōu)先查詢(xún)子查詢(xún)，然后匹配外層查詢(xún)，若使用exists，則oracle會(huì)

38、優(yōu)先查詢(xún)外層表，然后再與內(nèi)層表匹配。最優(yōu)化匹配原則，拿最小記錄匹配大記錄。使用inselect last_ name, titlefrom s_emp where dep t_id in(select id from s_de pt where n ame='Sales');使用existsselect last_ name,titlefrom s_emp e where exists(select 'x'- 把查詢(xún)結(jié)果定為constant，提高效率from s_de pt s where s.id=e.de pt_id and s.n ame='Sal

39、es');2.3 not exists 的使用與exists含義相反，也在子查詢(xún)中使用，用于替代not in。其他一樣。如查詢(xún)不在銷(xiāo)售部的員工姓名select last_ name,titlefrom s_emp ewhere not exists（select 'x'- 把查詢(xún)結(jié)果定為constant，提高效率from s_de pt s where s.id=e.de pt_id and s.n ame='Sales');3. with子句Oracle9i新增語(yǔ)法1.使用with子句可以讓子查詢(xún)重用相同的with查詢(xún)塊，通過(guò)select調(diào)用，一般在

40、with查詢(xún)用到多次情況下。2.with子句的返回結(jié)果存到用戶(hù)的臨時(shí)表空間中，只做一次查詢(xún)，提高效率。3. 有多個(gè)查詢(xún)的時(shí)候，第1個(gè)用with，后面的不用 with，并且用逗號(hào)隔開(kāi)。,查詢(xún)必須用括4. 最后一個(gè)with子句與下面的查詢(xún)之間不能有逗號(hào)，只通過(guò)右括號(hào)分割號(hào)括起來(lái)5.如果定義了 with子句，而在查詢(xún)中不使用，那么會(huì)報(bào)ora-32035 錯(cuò)誤：未引用在with子句中定義的查詢(xún)名。（至少一個(gè)with查詢(xún)的name未被引用，解決方法是移除未被引用的with查詢(xún)）6.前面的with子句定義的查詢(xún)?cè)诤竺娴膚ith子句中可以使用。With子句目的是為了重用查詢(xún)。語(yǔ)法:With alias na

41、me as (selecti ), -as 和 select 中的括號(hào)都不能省略alias name2 as(select2 ),-后面的沒(méi)有with，逗號(hào)分割alias namen as(select n )-與下面的查詢(xún)之間沒(méi)有逗號(hào)Select .如查詢(xún)銷(xiāo)售部門(mén)員工的姓名:-with clausewith a as(select id from s_de pt where n ame='Sales' order by id)使用select查詢(xún)別名select last_ name,titlefrom s_emp where dep t_id in (select * fr

42、om a);-例1 :查詢(xún)citylnfo表同一省中具有最小id和最大id的城市信息方法1 :with sta as (select province,min (id) min Value,max(id) maxValuefrom cityl nfo group by provinceselect c.* from city Info c,stawhere (vin ce=sta .province)an d(c.id=sta. min Value or c.id=sta.maxValue)order by vince方法2 :with sta as ( select p

43、rovince,min(id) minValue,max(id) maxValue from cityInfo group by pr ovince)select * from cityInfo c where (pr ovince,id) in (select p rovince,minValue from sta)or (pr ovince,id) in (select pr ovince,maxValue from sta)order by pr ovince方法3 :with sta as ( select p rovince,min(id) minValue,max(id) maxV

44、alue from cityInfo group by pr ovince)select c.* from cityInfo cinner join sta on (c.pr ovince=sta .p rovince) and(c.id=sta.minValue or c.id=sta.maxValue)order by c.p rovince方法4:（感覺(jué)速度慢！，也許是因?yàn)橥獠樵?xún)記錄太多？）with sta as ( select province, min (id ) minValue ,max (id ) maxValue from cityInfo group by provinc

45、e)select c.* from cityInfo cwhere exists (select 1 from sta where(vince=vince)and (c.id =sta. minValue or c.id =sta. maxValue )order by vince例2 :查詢(xún)出部門(mén)的總薪水大于所有部門(mén) 平均總薪水 的部門(mén)。部門(mén)表s_dept，員工表s_emp。1步with查分析：做這個(gè)查詢(xún)，首先必須計(jì)算出所有部門(mén)的總薪水，然后計(jì)算出總薪水的平均薪水，再篩選出部門(mén)的總薪水大于所有部門(mén)總薪水平均薪水的部門(mén)。那么第詢(xún)查出所有部門(mén)的總薪水，第2步用with從第1步獲得的結(jié)果表中查詢(xún)出平均薪水,最后利用這兩次的 with查詢(xún)比較總薪水大于平均薪水的結(jié)果，如下:方法1:-step1:查詢(xún)出部門(mén)名和部門(mén)的總薪水with dep t_costs as(select a.d name,sum(b.sal) dep t_total from dept a,e mp b where a.de ptno