(22.12.05) Database : 계층구조 질의(hierarchycal Query), pagination(페이지 나누기),VIEW 만들기, 외부키 제약조건

 

●●계층구조 질의(hierarchycal Query)●●

 

-부모글 /자식글 정렬
누구아래에 누구를 붙어라
root를 정해주면 가지가 붙는다

 

상사의 사원번호로 정렬을 하게 된다면 

 

 

루트를 정해주고 그곳부터 사원번와 상사번호가 같은 사람끼리 정렬한다. 하지만 들여쓰기를 하지 않아 보기가 불편하다.

 

 

 

LPAD를 사용하여 들어 쓰기를 해준다. 트리의 가지들은 가지치기 한 만큼 들여쓰기 되게 된다.

NAME :  KING의 레벨이 1이므로  레벨 2부터 들여쓰기 해주고 싶으니 (LEVEL-1)을 해준다.

 

이름에 빈칸을 주어 들여쓰기 하고 싶은데 시스템 상에선 공백은 하나만 취급을 한다. 따라서 특수문자를 사용해준다. 'ㄱ'을 쓰고 한자 키를 누르면 1번에 공백이 있는 걸 확인할 수 있다.

 

공백대신 '*'을 쓴다면 이와 같이 나온다.

 

 

 

 

●●PAGINATION●●

 

 

테이블에는 없는 새로운 컬럼 RN을 ROWNUM 함수를 이용하여 만든다.

노란줄 표시된 한 로직은 INLINE VIEW로 하나의 테이블로서 다룰 수 있다.

 

 

 

인라인 뷰의 가상의 테이블의 이름을 T1이라고 해주고 T1 의 전체 내용을 보여주게 해보았다.

 

 

인라인 뷰를 품고 있는 더큰 인라인 뷰를 T2라고 하고 T2의 전체 내용중에 RN이 1~5에서 해당하는 부분을 달라고 할 수 있다.

 

 

 

한 화면에 3개 행씩 가져오게 하기 위하여 RN을 이용하여 로직을 구성한다.

RN에서 1을 뺀준 후 3으로 나누고 그후에 1을 더한다.

이렇게 해주는 이유는 FLOOR을 쓰기 위함인데

 

나눈 값이 0.7이라고 가정했을 때 FLOOR함수는 0을 CEIL은 1 가장 가까운 정수를 나타낸다.

위에서 5를 예로 들었을 때 5에서1을 빼고 (4)  이걸 3으로 나누면 1.3333....이 나오게 된다. FLOOR함수를 통해 이는 1이 되고 여기에 1을 더하므로 2페이지가 되게 된다.

 

 

EMP 테이블을 계층구조식으로 바꾼 후 5개를 페이지로 나누어 1페이지를 보여주게 했을 때 결과

 

 

 

 

●●VIEW 만들기●●

쿼리가 길어져 복잡해졌을 땐 하나의 VIEW로 지정해서 저장해 줄 수 있다.

 

뷰를 만들기 위한 문법으로는 

 

CREATE VIEW  (뷰명)  AS

SELECT (컬럼명)  FROM (테이블 명)

 

위와같이 해주면 오류보고가 뜬 걸 확인할 수 있다.

이유는 바로 SCOTT 계정엔 뷰를 만들수 있는 권한이 없기 때문이다.

따라서 시스템 계정에 로그인하여 권한을 SCOTT 계정에 뷰를 만들 수 있는 권한을 부여해줘야한다.

 

시스템 계정에서 권한 부여하기

GRANT ( 부여할 권한) TO (계정 명);

 

 

권한 부여 후엔 제대로 돌아가는 걸 확인 할 수 있다.

 

 

 

뷰를 이용해서 간단하게 페이지를 부를 수 있게 됐다.

 

 

 

 

●●테이블 연결하기 JOIN 문●●

 

JOIN 문을 이용하면 두개의 테이블을 묶을 수 있게된다.

EMP 테이블의 사원번호와 DEPT 테이블의 사원번호가 같으므로 이것을 기준으로 테이블을 합칠 수 있다.

 

 

 

 

 

 

 

외부 키 제약조건 (FOREIGN KEY CONSTRAINT)

 

테이블을 새로 하나 만든다.

 

 

EMP3테이블에 DEPT 테이블의 deptno을 참고하여 제약 조건을 형성하였다.