696
yesterday 772
visitor 23,540,540
71

오라클 고급쿼리 - 계층적 쿼리 ( connect by )

조회 수 138493 추천 수 0 2011.10.20 14:05:50

Connect by 계층적 쿼리는 오라클만이 가진 기능 중 하나로, 데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.

예를 들면,  아래와 같이 직원 테이블이 있다고 생각 하자.

 

직원   직속상사      직급

--------------------

철수     순희         대리

순희     영희        과장

길동     순희        대리

영희     개똥        부장

개똥                   사장

 

기본적인 SQl을 사용하여 계층 관계를 표현하는것은 불가능하다. 하지만 재귀 PL/SQL 루틴connect by 를 사용한다면 표현이 가능하다.

재귀 PL/SQL은개발과 처리 과정에서 다소 많은 시간이 필요로 한다는 단점이 있으며, 변경사항이 있을 때 다른 저장 프로시저를 만들거나 보다 복잡하게 변경해야한다는 점도 무시 할수 없다.

이에 오라클에서는 connect by라는 확장된 select 구문을 지원한다.

 

기본형식

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직원 = '개똥'
connect by 직속상사 = prior 직원

   직원      직급

-------------

개똥         사장
  영희       부장
    순희     과장
      철수   대리
      길동   대리

 

 

start with

select 구문의 start with 절은 계층 구조가 어떤 행에서 시작하는지 지정하는 기능을 한다.

 정의 : start with <조건>

where 절의 내용으로 쓸 수 있는 조건이라면 start with로도 사용이 가능하며, 하나 이상의 조건을 결함하는 것도 가능하다.

 ex) start with 직원 ='개똥'and 직원 ='순희'

start with 적의 조건에 맞는 행은 결과셋의 루트 노드가 된다. 주의할점은 조건에 맞는 행이 한 번 이상 등장할 경우이다.

예를 들면 start with 직원 ='개똥'and 직원 ='순희' 사용하면 개똥 이 순희 하위에 있기 때문에 순희 트리가 두 번 만들어지게 된다.

(한번은 개똥의 하위에서, 그리고 한 번은 루트로서)

 

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직원 = '개똥' or 직원 ='순희'
connect by 직속상사 = prior 직원 
   직원      직급

 

-------------

순희         과장
  철수       대리
  길동       대리
개똥         사장
  영희       부장
   
순희     과장
      철수   대리
      길동   대리

 

같은 결과셋이 여러 번 만들어지는 것을 방지하기 위해서는 이러한 조건을 사용해서는 안 된다.
 

처음 쿼리의 예제에서 직원 ='개똥'이라는 조건을 사용했으며, 이는 회사의 가장 높은 사람을 의미하는 것으로 전체 직원에 대한 목록이 만들어 진다. 하지만 이러한 방법은 그다지 좋지 않다. 왜냐하면, 개똥이 테이블에서 빠져나간다면 새로운 쿼리를 작성하여 직속상사가 의 값이 NULL 인 직원으로 부터 루트 노드가 다시 시작되도록 해야할 것이다.

그러므로, 가능하면 보다 구체적인, 즉 결과셋의 양이 적은 조건을 사용하는 것이 바람직하다. 직원 테이블을 보면 개똥의 직속상사의 값이 NULL로 저장되어 있는데, 이는 개똥이라는 직원이 보고할 사람이 없음을, 즉 가장 최상의 간부임을 의미한다.

 

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직속상사 is null

connect by 직속상사 = prior 직원

 

   직원      직급

-------------

개똥         사장
  영희       부장
    순희     과장
      철수   대리
      길동   대리

 

Connect by Prior

connect by 절은 각 행이 어떻게 연결되는지를 오라클에게 알려주는 역할을 한다. 즉 계층 구조 내에서 각 행의 관계를 설정하는 것이다.

현재 행과 다른 행은 Prior라는 키워드를 통해 구별된다. Prior는 상위 행을 참조하는 것으로, 우리의 예제에서는 다음과 같이 사용되었다.

  connect by 직속상사 = prior 직원

이는 "방금 전 행의 직원 값이 현재 행의 직속상사 값인 모든 행을 찾아라"라는 의미이다.

쉽게 말하면, 방금전에 살펴본 직원이 현재 직원의 상사가 되는 방식으로 리턴하라는 것이다.

다음 예제 코드를 보면, prior 부분이 = 기호를 사이에 두고 반대편으로 건너갔는데, 결과는 다음과 같이 트리를 거슬러 내려가는 것이 아니라, 거슬러 올라가는 방식으로 리턴되었다.

 

select lpad(' ',(level-1)*2,' ')||직원 직원, 직급
  from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원

 

   직원      직급

-------------

철수         대리
  순희       과장
    영희     부장
      개똥   사장

