Oracle Database 10g 中新特性
2024-08-29 13:41:09
供稿：網友
這篇文章具體說明了 Oracle Database 10g 中三個新的 CONNECT BY 特性。這些示例使用的表和數據與我之前的文章中所用的表和數據相同。您可能希望讀那篇文章來重溫一下 CONNECT BY 查詢的工作方式。
問題的起源
假如您閱讀了我先前的文章，則您已經了解了如何在層次查詢中使用 PRIOR 操作符來從一個父行返回列數據。能夠返回到根行有時是很方便的。Oracle Database 10g 中新增的 CONNECT_BY_ROOT 操作符使您能夠從一個層次結構中的任何位置引用根行的值。
CONNECT_BY_ROOT 的一個用途是用來識別所有包含了一個給定部分的產品。設想您為一個制造公司工作。您發現 1019 號部件有缺陷，消費品安全委員會命令您收回已售出的所有包含了該部件的產品。您的第一個任務是確定您擔心的是哪些產品。您可以通過發出以下查詢來開始，作為第一步工作：
SELECT assembly_id, assembly_name
FROM bill_of_materials
WHERE part_number = 1019
START WITH parent_assembly IS NULL
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME
----------- -----------------------
141 Lock
144 Lock
這些結果看起來似乎是合理的，但它們存在一個問題。1019 號部件是一個鎖，但事實上它被用在兩個鎖組件中。然而，那些鎖組件隨后被用在左邊和右邊的門組件內，門組件隨后又被用在一個車身組件中，車身組件最后被用來制造一輛汽車，而我們售出的和要關心的是汽車。您不想要 1019 號部件的直接父組件；您想要 1019 號部件最終的父親。幸運的是，您的數據庫剛剛升級到 Oracle Database 10g，因此您可以利用新的 CONNECT_BY_ROOT 操作符：
SELECT DISTINCT CONNECT_BY_ROOT assembly_id,
CONNECT_BY_ROOT assembly_name
FROM bill_of_materials
WHERE part_number = 1019
START WITH parent_assembly IS NULL
CONNECT BY parent_assembly = PRIOR assembly_id;
CONNECT_BY_ROOTASSEMBLY_ID CONNECT_BY_ROOTASSEMBLY
-------------------------- -----------------------
100 Automobile
該查詢大部分和以前相同。只有兩點區別：使用了DISTINCT；在 SELECT 列表中的每一個列名前面出現了 CONNECT_BY_ROOT。CONNECT_BY_ROOT 操作符輸出我們關心的那個部件的最終父組件 ID 和名稱。DISTINCT 要害字防止一個產品在多個組件中包含了相同部件時被多次列出。因此，結果是您的汽車在它的右邊和左邊的門里都包含了鎖。
尋根究底
層次數據經常是深層嵌套的。考慮這樣一個問題：為機械師提供一輛汽車中的組件和部件的一個嵌套列表。汽車包含大量的部件。機械師很少希望馬上了解所有組件和部件的詳情。那樣的一個列表不僅將使人不知所措，而且當用戶只需要該數據的一部分時，從數據庫檢索那樣一個組件和部件的完整列表以及跨網絡傳遞那樣的信息也是非常低效的。相反，您可能選擇一開始僅提供頂層的組件，然后讓用戶從那里開始深入到層次結構內部。例如，您可能一開始提供給我們的用戶下列查詢的結果：
SELECT ASSEMBLY_ID,
RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,
quantity
FROM bill_of_materials
WHERE LEVEL <= 2
START WITH assembly_id = 100
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME QUANTITY
----------- ------------- ----------
100 Automobile
110 Combustion Engine 1
120 Body 1
130 Interior 1
通過查看第一級的組件，我們的用戶現在能夠確定他們是否要進一步向下查看。當有更多的數據要查看時，可以通過將組件名實現為 Web 鏈接來實現向下查看，或者您可以像 Windows 應用中常見的那樣實現一個樹狀控制。且慢！您怎么知道什么時候一個組件會有更多的數據？什么時候向下查看是可能的？當用戶試圖從該層次結構的底部向下查看時，您可以讓他們試著向下查看到任何組件中，然后給他們一條“沒有更多數據”的消息，但這是一種生硬的解決辦法，無疑將使他們感到灰心。最好能夠提前了解向下查看是否可能。Oracle Database 10g 使我們能夠通過 CONNECT_BY_ISLEAF 虛擬列來達到這一目的。
您可以使用以下查詢來開始：
SELECT ASSEMBLY_ID,
RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,
quantity, CONNECT_BY_ISLEAF
FROM bill_of_materials
WHERE LEVEL <= 2
START WITH assembly_id = 100
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME QUANTITY CONNECT_BY_ISLEAF
----------- ----------------------- ---------- -----------------
100 Automobile 0
110 Combustion Engine 1 0
120 Body 1 0
130 Interior 1 0
CONNECT_BY_ISLEAF 返回的零指示在該列表中顯示的組件中沒有一個是葉節點。換句話說，向下查看到它們的任何一個中都是無效的。假定用戶向下查看到 Combustion Engine 中。那么您可以發出以下查詢來獲取組成發動機的子組件：
SELECT ASSEMBLY_ID,
RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,
quantity, CONNECT_BY_ISLEAF
FROM bill_of_materials
WHERE LEVEL = 2
START WITH assembly_id = 110
CONNECT BY parent_assembly = PRIOR assembly_id;
ASSEMBLY_ID ASSEMBLY_NAME QUANTITY CONNECT_BY_ISLEAF
----------- ----------------------- ---------- -----------------
111 Piston 6 1
112 Air Filter 1 1
113 Spark Plug 6 1
114 Block 1 1
115 Starter System 1 0
該查詢和以前幾乎是相同的。Combustion Engine 的 START WITH 組件 ID 值變為 110，該查詢非凡請求 LEVEL = 2。在這個節點上，您不需要 LEVEL = 1，因為那將再次返回 Combustion Engine 的行，而您已經得到那一行了。
這次，您看到 CONNECT_BY_ISLEAF 有兩個值。Piston、Air Filter、Spark Plug 和 Block 的值為 1 指示那些組件是葉節點，且其下沒有發現更多的組件。了解到這一點，您就可以調整我們的顯示內容，這樣用戶就知道不用徒勞地在那些組件上向下鉆取。另一方面，Starter System 的 CONNECT_BY_ISLEAF 值為 0，這指示仍然存在要檢索的子組件。
跳出循環
只要您利用層次數據進行工作，您就可能碰到一個循環的層次。比如說，有人可能將一輛汽車的父組件設為一個火花塞：
UPDATE bill_of_materials
SET parent_assembly = 113
WHERE assembly_id=100;
嘗試在該組件樹中查詢 "Automobile" 現在就將失敗：
SELECT RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,
quantity
FROM bill_of_materials
START WITH assembly_id = 100
CONNECT BY parent_assembly = PRIOR assembly_id;
ERROR:
ORA-01436:CONNECT BY loop in user data
當您獲得像這樣的一條錯誤消息時，您可以使用 CONNECT_BY_ISCYCLE 虛擬列來確定引起問題的行的位置。要做到這一點，您還必須添加 NOCYCLE 要害字到 CONNECT BY 子句中，防止數據庫進入層次結構中的任何循環：
SELECT RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,
quantity, CONNECT_BY_ISCYCLE
FROM bill_of_materials
START WITH assembly_id = 100
CONNECT BY NOCYCLE parent_assembly = PRIOR assembly_id;
ASSEMBLY_NAME QUANTITY CONNECT_BY_ISCYCLE
------------------------------ ---------- ------------------
Automobile 0
Combustion Engine 1 0
Piston 6 0
Air Filter 1 0
Spark Plug 6 1
Block 1 0
注重，CONNECT_BY_ISCYCLE 為 "Spark Plug" 行返回一個 1。
當您使用 NOCYCLE 時，數據庫通過層次跟蹤它的路徑，不斷檢查確保它不會進入循環。在完成從 "Automobile" 到 "Combustion Engine" 再到 "Spark Plug" 的遍歷后，數據庫發現 "Spark Plug" 的孩子是 "Automobile"，該行所處的路徑通向 "Spark Plug"。這樣的一行代表一個循環。NOCYCLE 防止數據庫進入循環，CONNECT_BY_ISCYCLE 返回一個 1 來指示出現循環的行
