[SQL 기초 - 5장] 오라클 SQL 날짜(2), 숫자 표기법 / 조건문

안녕하세요!

오늘은 SQL 날짜와 숫자 관련 함수를 가져와 봤습니다.

아, 조건문도 살짝 포함되어 있네요!

​

날짜 관련 함수는 오라클에 내장되어 있답니다.

오라클에서는 개발자들이 많이 사용하는 함수들을 제공해주고 있어요.

자 그럼 하나씩 알아보겠습니다!!

 

그 전에~!

이전 시간 내용이 기억나지 않는 분들은 복습을 꼭 하고 와주세요~!

이전 시간 내용 복습하러가기

날짜 관련 함수

1. MONTHS_BETWEEN(date1, date2) : 두 날짜 사이의 개월수를 반환(일수가 다르면 소수점으로 계산된다.)

활용도 : ★

2. ADD_MONTHS(date1, NUMBER) : date1 날짜에 NUMBER 만큼의 개월수를 더하고, 뺀 날짜를 리턴

활용도 : ★★★★

3. NEXT_DAY(date1, 주간요일(1~7)) : date1 이후에 등장하는 첫번째 주간요일의 날짜 반환

활용도 : ★★★

4. LAST_DAY(date1) : date1 날짜가 속한 월의 마지막 날짜를 반환

활용도 : ★★★

오늘은 위와같이 네가지의 날짜 함수에 대해 알아보도록 하겠습니다.

MONTHS_BETWEEN

먼저, MONTHS_BETWEEN 부터 살펴보겠습니다.

MONTHS_BETWEEN

emp 테이블에서 ename 과 hiredate 컬럼, 그리고 날짜 함수를 사용해서

SYSDATE 와 hiredate 사이의 날짜가 몇일인지 조회해봤습니다.

간단하죠?

ADD_MONTHS

ADD_MONTHS

다음으로 ADD_MONTHS 입니다.

형식은 위의 쿼리와 같습니다.

오늘의 날짜의 개월수에 5 개월을 더하고 뺀 날짜를 추출한 결과입니다.

그렇다면 5 '일' 을 더하고 빼려면 어떻게 한다고 했죠?

그냥 SYSDATE + 5 하면 되겠죠?

복습은 필수입니다!!

NEXT_DAY​

NEXT_DAY

다음은 NEXT_DAY 인데요 해당 날짜 이후에 등장하는 첫 번째 주간 요일의 날짜를 조회해줍니다.

무슨말이냐 하면,

SQL은 요일에 숫자를 부여해주는데요, 일요일을 기준으로 '1' 이 됩니다.

그럼 저는 쿼리에 '7' 을 썼으니 토요일의 날짜가 나오겠죠?

이렇게 하면 SYSDATE 에서 가장 빨리 다가오는 요일의 날짜를 표시할 수 있습니다.

LAST_DAY

LAST_DAY

마지막으로 LAST_DAY 함수입니다.

해당 일자가 속한 월의 마지막 일자를 반환해주는 함수인데요,

저는 임의로 '2020/06/05' 를 설정해서 결과를 알아봤더니 '2020/06/30' 일이 나오네요!

이처럼 마지막 날이 몇일까지 있나 알아볼 수 있는 함수입니다.

​문제

그렇다면 여기서 문제를 하나 내보도록 하겠습니다!!

LAST_DAY 함수는 있는데 FIRST_DAY 는 매 월의 첫 째 날짜가 1일로 같기때문에 없겠죠?

이 FIRST_DAY 를 SQL로 직접 구현해 봅시다!

문제1

HINT 는

1. SYSDATE 를 문자로 변경하는데 포맷은 YYYYMM

2. 1번의 결과에다가 문자열 결합을 통해 '01' 문자를 뒤에 붙여준다.

3. 2번의 결과를 날짜 타입으로 변경

풀어보세요!

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

FIRST_DAY 구현

정답은 위와 같습니다.

힌트 순서대로 하셨다면 충분히 푸셨을거라 생각합니다.

하지만 그래도 어려우시다면

​

위 사진처럼 쿼리를 나눠서 생각해 보세요.

주석처리 하면서 작은 부분부터 하나씩 실행시켜 가다보면 정답이 나올겁니다!

문제2

다른 실습을 하나 더 해보겠습니다.

파라미터로 yyyymm 형식의 문자열을 사용 하여 (ex : yyyymm = 201912) 해당 년, 월에 해당하는

일자 수를 구해보세요.

201912 ==> 31

201911 ==> 30

201602 ==> 29

위와 같이 결과값이 일자로 나오면 됩니다!

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

다음과 같이 쿼리를 작성하셨다면 정답이 나왔겠네요!

저는 201911 을 입력했으니 30이 나왔습니다. 201912 를 입력하신 분들은 31을,

201602 를 입력하신 분들은 29가 나왔겠네요.

하지만 이 부분 처럼 변하는 값이라면 ':param' 처럼 바인딩 변수를 사용해도 되겠죠?

실행계획 맛보기

다음으로는 잠깐 다른얘기를 해보겠습니다.

