|
제목 |
오라클 고급쿼리(1) - CONNECT BY |
|
작성자 |
DEV.WON ( http://blog.naver.com/jadin1 ) |
|
작성일 |
2007-05-31 |
|
배포 |
PUBLIC (단, 출처는 밝혀 주세요) |
편의상 짧은 어투 양해바라며...
후... 강좌 올만에 올리는군요...그동안 블로그 관리에 소홀히 했는데 이제부턴 열심히 해야겠습니다.
오라클에서 제공하는 쿼리중 좀더 고급스러운 쿼리를 사용해 보도록하자.
처음으로 살펴볼 쿼리는 이른바 계층적 쿼리이다.
CONNECT BY 절을 사용하는데 이 계층적 쿼리는 오라클만이 가능 기능으로써,
데이터를 선택하여 계층적인 순서 그대로 리턴하는데 사용된다.
scott의 emp 테이블을 기억하는가?
그 사원테이블에는 사원번호 와 함께 사원의 메니저의 번호(fk) 를 참조하는 외부키 컬럼이 있다.
바로 self join을 사용하는 가장 흔한 예라 하겠다.
필자는 emp테이블로 설명을 하려 했으나, 역시나 지겨운 관계로 새로 테이블을 만들었다.
쇼핑몰이나 기타 솔루션에서 흔히 보게 되는 카테고리 정보 테이블을 예로 들면서 강좌를 진행하도록 하겠다.
우선 DB 데이블의 schema부터 보도록하자.
1. 카테고리 정보 테이블(PRT_CATE)
DDL
-- Create table
create table PRT_CATE
(
NO NUMBER not null,
CATE_NAME VARCHAR2(100) not null,
BASE_CATE_NO NUMBER,
CATE_MEMO VARCHAR2(500),
CATE_VIEW_CNT NUMBER default 0
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column PRT_CATE.NO
is 'pk';
comment on column PRT_CATE.BASE_CATE_NO
is '상위 카테고리 의 FK';
comment on column PRT_CATE.CATE_MEMO
is '카테고리의 설명.';
comment on column PRT_CATE.CATE_VIEW_CNT
is '카테고리 열람 횟수';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT_CATE
add constraint PRT_CATE_PK primary key (NO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
2. 상품 테이블(PRT)
-- Create table
create table PRT
(
NO NUMBER not null,
PRT_NAME VARCHAR2(100) not null,
PRT_PRICE NUMBER not null,
PRT_CATE_NO NUMBER
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Add comments to the columns
comment on column PRT.NO
is 'PK';
comment on column PRT.PRT_NAME
is 'NAME';
comment on column PRT.PRT_PRICE
is '가격';
comment on column PRT.PRT_CATE_NO
is '상품이 속한 카테고리 FK';
-- Create/Recreate primary, unique and foreign key constraints
alter table PRT
add constraint PRT_PK primary key (NO)
using index
tablespace SYSTEM
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
3. R-R
역시나 필자의 귀차니즘에 의한 엄청나게 허술한 그림이 돋보인다..(예전의 열정은 사라졌다. ㅎ)
위의 schema를 쭉 본 독자들이라면 거의 모두다 파악이 될것이다.
주목할 점은 바로 저, self join을 해야할 PRT_CATE테이블이다.
자 독자라면 여기서 카테고리의 계층 구조를 쿼리로 나타내어 보라.
(8번PK의 DB카테고리의 계층구조는 컴퓨터 > 소프트웨어 > DB 이다.)
우선 필자는 이렇게 처리하였다.
---------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE CATE_LISTING
(p_cateName VARCHAR2, p_level NUMBER DEFAULT 0)
AS
BEGIN
--PRINT
DBMS_OUTPUT.put_line(lpad(' ',p_level * 3,' ') || p_cateName);
FOR CATE_ROW IN
(
SELECT * FROM PRT_CATE C
WHERE C.BASE_CATE_NO IN (
SELECT C2.NO FROM PRT_CATE C2
WHERE C2.CATE_NAME = p_cateName
)
ORDER BY C.NO
)
LOOP
-- PROCEDURE CALL
CATE_LISTING(CATE_ROW.CATE_NAME,p_level + 1);
END LOOP;
END ;
-- EXECUTE
CALL CATE_LISTING('컴퓨터',0);
---------------------------------------------------------------------------------------------
결과>
--------------
컴퓨터
소프트웨어
DB
Tools
주변제품
---------------
재귀 프로시져 기법으로 처리하였다.
커서등을 사용하는것도 하나의 방법이 될수 있겠다.하지만, 너무 복잡하지않은가?
자 이쯤에서 오라클이 자랑하는(?) 기능중 계층적 쿼리를 사용해 보도록하자.
필자는 똑같은 결과를 이렇게 바꾸었다.
---------------------------------------------------------------------------------------------
SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
START WITH C.CATE_NAME = '컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;
---------------------------------------------------------------------------------------------
결과>
오, 놀랍지않은가? 엄청나게 코드가 줄었다.(그렇다면 퍼포먼스는?? 오라클 도큐먼트를 살펴보길 바란다.)
우선 START WITH 절부터 보도록 하자.
SELECT 구문의 START WITH 절은 계층 구조가 어떤 행으로부터 시작하는지 지정하는 절이다.
일종의 WHERE절이라 생각해도 좋다.
START WITH <조건>
필자는 예제에서
START WITH C.CATE_NAME = '컴퓨터'
라고 작성하였다.
바로 카테고리 이름이 '컴퓨터' 인 레코드부터 계층적 쿼리를 수행하라는 의미이다.
START WITH절은 기본적으로 WHERE절에서 사용가능한 sql을 모두 사용할수 있다.
이를 테면
---------------------------------------------------------------------------------------------
SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER
FROM PRT_CATE C
--START WITH C.CATE_NAME = '컴퓨터'
START WITH C.CATE_NAME IN
(SELECT CATE_NAME FROM PRT_CATE WHERE CATE_NAME = '컴퓨터' OR CATE_NAME = '가전제품')
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;
--------------------------------------------------------------------------------
와 같이 바꿀수도 있다.
이때 주의할점은 아래의 결과값과 같이 OR 연산 에 의해 두개의 레코드로 부터 계층쿼리가 시작이 되므로
결과값도 아래와 같이 두세트로 나온다는것에 주의하기 바란다.
(사실 이러한 경우때문에 가능하면 명료하고 구체적인 , 결과 레코드가 적은 조건을 사용하여야 쿼리 퍼포먼스가 향상된다. )
결과값>
다음은
CONNECT BY PRIOR 절을 살펴보도록하자.
CONNECT BY 절은 각각의 행들이 어떻게 연결되어야하는지 (여기서는 JOIN을 뜻한다.) 정보를 작성한다.
무슨말이냐하면, 계층적 구조 에서 각 행의 연결 관계를 설정하는것인데...
사원테이블의 사원번호와 , 사원테이블의 사수번호가 여기에 해당된다 하겠다.
그렇다면 어떤 컬럼과 어떤 컬럼이 상위이고 하위인지 이러한 계층정보는 어떻게 설정하는 것일까?
바로 이때 사용하는 키워드가 PRIOR 이다.
필자가 작성한 예제를 보면
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;
와 같이 작성하였는데..
우선 PRIOR 키워드를 빼고 생각해보자.
CONNECT BY
C.NO = C.BASE_CATE_NO;
이와 같이 되는데 이때 NO 컬럼과 BASE_CATE_NO이 계층적 연결 관계에 있다라고 설정하는것이다
(SELF JOIN이라 생각해도 좋다.)
이제 PRIOR를 생각해보자.
CONNECT BY
PRIOR C.NO = C.BASE_CATE_NO;
OUTER JOIN을 생각해보라.
C.NO (+) = C.BASE_CATE_NO; 와 같이, PRIOR 키워드의 위치에 집중하자.
바로 C.NO 쪽에 위치하고있다.!
그렇다면 어느쪽 컬럼에 PRIOR가 위치한다는것은 무엇을 의미할까?
이것을 말로 설명하기가 매우 어렵다. 말주변의 바닥... ㅜㅜ;;
"NO컬럼을 참조하는 BASE_CATE_NO 컬럼이 속한 레코드를 모두 찾아라" 정도로 이해하면 될듯싶다.
이렇게 쿼리를 작성하였기때문에 상위 '컴퓨터' 카테고리에 해당하는 하위 카테고리를 찾았고, 또 그 하위 카테고리의
하위 카테고리를 찾을 수 있게 된것이다.
(PRIOR 키워드의 위치를 바꾸어서 실습해보면 이해에 도움이 된다.)
자 이쯤에서 다음으로 넘어가자.
알면 유용한 키워드 LEVEL !
필자는 위의 예제에서 LEVEL 키워드 사용예를 위해 LEVEL키워드를 추가하였다.
--------------------------------------------------------------------------------
SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME = '컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO;
--------------------------------------------------------------------------------
결과값>
LEVEL은 오라클에서 실행되는 모든 쿼리내에서 ROWNUM과 더불어 가상-컬럼 이라 할수 있는데,
계층적 쿼리 트리내에서 어느한 위치,또는 단계(LEVEL)에 위치하는가를 나타내는 정수값 컬럼이다.
그렇다면 당근, 계층적 쿼리가 아닌 일반 쿼리에서 LEVEL컬럼의 값이 모두 0 으로 나올것이다.
자, LEVEL키워드를 어디에 사용할까??
예를들면 2단계까지의 카테고리 정보만을 출력하라 라는 식의 요구사항일때 유용하다.
예상되는 쿼리는
WHERE LEVEL < 3 일것같지만 사실 LEVEL키워드는 CONNECT BY절에 위치하여야한다.
--------------------------------------------------------------------------------
SELECT LPAD(' ' , (LEVEL - 1) * 3 , ' ') || C.CATE_NAME CATE_HIER , LEVEL CATE_LEVEL
FROM PRT_CATE C
START WITH C.CATE_NAME = '컴퓨터'
CONNECT BY PRIOR C.NO = C.BASE_CATE_NO AND LEVEL < 3;
--------------------------------------------------------------------------------
이로써 오라클이 제공하는 고급쿼리중 CONNECT BY 절에 대해 마칠까 한다.
부족한 강좌 끝까지 읽어주셔서 감사합니다.
[출처] [DEV.WON] 오라클 고급쿼리(1) - CONNECT BY|작성자 데브원