目錄：
=========================================
1.使用偽列Level顯示表中節點的層次關系
2.統計表中節點的層數
3.統計表中各個層次的節點數量
4.查找表中各個層次的節點信息
5.在Start with中使用子查詢
6.判斷節點和節點之間是否具有層次關系
7.刪除級聯表中的子樹
一、使用偽列Level顯示表中節點的層次關系：
Oracle9i對級聯查詢的支持不僅在于提供了像Start with...Connect by這樣的子句供我們很方便地執行查詢，而且還提供了一個偽列(Pseudocolumn): Level。這個偽列的作用是在遞歸查詢的結果中用來表示節點在整個結構中所處的層次。下面我們來看看實際的例子：
還是上次那個employee表，現在我們要在上次的需求上面增加點小玩意：輸出每個節點的層次值，看如下SQL：
SQL> select level, id, emp_name, manager_id from employee start with id = 2 connect by prior id = ma
nager_id order by id;
LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
1 2 mark 1
2 4 tom 2
2 5 paul 2
3 7 ben 4
SQL>
我們可以看到在LEVEL列，輸出了1,2,2,3的值，這就是Oracle為我們提供的一個偽列。此偽列只能用在start with...connect by子句中，下面我們來看另一種方式是否可行：
SQL> select level, p.* from (select * from employee start with id = 2 connect by prior id = manager_
id order by id) p;
LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
0 2 mark 1
0 4 tom 2
0 5 paul 2
0 7 ben 4
SQL>
可以看到Level列的值全部變成了0，可見在這里Oracle并不認為虛表P里面的數據是“層次關系”，因而對于Level都返回0
二、統計表中節點的層數：
假設現在我們想看一下當前employee表中員工總共分為幾個級別，我們應該如何做呢？請看下面的SQL
SQL> select * from employee;
ID EMP_NAME MANAGER_ID
---------- -------------------- ----------
1 king
2 mark 1
3 bob 1
4 tom 2
5 paul 2
6 jack 3
7 ben 4
7 rows selected.
SQL>
SQL>
SQL> select count(level) from employee start with manager_id is null connect by prior id = manager_i
d;
COUNT(LEVEL)
------------
7
SQL>
SQL> select count(distinct level) from employee start with manager_id is null connect by prior id =
manager_id;
COUNT(DISTINCTLEVEL)
--------------------
4
從這里我們可以看到，在統計的時候一定要使用distinct關鍵字，否則得到的錯誤的結果。
三、統計表中各個層次的節點數量：
假設我們想知道employee表中每個級別的員工數量，我們應該如何做呢--對了，使用Level和group by子句了
SQL> select level, count(level) from employee start with manager_id is null connect by prior id = ma
nager_id group by level;
LEVEL COUNT(LEVEL)
---------- ------------
1 1
2 2
3 3
4 1
四、查找表中各個層次的節點信息：
上面的例子很簡單，我們看到Level可以用在group by子句中，現在我們更進一步，查看指定層次的員工信息，比如說我現在打算查看Level=2的所有員工的記錄，應該如何做呢？很自然地我們想到了第一個SQL語句：
SQL> select level, id, emp_name, manager_id from employee where level >= 2;
no rows selected
很奇怪吧，這這里level關鍵字就不起作用了，這是因為level偽列只能在和start with...connect by子句結合時才能發揮作用，就想上面的統計各層節點數量一樣，于是我們又立馬想到了第二個SQL語句：
select *
from (select level, id, emp_name, manager_id
from employee
start with manager_id is null
connect by prior id = manager_id
order by id) p
where p.level = 2
看起來這個句子沒有什么問題吧，實際執行的效果如何呢？我們在SQL*PLUS下執行，結果卻是報錯：
ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification
很郁悶！為什么會報p.level不可識別呢？這是因為level是Oracle的偽列，并不屬于任何一個表，我們必須使用別名把這個偽列“偽裝”成一個實際的列，現在我們看第三個語句，注意語句高亮處。
SQL> select *
2 from (select level emp_level , id, emp_name, manager_id
3 from employee
4 start with manager_id is null
5 connect by prior id = manager_id
6 order by id) p
7 where p.emp_level = 2;
EMP_LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
2 2 mark 1
2 3 bob 1
SQL>
這次終于搞定了！不過實際上我們有更簡單的解決方法，請看第四個SQL語句：
SQL> select level, id, emp_name, manager_id
2 from employee
3 where level = 2
4 start with manager_id is null
5 connect by prior id = manager_id
6 order by id;
LEVEL ID EMP_NAME MANAGER_ID
---------- ---------- -------------------- ----------
2 2 mark 1
2 3 bob 1
上面我們是查看某個層次的所有節點信息，現在我們打算看看所有層次的節點信息，而且要求用一種直觀的信息顯示出來。下面的例子演示了如何使用空格縮進的方式來直觀顯示節點之間的層次關系：
SQL> select level, id, lpad(' ', 2 * (level - 1)) || emp_name name, manager_id
2 from employee
3 start with manager_id is null
4 connect by prior id = manager_id;
LEVEL ID NAME MANAGER_ID
---------- ---------- -------------------- ----------
1 1 king
2 2 mark 1
3 4 tom 2
4 7 ben 4
3 5 paul 2
2 3 bob 1
3 6 jack 3
7 rows selected.
請注意這里的lpad函數的作用，正是它利用了層次和空格進行縮進，讓我們可以很直觀地從NAME字段對齊方式就知道各個節點的層次關系。如果我們需要過濾其中的某些節點，只需要將where條件加在start with前面就可以了(注意必須是前面，否則會報語法錯誤)。
五、在Start with中使用子查詢：
在前面我們看到的例子中，start with的值都是一個固定的內容，但有些時候查詢的起始點并不容易確定，比如：查詢工號最小的員工節點及其子節點，這個時候工號最小很明顯是一個查詢的條件，需要我們先通過執行一個查詢得到確定的值，再作為查詢的起點。請看例子：
SQL> select level, id, lpad(' ', 2 * (level - 1)) || emp_name name, manager_id
2 from employee
3 start with id = (select min(id) from employee)
4 connect by prior id = manager_id;
LEVEL ID NAME MANAGER_ID
---------- ---------- -------------------- ----------
1 1 king
2 2 mark 1
3 4 tom 2
4 7 ben 4
3 5 paul 2
2 3 bob 1
3 6 jack 3
7 rows selected.
六、判斷節點和節點之間是否具有層次關系：
在日常工作中除了查詢節點的信息之外，另一個常見的應用就是判斷某個節點和另外一個/些節點之間是否具有層次關系。例如我想知道員工mark是不是員工jack的領導(直接或間接的都可以)，我應該怎么做呢？
考慮到start with...connect by會返回一棵節點樹，假如節點數上沒有jack節點，那么說明mark并不是jack的直接或間接領導，如果找到那說明mark是jack的父節點。方法簡單
SQL> select level,
2 id,
3 lpad(' ', 2 * (level - 1)) || emp_name employee_name,
4 manager_id
5 from employee
6 where emp_name = 'jack'
7 start with emp_name = 'mark'
8 connect by prior id = manager_id;
no rows selected
七、刪除級聯表中的子樹：
假設現在employee表中的mark及其下屬員工離職，那么我們為了維護數據的完整性，必須將mark及其下屬員工的節點都刪除，有了start with...connect by和level我們就可以輕松地做到這一點了。
【1】按名稱刪除節點樹：
SQL> delete from employee
2 where id in (select id
3 from employee
4 start with emp_name = 'mark'
5 connect by prior id = manager_id);
4 rows deleted.
【2】按層次刪除節點樹：
從上面的例子我們知道只需要在第一個SQL的基礎上改變一下：使用level區分節點的層次就做到了。
參考資料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004 0-596-00632-2)
-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。
posted on 2008-06-12 17:45
Paul Lin 閱讀(3957)
評論(0) 編輯 收藏 所屬分類:
Oracle 開發