[Oracle] SQL 함수

NVL 함수

• NULL값을 포함하는 컬럼을 지정된 값으로 변경하는데 사용.
• 일시적으로 NULL인 컬럼의 값을 대체하기위한것
• NVL함수는 모든 데이터타입에 적용 가능.
• NVL함수는 사용할 때 변경되는 값의 데이터타입과 일치해야함.

NVL ( A, B )
A: NULL값을 포함하고있는 컬럼/식	B: 변경하려는 값 (대체할 값)

NVL함수의 문형	전환 예시
NUMBER	NVL( commission_pct , 0 )
DATE	NVL( start_date, ‘17/01/01’ )
CHAR, VARVHAR2	NVL( mailid , ‘없음’ )

• 미국표준인 COALESCE 함수도 같은 기능.

 

 

 

DECODE 함수

• 오라클 시그니쳐 함수.
• 값을 비교하여 해당하는 값을 돌려주는 함수.

DECODE ( 형식, 비교값1, 결과값1, 비교값2, 결과값2, ... 기본값)
형식	비교값1	결과값1	기본값
컬럼이나 값	형식이 비교값1에 맞는지 비교	형식=비교값1 일 때 갖는 값	형식이 비교값들과 맞지 않을 때 갖는 값. 생략될 수 있음. 생략이면 NULL.

DECODE 예시

• 자바의 switch문 같은 것.

 

 

CASE문

• DECODE 함수로 대체할 수 있음.

 

 

 

PIVOT 함수

• 데이터를 피봇테이블의 형태로 보여줌.

PIVOT 함수

• PIVOT함수가 지원되지 않는 경우, DECODE함수로 처리할 수 있음

 

 

 

 

ROLLUP 함수

• 그룹별 합계를 보여주는 함수.
• GROUP BY절에서 사용함.
• GROUP BY절에서 지정한 컬럼 순서대로 결과 집합을 그룹화하여 계층 구조를 만듦.

ROLLUP 함수

 

 

 

CUBE 함수

• 그룹별 합계를 보여줌. -GROUP BY절에서 사용
• GROUP BY절에서 지정한 모든 칼럼의 조합에 대해 집계를 수행.
• 다차원 집계, 다차원 분석을 위해 사용함. ROLLUP함수와 유사하지만 보다 더 상세한 데이터를 제공함.
• REPORT프로그램이나 장표 작성에 사용함.

CUBE 함수

 

 

 

RANK 함수

• 행별 순위를 계산해서 보여줌

RANK 함수

• 그룹별 순위를 계산해서 보여줄 수 있음

그룹별 순위를 계산

 

 

 

상관 쿼리 Correlated SubQuery

➡️ ➡️ ➡️ ⬆️ ⬆️ ⬆️ ⬆️ ⬆️ ⬆️ ⬆️ ⬅️ ⬅️ ⬅️	GET candidate row holding
	EXCUTE candidate row값을 사용해서 innerQuery를 수행함
	USE innerQuery의 값이 candidate row에 만족하는 값을 선택

• OuterQuery의 candidate row가 더 이상 남지 않을 때까지 반복함.
• 상관 쿼리는 바깥 쿼리의 각 행에 대해 반복적으로 실행되며, 일치하는 행이 없을 때까지 반복됨.
• 메인쿼리와 서브쿼리가 서로 관련 있는 것.

 

 

상관 쿼리의 특징

1. 메인쿼리가 먼저 수행됨
2. 이 결과를 서브 쿼리가 가져다 씀

 

 

멀티 로우 비교 연산자(다중 행 비교 연산자)

IN	List에 있는 값에서 하나라도 일치하면 참(true)
NOT IN	List있는 값과 같지 않은지 비교. 일치하는 값이 하나라도 있으면 거짓(false) 반환.
ANY	서브쿼리에서 Return되는 값 중 어느 하나라도 있으면 true
ALL	서브쿼리에서 Return되는 모든 값을 비교. 모든 조건을 만족해야 참(true)
EXISTS	서브쿼리에서 만족되는 값이 발견되면 true가 되며, InnerQuety에서의 검색 종료.
NOT EXISTS	서브쿼리에서 만족되는 값이 발견되지 않으면 true가 되며, InnerQuety에서의 검색 종료.

 

 

상관쿼리 예제

외부쿼리=메인쿼리

 

 

연산자들의 차이점

IN	ANY	EXISTS
서브쿼리의 결과가 작은 경우 빠르게 작동함.	비교 연산자와 함께 사용됨.	서브쿼리의 결과가 큰 경우 빠르게 작동함
서브쿼리의 모든 결과를 검색한 후에 참/거짓을 반환함.		만족하는 값을 하나라도 찾으며 실행을 중지하고 참을 반환함. (검색을 종료함)

