Oracle Connect By 使用執行個體

在實際開發中  connect by可以替代plsql或java中的迴圈間化開發代碼，提高開發效率。如下是我在工作中遇到一個實際解決的問題

Oracle CONNECT BY的用法 

Oracle遞迴START WITH...CONNECT BY PRIOR子句用法

Oracle 有表串連的connect by 的最佳化

Oracle 樹查詢 效能最佳化紀實（start with, connect by）

Oracle Database 10g 中新的 CONNECT BY 特性

具體情境：

原系統是一個管理宿舍資訊的系統,dorm_room用於存放宿舍的房間資訊具體表結構是
 create table dorm_room(bno varchar2(2),fno varchar2(2),rno varchar2(2),bednum varchar2(2));
 這裡存放了樓棟號，層號，房間號，及房間中床位總數
 具體資料如下
 insert into dorm_room values(1,1,1,4);
 insert into dorm_room values(1,1,2,4);
 insert into dorm_room values(1,1,3,7);
 也就是說有三個房間，前兩個房間床位元都是4，最後一個房間的床位元是7
 目前的需求是根據床位元顯示出每個房間中所有床位的資料。期望結果是
 1  1  1  1
 1  1  1  2
 1  1  1  3
 1  1  1  4
 1  1  2  1
 1  1  2  2
 1  1  2  3
 1  1  2  4
 1  1  3  1
 1  1  3  2
 1  1  3  3
 1  1  3  4
1  1  3  5
 1  1  3  6
 1  1  3  7

如果用pl/sql根據每行的床位元判斷迴圈次數，顯示出每個房間的所有床位元，編寫起來必然要書寫較多行代碼，因此選用
 sql中的connect by的方式產生出所有的房間的床位元，具體sql為

select distinct bno,fno,rno,t.l
 from (
 select row_number()over(partition by bno,fno,rno order by level) rn,
        bno,fno,rno,level l
 from dorm_room
 connect by level<=bedno
 ) t
 order by bno,fno,rno

顯示結果

BN FN RN          L
 -- -- -- ----------
 1  1  1          1
 1  1  1          2
 1  1  1          3
 1  1  1          4
 1  1  2          1
 1  1  2          2
 1  1  2          3
 1  1  2          4
 1  1  3          1
 1  1  3          2
 1  1  3          3

BN FN RN          L
 -- -- -- ----------
 1  1  3          4
 1  1  3          5
 1  1  3          6
 1  1  3          7