이 쿼리에서는 철수가 루트 노드이며, 그의 상사가 오히려 아래에 표현되어 있다. 그 이유는 " 방금 전 행의 직속상사 값이 현재 행의 직원 값인 모든 행을 찾아라"라고 선언했기 때문이다. 이와 같이 prior 키워드를 등호의 반대편으로 넣어도 오류가 발생하지 않고, 전혀 다른 결과가 얻어짐을 알 수 있다.

 

prior 키워드는 또한 이전 행의 열을 참조하기 위해 다음과 같이 select 절 내에서 사용 될 수도 있다.

 

select lpad(' ',(level-1)*2,' ')||직원 직원, prior 직원 상사,직급
  from 직원
start with 직원 ='철수'
connect by prior 직속상사 = 직원

   직원      상사   직급

-------------------

철수                  대리
  순희       철수   과장
    영희     순희   부장
      개똥   영희   사장

여기서는 직원과 직속상사의 이름을 동시에 선택하였는데, 사실 두 값은 같은 행에 존재하는 것이 아니기 때문에 평범한 방법으로는 이와 같은 결과를 얻을 수 없다. 그래서 예제에서는 두 행을 동시 접근하여 각각 값을 얻어낸 것이다.

 

Level

level은 오라클에서 실행되는 모든 쿼리 내에서 사용 가능한 가상-열로서, 트리 내에서 어떤 단계(level)에 있는지를 나타내는 정수값이다.

계층적인 쿼리가 아니라면 다음과 같이 모든 값이 0, 즉 같은 단계를 가질 것이다.

 

select 직원,level

  from 직원

 

 직원  level

-----------

 철수    0
 순희    0
 길동     0
 영희     0
 개똥     0

한편, 계층적 쿼리에서는 level의 값을 통해 트리에서의 위치를 확인할 수 있다. 루트 노드의 level 값이 1이다.

 

select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
  from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사

 

   직원      직급   level

-------------------

개똥         사장      1
  영희       부장      2
    순희     과장      3
      철수   대리      4
      길동   대리      4

트리를 한 단계씩 거슬러 내려갈 때마다 값이 1씩 증가함을 알 수 있다.

 

level은 여러 가지 면에서 아주 유용하다. 먼저, 다음과 같이 각 항목을 출력할 때 앞에 붙는 공백의 양을 조절하여 계층적인 형식을 한눈에 알아볼 수 있도록 하는 것이 가능하다.

 

 select lpad(' ',(level-1)*2,' ')||직원 직원

 

또한, level 값이 3까지인 내용만을 출력하라. 등의 명령도 가능하다.

 

select lpad(' ',(level-1)*2,' ')||직원 직원,직급,level
  from 직원
start with 직속상사 is null
connect by prior 직원 = 직속상사 and level <=3

 

   직원      직급   level

-------------------

개똥         사장      1
  영희       부장      2
    순희     과장      3

철수와 길동의 경우는 level 값이 4이기 때문에 출력되지 않았다.

level <=3 이라는 조건을 where 절이 아닌 connect by 절에 넣은 것에 주의해야한다.  어떤 곳에 넣어도 결과는 같지만, where 절에 넣으면 전체 트리를 구성한 후에 다시 선택하는 반면, connect by 절에 넣으면 이 조건을 사용해서 트리를 구성하기 때문에 보다 효과적이라고 할 수 있다.

 

 

'헬로마켓'과 함께하는 스마트한 중고 아이템 거래

https://www.hellomarket.com


4
profile

ㅇㅇ

July 12, 2016
*.216.244.2

혹시 이거 조금만더 쉽게 풀어주실수있으세요 ?

이해하기가 너무어렵네요 ㅠㅠ

profile

glock 42

June 16, 2022
*.0.84.230


<a href="https://gunsincshop.com/" rel="dofollow">glock</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 17</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 26</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 43</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 20</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 43 price</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 23</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 gen 4</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 22</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 40</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 9mm</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 21</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 17 gen 4</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 27</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 30</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 45</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock gen 5</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 18</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 gen 3</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 42</a>
<a href="https://gunsincshop.com/" rel="dofollow">9mm glock</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 price</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 23 gen 4</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 34</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 gen 4</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 gen 3</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 price</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 17 vs 19</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 for sale</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 magazine</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 19 gen 5</a>
<a href="https://gunsincshop.com/" rel="dofollow">glock 17l slide</a>
<a href="https://gunsincshop.com/" rel="dofollow">buy glock 17l</a>
<a href="https://gunsincshop.com/" rel="dofollow"> S&W AR 15 Tactical Package ar 15 M&P ar 15 RED DOT ar 15</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G23 GEN5 MOS COMPACT | .40 S&W</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G27 GEN5 SUBCOMPACT | .40 S&W</a>
<a href="https://gunsincshop.com/" rel="dofollow"> 1000 ROUNDS OF 115GR FMJ 9MM AMMO BY SELLIER & BELLOT</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G17T FX/FOF STANDARD | TRAINING AMMUNITION</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G27 GEN4 SUBCOMPACT | .40 S&W</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G20 SF STANDARD | 10MM AUTO</a>
<a href="https://gunsincshop.com/" rel="dofollow"> SAV 110 ULTRALITE CAMO 308WIN 22″</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G35 GEN4 COMPETITION | .40 S&W GLOCK 35 GEN 4</a>
<a href="https://gunsincshop.com/" rel="dofollow"> SAV 110 ULTRALITE CAMO 308WIN 22″</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G29 SF SUBCOMPACT | 10MM AUTO</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G19 GEN4 MOS COMPACT | 9X19MM</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G17 GEN5 MOS STANDARD | 9X19MM</a>
<a href="https://gunsincshop.com/" rel="dofollow"> G43X BLACK SUBCOMPACT | 9X19MM</a>