NOT IN	NOT EXISTS
서브쿼리의 결과 중 하나라도 NULL이면 외부 쿼리의 모든 행에 대해 거짓을 반환해서 제대로 검색하지 않음.	서브쿼리가 하나의 행도 반환하지 않으면 참을 반환합니다. 따라서 NOT EXISTS는 서브쿼리의 결과에 NULL 값이 있어도 올바르게 작동함.
NOT IN 가 외부 쿼리의 각 행마다 실행되며, 서브쿼리의 결과 중 하나라도 NULL이면 해당 행에 대해 거짓을 반환한다는 의미

 

 

 

다중 행 비교 연산자 – ANY

• 본인의 급여가 각 부서별 평균 급여 중 어느 한 부서의 평균 급여보다 적은 급여를 받는 직원에 대해 이름, 급여 부서번호를 출력하시오.

 

 

다중 행 비교 연산자 – EXISTS

• 본인dl 다른 사람의 관리자로 되어있는 직원의 사번, 이름, 직책 부서번호를 나타내시오.

 

 

다중 행 비교 연산자 – NOT EXISTS

• 서브쿼리에서 만족하는 데이터를 발견하지 못하는 경우 SEARCH종료.
• NOT IN연산자와 거의유사하게 사용함.
• NOT EXISTS 연산자는 특히 서브쿼리에서  NULL값이 RETURN 될 때 유용함.
• NOT IN인 경우에는 NULL값이 RETURN될 때까지 FALSE가 됨.

 

 

 

Pseudo column(가상컬럼) - RowId, RowNum

RowId	-테이블에 있는 각 행의 물리적인 주소(저장위치)를 고유하게 지정함. -RowId를 알면 해당 데이터 한 건을 엑세스 할 수 있다는 의미. -각 행이 존재하는 동안은 불변값.
RowNum	-쿼리의 결과로 반환되는 각 행들에 대해 테이블에서 선택된 순서로 RowNum이 지정됨. -쿼리에 의해서 Return되는 행의 수를 제한하는 경우에 사용가능함.

 

 

 

가상컬럼 - RowId

-RowId는 한 행의 고유한 물리적주소(저장위치)

RowId 80bit(18자리) = 10byte
오브젝트 번호	상대 파일 번호	블록 번호	데이터 번호
32bit(16자리)	10bit(3자리)	22bit(6자리)	16bit(3자리)
해당 데이터가 속하는  오브젝트 번호	데이터가 저장된 데이터파일의 번호	데이터파일 안의 어느 블록인지 의미	블록에서 데이터의 위치

AAAStG	AAB	AAAbmR	AAA
오브젝트번호. 이 테이블의 모든 데이터의 RowId는 AAAStG로 시작.	상대파일번호. 같은 테이블이라도 행마다 다를 수 있음.	블록번호. 조회할 데이터가 같은 블록에 있으면 성능이 향상됨.	데이터 번호. 데이터가 저장된 순서를 의미함.

 

 

 

INDEX의 효율적인 사용

효율적인 사용 =

INDEX가 존재하지만 사용되지않는 경우를 피하기

• INDEXED칼럼(인덱스를 생성한 칼럼)이 비교되기 전에 변형이 일어난 경우 인덱스 사용안됨

 

• 조건이 부정으로 기술된 경우(NOT, <>)  인덱스 사용안됨

• 일반적으로 WHERE 절에서 부정 연산자(예: NOT, <>)를 사용하는 경우 해당 칼럼의 인덱스를 사용하지 않고, 전체 테이블을 스캔하게됨.
• 조건문에서 바깥테이블(e1)을 사용하는 이유: 서브쿼리가 바깥 쿼리분의 각 행에 대해 실행되어야 하기 때문
• INDEXED칼럼이 NULL로 비교할 경우 (컬럼 값이 NULL인 행은 INDEX에 저장안됨)
• Optimizer의 취사선택
  • Optimizer는 해당 쿼리에 가장 최적인 실행 계획을 선택하기 위해 쿼리의 복잡성, 조건절, 인덱스 선택도 등 여러 가지 요소를 고려함. 따라서 인덱스가 존재하더라도 옵티마이저가 해당 인덱스를 선택하지 않을 수 있음.

 

 

자동 실행 프로그램 TRIGGER

• 임의의 테이블에 특정한 DML문이 수행됐을 때, 데이터베이스에서 자동적으로 동작하도록 작성된 프로그램.
• 어떤 작업이 실행되면 연쇄적으로 프로그램이 동작하도록 한 것.

타이밍에 따른 분류	-DML 이전에 수행 -DML 이후에 수행
수행 차수에 따른 분류	-한 번만 수행 -row마다 수행