Oracle偽列和偽表

Oracle的偽列以及偽表

   oracle系統為了實現完整的關聯式資料庫功能，系統專門提供了一組成為偽列（Pseudocolumn）的資料庫列，這些列不是在建立對象時由我們完成的，而是在我們建立時由Oracle完成的。Oracle目前有以下偽列：

一、偽列：

  CURRVAL AND NEXTVAL 使用序號的保留字

  LEVEL 查詢資料所對應的層級

  ROWID 記錄的唯一標識

  ROWNUM 限制查詢結果集的數量

 Rowid的概念：rowid是一個偽列，既然是偽列，那麼這個列就不是使用者定義，而是系統自己給加上的。對每個表都有一個rowid的偽列，但是表中並不實體儲存體ROWID列的值。不過你可以像使用其它列那樣使用它，但是不能刪除改列，也不能對該列的值進行修改、插入。一旦一行資料插入資料庫，則rowid在該行的生命週期內是唯一的，即即使該行產生行遷移，行的rowid也不會改變。

 ROWNUM是一個序列，是oracle資料庫從資料檔案或緩衝區中讀取資料的順序。它取得第一條記錄則rownum值為1，第二條為2，依次類推。如果你用>,>=,=,between...and這些條件，因為從緩衝區或資料檔案中得到的第一條記錄的rownum為1，則被刪除，接著取下條，可是它的rownum還是1，又被刪除，依次類推，便沒有了資料。

二、偽表

DUAL 表

該表主要目的是為了保證在使用SELECT語句中的語句的完整性而提供的。

一般用於驗證函式。例如：

select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mm:ss') from dual

oracle的幾個偽列函數ORACLE有幾個函數專門用來產生偽列的,rownum,rowid,row_number(),rank,dense_rank,lan

 1 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 rownum按行的順序自動增加產生

row_number() 給每個組內的不同記錄進行排號(分組可不設)

1. select t.c_group,  
2.        t.c_code,  
3.        t.c_desc,  
4.        <span style="color:#ff0000;">row_number</span>() over(PARTITION BY c_group ORDER BY c_desc DESC) rn  
5.   from tp_dictionary t  
6.  where t.c_group in ('PPSJ001', 'PPSJ002');  

 3 Rank() 按並列情況跨越排序

1. select t.c_group,  
2.        t.c_code,  
3.        t.c_desc,  
4.        <span style="color:#ff0000;">RANK</span>() over( ORDER BY c_code ) rn  
5.   from tp_dictionary t  
6.  where t.c_group in ('PPGL001', 'PPSJ002');  

 4 Dense_Rank() 按並列情況排序，不跳躍

1. select t.c_group,  
2.        t.c_code,  
3.        t.c_desc,  
4.        <span style="color:#ff0000;">Dense_RANK</span>() over( ORDER BY c_code ) rn  
5.   from tp_dictionary t  
6.  where t.c_group in ('PPGL001', 'PPSJ002');  

 5 Lag對列進行位移(下面是位移2列)

1. select t.c_group,  
2.        t.c_code,  
3.        t.c_desc,  
4.        lag(c_code,2,null) over(PARTITION BY c_group ORDER  
5.  BY c_desc asc) rn  
6.   from tp_dictionary t  
7.  where t.c_group in ('PPGL001', 'PPSJ002');