connect by 使用執行個體

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

具體場景：

原系統是一個管理宿舍資訊的系統,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

) 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

1  1  3           4

1  1  3           5

1  1  3           6

1  1  3           7