oracle的偽列以及偽表
oracle系統為了實現完整的關系數據庫功能，系統專門提供了一組成為偽列（Pseudocolumn）的數據庫列，這些列不是在建立對象時由我們完成的，而是在我們建立時由Oracle完成的。Oracle目前有以下偽列：
一、偽列：
CURRVAL AND NEXTVAL 使用序列號的保留字
LEVEL 查詢數據所對應的層級
ROWID 記錄的唯一標識
ROWNUM 限制查詢結果集的數量
二、偽表
DUAL 表
該表主要目的是為了保證在使用SELECT語句中的語句的完整性而提供的。
一般用於驗證函數。例如：
select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mm:ss') from dual
Oracle偽列RowID
一、什麼是偽列RowID？
1、首先是一種數據類型，唯一標識一條記錄物理位置的一個id，基於64位編碼的18個字符顯示。
2、未存儲在表中，可以從表中查詢，但不支持插入，更新，刪除它們的值。
二、RowID的用途
1,在開發中使用頻率應該是挺多的，特別在一些update語句中使用更加頻繁。所以oracle ERP中大部份的視圖都會加入rowid這個字段。
在一些cursor定義時也少不了加入rowid。但往往我們在開發過程中，由於連接的表很多，再加上程序的復制，有時忽略了rowid對應的是那一個表中rowid，所以有時過程出錯，
往往花上很多時間去查錯，最後查出來既然是update時帶的rowid並非此表的rowid,所以在發現很多次的錯誤時，重視rowid起來了，開發中一定要注意rowid的匹配
2，能以最快的方式訪問表中的一行。
3，能顯示表的行是如何存儲的。
4，作為表中唯一標識。
三，RowID的組成
rowid確定了每條記錄是在Oracle中的哪一個數據對象，數據文件、塊、行上。
ROWID 的格式如下：
數據對象編號 文件編號 塊編號 行編號
OOOOOO FFF BBBBBB RRR
由 data_object_id# + rfile# + block# + row# 組成，占用10個bytes的空間，
32bit的 data_object_id#,
10 bit 的 rfile#,
22bit 的 block#,
16 bit 的 row#.
所以每個表空間不能超過1023個 數據文件。
四、RowID的應用
1。准備數據：
當試圖對庫表中的某一列或幾列創建唯一索引時，
系統提示 ORA-01452 ：不能創建唯一索引，發現重復記錄。
,id,name from (
select * from student order by name
);
ROWNUM ID NAME
---------- ------ ---------------------------------------------------
1 200003 李三
2 200002 王二
3 200001 張一
4 200004 趙四
這樣就成了按name排序，並且用rownum標出正確序號（有小到大）
筆者在工作中有一上百萬條記錄的表，在jsp頁面中需對該表進行分頁顯示，便考慮用rownum來作，下面是具體方法(每頁顯示20條)：
“select * from tabname where rownum<20 order by name" 但卻發現oracle卻不能按自己的意願來執行，而是先隨便取20條記錄，然後再order by，後經咨詢oracle,說rownum確實就這樣，想用的話，只能用子查詢來實現先排序，後rownum，方法如下：
"select * from (select * from tabname order by name) where rownum<20",但這樣一來，效率會低很多。
後經筆者試驗，只需在order by 的字段上加主鍵或索引即可讓oracle先按該字段排序，然後再rownum；方法不變：
“select * from tabname where rownum<20 order by name"
取得某列中第N大的行
select column_name from (
select table_name.*,dense_rank() over (order by column desc) rank
from table_name
)
where rank = &N；
假如要返回前5條記錄：
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9條記錄：
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
選出結果後用name排序顯示結果。(先選再排序)
注意：只能用以上符號(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前９條記錄。
不能用：>,>=,=,Between...and。由於rownum是一個總是從1開始的偽列，Oracle 認為這種條件不成立。
另外，這個方法更快：
select * from (
select rownum r,a from yourtable where rownum <= 20
order by name
)
where r > 10
這樣取出第11-20條記錄!(先選再排序再選)
要先排序再選則須用select嵌套：內層排序外層選。
rownum是隨著結果集生成的，一旦生成，就不會變化了；同時,生成的結果是依次遞加的，沒有1就永遠不會有2!
rownum 是在查詢集合產生的過程中產生的偽列，並且如果where條件中存在 rownum 條件的話，則:
1： 假如判定條件是常量，則：
只能 rownum = 1, <= 大於1 的自然數， = 大於1 的數是沒有結果的；大於一個數也是沒有結果的
即 當出現一個 rownum 不滿足條件的時候則 查詢結束 this is stop key（一個不滿足，系統將該記錄過濾掉，則下一條記錄的rownum還是這個，所以後面的就不再有滿足記錄，this is stop key）；
2： 假如判定值不是常量，則：
若條件是 = var , 則只有當 var 為1 的時候才滿足條件，這個時候不存在 stop key ,必須進行full scan ,對每個滿足其他where條件的數據進行判定，選出一行後才能去選rownum=2的行……
以下摘自《中國IT實驗室》
1.在oracle中實現select top n
由於oracle不支持select top語句，所以在oracle中經常是用order by跟rownum的組合來實現select top n的查詢。
簡單地說，實現方法如下所示：
select 列名１．．．列名ｎ from
(select 列名１．．．列名ｎ from 表名 order by 列名１．．．列名ｎ)
where rownum<=n（抽出記錄數）
order by rownum asc
下面舉個例子簡單說明一下。
顧客表customer(id,name)有如下數據：
ID NAME
01 first
02 Second
03 third
04 forth
05 fifth
06 sixth
07 seventh
08 eighth
09 ninth
10 last
則按NAME的字母順抽出前三個顧客的SQL語句如下所示：
select * from
(select * from customer order by name)
where rownum<=3
order by rownum asc
輸出結果為：
ID NAME
08 eighth
05 fifth
01 first
序列
可以保證多個用戶對同一張表進行操作時生成唯一的整數，通常用來做表的主鍵。
創建序列：
create sequence <序列名字>
start with <起始值>
increment by <增長值>
[MaxValue <最大值>]
[NoMaxValue] //沒有上限
例如：
create sequence mySeq
start with 1
increment 1
刪除序列：
drop sequence <序列名字>
修改序列:
alter sequence <序列名字>
[start with <起始值>]
[increment by <增長值>]
[MaxValue <最大值>]
查看序列：
使用下列視圖之一：
Dba_Sequences
All_ Sequences
User_ Sequences
訪問序列：
CurVal 返回序列的當前值
NextVal 返回序列的下一個值
例如：select mySeq.NextVal,city from post
Connect by 語句
該語句結合偽列rownum或level 可以產生一個結果集.
1. 基本用法：
產生1~~100之間的整數
Select rownum xh from dual connect by rownum<=100;
Select level xh from dual connect by level<=100;
2. 高級用法
2.1.產生所有漢字，漢字內碼為：19968~~~40869之間
select t.* from(
select rownum xh,nchr(rownum) hz from dual
connect by rownum<65535
) t
where t.xh between 19968 and 40869
2.2.查找某個漢字的內碼
使用CTE：
with myChinese as(
select t.* from(
select rownum xh,nchr(rownum) hz from dual
connect by rownum<65535
) t
where t.xh between 19968 and 40869
)
select * from myChinese where hz='東' –查找漢字'東'的內碼
2.3.拆分字符串
with t as (select '中華人民共和國' sentence from dual)
select substr(sentence,rownum,1) from t
connect by rownum<=(select length(sentence) from t)
--order by NLSSORT(substr(sentence,rownum,1) , 'NLS_SORT=SCHINESE_STROKE_M');--按筆畫排序
一、集合操作
UNION 由每個查詢選擇的所有不重復的行 並集不包含重復值
UNION ALL 由每個查詢選擇的所有的行，包括所有重復的行 完全並集包含重復值
INTERSECT 由每個查詢選擇的所有不重復的相交行 交集
MINUS 在第一個查詢中，不在後面查詢中，並且結果行不重復 差集
所有的集合運算與等號的優先級相同，如果SQL語句包含多個集合運算並且沒有圓括號明確地指定另一個順序，Oracle服務器將以從左到右的順序計算。你應該使用圓括號來明確地指定帶另外的集合運算的INTERSECT (相交) 運算查詢中的賦值順序。
Union all 效率一般比union高。
1.1.union和union all
UNION(聯合)運算
UNION運算返回所有由任一查詢選擇的行。用UNION運算從多表返回所有行，但除去任何重復的行。
例：
Sql代碼
select e1.empno,e1.ename,e1.mgr from emp e1 union select e2.empno,e2.job,e2.sal
from emp e2
select e1.empno,e1.ename,e1.mgr from emp e1 union select e2.empno,e2.job,e2.sal
from emp e2
原則 :
(1)被選擇的列數和列的數據類型必須是與所有用在查詢中的SELECT語句一致。列的名字不必相同。
(2)聯合運算在所有被選擇的列上進行。
(3)在做重復檢查的時候不忽略空(NULL)值。
(4)IN運算有比UNION運算高的優先級。
(5)在默認情況下，輸出以SELECT子句的第一列的升序排序。 在例子中將輸出empno,ename,mgr三列數據。
全聯合(UNION ALL)運算
用全聯合運算從多個查詢中返回所有行。
原則：
(1)和聯合不同，重復的行不被過濾，並且默認情況下輸出不排序。
不能使用DISTINCT關鍵字。 (2)
使用：
Select statement union | union all Select statement;
1.2.intersect交集操作
相交運算
用相交運算返回多個查詢中所有的公共行。 無重復行。
原則:
(1).在查詢中被 SELECT 語句選擇的列數和數據類型必須與在查詢中所使用的所有的 SELTCT 語句中的一樣，但列的名字不必一樣。
相交的表的倒序排序不改變結果。 (2).
相交不忽略空值。 (3).
使用：
Select statement intersect all Select statement;
1.3. minus差集操作
相減運算
用相減運算返回由第一個查詢返回的行，那些行不出現在第二個查詢中 (第一個SELECT語句減第二個SELECT語句)。
原則:
(1)在查詢中被SELECT語句選擇的列數和數據類型必須與在查詢中所使用的所有的SELTCT語句中的一樣，但列的名字不必一樣。
(2)對於MINUS運算，在WHERE子句中所有的列都必須在SELECT子句中。
集合運算的原則
•在兩個SELECT列表中的表達式必須在數目上和數據類型上相匹配
•可以用圓括號改變執行的順序
•ORDER BY子句：–只能出現在語句的最後–從第一個SELECT語句接收列名、別名，或者位置記號
注：•除了UNION ALL，重復行自動被清除
•在結果中的列名是第一個查詢中出現的列名
•除了UNION ALL，默認情況下按升序順序輸出
二、exists和not exists的使用
1. 謂詞exists和in概述
Exists用於只能用於子查詢，可以替代in，若匹配到結果，則退出內部查詢，並將條件標志為true，傳回全部結果資料.
in不管匹配到匹配不到都全部匹配完畢.
使用exists可以將子查詢結果定為常量，不影響查詢效果，而且效率高。如查詢所有銷售部門員工的姓名，對比如下：
IN is often better if the results of the subquery are very small
When you write a query using the IN clause, you're telling the rule-based optimizer that you want the inner query to drive the outer query.
When you write EXISTS in a where clause, you're telling the optimizer 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 specify a join condition, which can invoke an INDEX scan. However, IN is often better if the results of the subquery are very small. You usually want to run the query that returns the smaller set of results first.
2.In和exists使用原則：
2.1.若子查詢結果集比較小，優先使用in。
2.2.若外層查詢比子查詢小，優先使用exists。因為若用in，則oracle會優先查詢子查詢，然後匹配外層查詢，若使用exists，則oracle會優先查詢外層表，然後再與內層表匹配。最優化匹配原則，拿最小記錄匹配大記錄。
使用in
select last_name, title
from s_emp
where dept_id in
(select id
from s_dept
where name='Sales');
使用exists
select last_name,title
from s_emp e
where exists
(select 'x' --把查詢結果定為constant，提高效率
from s_dept s where s.id=e.dept_id and s.name='Sales');
2.3 not exists的使用
與exists 含義相反，也在子查詢中使用，用於替代not in。其他一樣。如查詢不在銷售部的員工姓名
select last_name,title
from s_emp e
where not exists
(select 'x' --把查詢結果定為constant，提高效率
from s_dept s where s.id=e.dept_id and s.name='Sales');
3．with子句
Oracle9i新增語法
1.使用with子句可以讓子查詢重用相同的with查詢塊,通過select調用，一般在with查詢用到多次情況下。
2.with子句的返回結果存到用戶的臨時表空間中，只做一次查詢，提高效率。
3.有多個查詢的時候，第1個用with，後面的不用with，並且用逗號隔開。
4.最後一個with子句與下面的查詢之間不能有逗號，只通過右括號分割,查詢必須用括號括起來
5.如果定義了with子句，而在查詢中不使用，那麼會報ora-32035錯誤：未引用在with子句中定義的查詢名。（至少一個with查詢的name未被引用，解決方法是移除未被引用的with查詢）
6.前面的with子句定義的查詢在後面的with子句中可以使用。
With子句目的是為了重用查詢。
語法：
With alias_name as (select1), --as和select中的括號都不能省略
alias_name2 as (select2),--後面的沒有with，逗號分割
…
alias_namen as (select n) –與下面的查詢之間沒有逗號
Select ….
如查詢銷售部門員工的姓名：
--with clause
with a as
(select id from s_dept where name='Sales' order by id)
select last_name,title
from s_emp where dept_id in (select * from a);--使用select查詢別名
例1：查詢cityInfo表同一省中具有最小id和最大id的城市信息
方法1：
with sta as (
select province,min(id) minValue,max(id) maxValue
from cityInfo
group by province
)
select c.* from cityInfo c,sta
where (c.province=sta.province)
and(c.id=sta.minValue or c.id=sta.maxValue)
order by c.province
方法2：
with sta as ( select province,min(id) minValue,max(id) maxValue from cityInfo group by province)
select * from cityInfo c where (province,id) in (select province,minValue from sta)
or (province,id) in (select province,maxValue from sta)
order by province
方法3：
with sta as ( select province,min(id) minValue,max(id) maxValue from cityInfo group by province)
select c.* from cityInfo c
inner join sta
on (c.province=sta.province) and(c.id=sta.minValue or c.id=sta.maxValue)
order by c.province
方法4：(感覺速度慢！,也許是因為外查詢記錄太多？)
with sta as ( select province,min(id) minValue,max(id) maxValue from cityInfo group by province)
select c.* from cityInfo c
where exists(
select 1 from sta where
(c.province=sta.province) and(c.id=sta.minValue or c.id=sta.maxValue)
)
order by c.province
例2：查詢出部門的總薪水大於所有部門平均總薪水的部門。部門表s_dept，員工表s_emp。
分析：做這個查詢，首先必須計算出所有部門的總薪水，然後計算出總薪水的平均薪水，再篩選出部門的總薪水大於所有部門總薪水平均薪水的部門。那麼第1步with查詢查出所有部門的總薪水，第2步用with從第1步獲得的結果表中查詢出平均薪水，最後利用這兩次的with查詢比較總薪水大於平均薪水的結果，如下：
方法1：
--step1:查詢出部門名和部門的總薪水
with dept_costs as(
select a.dname,sum(b.sal) dept_total
from dept a,emp b
where a.deptno=b.deptno
group by a.dname
),
--step2:利用上一個with查詢的結果，計算部門的平均總薪水
avg_costs as(
select sum(dept_total)/count(*) dept_avg
from dept_costs
)
--step3:從兩個with查詢中比較並且輸出查詢結果
select dname,dept_total
from dept_costs
where dept_total> (
select dept_avg
from avg_costs
)
order by dname
with
--step1:查詢出部門名和部門的總薪水
dept_costs as(
select a.dname,sum(b.sal) dept_total
from
dept a,emp b
where a.deptno=b.deptno
group by a.dname
),
--step2:利用上一個with查詢的結果，計算部門的平均總薪水
avg_costs as(
select sum(dept_total)/count(*) dept_avg
from dept_costs
)
--step3:從兩個with查詢中比較並且輸出查詢結果
select dname,dept_total
from dept_costs
where
dept_total>
(
select dept_avg
from
avg_costs
)
order by dname;
從上面的查詢可以看出，前面的with查詢的結果可以被後面的with查詢重用，並且對with查詢的結果列支持別名的使用，在最終查詢中必須要引用所有with查詢，否則會報錯ora-32035錯誤.
方法2：
with a as(
select avg(sum(sal)) as avg_sal from emp group by deptno
)
Select * from (select deptno ,sum(sal) as total2 from emp group by deptno )
where total2 >(select a.avg_sal from a)
with a as(
select avg(sum(sal)) as avg_sal from emp group by deptno
)
Select * from (select deptno ,sum(sal) as total2 from emp group by deptno )
where total2 >(select a.avg_sal from a)
注意：列別名不能在where中使用.
例3：找出平均成績大於各班最小平均成績的班名及其該班平均成績
--各班最小的平均分
with minAvgScore as
(
select min(avgScore) minValue from (
select class,avg(score) avgScore from studentscore group by class
)
)
select class,avg(score) avgScore
from studentscore
group by class
having avg(score)>(select minValue from minAvgScore)
例4：一個查詢，如果查詢的結果行不滿足是10的倍數，則補空行，直到是查詢出的行數是10的倍數。例如：select * from trademark這個查詢。
select 10-mod(count(*),10) shumu from trademark 返回表trademark 中的總行數差幾個才為10的倍數。
with cnt as (select 10-mod(count(*),10) shumu from trademark) –查詢比10的倍數差幾個空行
--Oracle 10g寫法：
select id,name from trademark
union all --空行加進去
select null,null --補空行
from dual connect by rownum<=(select shumu from cnt); --10個中connect by可以使用子查詢
Oracle 9i 寫法：
with cnt as (select 10-mod(count(*),10) shumu from trademark) –查詢比10的倍數差幾個空行
select id,name
from trademark
union all --空行加進去
select null,null --補空行
from all_objects where rownum<=(select shumu from cnt);--使用all_objects行比較多
其中：all_objects 為一個系統視圖，大約有40000條記錄
