CONNECT BY LEVEL

 

select rownum 고객번호      , '2008' || lpad(ceil(rownum/100000), 2, '0') 판매월      , decode(mod(rownum, 12), 1, 'A', 'B') 판매구분      , round(dbms_random.value(1000,100000), -2) 판매금액 from   dual connect by level <= 1200000 ;
select 99990+rownum 도서번호      , '오라클 성능 고도화 원리와 해법 ' || lpad(rownum, 2, '0') 도서명      , round(dbms_random.value(1000, 100000), -3) 가격      , dbms_random.string('l', 10) 저자      , dbms_random.string('u', 10) 출판사      , lpad(mod(rownum, 10), 4, '0') || dbms_random.string('l', 4) ISBN from   dual connect by level <= 10 ;
일정기간 혹은 한달간의 날짜 목록을 생성하려면 어떻게 해야할까. CONNECT BY 절과 LEVEL 을 이용해서 작성 할 수 있다.
SELECT TO_DATE ('2023-01-01', 'YYYY-MM-DD') + LEVEL - 1  AS DD       FROM DUAL CONNECT BY LEVEL <= TO_DATE ('2023-02-01', 'YYYY-MM-DD') - TO_DATE ('2023-01-01', 'YYYY-MM-DD');

Oracle CONNECT BY 사용법 완전 정복!

계층형(계보) 데이터를 처리하는 가장 강력한 도구

📌 CONNECT BY란?

Oracle의 CONNECT BY는 계층형 데이터(Hierarchical Data) 를 조회할 때 사용하는 구문입니다.
조직도, 카테고리 구조, 트리 형태의 데이터를 처리할 때 매우 유용하죠.

sql

복사편집

SELECT ... FROM 테이블명 START WITH 조건 CONNECT BY PRIOR 부모컬럼 = 자식컬럼;

START WITH: 최상위(루트) 데이터를 지정
CONNECT BY: 부모-자식 관계를 정의

---

🧩 기본 예제: 직원 테이블 (조직도)

sql

복사편집

-- 사원 테이블 구조 (EMP) -- EMPNO: 사원번호 -- ENAME: 이름 -- MGR: 상사의 사원번호 SELECT LEVEL, EMPNO, ENAME, MGR FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;

결과 예시:

LEVELEMPNOENAMEMGR

1	7839	KING	NULL
2	7566	JONES	7839
3	7788	SCOTT	7566
4	7876	ADAMS	7788

LEVEL: 트리의 깊이를 나타냄 (1부터 시작)

---

🌲 트리 구조 시각화 (INDENT 추가)

sql

복사편집

SELECT LPAD(' ', LEVEL * 2) || ENAME AS TREE_VIEW FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;

출력 예시:

markdown

복사편집

KING JONES SCOTT ADAMS

---

🔄 역방향 탐색 (Bottom-up)

조직의 상사 계보를 역으로 추적하고 싶다면?

sql

복사편집

SELECT LEVEL, ENAME, EMPNO, MGR FROM EMP START WITH ENAME = 'ADAMS' CONNECT BY PRIOR MGR = EMPNO;

---

🧠 CONNECT BY 관련 유용한 함수들

함수설명

LEVEL	계층의 깊이 (1부터 시작)
SYS_CONNECT_BY_PATH(column, delimiter)	루트부터 현재까지 경로 표시
CONNECT_BY_ISLEAF	마지막 노드(자식 없음) 여부 (1이면 leaf)
CONNECT_BY_ROOT column	루트 노드의 값

예시:

sql

복사편집

SELECT ENAME, SYS_CONNECT_BY_PATH(ENAME, ' → ') AS PATH FROM EMP START WITH MGR IS NULL CONNECT BY PRIOR EMPNO = MGR;

---

⚠️ 주의사항

• 사이클(CYCLE) 발생 시 무한 루프에 빠질 수 있으므로 NOCYCLE 옵션 사용 고려
• CONNECT BY PRIOR의 방향에 주의 → 부모 = 자식 순서

sql

복사편집

CONNECT BY NOCYCLE PRIOR EMPNO = MGR;

---

💡 실무 팁

• 트리 구조를 화면에 출력할 때는 LEVEL, LPAD, SYS_CONNECT_BY_PATH 조합이 핵심
• 카테고리 구조, 조직도, 메뉴 구조 등 트리형 데이터를 다루는 경우 매우 유용
• 다중 루트(예: 여러 부서장) 지원 가능

---

📝 마무리

CONNECT BY는 Oracle 고유의 매우 강력한 기능입니다. 처음엔 다소 낯설지만 한 번 익숙해지면 복잡한 계층 구조도 간단히 처리할 수 있게 됩니다.

필요하다면 WITH RECURSIVE를 지원하는 다른 DBMS(PostgreSQL, MySQL 등)로 포팅도 가능하니 개념 자체를 이해해두는 것이 중요해요!