팁모음

오라클 온라인 실습

SQL 좀 배워볼려면 이것 저것 깔아야 할 것이 많다. DBMS도 물론 필요하고 쿼리툴도 따로 설치해야 한다. 이런 번거로운 준비 과정없이 SQL 실습만 해 보고 싶다면 오라클의 다음 사이트에서 온라인으로 실습해 볼 수 있다.

https://livesql.oracle.com/

온라인에서 DBMS 서비스를 잠시 빌려 쓰고 쿼리툴은 웹 페이지로 대체하는 형식이다. 접속하면 다음 페이지가 나타난다.

여깃 Start Coding Now 버튼을 누르면 로컬에 아무것도 설치하지 않아도 웹 브라우저 상에서 쿼리 실습을 할 수 있다. 공짜로 빌려 쓰는 서비스이므로 누가 쓰는지는 알아야 하니 로그인을 요구한다.

오라클 계정이 있다면 바로 로그인하면 되고 없다면 계정을 만들어야 한다. 서비스를 받으려면 신분을 밝히기도 해야 하지만 실습중인 세션을 저장하려면 아이디가 필요하다. 어차피 오라클 SQL을 배우려면 회원가입은 필수이니 가입하자.

로그인하면 실습할 수 있는 페이지로 이동한다. 전체적인 모양은 쿼리툴과 유사하다. 워크시트에 쿼리문을 입력하고 Run 버튼을 누르거나 Ctrl+Enter를 누르면 실행 결과를 아래쪽에 보여준다.

여기서 테이블을 만들고 레코드도 삽입한 후 이런 저런 쿼리를 날려가며 실습하면 된다. 단 여기서 만든 테이블과 레코드는 세션중에만 유효하며 재접속하면 리셋된다. 저장해 두려면 Save 버튼을 눌러 세션을 저장해 둔다.

간편하다는 면에서 훌륭한 실습도구이지만 아무래도 제약은 있을 수밖에 없다. 실습용 도구로 계정 생성이나 권한 부여, 최적화 같은 DBMS의 고급 기능을 다 활용할 수는 없다. 실습하던 세션이 일정 기간만 저장되며 가끔 일요일은 공부하지 말고 쉬어라는 메시지가 뜨기도 한다.

진짜 DBMS나 쿼리툴을 갖추어 놓고 실습하는 것에 비할바는 못되지만 간편하게 실습해 볼 때는 나름 쓸만하다. 회사에서 공용으로 쓰는 DB에 테이블을 함부로 만들 수 없고 자칫 실수하면 사고를 칠 수도 있다. 온라인 실습 환경은 그런 제약이나 위험이 없어 마음대로 만들어 볼 수 있고 모든 시도를 다 해 볼 수 있다.

NULL의 정렬 순서

NULL은 값이 아니라 특수한 상태여서 일반적인 값과 대소를 비교할 수 없다. NULL이 값에 비해 큰지, 작은지 결정할 수 없으며 표준에도 딱히 정해진 규정이 없다. NULL을 어떻게 정의하나 별 문제는 없지만 결과셋을 정렬하여 출력할 때 심미적인 문제가 발생한다. 다음 쿼리문을 실행해 보자.

SELECT * FROM tStaff ORDER BY score;

직원 테이블을 성취도에 따라 정렬하는 쿼리문이되 score필드에 NULL이 두 개 있다. 이 필드가 어디에 출력되는지 관찰해 보자. 성취도가 있는 직원은 당연히 성취도순으로 정렬하되 성취도가 NULL인 직원이 어디에 나타나는가가 문제이다.

왼쪽은 SQL 서버, 오른쪽은 오라클에서의 실행 결과이다. SQL 서버는 NULL을 가장 작은 값으로 평가하여 제일 먼저 출력하는데 비해 오라클은 NULL을 가장 큰 값으로 평가하여 마지막에 출력한다. MySQL, MariaDB는 SQL 서버와 마찬가지로 NULL을 가장 작은 값으로 취급한다.

어떤게 맞다고 단정할 수 없지만 어떤게 더 적합한가는 취향이나 결과셋의 사용 목적에 따라 갈릴 수 있는 문제이다. 알 수도 없는 값을 제일 먼저 출력하는 것이 보기 싫을 수도 있고 제일 큰 값으로 취급하는 것도 뭔가 어색하다. ORDER BY의 뒤에 desc를 붙이면 반대의 결과가 나오지만 이 경우 값이 있는 레코드의 순서도 영향을 받아 NULL값의 순서만 조정하는 명령은 아니다.

그래서 NULL을 어느 위치에 출력할 것이지 별도로 지정해야 한다. NULL값의 정렬 순서를 지정하는 표준 문법은 없고 DBMS마다 고유한 옵션을 제공한다. 오라클이 가장 깔끔하다.

SELECT * FROM tStaff ORDER BY score NULLS first 또는 last;

ORDER BY 뒤에 NULLS first 또는 NULLS last 구문으로 NULL의 위치를 직접 지정한다. 디폴트는 last이며 필요시 first로 바꿀 수 있다. 다음 명령을 실행해 보자.

SELECT * FROM tStaff ORDER BY score NULLS first;

NULL first가 아니라 NULLS first임을 유의한다. 이렇게 하면 MSSQL이나 MariaDB와 같이 NULL을 목록의 제일 처음에 출력한다. 마리아는 다음 구문을 사용한다.

SELECT * FROM tStaff ORDER BY score IS NULL ASC, score;

NULL인 상태에 대해 오름차순 정렬하면 NULL이 제일 아래로 이동하며 내림차순 정렬하면 제일 위로 올라온다. 그리고 2차 정렬 기준으로 score를 지정하면 NULL 아닌 행끼리 성적순으로 정렬된다.

NULL여부로 1차 정렬, score로 2차 정렬하는 것이다. 평이한 거 같지만 이 구문은 마리아에서만 되며 MSSQL이나 오라클은 지원하지 않는다. MSSQL은 CASE문으로 NULL 여부를 평가하여 1차 정렬하고 score로 2차 정렬한다.

SELECT * FROM tStaff ORDER BY CASE WHEN score IS NULL THEN 1 ELSE 0 END, score;

NULL여부에 따라 1 또는 0의 값을 만든 후 이 값을 1차 정렬 기준으로 사용하고 NULL이 아닌 경우는 성적순으로 정렬한다. 세 DBMS 모두 어떤 방법을 쓰든간에 원하는 순서대로 출력할 수는 있다.

NULL을 제일 앞이나 뒤가 아닌 중간에 돌 수도 있다. 이때는 NULL을 특정값으로 가정하는 NVL, ISNULL 함수를 사용한다. NULL을 충분히 큰 값이나 충분히 작은 값으로 치환하면 원하는 자리에 출력할 수 된다.

오라클 : SELECT * FROM tStaff ORDER BY NVL(score, -1);

MSSQL : SELECT * FROM tStaff ORDER BY ISNULL(score, 101);

사실 이 경우는 점수의 범위가 0~100까지임을 명확히 알 수 있기 때문에 쓸 수 있는 방법이다. 범위 바깥의 값을 지정함으로써 강제로 제일 처음이나 앞에 배치한다. 별 실용성은 없지만 ISNULL(score, 50)이나 NVL(score, 50)으로 지정하면 NULL을 중간쯤에 표시할 수도 있다. 마리아는 이상하게도 NULLIF(score, 101)로 해도 말을 듣지 않는다.

GREATEST

