Oracle Connect By instance

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.