profile

Holder

October 06, 2022
*.209.252.231

Pandora Jewelry Pandora Jewelry Pandora Ring Balenciaga Pandora Outlet Asics Jordan Retro 1 Jordan Shoes Nike 270 Air Max Moncler Pandora Jewelry Official Site Pandora Outlet Puma Shoes Jordan Retro 1 Adidas Running Shoes Women Ferragamo Shoes NMD Pandora Wholesale Jordan Shoes Nike Shoes Jordan 1 Retro High Pandora Charms Ferragamo Shoes Women Pandora Outlet Online Air Max 97 Goyard Handbags Dior Jordan 1 Pandora Charms Sale Clearance Pandora Charms Asics Shoes For Men Pandora Bracelet Charms Outlet Moncler Nike Canada Online Moncler Outlet Air Jordan 1 High Nike Store Women Nike Shoes Asics Running Shoes Balenciaga Cheap Jordans Wholesale Goyard Bag Pandora Jewelry Balenciaga Sneakers Pandora Charms Nike Store Pandora Pandora Charms Yeezys Air Jordan 1 Mid Pandora Bracelet Adidas Trainers Pandora Bracelets Jordan 1 Lows Moncler Jacket Jordan Ones Pandora Jewelry Pandora Pandora Jewellery Moncler Jackets Air Jordan 1 Charms Pandora Pandora Jewelry Pandora Jewelry Black Friday New Nike Shoes Nike Air Max Adidas Official Website Pandora Outlet Adidas Yeezy Pandora Charms Outlet Nike Snkrs Canada Pandora Jewelry Adidas Shoes Women Jordan 1 Lows Air Max 270 Men Nike Men Shoes Pandora Charms Outlet Pandora Jewelry Pandora Bracelets Charms Nike Men's Shoes Air Jordan Balenciaga Shoes Men Pandora Charms Jewelry Moncler Coats Nike Outlet Wholesale Jordan Jordan 1s Nike Sneakers For Women Pandora Jewelry Pandora Jewelry Official Site Nike Air Jordan Asics Sneakers For Men Moncler Coats For Women Ferragamo Belts Jordan 1 Nike Canada Online Pandora Jewelry Pandora Nike Air Jordan 1 Balenciaga Air Jordan 1 Retro Salvatore Ferragamo Belt Pandora Official Site Pandora Jewelry Official Site Pandora Charms Moncler Outlet Pandora Charms Asics Shoes Outlet Pandora Official Site Adidas Outlet Store Nike Shoes Women Balenciaga Cheap Moncler Pandora Jewellery Pandora Jewelry Official Site Pandora Charms Pandora Jewelry Charms Jordan Sneakers Mens Adidas Shoes New Nike Shoes Pandora Jewelry Wholesale Jordan Pandora Outlet Moncler Coat Men Pandora Jewelry Womens Basketball Shoes Cheap Moncler Nike UK Adidas Clearance Sale Adidas Balenciaga Triple S Jordans 1 Jordan Retro 5 Pandora Jewelry Pandora Bracelets Asics Pandora Bracelet Cheap Womens Adidas Shoes Pandora Charms Nike Factory Store Salvatore Ferragamo Nike Shoes Pandora Nike Outlet Adidas Canada Nike Outlet Store Nike Sneakers For Men Nike Shoes For Women Jordans 1 Pandora Bracelet Nike Running Shoes Pandora Moncler Jackets Pandora Bracelets Pandora Rings Adidas Sneakers For Women Asics Kayano Pandora
profile

rewef

November 10, 2022
*.32.3.97