둘 중 큰 값을 찾는다. 예를 들어 현금가, 카드가 중 비싼 가격을 찾기. 테이블 따로 만들기 귀찮으니 tCity의 popu, area로 비교해 보자. 설마 다음과 같이 생각하는 사람은 없기를

SELECT name, popu, area, MAX(popu, area) value FROM tCity;

일반 프로그래밍 언어에서의 MAX와는 다르다. MAX는 종으로 레코드 사이의 한 필드중 제일 큰 값을 찾는 집계 함수이다. 같은 레코드의 필드중 큰 값을 찾는 함수는 GREATEST이다. 여러 개의 값을 주면 그 중 제일 큰 값을 찾아 리턴한다.

SELECT name, popu, area, GREATEST(popu, area) value FROM tCity;

작은값을 찾는 함수는 LEAST이다. 오라클, 마리아만 됨. MSSQL은 CASE 사용

SELECT name, popu, area,

CASE

WHEN popu > area THEN popu

ELSE area

END value

FROM tCity;

MSSQL에 이게 왜 없는지 참 의아스럽다. 함수로 만들어 두고 쓸 수는 있다. 그러나 타입별로 따로 만들거나 모든 타입을 포괄하는 범용 타입을 사용해야 한다. 함수가 없어 불편하다.

DISTINCT와 GROUP BY

이 두 명령은 비슷한 값을 합쳐 중복을 제거한다는 면에서 기능이 거의 비슷하다. 실제로 내부 알고리즘도 비슷해서 어느 정도 서로 대체도 가능하다. 부서의 종류를 구하고 싶다면 다음 두 명령 중 하나를 사용한다.

SELECT DISTINCT depart FROM tStaff;

SELECT depart FROM tStaff GROUP BY depart;

둘 다 세 개의 부서 이름을 출력한다. DISTINCT는 출력할 때 중복을 제거하고 GROUP BY는 그룹을 나눌 때 중복을 제거한 후 추려진 부서명을 출력한다. 부서명을 한번만 쓰는 DISTINCT가 구문이 좀 더 단순하다. 그룹핑 기준이 2개 이상이어도 효과 같다.

SELECT DISTINCT depart, gender FROM tStaff;

SELECT depart, gender FROM tStaff GROUP BY depart, gender;

기준이 늘어나면 DISTINCT의 구문이 더 짧게 보인다. 둘 다 그룹핑 기준의 조합을 구한 후 조합내의 중복을 제거한다. 부서별, 성별로 그룹핑하여 5개의 결과셋을 보여 준다. 원래 6개여야 하지만 총무부에 여직원이 없어 5개의 그룹만 나온다. 두 명령은 내부 알고리즘이 같아 용도상 대체 가능하다.

물론 약간의 차이점도 있는데 중복을 제거한 후 정렬까지 수행하는지가 다르다. 그러나 정렬 방식은 두 구문의 차이보다 DBMS마다 다른 경우가 더 많으며 심지어 같은 DBMS도 버전별로 차이가 있다. 조사해 보면 다음과 같다.

오라클 : 둘 다 정렬하지 않는다. 두 구문의 순서는 같다. 10g 이전에는 정렬을 했었다.

MSSQL : 둘 다 정렬한다. 두 구문의 순서는 같다.

마리아 : DISTINCT는 정렬하지 않고 GROUP BY는 정렬한다.

결과셋을 정렬한다고 서비스가 좋다고 평가할 수는 없으며 쓸데 없는 짓을 하여 시간을 낭비하는 것이다. 어차피 출력 순서는 별 의미가 없고 ORDER BY로 원하는 정렬을 수행할 수 있어 사실 중요한 차이점은 아니다.

알고리즘 구현 방식에 따라 성능에도 약간의 차이가 있다. DISTINCT가 임시 테이블을 사용하여 느린 면이 있고 정렬 생략으로 더 빠른면도 있어 결국 큰 차이는 없다. 성능 차이도 DBMS마다 달라 절대적이지는 않다. 두 구문의 가장 큰 차이점은 사용처이다. 집계 함수와 함께 두 구문을 사용해 보자.

SELECT COUNT(DISTINCT depart) FROM tStaff;

SELECT COUNT(depart) FROM tStaff GROUP BY depart;

위쪽 명령은 부서의 개수를 조사하며 3이 출력된다. 아래쪽 명령은 부서별로 나눈 후 부서에 속한 사람의 개수를 조사한다. GROUP BY로 조사해서는 부서 개수를 조사하기 어렵다. 정 하려면 서브 쿼리로 감싼 후 레코드 개수를 세어야 한다.

SELECT COUNT(*) FROM (SELECT depart FROM tStaff GROUP BY depart) A;

보다시피 집계 함수와 사용할 때는 DISTINCT가 더 간편하다. GROUP BY는 필드 리스트에 집계 함수를 쓸 수 있다는 장점이 있다. 다음 명령은 부서별 월급 총합을 구한다.

SELECT depart, SUM(salary) FROM tStaff GROUP BY depart;

부서별로 그룹핑하고 각 그룹에 대해 집계 함수를 적용하여 한 결과셋에 깔끔하고 보고서를 출력한다. DISTINCT로는 이런 결과셋을 만들기 쉽지 않다.

중복을 제거한 결과만 구할 때는 DISTINCT가 간편하다. 그러나 그룹핑 한 후 집계나 필터링까지 해야 한다면 이때는 GROUP BY를 사용해야 한다. GROUP BY는 HAVING 절로 집계후의 결과까지 필터링할 수 있고 ROLL UP, CUBE로 소계까지 뽑을 수 있어 기능이 더 많다.

rownum의 한계

rownum 의사컬럼은 실제 테이블에 있는 필드가 아니라 쿼리문 실행중에 붙이는 순번이다. 이 순번이 매겨지는 정확한 시점은 WHERE 절의 조건을 적용하여 결과셋을 만들 때이다. 다음 쿼리문으로 이를 확인할 수 있다.

SELECT rownum, tCity.* FROM tCity WHERE metro = 'n';

SELECT rownum, tCity.* FROM tCity WHERE metro = 'n' ORDER BY area DESC;

광역시가 아닌 도시 목록을 조사하며 rownum을 같이 출력했다. 이 조건에 맞는 도시 목록에 순번이 차례대로 매겨진다. 여기에 면적 내림차순으로 정렬하는 구문을 넣어 보면 결과는 다음과 같다.

WHERE절만 적용할 때는 rownum이 순서대로 매겨지지만 여기에 ORDER BY를 적용하면 rownum까지도 같이 정렬되어 버린다. 조건을 적용하여 한행 한행을 만들 때마다 순번을 매기는 식이며 조건을 다 적용한 후에는 이미 순번이 다 부여되었다. 이 상태에서 정렬을 하니 순번도 정렬 대상이 되어 버린다. 그래서 rownum은 정렬한 후에는 아무짝에도 쓸모 없는 값이다.

오라클의 쿼리 엔진은 WHERE 절의 조건을 만족하는 레코드를 찾을 때마다 1부터 순서대로 증가하는 순번을 매긴다. 만약 첫번째 행 하나만 보고 싶다면 rownum = 1 조건을 붙이면 된다.

SELECT * FROM tCity WHERE rownum = 1;

이러면 제일 첫 행인 서울이 출력된다. 서울 다음의 부산은 행번호가 2번이므로 제외되고 그 후의 행도 다 제외되어 결국 서울만 조건을 만족한다. 그렇다면 두 번째 행만 보고 싶을 때는 다음과 같이 하면 될 거 같다.

SELECT * FROM tCity WHERE rownum = 2;

