#14 14일차_계층형 질의, 정규표현식

14일차에 배운 내용을 정리하였다.

목차는 다음과 같다. :

 

01. 계층형 질의

 - 01 정의 및 문법

 - 02 조건의 형태

 - 03 계층형 질의 가상 표현식(함수, 컬럼)

 

02. 정규표현식

---

01. 계층형 질의

 - 01 정의 및 문법

SELECT *
  FROM 테이블명
 [START WITH 시작조건]
 CONNECT BY [NOCYCLE] (PRIOR) 컬럼1 = (PRIOR) 컬럼2;

 

한 테이블 내 서로 다른 행끼리 부모-자식 간의 관계를 갖는 경우 그 관계를 표현하는 기법이다.

---

 - 02 조건의 형태

1) CONNECT BY
    - START WITH 절의 시작점은 무조건 출력한다.
    - 시작점으로부터 CONNECT BY 절의 모든 조건을 만족하는 경우만 하위레벨로 인정한다.

 

2) WHERE
    - 최종 출력 형태를 결정한다.
    - START WITH / CONNECT BY를 모두 수행한 후 WHERE 조건에 만족하는 대상만 출력한다.
    - START WITH 절의 시작점이 생략될 수 있다.(WHERE 절 조건에 부합하지 않는 경우)

 

 예제) DEPT2 테이블의 부서의 상하관계를 나타내는 계층형 질의절에 추가 조건 전달
 CASE1) CONNECT BY 전달

 

SELECT D.*, LEVEL
  FROM DEPT2 D
 START WITH PDEPT IS NULL
 CONNECT BY PRIOR DCODE = PDEPT AND AREA = '서울지사';

 

 

-> 사장실은 서울지사가 아니더라도 출력되며, 사장실로부터 하위 부서를 찾을 때 서울지사 조건을 만족하는 경우만 선택

 

CASE2) WHERE 절 전달

 

SELECT D.*, LEVEL
  FROM DEPT2 D
 WHERE AREA = '서울지사'
 START WITH PDEPT IS NULL
 CONNECT BY PRIOR DCODE = PDEPT;

 

 

-> 모든 상하관계를 표현한 후 최종적으로 서울지사만 출력하므로 사장실은 생략

---

 - 03 계층형 질의 가상 표현식(함수, 컬럼)

1) LEVEL
    - DEPTH를 표현한다. (1부터 시작해서 하위로 갈수록 1씩 증가)
    - START WITH 지점이 1레벨이다.

SELECT D.*, LEVEL
  FROM DEPT2 D
CONNECT BY PRIOR DCODE = PDEPT
 ORDER BY LEVEL;        

 

  -> START WITH 생략 시 모든 행이 시작점이 되며 그 행을 기준으로 하위탐색

  -> 예) 영업 1팀의 경우 원래는 level 4이지만 start with 조건 생략으로 인해 level 1,2,3,4에 대한 행이 추가적으로 생성
 
 2) CONNECT_BY_ISLEAF
 - LEAR NODE 여부를 확인한다.(참 : 1, 거짓 : 0)
SELECT D.*, LEVEL, CONNECT_BY_ISLEAF
  FROM DEPT2 D 
 START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT;
 

 

-> leaf node에 해당되는 행은 1, 그렇지 않은 행은 0이 리턴

 

 3) CONNECT_BY_ROOT 컬럼명
 - ROOT 노드의 컬럼명을 출력한다.
 - 각 노드(행)마다의 뿌리노드의 특정컬럼값 출력
SELECT D.*, LEVEL, CONNECT_BY_ROOT DNAME
  FROM DEPARTMENT D
 START WITH PART IS NULL
CONNECT BY PRIOR DEPTNO = PART;

 

 

 -> 각 행의 root node가 리턴

 4) SYS_CONNECT_BY_PATH(컬럼, 구분자)
 - ROOT NODE로부터의 연결 경로 출력
SELECT D.*, LEVEL, SYS_CONNECT_BY_PATH(DNAME, '-')
  FROM DEPARTMENT D
 START WITH PART IS NULL
CONNECT BY PRIOR DEPTNO = PART;

 

 

 -> 각 행의 경로가 리턴

 5) ORDER SIBLINGS BY
 - 계층형 질의절 결과에서 같은형제끼리의 정렬을 원할 때 사용한다.

 6) CONNECT_BY_ISCYCLE
 - 순환구조 발생시점을 확인한다.(참 : 1, 거짓 : 0)

 

* 순환구조 출력