<a href="https://ksmartlink.com//" rel="dofollow">glock 17l slide</a>
<a href="https://ksmartlink.com//" rel="dofollow">buy glock 17l</a>
<a href="https://ksmartlink.com//" rel="dofollow">PSE Stinger X RTS Compound Bow Archery Hunting</a>
<a href="https://ksmartlink.com//" rel="dofollow">Mathews Triax</a>
<a href="https://ksmartlink.com//" rel="dofollow">Mathews v3 right hand</a>
<a href="https://ksmartlink.com//" rel="dofollow">Bear Archery </a>
<a href="https://ksmartlink.com//" rel="dofollow">glock 17</a>
<a href="https://ksmartlink.com//" rel="dofollow">G23 Gen5 </a>
<a href="https://ksmartlink.com//" rel="dofollow">Glock Glock 22</a>
<a href="https://ksmartlink.com//" rel="dofollow">Glock 27</a>
<a href="https://ksmartlink.com//" rel="dofollow">Glock 35</a>
<a href="https://ksmartlink.com//" rel="dofollow">Glock 29</a>
<a href="https://ksmartlink.com//" rel="dofollow">glock 40</a>
<a href="https://ksmartlink.com//" rel="dofollow">American Tactical</a>
<a href="https://ksmartlink.com//" rel="dofollow">Rock Island Armory</a>
<a href="https://ksmartlink.com//" rel="dofollow">Benelli M1014 </a>
<a href="https://nolacontainers.com/" rel="dofollow">20 foot container for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">20ft containers for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">40′ HIGH CUBE OPEN SIDE</a>
<a href="https://nolacontainers.com/" rel="dofollow">40′ HIGH CUBE REFRIGERATED CONTAINER</a>
<a href="https://nolacontainers.com/" rel="dofollow">40′ shipping container for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">40’ STANDARD USED REFRIGERATED CONTAINER</a>
<a href="https://nolacontainers.com/" rel="dofollow">45ft container</a>
<a href="https://nolacontainers.com/" rel="dofollow">53 foot container for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">53 foot container for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">Cargo Container For Sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">cargo shipping containers for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">cheap used shipping containers</a>
<a href="https://nolacontainers.com/" rel="dofollow">container house for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">custom plastic storage containers</a>
<a href="https://nolacontainers.com/" rel="dofollow">high cube shipping container</a>
<a href="https://nolacontainers.com/" rel="dofollow">refrigerated container</a>
<a href="https://nolacontainers.com/" rel="dofollow">shipping container homes for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">shipping containers for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">shipping containers for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">shipping containers high cube and Standard</a>
<a href="https://nolacontainers.com/" rel="dofollow">STANDARD HEIGHT NEW REFRIGERATED CONTAINER </a>
<a href="https://nolacontainers.com//" rel="dofollow">STANDARD ONE TRIP DOUBLE DOOR CONTAINER</a>
<a href="https://nolacontainers.com/" rel="dofollow">steel shipping containers for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">used shipping containers for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">used shipping containers for sale</a>
<a href="https://nolacontainers.com/" rel="dofollow">used shipping containers for sale cheap</a>
<a href="https://nolacontainers.com/" rel="dofollow">wholesale shipping containers</a>
<a href="https://nolacontainers.com/" rel="dofollow">53 foot shipping containers price</a>
<a href="https://nolacontainers.com/" rel="dofollow">45 foot shipping containers price</a>
<a href="https://nolacontainers.com/" rel="dofollow">40 foot shipping containers price</a>
<a href="https://nolacontainers.com/" rel="dofollow">20 foot shipping containers price</a>
<a href="https://nolacontainers.com/" rel="dofollow">10 foot shipping containers price</a>
<a href="https://nolacontainers.com/" rel="dofollow">8 foot shipping containers price</a>
<a href="https://nolacontainers.com/" rel="dofollow">buy shipping containers for sale</a>
문서 첨부 제한 : 0Byte/ 2.00MB
파일 제한 크기 : 2.00MB (허용 확장자 : *.*)
List of Articles
번호 제목 글쓴이 날짜 조회 수
11 Leonbets-pt.com ijygyhab 2022-05-19 17867
10 Brazzers.vip oqinen 2022-05-18 10878
9 Скамейка садовая деревянная своими руками atikidobo 2022-05-14 11662
8 오라클 락 해제, 조회 [3] 제리 2011-11-01 264289
7 오라클 날짜함수 [9] 제리 2011-10-20 191376
6 NLS_LANG 정리 [2] 제리 2011-10-20 125150
5 오라클 권한 추가 제거 [15] 제리 2011-10-20 166887
4 오라클의 CONNECT BY LEVEL 예제 [28] 제리 2011-10-20 81109
» 오라클 고급쿼리 - 계층적 쿼리 ( connect by ) [4] 제리 2011-10-20 138493
2 오라클 sys , system 암호(패스워드) 분실시 [3] 제리 2011-10-18 68573
1 오라클 각종 정보 알아보기 [15] 제리 2011-10-18 104233