예상과는 달리 이 문장은 아무 것도 출력하지 않는다. 첫 행인 서울에 행번호 1을 붙이고 보니 2행이 아니어서 조건을 만족하지 않는다. 그래서 서울은 결과셋에서 제외된다. 다음행인 부산을 읽은 후 또 행번호 1을 붙인다. 이 행도 2번은 아니므로 제외된다. 결국 1번을 먼저 만족하는 레코드가 없으니 2번 레코드는 때려 죽여도 나올 수가 없다.

이런 식이다 보니 rownum은 그보다 작은 레코드를 제한하는 용도로는 쓸 수 있지만 그 이후의 레코드를 제한하는 용도로는 쓸 수 없다. rownum과 함께 쓸 수 있는 연산자는 <=, < 뿐이며 특수하게 = 1만 가능하다. 4번 이후의 행만 출력하는 다음 조건문을 작성해 보자.

SELECT * FROM tCity WHERE rownum >= 4;

이 문장 역시 아무 것도 출력하지 않는다. 이유는 WHERE rownum = 2에 해당하는 행이 하나도 없는 것과 같다. 2번 행도 못 찾는데 4번 이상의 행을 어떻게 찾겠는가? 더 큰 쪽을 찾지 못하니 BETWEEN AND 조건으로 범위를 제한하는 것도 당연히 안된다.

이 문장이 동작하지 않는 이유는 분명 있지만 초보자가 보기에는 직관적이지 못하다. 게다가 논리적으로 틀린 문장은 아니어서 그냥 침묵할 뿐이다. 결과셋이 나오지 않을 뿐 왜 안된다는 에러 메시지도 없어 내부 구조를 모르면 더 헷갈리는 구문이다.

문제의 근본 원인은 행수를 제한하는 문법이 조건을 점검하는 구문으로 되어 있다는 점이다. 이 둘은 별개의 동작이어야 하는데 행수를 붙여 가며 조건을 점검하니 상식과는 다르게 동작한다. 결국 오라클의 rownum 의사 컬럼은 앞쪽부터 n개를 찾을 때만 제한적으로 쓸 수 있다.

LISTAGG 함수

LISTAGG 함수는 여러 행의 값을 하나로 합쳐 긴 문자열 필드로 생성한다. 오라클 9에서는 XMLAGG, 10에서는 WM_CONCAT 함수를 사용했으나 11g R2 부터는 LISTAGG를 주로 사용한다. 기본 형식은 다음과 같다.

LISTAGG(컬럼, 구분자) WITHIN GROUP(ORDER BY 정렬필드)

구분자 이하는 생략 가능하다. 다음 명령은 tCity의 도시명을 콤마로 구분하여 나열한다. name이 CHAR 타입이어서 뒤쪽 공백을 제거하기 위해 TRIM 함수로 감쌌는데 VARCHAR 타입이라면 그럴 필요가 없다.

SELECT LISTAGG(TRIM(name), ', ') FROM tCity;

각 도시는 콤마와 공백 하나로 구분한다. 가장 일반적인 목록 형태이며 공백을 빼고 콤마만 넣어도 되지만 너무 딱 붙어 있으면 답답해 보인다. 구분자를 생략하면 NULL이 적용되어 모든 도시명을 붙여 출력한다. 임의의 문자열을 구분자로 사용할 수 있다.

SELECT LISTAGG(TRIM(name), ' -> ') FROM tCity;

SELECT LISTAGG(TRIM(name), ' 찍고 ') FROM tCity;

LISTAGG는 대상 컬럼을 오름차순으로 정렬하여 합친다. 그래서 부산이 제일 먼저 오고 홍천이 가장 나중에 온다. 목록 내의 순서를 조정하려면 정렬해야 하는데 이름 역순으로 출력해 보자. 정렬 명령은 당근 ORDER BY절이지만 예상 외로 다음 명령은 효과가 없다.

SELECT LISTAGG(TRIM(name), ', ') FROM tCity ORDER BY name DESC;

에러는 발생하지 않지만 ORDER BY를 붙이나 마나 목록은 여전히 이름순이다. 왜냐하면 LISTAGG는 연산자여서 ORDER BY 보다 우선순위가 높기 때문이다. LISTAGG는 모두 합쳐 1행을 생성하는데 그 1행의 출력 순서를 지정해 봤자 아무 의미도 없고 효과도 없다.

목록을 만든 후 내부의 항목을 정렬할 수는 없는 노릇이고 목록을 만들 때 정렬해야 한다. 그래서 LISTAGG 뒤에 별도의 정렬 구문이 따로 있다. WITHIN GROUP 안에 ORDER BY 절을 넣어 정렬 후 합친다. 다음 명령은 이름 내림차순으로 목록을 생성한다.

SELECT LISTAGG(TRIM(name), ', ') WITHIN GROUP (ORDER BY name DESC) FROM tCity;

물론 꼭 이름순으로만 정렬해야 하는 것은 아니고 임의의 컬럼을 기준으로 정렬할 수 있다. 다음 명령은 인구순으로, 테이블의 원래 순서대로 정렬한다. rowid 의사 컬럼으로 정렬하면 입력한 순서대로 목록을 작성하는데 이는 곧 정렬하지 않는 것과 같다.

SELECT LISTAGG(TRIM(name), ', ') WITHIN GROUP (ORDER BY popu) FROM tCity;

SELECT LISTAGG(TRIM(name), ', ') WITHIN GROUP (ORDER BY rowid) FROM tCity;

별다른 지정이 없으면 테이블내의 모든 행이 병합 대상이다. tCity의 모든 도시가 다 목록에 포함되었다. 일부 행만 병합하려면 조건을 지정하는데 이때는 물론 WHERE 절을 사용한다. 다음 명령은 광역시는 제외하고 나열한다. 부산과 서울이 제외된다.

SELECT LISTAGG(TRIM(name), ', ') FROM tCity WHERE METRO = 'n';

정렬과 조건을 동시에 지정하는 것도 물론 가능하다. WHERE는 뒤에 붙고 ORDER BY는 WITHIN GROUP 안에서 지정한다. 다음 명령은 '천'으로 끝나는 도시를 면적순으로 정렬하여 목록을 작성한다.

SELECT LISTAGG(TRIM(name), ', ') WITHIN GROUP (ORDER BY area) FROM tCity WHERE TRIM(name) LIKE '%천';

다음은 중복 목록 제거에 대해 알아보자. 도시명은 중복이 없어 하나씩만 나타나는데 중복이 있을 경우는 같은 항목이 여러 번 나타나기도 한다. 지역명인 region을 목록으로 작성하여 출력해 보자.

SELECT LISTAGG(region, ', ') FROM tCity;

지역을 잘 나열하지만 경기, 전라, 강원 등의 항목이 중복되어 있다. 중복없이 목록을 만들려면 DISTINCT 키워드를 사용한다.

SELECT LISTAGG(DISTINCT region, ', ') FROM tCity;

중복없이 지역명만 잘 뽑아낸다. 그러나 이 명령은 될 수도 있고 안될 수도 있다. 원래 LISTAGG는 DISTINCT를 지원하지 않았다. 그래서 예전 함수인 WM_CONCAT 함수를 대신 사용하곤 했었는데 다행히 오라클 19c부터는 DISTINCT를 지원한다.

IBM DB2도 이 구문을 잘 지원하는데 오라클은 도입이 좀 늦었다. 그 이전 버전에서는 이 기능이 없어 중복을 먼저 제거한 후 목록을 만들었다.

