SQLD 오답노트(6) - SQL 활용

계층형 질의
- SQL Server에서의 계층형 질의문은 CTE를 재귀호출함으로써 계층구조를 전개한다.
- SQL Server에서의 계층형 질의문은 앵커멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다
- 오라클 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후의 필터조건으로서, 조건을 만족하는 데이터만을 추출하는데 활용된다.
- CONNECT BY 절에 작성된 조건은 where 조건과 다르다. START WITH 절에서 필터링된 시작 데이터는 결과에 포함되며, 이후 이어지는 자식 데이터는 CONNECT BY 절에 의해 필터링 된다.
1) 순방향 전개 :
START WITH 최상위노드
CONNECT BY 부모노드 = PRIOR 자식노드
혹은
START WITH 최하위 노드
CONNECT BY PRIOR 부모노드 = 자식노드
2)  역방향 전개:
START WITH  최하위 노드
CONNECT BY 부모노드 = PRIOR 자식노드
혹은
START WITH 최상위 노드
CONNECT BY PRIOR 부모노드 = 자식노드

서브쿼리
- 서브쿼리는 단일 행, 또는 복수행 비교 연산자와 함께 사용할 수 있다.
- 서브쿼리는 select 절, from 절, having 절, order by절 등에서 사용 가능하다
- 서브쿼리의 결과가 복수행 결과를 반환하는 경우에는 in, all, any 등의 연산자와 사용해야한다
단일행은 < > =  비교연산자 사용 (in , all, any 도 사용은 가능)
- 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 포함하고 있는 형태의 서브 쿼리이다
- 비연관 서브쿼리는 주로 메인쿼리에 값을 제공하기 위한 목적으로 사용된다
- 다중컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 비교되는데, SQL Server에서는 지원되지 않는다

GROUP BY 절에 쓰이는 그룹함수
- ROLLUP(인자1, 인자2): 나열된 컬럼에 대해 계층구조로 합계를 출력하는 함수이다. ROLLUP 함수를 쓰면
인자1별 인자2 + 인자1별 인자2의 소계(SUBTOTAL) + 총계(GRAND TOTAL)의 결과가 나온다. 나열된 컬럼의 순서가 변경되면 수행 결과도 변경된다
- GROUPING SETS (인자1,인자2): 인자1별 소계, 인자2별 소계 출력. 컬럼순서가 달라도 결과는 똑같다.
- CUBE : 그룹핑 컬럼이 가질 수 있는 모든 경우의 수에 대하여 소계(SUBTOTAL)과 총계(GRAND TOTAL)을 생성한다. 따라서 ROLLUP 함수와는 다르게 인자의 순서가 달라도 결과는 같다.


뷰 사용의 장점
1) 독립성: 테이블 구조가 변경되어도 뷰를 사용하는 으용 프로그램은 변경하지 않아도된다
2) 편리성: 복잡한 질의, 혹은 자주 사용하는 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
3) 보안성: 직원의 급여정보와 같이 숨기고 싶은 정보가 있다면 뷰를 생성할 때 해당 칼럼을 뺴고 생성함으로써 보안성을 높일 수 있다

윈도우함수

결과에 대한 함수처리로 결과 건수는 영향이 없다

1) RANK

- 특정 칼럼에 대한 순위를 구하는 함수이며 동일한 값에 대해서는 동일한 순위를 부여하게 된다.

- DENSE RANK : 동일한 순위를 갖는 레코드를 하나의 건수로 다룬다.

2) ROW NUMBER() : Rank나 DENSE RANK 가 동일한 값에 대해 동일 순위를 부여하는 것과 달리 모든 값에 고유 순위를 부여한다.

3) LAG: 현재 읽혀진 데이터의 이전값을 알아내는 함수 (이전 행의 값 반환)

4) LEAD: 현재 일혀진 데이터의 이후 값을 알아내는 함수 (다음 행의 값 반환)

 

PL/SQL의 특징

- 블록구조로 되어있어 각 기능별로 모듈화가 가능하다

- 변수, 상수들을 선언하여 SQL문장 간 값을 교환한다.

- IF/LOOP 등의 저차형 언어를 사요앟여 절차적 프로그램이 가능하도록 한다.

- DBMS정의 에러나 사용자 정의 에러를 정의하여 사용할 수 있다

- ORACLE에 내장되어 있으므로 오라클과 PL/SQL을 지원하는 어떤 서버로도 프로그램을 옮길 수 있다

- 응용 프로그램의 성능을 향상시킨다

- 여러 SQL문장을 블록으로 묶고 한번에 블록 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다

- 프로시저, 사요자함수, 트리거 객체를 작성할 수 있다

- 프로시저 내부에 작성된 절차적 코드는 PL/SQL 엔진이 처리하고 일반적인 SQL문장은 SQL 실행기가 처리한다

- 동적 SQL 또는 DDL 문장을 실행할 떄 excute immediate를 사용해야한다

 

트리거

- CREATE TRIGGER 

- 저장모듈 중 하나로 데이터베이스에 의해서 자동으로 호출/수행된다 

- 특정 테이블에 대해서 INSERT, UPDATE, DELETE문이 수행 되었을 때 호출되도ㅗㄺ 정의할 수 있다

- 커밋이나 롤백 같은 TCL을 사용할 수 없다

- 데이터베이스에 로그인하는 작업에도 정의할 수 있다