'Database/Oracle'에 해당되는 글 11건
- 2010/11/15 :: SYS_CONNECT_BY_PATH , CONNECT BY
- 2010/11/15 :: Start With ~ Connect by Prior ~
- 2010/11/15 :: Oracle 유용 구분 정리 해석내용 - PARTITION BY, START WITH...CONNECT BY PRIOR
- 2010/11/15 :: Oracle 유용 구분 정리 - PARTITION BY, START WITH...CONNECT BY PRIOR
- 2010/11/02 :: ORA-29275 또는 오라클 DB 클라이언트 접속시 한글깨짐
1. SYS_CONNECT_BY_PATH(column,char)은 데이터의 셀프조인에 의한 트리구조 데이테에 대해서 한row에서 표현할때 사용하는 것입니다.
간단히 말씀드리면, FIle시스템에서 파일이 위치하는 경로에 대해서 표현하는것과 같다고 생각하시면 됩니다.
2. "START WITH rnum = 1" 은 경로중에서 root에 해당하는 부분을 어디서 부터 할것인지 설정한는 조건을 말합니다.
3. "CONNECT BY PRIOR rnum = rnum - 1 AND PRIOR a = a"은 계층을 만들기 위한 조인부분으로 경로의 다음 연결 경로를 지정하는 역할을 합니다.
즉, A -> B -> C 로 데이터의 연결을 만들기 위함입니다.
4. 이함수는 Oracle에서 사용하는 것으로, 특별한 환경 설정은 없으며, 단지 Data에 대해서 계층구조를 가지고 있다면 언제든지 사용을 할수 있습니다.
가장 대표적인 예제로는 회의의 조직도 Data가 되겠네요.. ^^
출처 : Tong - redyoon님의 DB통
간단한 샘플
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
다음의 실행하면 결과값은 다음과 같이 나올 것이다.
1 | 엄마,아빠,이모
2 | 삼촌,오빠
---------------
어떻게 만들어지는지 분석모드
SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL
A B
1 엄마
1 아빠
1 이모
2 삼촌
2 오빠
일단 기본 데이터를 정의한다.
SELECT A,B,
ROW_NUMBER() OVER( ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
A B RNUM
1 엄마 1
1 아빠 2
1 이모 3
2 삼촌 4
2 오빠 5
기본데이터를 ROW_NUMBER() OVER( ORDER BY A) 를 사용해서 A 컬럼을 기준으로 정렬을해서 RNUM 을 정의해준다.
SELECT A
,SUBSTR(SYS_CONNECT_BY_PATH(B, ','),2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
A B
1 엄마
1 엄마,아빠
1 엄마,아빠,이모
2 삼촌
2 삼촌,오빠
위에 나온데이터를 기준으로 SYS_CONNECT_BY_PATH 를 써줬다.
START WITH RNUM = 1 시작 기준점을 RNUM 1을 시작점으로 잡고
CONNECT BY PRIOR RNUM = RNUM - 1 RNUM과 하나작은 RNUM과 결합하면서,
AND PRIOR A = A A값을 기준으로 같은거 끼리 묶어줬다.
CONNECT BY PRIOR RNUM = RNUM - 1 를 이해할때는 하나의 BOM을 생각하면 될거다.
시작은 RNUM = 1 로 시작하고
RNUM 이 2이면 RNUM 1과 2를 합치면서 A와 A가 같은걸 결합
RNUM 이 3이면 RNUM 3과 2를 합치고 A와 A가 같은걸 결합
SELECT A
, SUBSTR(MAX(SYS_CONNECT_BY_PATH(B, ',')), 2) AS B
FROM (SELECT A
, B
, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS RNUM
FROM (SELECT 1 A , '엄마' B FROM DUAL
UNION ALL
SELECT 1 A , '아빠' B FROM DUAL
UNION ALL
SELECT 1 A , '이모' B FROM DUAL
UNION ALL
SELECT 2 A , '삼촌' B FROM DUAL
UNION ALL
SELECT 2 A , '오빠' B FROM DUAL)
)
START WITH RNUM = 1
CONNECT BY PRIOR RNUM = RNUM - 1 AND PRIOR A = A
GROUP BY A
A B
1 엄마,아빠,이모
2 삼촌,오빠
그룹함수를 써서 A로 그룹지어서 가장큰 값을 뽑아오면 끝
[출처] SYS_CONNECT_BY_PATH , CONNECT BY|작성자 아기대장
계층형 게시판 같이 트리구조로 되어있는
디비를 삭제, 정렬, 기타 등등 트리구조에서 아주아주아주~~~~ 편하게 사용할 수 있다.
| 글번호 | 그룹 | 단계 | 순서 | 부모글 | |
| 1 | 1 | 0 | 1 | 0 | 1 |
| 3 | 1 | 1 | 2 | 1 | 3 |
| 5 | 1 | 2 | 3 | 3 | 5 |
| 6 | 1 | 3 | 4 | 5 | 6 |
| 2 | 1 | 1 | 5 | 1 | 2 |
| 4 | 1 | 2 | 6 | 2 | 4 |
사용법) 3번글을 지우려한다면 그 하위 답변인 5,6번 게시물도 지워져야한다.
이때 쿼리 짜낼려고 별짓을 다 했는데... 간단한 방법이 있었으니...
delete from s_board
where 글번호 in (
select *
from s_board
start with 글번호=3 // 시작하는 부분
connect by prior 글번호=부모글 // 검색대상을 트리형태로 검색
order by 그룹 desc, 순서
)
요렇게 start with ~ connect by prior 을 써주면 된다.
* prior의 위치
- connect by prior 자식컬럼 = 부모컬럼 => 부모에서 자식으로 트리 구성
- connect by 자식컬럼 = prior 부모컬럼 => 자식에서 부모로 트리구성
참고 ) http://blog.naver.com/humanlinux?Redirect=Log&logNo=110005644075
이전에 유용 구분이라고 작성한 내용을 해석해 보겠다.
SELECT 'a' AS ac , '1a' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '3a' AS acv FROM dual UNION ALL
SELECT 'e' AS ac , '2e' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '4a' AS acv FROM dual UNION ALL
SELECT 'b' AS ac , '1b' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '1c' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '2g' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '2c' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '3c' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '2a' AS acv FROM dual UNION ALL
SELECT 'd' AS ac , '1d' AS acv FROM dual UNION ALL
SELECT 'e' AS ac , '1e' AS acv FROM dual UNION ALL
SELECT 'f' AS ac , '1f' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '1g' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '3g' AS acv FROM dual UNION ALL
SELECT 'h' AS ac , '1h' AS acv FROM dual UNION ALL
SELECT 'h' AS ac , '2h' AS acv FROM dual UNION ALL
SELECT 'i' AS ac , '1i' AS acv FROM dual
)
FROM (
SELECT
ac, acv
,(ROW_NUMBER () OVER (PARTITION BY ac ORDER BY ac, acv )) RNUM -- (1)
FROM TVIEW
) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = (RNUM - 1)
AND PRIOR AC = AC
GROUP BY AC
SYS_CONNECT_BY_PATH( column, char )SYS_CONNECT_BY_PATH(column, char) 함수는 계층적 쿼리(계층구조)에서만 유효하며, column의 절대 경로를 char로 지정한 문자로 분리하여 반환한다.
아래와 같은 임시 테이블(임시VIEW)의 정보를 하나의 ac에 대해서 속해있는 acv 값을 하나의 string로 나열하기.
SELECT 'a' AS ac , '1a' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '3a' AS acv FROM dual UNION ALL
SELECT 'e' AS ac , '2e' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '4a' AS acv FROM dual UNION ALL
SELECT 'b' AS ac , '1b' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '1c' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '2g' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '2c' AS acv FROM dual UNION ALL
SELECT 'c' AS ac , '3c' AS acv FROM dual UNION ALL
SELECT 'a' AS ac , '2a' AS acv FROM dual UNION ALL
SELECT 'd' AS ac , '1d' AS acv FROM dual UNION ALL
SELECT 'e' AS ac , '1e' AS acv FROM dual UNION ALL
SELECT 'f' AS ac , '1f' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '1g' AS acv FROM dual UNION ALL
SELECT 'g' AS ac , '3g' AS acv FROM dual UNION ALL
SELECT 'h' AS ac , '1h' AS acv FROM dual UNION ALL
SELECT 'h' AS ac , '2h' AS acv FROM dual UNION ALL
SELECT 'i' AS ac , '1i' AS acv FROM dual
)
예로 다음과 같이 결과를 만들기.
ac acvl
=== ==============================
a |1a|2a|3a|4a
b |1b
c |1c|2c|3c
...
다음과 같이 쿼리를 작성하면 된다.
FROM (
SELECT
ac, acv
,(ROW_NUMBER () OVER (PARTITION BY ac ORDER BY ac, acv )) RNUM
FROM TVIEW
) START WITH RNUM = 1 CONNECT BY PRIOR RNUM = (RNUM - 1)
AND PRIOR AC = AC
GROUP BY AC
설명 및 테스트 결과 설명은 다음 글에서 ...
ORA-29275 : 부분 다중 바이트 문자
단순 SQL 문을 만들어서 조회를 하는데도 위와 같은 오류가 난다면,
1. 서버 ORACLE 환경
2. 로컬 윈도우의 오라클환경(CLIENT)
위 두개의 CHARACTERSET 을 확인해보자.
1. 서버 ORACLE 환경
- SELECT * FROM SYS.PROPS$
WHERE NAME = 'NLS_CHARACTERSET'
<결과>
NAME VALUE$ COMMENT$
---------------------------------------------------------
NLS_CHARACTERSET KO16KSC5601 Character set
2. 로컬 윈도우의 오라클환경(CLIENT)
- 레지스트리 편집기에서 "NLS_LANG" 을 찾아보자.
나 같은 경우는 찾기를 했더니 요기에 있더라
HKEY_LOCLA_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10G_home1
NLS_LANG 값이 KOREAN_KOREA.KO16MSWIN949 이거로 되어있다.
만약 위와 같이 1/2의 CHARSET 이 다르다면 ORA-29275 에러를 만나게 되리라. ^^
그렇다면 어떻게 한다.
잘 쓰고 있는 서버에 CHARSET 을 변경한다는건 너무 무모한 짓이고
로컬컴터의 레지스트리를 바꾸어주자. ^^
서버 ORACLE 환경의 CHARSET 으로 ...
NLS_LANG 값을 KOREAN_KOREA.KO16KSC5601 로 바꾸자.
다시 한번 SQL 을 날려보라~~
잘 될것이다.