SELECT LISTAGG(region, ', ') FROM (SELECT DISTINCT region FROM tCity);

인라인뷰에 DISTINCT 키워드를 사용하여 중복을 먼저 제거하고 이 테이블에서 region 컬럼을 목록으로 만들면 된다. 그러나 보다시피 LISTAGG로 조사한 목록과는 달리 정렬을 하지 않는 문제가 있다. 정확하게 같은 구문을 만들려면 정렬을 따로 해야 한다.

SELECT LISTAGG(region, ', ') WITHIN GROUP (ORDER BY region) FROM (SELECT DISTINCT region FROM tCity);

오라클 19c부터는 이럴 필요가 없지만 항상 최신 버전에서 실행한다고 보장할 수 없어 호환성은 오히려 이 구문이 더 좋다. 잘 만들어 놨는데 12c 버전 시스템으로 옮기면 되던 것도 안되는 경우가 종종 있다. 그래서 어떤 기능이 언제 들어 갔는지도 잘 알아 두어야 한다.

다음은 그룹핑 목록을 작성해 보자. 전체 목록이 아닌 그룹별 목록을 작성하려면 GROUP BY 구문을 사용한다. 다음 명령은 지역별 도시 목록을 조사한다.

SELECT region, LISTAGG(TRIM(name), ', ') FROM tCity GROUP BY region;

1차원의 길다란 정보를 2차원의 표형식으로 간략하게 요약해서 보여주는 실용성 높은 구문이다. 어느 지역에 어느 도시가 있는지 한눈에 파악할 수 있다. 목록은 최대 4000바이트까지 작성할 수 있어 웬만한 길이는 충분히 수용한다.

만약 위 목록을 지역순으로 정렬하고 싶다면 뒤에 ORDER BY region을 덧붙인다. WITHIN GROUP 내의 ORDER BY는 목록내의 순서이고 문장의 ORDER BY는 전체 결과셋의 순서이다. 다음 명령은 지역순으로 정렬하되 한 지역의 도시는 이름 역순으로 정렬한다.

SELECT region, LISTAGG(TRIM(name), ', ') WITHIN GROUP (ORDER BY name DESC) FROM tCity GROUP BY region ORDER BY region;

LISTAGG 명령 자체에도 PARTITION BY 구문으로 그룹핑을 할 수 있다. 그러나 기대하는 것과는 결과가 조금 다르다. PARTITION BY는 같은 그룹에 속한 항목을 합쳐 주지만 매 항목마다 그룹을 새로 만들어 중복이 발생한다.

SELECT region, LISTAGG(TRIM(name), ', ') OVER (PARTITION BY region) FROM tCity;

즉, 홍천에 대해 지역이 같은 춘천을 합치고 춘천에 대해서도 같은 강원도에 있는 홍천을 합친다. 이런 식이니 지역별로 도시 개수만큼 중복이 발생한다. 만약 정 중복을 없애고 싶다면 전체를 감싼 후 지역과 목록으로 그룹핑을 다시 하면 된다.

SELECT * FROM (

SELECT region, LISTAGG(TRIM(name), ', ') OVER (PARTITION BY region) AS list FROM tCity

) GROUP BY region, list;

이렇게 하면 애초에 GROUP BY로 뽑은 목록과 같아진다. 이런 복잡한 구문을 쓸 바에야 굳이 PARTITION BY를 쓸 필요가 없다. 단순히 그룹별 목록이 필요하다만 GROUP BY가 훨씬 간결하고 쉽다.

LISTAGG의 PARTITION BY는 각 행에 대해 개별적으로 그룹핑을 수행한다. 즉 지역이 주가 아니라 도시가 주이며 해당 도시와 같은 지역의 도시 목록을 그룹화해서 보여준다. 다음 결과가 필요하다면 PARTITION BY를 사용하는 것이 적절하다.

SELECT name, region, LISTAGG(TRIM(name), ', ') OVER (PARTITION BY region) FROM tCity;

각 도시의 정보를 출력하되 소속 지역과 같은 이웃 도시의 목록을 조사한 것이다. 이럴 때는 PARTITION BY가 실용적이다. 그룹핑 조건은 임의적이어서 중복이 있는 어떤 컬럼이든지 그룹핑할 수 있다. 다음 명령은 광역시/비광역시로 그룹핑한다.

SELECT name, region, LISTAGG(TRIM(name), ', ') OVER (PARTITION BY metro) FROM tCity;

도시 목록을 출력하며 같은 광역시는 어디인지, 자기와 같은 비광역시는 또 어디가 있는지 뽑아볼 수 있다.

XLMELEMENT와 XMLAGG 함수도 비슷한 동작을 수행한다. 최종 명령만 보면 복잡하고 정신이 없어 보이니 단계별로 도시명 목록을 작성하는 코드를 작성해 보자. 먼저 XMLELEMENT 명령으로 도시명을 엘리먼트로 만든다.

SELECT XMLELEMENT(element, TRIM(name)).getStringVal() FROM tCity;

엘리먼트명과 필드명을 지정하면 필드를 엘리먼트로 감싼다. element로 지정했는데 어차피 임시적인 엘리먼트여서 A나 B로 줘도 상관 없다. GetStringVal 함수는 엘리먼트를 문자열로 바꿔서 보여주는데 결과 확인용이다. 이 메서드가 없으면 (XMLTYPE)이라고만 나와 내부를 볼 수 없다.

도시명만 나열하면 구분이 안되므로 중간에 구분자를 넣어 준다. 두 번째 인수로 구분자를 지정해도 되고 도시명 앞에 구분자 문자열을 덧 붙여도 된다. 구분자를 뒤에 넣어도 상관 없지만 나중에 잘라내기가 귀찮아 보통 앞에 붙인다.

SELECT XMLELEMENT(element, ', ',TRIM(name)).getStringVal() FROM tCity;

SELECT XMLELEMENT(element, ', ' || TRIM(name)).getStringVal() FROM tCity;

모든 도시명 앞에 콤마와 공백을 추가했다. 다음은 엘리먼트의 텍스트만 추출한다. Extract 메서드로 텍스트만 추리면 양쪽의 태그가 떨어진다. 이번에도 내용 확인을 위해 getStringVal 호출이 필요하다.

SELECT (XMLELEMENT(element, ', ',TRIM(name))).Extract('//text()').getStringVal() FROM tCity;

이렇게 만든 목록을 XMLAGG 메서드로 하나로 합쳐 출력한다. 위 출력문의 모든 행이 하나의 긴 문자열 목록으로 합쳐진다.

SELECT XMLAGG(XMLELEMENT(element, ', ',TRIM(name))).Extract('//text()').getStringVal() FROM tCity;

도시의 이름만으로 목록을 작성했다. 여기서 문제는 제일 앞에 있는 콤마인데 이건 필요 없으니 제거해야 한다. 구분자를 뒤에 붙였어도 제거해야 하는 것은 마찬가지이다. SUBSTR 함수로 제일 앞의 문자 하나만 제거하면 된다. 이때는 리턴타입이 문자열이므로 GetStringVal 함수는 필요치 않다.

SELECT SUBSTR(XMLAGG(XMLELEMENT(x, ', ', TRIM(name))).Extract('//text()'), 2) FROM tCity;

원하는 목록만 깔끔하게 출력했다. 정렬 순서를 지정할 때는 엘리먼트로 추출할 때 ORDER BY 절을 지정한다. 다음 명령은 이름 내림차순으로 정렬하여 목록을 만든다.

