In actual development, connect by can replace the cyclical code in plsql or java to improve development efficiency. The following is a problem I encountered during my work
Usage of Oracle CONNECT
Oracle recursive start with... connect by prior clause usage
Optimization of connect by with table connection in Oracle
Oracle tree query performance optimization documentary (start with, connect)
New connect by feature in Oracle Database 10g
Specific scenarios:
The original system is a system for managing dormitory information. The specific table structure of dorm_room used to store dormitory room information is
Create table dorm_room (bno varchar2 (2), fno varchar2 (2), rno varchar2 (2), bednum varchar2 (2 ));
The building number, floor number, room number, and total number of beds in the room are stored here
The specific data is as follows:
Insert into dorm_room values (1, 1, 4 );
Insert into dorm_room values (1, 1, 2, 4 );
Insert into dorm_room values (1, 1, 3, 7 );
That is to say, there are three rooms, the first two rooms have 4 beds, and the last room has 7 beds.
The current requirement is to display the data of all the beds in each room based on the number of beds. Expected result:
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
If pl/SQL is used to determine the number of cycles based on the number of beds in each row and show the number of beds in each room, many lines of code must be written.
The connect by method in SQL generates the number of beds in all rooms. The specific SQL is
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
Display result
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