CREATE TABLE CYCLE_TEST(ID NUMBER, NAME VARCHAR2(10), PID NUMBER);
INSERT INTO CYCLE_TEST VALUES(1000, '홍길동', 1001);
INSERT INTO CYCLE_TEST VALUES(1001, '김길동', 1000);
COMMIT;

 

ID      NAME        PID
1000    홍길동         1001
1001    김길동         1000

홍길동 1
김길동 2   <- 순환구조 발생시점
홍길동 3   <- 출력불가

 

SELECT C.*, LEVEL
  FROM CYCLE_TEST C
 START WITH ID = 1000
CONNECT BY PRIOR ID = PID;        -- ERROR(CONNECT BY 의 루프가 발생되었습니다)

 

SELECT C.*, LEVEL
  FROM CYCLE_TEST C
 START WITH ID = 1000
CONNECT BY NOCYCLE PRIOR ID = PID; -- 정상(순환구조를 깨면서 강제 출력)

 

SELECT C.*, LEVEL, CONNECT_BY_ISCYCLE
  FROM CYCLE_TEST C
 START WITH ID = 1000
CONNECT BY NOCYCLE PRIOR ID = PID; -- 정상(순환구조를 깨면서 강제 출력)

 

 

---

02. 정규표현식

문자열이 가지는 공통된 규칙을 일반화하여 표현하는 방법이며, 대소문자를 구분한다.

regexp_???? 함수에만 사용이 가능하다.

\d	digit. d 하나에 한 자릿수
\D	숫자가 아닌 것. 문자, 특수기호, 공백 등
\s	공백, 하나에 공백 한 칸
\S	공백이 아닌 것
\w	단어. 숫자, 문자, 언더바까지 포함 (언더바는 글자에 포함한다)
\W	단어가 아닌 것, 대부분 특수기호, 공백 포함
\t	tab
\n	엔터문자

▲ 여기까지는 기본적으로 외워야 한다.

 

^	시작되는 글자. 캐럿이라고 읽으며 통계에서는 hat이라고 읽는다

 where id like 'S&'
 where regexp_like(id, '^S')

$

마지막글자

-- where id like '%S'
-- where regexp_like(id, 'S$')

 

 where regexp_like(id, '^$')
 ▲ 빈 라인(빈 문자열) - 단, 오라클을 제외한 파이썬 등에서만 작동 (오라클은 빈 문자열을 null로 인지)

\	escape character (바로 뒤에 오는 특수기호를 일반기호 그대로 전달)

 두 번째 글자가 _인
 where id like 'S!_%' excape '!'
 where regexp_like(id, '^S_')  -- 첫번째글짜가 s, 뒤에 _만 오면 모두 포함하는 구문
 where regexp_like(id, '^S_%') -- %를 넣어버리면 세 번째 글자가 %인 <- 이것이 됨
 where regexp_like(id, '^S$') -- 'S' 한 자만 찾아달라는 뜻이 됨
 where regexp_like(id, '^S\$') -- 첫 번째 글자가 S, 두 번째 글자가 $

 

|

또는

 ex) a 또는 b로 시작하는
where id like 'a%' or id like 'b%'
where regexp_like(id, ^'a|b')   -- (X)
정규표현식도 앞에서부터 해석하기 시작한다. 따라서 위의 쿼리는
a로 시작하거나 b를 포함하는 <-이  되어버린다.
where regexp_like(id, ^'a|b')   -- (X)
where regexp_like(id, '^(a|b)') -- a 또는 b로 시작하는

 

.	엔터를 제외한 모든 한 글자

 like 연산자의 _ 역할을 한다. '한 글자 자릿수의 모든 문자'
 숫자와 숫자 사이에 무조건 한 글자(문자, 특수기호, 숫자) 포함
where regexp_like(id, '\d.\d')

 [] : 한 글자 안에 들어갈 수 있는 패턴을 정의
 대괄호 하나당 한 글자
 -는 메타문자로 인정되지 않지만, '범위'를 나타낼 때 사용

 a 또는 b로 시작하는
where regexp_like(id, '^[ab]')
 숫자로 시작하는
where regexp_like(id, '^[0-9]')
 '^[0-9-]' = 숫자로 시작하거나 하이픈을 포함하는 '^(\d|-)'
where regexp_like(id, '^[0-9-]')
                          -> 1aAA, 2BBBB, -123A 등이 출력됨
 숫자 - 영문(대문자) - 영문(소문자)를 포함하는
where regexp_like(id, '[0-9][A-Z][a-z]')
 [가-힣] 모든 한글 (character set에 따라 [가-힝] 으로 전달해야 하는 경우도 있음)
 [^ab] a 또는 b가 아닌 모든 문자(제외)

---