SELECT SUBSTR(XMLAGG(XMLELEMENT(x, ', ', TRIM(name)) ORDER BY name DESC).Extract('//text()'), 2) FROM tCity;

XMLAGG는 오라클9부터 사용할 수 있고 4000바이트가 넘는 목록을 만들 수도 있어 좀 더 범용적이다. 그러나 형식이 복잡하고 단계가 길어 간편하게 목록을 구할 때는 LISTAGG가 더 쉽다.

MSSQL에도 비슷한 함수가 있다. 2017 이상의 버전에서는 STRING_AGG 함수를 사용하는데 LIST_AGG와 사용법이 유사하다.

SELECT STRING_AGG(TRIM(name), ', ') FROM tCity;

2017이하의 버전에서는 FOR XML PATH 구문을 사용한다. XMLAGG와 유사한 구문이다. 과정은 좀 복잡한데 결과만 보이면 다음과 같다.

SELECT STUFF((SELECT ', ' + TRIM(name) FROM tCity FOR XML PATH('')),1,1,'');

태그명과 공백 제거하고 모두 합친 후 앞쪽 콤마 하나를 제거하는 식이라 XMLAGG와 절차가 비슷하다. 더 자세한 이론과 구문을 따로 연구해 봐도 되겠지만 사용만을 목적으로 한다면 위 구문을 복사한 후 조금만 응용하면 된다.

다중 컬럼 피봇

피봇은 보통 하나의 컬럼에 대해 옆으로 돌리는 것이지만 여러 개의 컬럼에 대해서도 회전할 수 있다. tSeason에서 계절별 판매량 뿐만 아니라 정보의 개수도 같이 조사하고 싶다고 해 보자. 이때는 sale 컬럼에 대해 SUM과 COUNT 집계 함수를 각각 지정한다.

SELECT * FROM tSeason2 PIVOT (SUM(sale) AS total, COUNT(sale) AS num FOR season IN ('봄', '여름', '가을', '겨울')) pvt;

tSeason에는 돌릴만한 컬럼이 sale 밖에 없어 같은 컬럼에 대해 SUM과 COUNT를 각각 지정했다. 이러면 각 계절별로 2개의 집계 컬럼이 생성되는데 컬럼끼리 구분하기 위해 집계 결과에 별명을 반드시 지정해야 한다. 결과는 다음과 같다.

필드명_집계별명 형식으로 필드를 생성한다. 봄에 대해 TOTAL과 SUM 각 컬럼이 생성되며 나머지 계절도 마찬가지이다. PIVOT 다음의 컬럼 개수와 IN 절의 값 개수의 곱만큼 컬럼을 만든다.

FOR 절의 피봇 대상 컬럼도 여러 개 지정할 수 있다. 이 경우 IN 절에는 대상 컬럼의 조합을 써 주고 별명도 지정해야 한다. 먼저 앞에서 만들었던 다음 두 구문을 복습해 보자.

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR car IN ('승용차', '트럭')) pvt;

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR hour IN ('1', '2', '3')) pvt;

각각 통행량을 차종별, 시간별로 피봇한 것이다. 이 구문을 완벽하게 이해하고 있어야 다중 컬럼 피봇을 연구해 볼 수 있다. 다음 명령은 차종과 시간에 대해 동시에 피봇하여 각 차종이 각 시간대에 얼마나 통했는지 요약한다.

SELECT * FROM tTraffic PIVOT (SUM(traffic) FOR (car, hour) IN (

('승용차', '1') AS S1, ('승용차', '2') AS S2, ('승용차', '3') AS S3,

('트럭', '1') AS T1, ('트럭', '2') AS T2, ('트럭', '3') AS T3)) pvt;

FOR 문에 car와 hour 컬럼을 지정하여 두 컬럼의 값에 대해 회전하라고 지시한다. IN 절에는 두 컬럼값의 조합을 나열하며 각 조합에 대해 별명도 지정한다. S1 컬럼은 승용차의 1시 통행량이고 T2는 트럭의 2시 통행량이라는 뜻이다. 결과는 다음과 같다.

별명을 생략하면 두 컬럼의 값을 _로 연결한 컬럼을 생성한다. 값의 길이가 일정하면 이 편이 읽기는 더 편하지만 결과 컬럼을 참조하기가 번거로워진다.

애초의 통행량 테이블에 비해 노선만 남겨두고 차종과 시간이 모두 컬럼으로 변환되어 요약적인 정보를 생성한다. 관심없는 조합은 생략해도 상관 없다. 예를 들어 승용차의 3시 통행량은 빼고 싶다면 ('승용차', '3') AS S3를 빼 버리면 된다.

다중 컬럼 피봇을 한번에 이해하기는 사실 쉽지 않다. 피봇 자체도 직관적이지 않고 최종 결과를 이해하기도 쉽지 않다. 겨우 이해했어도 잘 외워지지도 않고 응용은 더 어렵다. 헷갈린다면 처음부터 하나씩 순서대로 다시 복습하며 내부 동작을 잘 유추해 보는 방법밖에 없다.

오라클 팁

짤막 짤막한 오라클 쿼리 관련 팁을 소개한다. 사용자와 DB 등을 다루고 관리하는 여러 가지 기법을 익힐 수 있다.

- 현재 접속한 계정을 확인한다. 업무 환경에서는 목적에 따라 로그인하는 계정을 선택하는데 예를 들어 테이블을 만들거나 수정할 때는 관리자 계정을, 단순히 읽기만 할 때는 사용자 계정을 쓰는 것이 안전하다. 쿼리창을 옮겨 다니다 보면 지금 어느 계정으로 로그인했는지 깜빡 잊을 때가 있는데 이때 다음 명령으로 확인한다.

SHOW USER;

실습 환경에서는 SYSTEM이라고 출력될 것이다. 로그인한 계정에 따라 할 수 있는 작업의 종류가 달라지며 계정에 적합한 작업만 해야 안전하다.

- 현재 접속한 DB 확인. 마찬가지로 DB를 여기 저기 옮겨 다니며 작업할 때는 현재 DB를 알아야 한다. 이때는 다음 명령을 사용한다. STUDY라고 출력된다.

SELECT name FROM v$database;

v$로 시작하는 테이블은 오라클이 동적으로 생성하는 정보 뷰이다. 기본 테이블이나 메모리, 프로세스 영역 등에서 필요한 정보를 조합하여 테이블 형태로 보여 준다. 참고로 gv$는 클러스터의 정보까지 같이 보여 준다.

- 테이블 목록 확인. 현재 DB의 테이블 목록을 조사할 때는 여러 가지 방법을 사용할 수 있다. 원론적인 명령은 user_objects 테이블을 덤프하는 것이다. 이 테이블에는 프로시저, 시퀀서, 인덱스 등의 모든 DB 오브젝트 정보가 저장되어 있다. 테이블만 보려면 object_type 필드를 'TABLE'로 지정한다.

SELECT * FROM user_objects WHERE object_type='TABLE';

시스템 테이블도 있고 중간쯤에 보면 우리가 만든 테이블도 있다. 컬럼을 보면 생성 날짜, 수정 날짜 등도 나타난다. 생성 날짜 역순으로 조회하면 최근에 만든 테이블을 볼 수 있으며 특정 테이블이 존재하는지도 확인할 수 있다. 다음은 더 간단한 방법이다.

SELECT * FROM user_tables;

SELECT * FROM tabs;

테이블 할당량이나 크기에 대한 여러 가지 정보를 표시한다. 다음 두 명령은 모든 테이블에 대한 모든 정보를 상세하게 보여 준다.