실행계획 이라는 것인데요, 조금 어려운 부분이니 개념만 살짝 보고 다음에 다시 다루도록 하겠씁니다.

​

실행계획 : DBMS가 요청받은 SQL을 처리하기 위해 세운 절차

실행계획 보는 방법 :

1. 실행계획을 생성

2. 실행계획을 보는 단계

위의 설명을 쿼리로 작성해보면 아래와 같습니다.

실행계획 보는 방법

실행 결과는 위와 같습니다. 중간에 주석에 달아 놓은 것 처럼 형변환이 일어났다는 정보를 알 수 있습니다.

 

7. 결과에만 만족하지 말고 실행계획(Execution plan)에 관심을 가지고 절차형 로직(If Then Else)을 버리고 집합적 하나의 SQL로 임무를 완수토록 하라.

 

 

위는 SQL 칠거지약의 7번째 내용입니다.실행 결과는 위와 같습니다.

결과에만 만족하지 말고 실행계획도 잘 보라는 말인것 같네요......^^

숫자의 문자열 포맷팅

숫자의 문자열 포맷팅에 대해서 얘기하도록 하겠습니다.

​

전세계의 날짜와 숫자는 나라마다 표기 방법이 다른데요,

예를 들어, 날짜를 표기 할때는

한국 : yyyy-mm-dd

미국 : mm-dd-yyyy

이렇게 표기하고 숫자를 표기 할때는

한국 : 9,000,000.00

독일 : 9.000.000,00

위와 같이 표기합니다.

이렇게 표기법이 다르기 때문에 숫자의 문자열 포맷팅을 이용합니다.

 

숫자의 문자열 포맷팅

 

emp 테이블에서 sal 와 그에 맞는 문자열 포맷팅을 해봤습니다.

우리나라 에서는 그냥 sal 컬럼만 있어도 알아볼 수 있겠지만

다른 나라 사람들이 보기에는 헷갈릴수도 있기때문에 정확한 표기를 해줍니다.

쿼리의 'L' 은 화폐단위(사용자지역)을 의미하고 '9' 는 숫자를 의미합니다.

NULL 관련 함수

다음으로 null 과 관련된 함수를 알아보도록 하겠습니다.

​

1. NVL(expr1, expr2) : expr1 이 null 이면 expr2 를 반환한다.

2. NVL2(expr1, expr2, expr3) : expr1 이 null 이면 expr2를, null 이 아니면 expr3을 반환한다.

3. NULLIF(expr1, expr2) : expr1 과 expr2 가 같으면 null 을 반환하고 다르면 expr1 을 반환한다.

4. COALESCE(expr1, expr2,....) : 인자중에 가장 처음으로 null 값이 아닌 값을 갖는 인자를 반환한다.

​

네 위와 같이 네가지의 null 과 관련된 함수들이 있는데요

null 값을 다른값으로 치환 하거나, 혹은 강제로 null을 만들때 사용합니다.

예시를 보도록 할까요?

NVL​

NVL 함수

 

NVL을 사용한 예시입니다.

제가 지난번에 null 에 어떤 값을 더해도 null 이라고 했죠?

sal 과 comm 을 더해서 최종 sal 을 알고 싶은데 중간에 null 값들이 있습니다.

이러한 경우에 null 관련 함수를 사용해서 null 값을 치환하여 계산할 수 있습니다.

COALESCE

COALESCE 함수

 

이번에는 COALESCE 예시 입니다.

COALESCE 의 인자는 여러개가 들어갈 수 있는데

그 인자들 중에서 null 이 아닌 첫 번째 인자를 반환합니다.

30과 50을 넣어줬지만 먼저 입력된 30이 출력 된것을 볼 수 있습니다.

문제

그럼 문제를 하나 풀어 볼까요?

emp 테이블에서 다음과 같은 결과를 조회하려면 어떤 쿼리를 작성해야 할까요?

단, NVL, NVL2, COALESCE 를 사용해보세요!

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

 

​

정답입니다!

조건문

마지막으로 SQL 조건문에 대해서 설명하도록 하겠습니다.

일단 형식을 살펴보면,

 

위와 같이 표현 할 수 있는데요,

SQL 조건문은 결과값에서 하나의 컬럼이라고 생각하시면 됩니다!

형식에 END가 들어간다는 점! 꼭 기억해 주세요!

바로 문제 풀어보겠습니다!

​

​

emp 테이블에서 deptno에 따라 부서명으로 변경해서 다음과 같이 조회되는 쿼리를 작성하세요.

10 => 'ACCOUNTING'

20 => 'RESEARCH'

30 => 'SALES'

40 => 'OPERATIONS'

그 외 => 'ETC'

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

위와 같이 써주시면 정답입니다!

물론 UPPER 를 빼고 싱글 쿼테이션 안의 문자열을 대문자로 써도 정답인데..

제가 할 때 소문자로 쓰는 실수를 해서 UPPER를 써서 바꿔봤습니다ㅎ

이상으로 오늘의 내용을 마치겠습니다.

오늘도 끝까지 봐주셔서 감사합니다~!