oracle偽列以及偽表

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

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

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

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

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

6、y Info awhere rowid <>(select max (rowid ) from dup City Infowhere id =a. id）；3. 刪除重復記錄的幾種方法:（1）.適用于有大量重復記錄的情況例上建有索引的時候，用以下語句效率會很高）：方法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-每個組中最小的 ROWIDGroup By id );Select Min (ROWID ) From dupCityInfo（2）.適用于有少量重復記錄的情況（注意，對于有大量重復記錄的情況，用以下語句效率會很低）：where rowid <>(Delete from dup City Info aselect max (rowid)from dup City Info where id =a. id );Oracle

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

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

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

11、念，那我們可以來認識使用 幾種現(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返回的結果集是一樣的呢?因為是在查詢到結果集后，顯示完第9條記錄后，之后的記錄也都是者>=10,所以只顯示前面 9條記錄。也可以這樣理解，rownum 為9后的記錄的rownum為10,因條件為！=10，所以去掉，其后記錄補上，rownum 又是10，也去掉，如果下去 也就只會顯示前面 9條記錄了。2.為什么 rownum

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

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

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

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

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

17、必須使用子查詢。 例如要查詢rownum 在第二行到第三行之間的數(shù)據(jù)，包括第二行和第三行數(shù)據(jù)，那么我們只能寫以下語句， 先讓它返回小于等于三的記錄行，然后在主查詢中判斷新的rownum 的別名列大于等于二的記錄行。但是這樣的操作會在大數(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ù)的時候產(chǎn)生的序號，所以想對指定

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列來生成的序號。系統(tǒng)是按照記錄插入時的順序給記錄排的號，rowid也是順序分配的。為了解決這個問題，必須使用子查詢;select row num /* 主查詢中的 rownum, 與子查詢無關 */ ,id, name from (select * from stude nt order by n

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

20、能用子查詢來實現(xiàn)先排序，后rownum ，方法如下:"select * from (select * from tab name order by n ame) where rownum <20",但這樣一來，效率會低很多。后經(jīng)筆者試驗，只需在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選出結果后用name排序顯示結果。（先選再排序注意：只能用以上符號（、=、!=）。返回的是前9條記錄。不能用：>,>=,=,Betwee n.a nd。由于 row num是一個總是從1開始的偽列，Oracle認select * from table name where row num != 10;為這種條件不成立。另外，這個方法更快: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是隨著結果集生成的,旦生成，就不會變化了；同時,生成的結果是依次遞加的,沒有1就永遠不會有2!rownum 是在查詢集合產(chǎn)生的過程中產(chǎn)生的偽列，并且如果where 條件中存在 rownum條件的話，則：1 :假如判定條件是常量，則:只能rownum = 1, <= 大于1的自然數(shù)，=大于1的數(shù)是沒有結果的；大于一個數(shù)也是沒有結果的即 當出現(xiàn)一個 rownum 不滿足條件的時候則查詢結束this is stop key（一個不滿足,系統(tǒng)將該記錄過濾掉， 則下一條記錄的rownum 還是這個，所以后面

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

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

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

27、op seque nee <序列名字修改序列:alter seque nee序列名字start with起始值in creme nt by增長值MaxValue最大值查看序列:使用下列視圖之一:Dba_SequencesAll_ Seque ncesUser_ Seque nces訪問序列:CurVal返回序列的當前值NextVal返回序列的下一個值例如：select mySeq.NextVal,city from postConnect by語句該語句結合偽列rownum或level可以產(chǎn)生一個結果集.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.高級用法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查找某個漢字的內(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'中華人民共和國'sentence fromdual)select substr(sentence, rownum,1) from tfrom t)-order by NLSSORT(subs

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

31、服務器將以從左到右的順序計算。你應該使用圓括號來明確地指定帶另外的集合運算的INTERSECT （相交）運算查詢中的賦值順序。Un io n all 效率一般比un io n高。1.1.union禾R union allUNION（聯(lián)合）運算UNION運算返回所有由任一查詢選擇的行。用UNION 運算從多表返回所有行，但除去任 何重復的行。例: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語句一致。列的在例子中將

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

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

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

35、查詢，可以替代 in，若匹配到結果，則退出內(nèi)部查詢，并將條件標志為true，傳回全部結果資料.in不管匹配到匹配不到都全部匹配完畢使用exists可以將子查詢結果定為常量，不影響查詢效果，而且效率高。如查詢所有銷售部門員工的姓名，對比如下: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.若子查詢結果集比較小，優(yōu)先使用in。2.2.若外層查詢比子查詢小，優(yōu)先使用exists。因為若用in，則oracle會優(yōu)先查詢子查詢，然后匹配外層查詢，若使用exists，則oracle會

38、優(yōu)先查詢外層表，然后再與內(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'- 把查詢結果定為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含義相反，也在子查詢中使用，用于替代not in。其他一樣。如查詢不在銷售部的員工姓名select last_ name,titlefrom s_emp ewhere not exists（select 'x'- 把查詢結果定為constant，提高效率from s_de pt s where s.id=e.de pt_id and s.n ame='Sales');3. with子句Oracle9i新增語法1.使用with子句可以讓子查詢重用相同的with查詢塊，通過select調用，一般在

40、with查詢用到多次情況下。2.with子句的返回結果存到用戶的臨時表空間中，只做一次查詢，提高效率。3. 有多個查詢的時候，第1個用with，后面的不用 with，并且用逗號隔開。,查詢必須用括4. 最后一個with子句與下面的查詢之間不能有逗號，只通過右括號分割號括起來5.如果定義了 with子句，而在查詢中不使用，那么會報ora-32035 錯誤：未引用在with子句中定義的查詢名。（至少一個with查詢的name未被引用，解決方法是移除未被引用的with查詢）6.前面的with子句定義的查詢在后面的with子句中可以使用。With子句目的是為了重用查詢。語法:With alias na

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

42、om a);-例1 :查詢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:（感覺速度慢！，也許是因為外查詢記錄太多？）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 :查詢出部門的總薪水大于所有部門 平均總薪水 的部門。部門表s_dept，員工表s_emp。1步with查分析：做這個查詢，首先必須計算出所有部門的總薪水，然后計算出總薪水的平均薪水，再篩選出部門的總薪水大于所有部門總薪水平均薪水的部門。那么第詢查出所有部門的總薪水，第2步用with從第1步獲得的結果表中查詢出平均薪水,最后利用這兩次的 with查詢比較總薪水大于平均薪水的結果，如下:方法1:-step1:查詢出部門名和部門的總薪水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