SELECT * FROM all_tables;

SELECT * FROM all_all_tables;

숨겨진 내부 관리 테이블까지 상세하게 보여준다. 관리자인 경우는 모든 계정의 DB를 다 보여 주지만 그렇지 않을 경우 현재 계정과 관리자 DB만 보인다.

- 테이블 구조 확인. 그래픽 환경은 테이블 구조를 보기 좋게 출력해 주지만 마우스로 손이 가야 해서 번거롭고 느리다. 쿼리창에서 DESC(RIBE) 명령으로 테이블 구조를 바로 확인할 수 있다.

DESC tCity;

스크립트 출력창에 필드 정보를 간략하게 보여 준다.

이름 널? 유형

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

NAME NOT NULL CHAR(10)

AREA NUMBER(38)

POPU NUMBER(38)

METRO NOT NULL CHAR(1)

REGION NOT NULL CHAR(6)

MAYOR CHAR(12)

어떤 필드가 있고 타입은 무엇인지 등을 간편하게 확인할 수 있다. 좀 더 길게 다음 명령으로도 확인할 수 있다.

SELECT * FROM user_tab_cols WHERE table_name = 'TCITY';

테이블은 이름은 반드시 대문자로 써야 한다. 명령문이 길어 DESC 명령보다는 불편하다. user_tab_cols는 간단하게 cols로 줄여 써도 된다.

- 사용자 계정을 확인한다.

SELECT * FROM dba_users;

ACCOUNT_STATUS 컬럼에 계정의 상태가 표시되어 있다. OPEN은 사용 가능하다는 뜻이며 LOCK은 잠겨 있다는 뜻이고 EXPIRED는 비밀 번호가 만료되었다는 뜻이다. 잠긴 계정을 풀 때는 다음 명령을 사용한다.

ALTER USER outln ACCOUNT UNLOCK;

이 명령을 내린 후 OUTLN 계정을 보면 OPEN으로 바뀌어 있을 것이다. 계정을 다시 잠글 때는 LOCK 명령을 주면 된다. 잠금을 풀었다고 해서 이 계정을 바로 쓸 수 있는 것은 아니며 비밀 번호를 지정해야 한다.

ALTER USER outln IDENTIFIED BY "asdf";

비밀 번호는 대소문자를 구분하며 특수문자도 사용할 수 있기 때문에 항상 따옴표로 감싸는 것이 좋다. 오라클은 일정한 주기로 비밀 번호를 바꿀 것을 요구한다. SYSTEM 계정의 만료일이 7월 7일로 고작 보름밖에 남지 않았다. 이 만료 날짜는 다음 명령으로 확인한다.

SELECT * FROM dba_profiles;

패스워드 유효 기간이 180일, 즉 6개월로 지정되어 있다. 실무 환경에서는 주기적으로 비밀번호를 바꾸는 것이 권장되며 안전하지만 나 혼자 사용하는 실습 환경에서는 무척 번거롭다. 만료 날짜는 원하는대로 지정할 수 있으며 unlimited로 지정하면 만료되지 않는다.

ALTER profile default limit password_life_time unlimited;

이 명령을 수행한 후 dba_users 테이블을 다시 조사해 보면 SYSTEM의 만료 날짜가 (null)로 바뀌며 이후 비번 변경없이 계속 사용할 수 있다.

- 오라클 버전 확인. 현재 사용중인 오라클의 버전은 다음 명령으로 확인한다. 내가 내 컴퓨터에 설치했다면 버전은 당연히 알고 있겠지만 원격 서버의 경우는 조사해 봐야 알 수 있다.

SELECT * FROM product_component_version;

SELECT * FROM v$version;

버전은 곧 사용 가능한 명령어를 결정한다. 실무에서 OFFSET FETCH 문을 사용했는데 자꾸 에러가 발생해 버전을 확인해 보니 11g였다. 이 명령은 12c 부터 추가된 것이어서 원래 11g는 알아 듣지 못하는 명령이다. 버전을 제대로 확인하지 않으면 이런 엉뚱한 삽질을 하게 된다.

대소문자 구분

오라클은 문자열의 대소문자를 구분한다. 그래서 다음 명령은 아무 것도 출력하지 않는다.

SELECT * FROM tCity WHERE metro = 'Y';

metro 필드는 광역시 여부를 표현하며 y 또는 n으로 적는다. 대문자 Y는 소문자 y와는 달라 위 명령은 아무 것도 검색하지 못한다. 이에 비해 SQL Server와 MariaDB는 대소문자를 구분하지 않아 Y로 검색해도 y를 찾아낸다. 이는 설정의 차이일 뿐이며 오라클도 다음 명령으로 대소문자를 무시하고 검색할 수 있다.

ALTER SESSION SET NLS_SORT = BINARY_CI;

ALTER SESSION SET NLS_COMP = LINGUISTIC;

이렇게 바꾼 후 위 명령을 다시 실행하면 서울과 부산을 검색해 낸다. 단, 이 명령은 현재 세션에만 영향을 미칠 뿐이어서 다시 접속하면 리셋된다. 이 옵션을 변경하지 않더라도 UPPER, LOWER 함수로 대소문자를 변환한 후 비교하면 구분없이 검색할 수 있다.

난수의 생성

난수는 여로 모로 쓸모가 많다. 무작위로 샘플을 생성하거나 표본을 추출할 때 난수를 사용한다. 알수 없는 초기 비밀 번호를 생성한다거나 임의의 대상을 추첨할 때도 유용하다. 오라클은 DBMS_RANDOM 패키지의 메서드로 난수 생성을 지원한다. 메서드에 따라 난수를 생성하는 범위가 다르다.

메서드	설명
RANDOM	음양으로 임의의 정수를 생성한다. 크기는 2의 31승이며 32비트 부호있는 정수 중 하나를 리턴한다.
VALUE	0 ~ 1사이의 실수를 리턴한다. 인수로 (시작, 끝) 범위를 지정하면 이 범위 내의 난수를 리턴한다.
NORMAL	표준 정규 분포(가우스 분포)의 임의값을 실수로 리턴한다.
STRING('서식', 길이)	일정 길이의 문자열을 리턴한다. 서식은 알파벳 한글자로 쓰되 대소문자는 구분하지 않는다. P : 인쇄 가능한 문자 U : 대문자. 그 외의 서식 지정시 U가 디폴트이다. L : 소문자 A : 대소문자 구분없는 알파벳 X : 대문자와 숫자.

메서드만 호출하면 되니 사용하기는 무척 쉽다. 다음 문장은 2의 32승 범위에서 임의의 정수를 리턴한다. RANDOM은 함수이므로 원칙적으로 괄호를 붙여야 하지만 생략해도 별 문제는 없다.

SELECT DBMS_RANDOM.RANDOM FROM DUAL;

SELECT DBMS_RANDOM.RANDOM() FROM DUAL;

실행할 때마다 매번 다른 정수를 출력하며 어떤 수가 나올지는 미리 알 수 없다. 이 방법보다는 VALUE로 범위를 지정하여 가능한 값 중 하나를 고르는 것이 실용적이다. 예를 들어 주사위의 숫자중 하나를 무작위로 생성해 보자.

SELECT DBMS_RANDOM.VALUE(1, 6) FROM DUAL;

범위를 1~6까지로 주면 이 범위내의 난수를 골라준다. 최소값은 범위에 포함되며 최대값은 제외된다. 그러나 이렇게 생성한 숫자는 실수여서 주사위의 숫자라고 할 수는 없다. 소수점을 잘라 버리되 이 경우 범위의 최대값은 7로 주어야 한다.

SELECT TRUNC(DBMS_RANDOM.VALUE(1, 7)) FROM DUAL

7은 범위에서 제외되므로 6.9999는 6이 되며 균일한 난수가 된다. 반올림하는 ROUND가 아님을 주의하자. 1.0 ~ 6.0미만까지 반올림하면 1과 6의 출현 확률은 다른 수에 비해 절반밖에 되지 않아 부정확하다.

문자열 난수를 생성할 때는 STRING 메서드로 문자의 종류와 길이를 지정한다. 다음 명령은 대문자로 된 10자 길이의 무작위 문자열을 만든다.

SELECT DBMS_RANDOM.STRING('U', 10) FROM DUAL;

IJSIBGJOAM나 CPGXPVQFFP 등을 리턴한다. 숫자 4자리와 대문자 4자리로 된 임의의 코드를 생성하려면 다음 명령을 사용한다. 조금만 응용하면 얼마든지 다양한 형식의 난수를 만들어낼 수 있다.

SELECT TRUNC(DBMS_RANDOM.VALUE(1000, 10000)) || DBMS_RANDOM.STRING('U', 4) FROM DUAL;

난수는 무작위로 생성되므로 규칙성이 없다. 무작위이되 규칙성이 있는 일련의 난수를 생성하고 싶다면 SEED 메서드로 난수 생성의 시작값을 지정한다. 난수 알고리즘은 이 시작값으로부터 난수를 생성하도록 되어 있어 시작값이 일정하면 이후 생성되는 난수도 일정하다.

항상 일정한 결과가 필요하거나 디버깅을 위해 매번 같은 난수로 테스트하고 싶을 때 유용하다. 단, SEED 메서드를 사용하려면 코드를 작성해야 한다. 그렇지 않으면 매 난수 메서드 호출시마다 시작점이 초기화되어 일정한 패턴을 얻을 수 없다. 다음 코드를 실행해 보자.

SET SERVEROUTPUT ON

BEGIN

DBMS_RANDOM.SEED(629);

FOR v_num IN 1..10

LOOP

DBMS_OUTPUT.PUT_LINE(DBMS_RANDOM.RANDOM);

END LOOP;

END;

코드 시작시 629를 시작점으로 지정하고 루프를 돌며 10개의 난수를 생성하여 출력했다. 이 코드는 어떤 시스템에서 실행하나 다음 난수의 집합을 생성한다. 시작점이 바뀌면 난수 집합도 바뀐다.

846085770

1112317425

1733337435

-1034181684

-859789045

804917861

644485921

468047019

-1054075489

-90591509

SQL Server의 난수 생성 함수는 RAND()이며 0~1사이의 무작위 난수를 생성한다. 일정 범위의 난수를 구하는 함수는 따로 없으며 실수 난수를 곱하고 더해서 원하는 난수 범위를 조정한다. 예를 들어 1 ~ 6 사이의 난수는 다음 명령으로 생성한다.

SELECT ROUND(RAND() * 6 + 1, 0, 1);

RAND()는 0 ~ 0.999까지의 난수를 생성하며 여기에 6을 곱하면 0 ~ 5.999까지가 되며 1을 더하면 1 ~ 6.999까지의 난수가 생성된다. 이 값에서 소수점을 잘라 1~6까지의 정수 난수로 만든다. MSSQL은 정수화하는 TRUNC 함수가 없어 ROUND(n, 0, 1)을 대신 사용한다. 이 코드가 어려우면 SELECT CAST(RAND() * 6 + 1 AS INT); 형식으로 캐스팅해도 된다.

NEWID 함수는 GUID를 생성한다. GUID는 시간과 장소까지 고려하여 생성하는 완전한 난수이며 길이가 128비트나 되어 전세계적으로 유일한 ID를 생성해 준다. 결코 같은 ID를 두 번 생성하지 않는다.

MariaDB의 난수 생성 함수도 SQL Server와 마찬가지로 RAND()이다. DBMS마다 난수를 만드는 방법은 제각각이지만 개념이나 활용하는 방법은 거의 비슷하다.

CONNECT BY

START WITH... CONNECT BY 계층 구조의 테이블을 순회하는 명령이다. 앞에서 만든 tDirectory 테이블은 부모와 자식간의 계층 관계를 표현하는 전형적인 예이다. 이처럼 계층 관계의 행을 탐색할 때 계층 쿼리를 사용한다. 기본 형식은 다음과 같다.

START WITH 루트조건 CONNECT BY 자식조건

트리를 모두 탐색하려면 한번에는 안되며 노드 사이를 이동하며 반복적으로 순회해야 한다. START WITH 절은 최초 딱 한번 실행되며 루트, 즉 순회의 시작 지점을 지정한다. CONNECT BY 절은 자식을 찾을 조건을 지정하며 루트에서 시작하여 계속 다음 자식을 찾아 나간다. 이 과정은 더 이상 자식이 발견되지 않을 때까지 반복한다. tDirectory 테이블에 대해 이 구문을 적용해 보자.

SELECT name, id, parent, level FROM tDirectory START WITH parent = 0 CONNECT BY PRIOR id = parent;

루트를 찾을 조건은 parent = 0, 즉 부모가 없는 노드를 찾는 것이다. 트리의 용어으로 각 항목을 노드라고 표현하는데 테이블에서는 행 하나가 노드이다. 루트는 시작점이며 유일해서 다른 노드와는 다른 특수한 조건으로 찾을 수 있다. 더 이상 위쪽이 없는 노드가 루트이다. 계층 순회중에 의사 컬럼 level이 생성되며 순회 단계를 표현한다. 루트를 찾았을 때 1이며 아래로 내려갈수록 1씩 증가한다.

최초 Root 행을 먼저 찾고 아래쪽으로 자식 노드를 찾아 나간다. 자식을 찾을 조건은 상위 행의 id와 parent 필드가 같은 행으로 지정되어 있다. PRIOR 연산자는 앞 단계에서 찾은 행을 의미하는데 이 경우는 Root 행을 의미한다. 현재 상위행인 Root의 id는 1이며 조건에 의해 parent가 1인 Data, Program 행을 찾는다. 이때 level은 1 증가하여 2가 된다.

이후의 과정도 동일하며 모든 노드에 대해 같은 과정을 반복하며 이때마다 level이 1씩 증가한다. Data의 id인 2를 parent로 가지는 Sound, Picutre를 찾고 Program의 id인 3을 parent로 가지는 Game을 찾는다. 이 노드들은 소속은 달라도 모두 같은 3레벨이며 부모 노드 아래쪽에 배치된다.

다음 단계에서 Sound, Picture는 자식 노드가 없고 Game의 자식 노드인 StarCrate를 찾는다. 마지막으로 찾은 StarCraft는 level4이며 더 이상 자식 노드가 없으므로 여기서 순회를 종료한다. 위 쿼리는 확인을 위해 level 의사컬럼을 숫자로 바로 출력했는데 이를 잘 활용하면 들여쓰기로 계층적인 관계를 시각화할 수 있다.

SELECT LPAD(' ', (level - 1) * 2) || name as name, id, parent

FROM tDirectory START WITH parent = 0 CONNECT BY PRIOR id = parent;

level당 앞쪽에 공백 2개씩 삽입하여 들여쓰면 자식은 부모보다 두 칸 오른쪽에 표시되고 형제는 들여쓰기가 같아 소속을 쉽게 확인할 수 있다. 루트는 부모가 없으니 들여쓰지 않아도 상관 없다. 그래서 level에서 1을 뺀 후 들여쓸 칸수인 2를 곱한만큼의 공백을 name 앞쪽에 덧붙인다. 4칸으로 늘리면 포함관계가 더 분명히 보인다.

PRIOR 연산자는 앞 단계에서 찾은 노드를 의미한다. PRIOR id = parent 조건은 직전 단계에서 찾은 행의 id를 parent로 가지는 행을 찾으라는 뜻이다. 비교 연산자는 교환 법칙이 성립하므로 parent = PRIOR id로 써도 마찬가지이다. "직전 단계에서 찾은 노드의 id와 부모의 번호가 같다."라는 조건으로 자식 노드를 찾아 나간다. 그러나 다음 두 구문은 제대로 동작하지 않는다.

SELECT ... START WITH parent = 0 CONNECT BY id = parent;

SELECT ... START WITH parent = 0 CONNECT BY id = PRIOR parent;

PRIOR 연산자를 빼 버리면 자신의 id와 parent를 비교하는데 이 둘이 일치하는, 즉 스스로가 자신의 부모인 노드는 없다. PRIOR를 parent에 붙이면 전행의 부모가 나인 행을 찾으므로 순회 방향이 반대이다. 두 명령 모두 최초의 노드인 Root만 찾고 다음 노드를 찾지 못하므로 더 이상 순회하지 못한다.

PRIOR를 반대쪽에 붙이면 역방향으로 순회하는데 이것이 의미 있으려면 루트가 아닌 마지막 노드에서부터 시작해야 한다. START WITH 절에 이름이 StarCraft인 노드를 먼저 찾도록 지정하고 앞 단계 노드의 parent가 자신의 id인 노드를 순서대로 찾아 나간다.

SELECT LPAD(' ', (level - 1) * 2) || name as name, id, parent

FROM tDirectory START WITH name = 'StartCraft' CONNECT BY id = PRIOR parent;

Game, Program, Root까지 역방향으로 순회하며 Root는 더 이상 부모가 없어 종료한다. CONNECT BY 문은 지정한 조건에 따라 다음 노드를 찾을 뿐이므로 꼭 정방향 순회만 가능한 것은 아니며 조건만 맞으면 어떤 식으로든 순회할 수 있다. 다만, 역방향 순회일 때는 노드당 부모는 하나만 존재하는 것이 보통이다.

START WITH와 CONNECT BY는 둘 다 조건문이서 WHERE 절과 작성하는 문법이 비슷하며 부모든 자식이든 행을 제한하는 역할을 한다. 조건을 지정하면 꼭 전체 트리를 다 순회하지 않고 그 중 일부를 제외할 수 있다. 결과 트리가 너무 깊고 장황하다면 level을 제한하여 일부분만 표시한다.

SELECT LPAD(' ', (level - 1) * 2) || name as name, id, parent

FROM tDirectory START WITH parent = 0 CONNECT BY PRIOR id = parent AND level < 3;

CONNECT BY 조건에 level < 3 조건을 지정했다. 이렇게 하면 leve이 1, 2인 노드만 나타나며 3이상인 노드는 제외된다. 행을 제한할 목적이라면 WHERE 절에 조건을 기술해도 된다. WHERE는 START WITH 보다 더 앞쪽에 쓴다.

SELECT LPAD(' ', (level - 1) * 2) || name as name, id, parent

FROM tDirectory WHERE level < 3 START WITH parent = 0 CONNECT BY PRIOR id = parent;

이렇게 해도 결과는 같다. 그러나 CONNECT BY에 있는 조건과 WHERE에 있는 조건은 실행 순서가 다르다. CONNECT BY가 먼저 실행되며 WHERE는 순회가 끝난 후 실행하도록 되어 있어 다음과 같은 차이가 있다.

- CONNECT BY의 조건은 아예 순회할 때 결과에서 제외해 버린다. 반면 WHERE 조건은 순회를 마친 후에 결과에서 제외한다. 따라서 WHERE 조건으로 필터링하면 표시하지도 않을 노드를 찾는 셈이어서 더 느리다.

- CONNECT BY에서 제외한 행은 그 자식까지 같이 제외되는데 비해 WHERE에서 제외한 행은 자신만 제외할 뿐 자식은 필터링하지 않는다.

이 차이점을 확인해 보고 싶으면 위 두 쿼리의 조건을 level != 3으로 바꿔 보면 된다. level 3을 제외하는 똑같은 조건이라도 CONNECT BY는 레벨 3을 순회에서 제외하므로 그 이하의 레벨도 나타나지 않는다. 반면 WHERE는 순회를 일단 다 한 후 그 상태에서 3만 제외한다. 그래서 3 이하의 레벨, 이 경우는 레벨 4의 StarCraft가 있는가 없는가만 차이가 난다.

START WITH는 루트를 지정하는 중요한 역할을 하지만 반드시 루트를 지정해야 하는 것은 아니다. 중간의 한 노드를 지정하면 그 이하의 자식 노드만 찾는다. 다음 명령은 Data 이하의 자식 노드만 찾는다.

SELECT LPAD(' ', (level - 1) * 2) || name as name, id, parent

FROM tDirectory START WITH name = 'Data' CONNECT BY PRIOR id = parent;

START WITH 절을 아예 생략해 버리면 각 디렉토리의 자식을 모두 찾는다. 별도의 루트를 지정하지 않았으니 각각의 디렉토리가 루트가 된다. 원래 테이블에 있던 7개의 행에 대해 개별적으로 순회한 합집합을 생성한다.

SELECT LPAD(' ', (level - 1) * 2) || name as name, id, parent

FROM tDirectory CONNECT BY PRIOR id = parent;

다음은 CONNECT BY 구문 실행중에 쓸 수 있는 여러 가지 장치(연산자, 의사컬럼, 함수)이다.

장치	설명
CONNECT_BY_ROOT 컬럼	루트로 지정된 행이며 루트의 컬럼을 참조한다.
COONECT_BY_ISLEAF	자식이 없는 노드이면 1, 아니면 0이다.
CONNECT_BY_ISCYCLE	순환 참조가 발생했으면 1이다.
SYS_CONNECT_BY_PATH(컬럼, 구분자)	루트에서부터 현재 노드까지의 모든 노드에 대한 컬럼값을 구분자로 연결한 경로

사용 방법이 복잡하지 않아 한번씩 사용해 보면 용도를 쉽게 알 수 있다. 특히 경로를 조사하는 SYS_CONNECT_BY_PATH 함수가 실용적이다.

SELECT LPAD(' ', (level - 1) * 2) || name as name, id, parent

, CONNECT_BY_ROOT name as root, CONNECT_BY_ISLEAF as leaf,

SYS_CONNECT_BY_PATH(name, '/')

FROM tDirectory START WITH parent = 0 CONNECT BY PRIOR id = parent;

CONNECT BY 구문은 계층적인 테이블에 주로 사용하지만 일반 테이블에도 반복적인 처리를 위해 간편하게 사용할 수 있다. 다음 예를 보자.

SELECT dbms_random.random FROM dual CONNECT BY level <= 5;

dual은 하나의 행만 가지는 초간단 테이블이다. 유일한 행 x를 루트로 하며 각 행이 하나의 노드가 되어 레벨이 계속 증가한다. 레벨이 5 이하일 때까지로 제한하면 다섯번 실행되어 다섯개의 난수를 생성한다. 루프를 쓰지 않고도 일정 횟수 반복하는 효과를 쉽게 구현할 수 있다.