[Oracle/SQL] 2. 함수 | 그룹함수 | 단일행함수 | 형변환함수 | 선택함수

목차

1. 함수(function)
   1-1. 함수의 정의
   1-2. 함수의 유형
2. 그룹함수(group function)
   2-1. SUM
   2-2. AVG
   2-3. MIN
   2-4. MAX
   2-5. COUNT
3. 단일행함수(single row function)
   3-1. 문자 처리 함수 : 1. LENGTH/ LENGTHB, 2. INSTR, 3. LPAD/RPAD, 4. LTRIM/RTRIM, 5. SUBSTR/SUBSTRB, 6. LOWER/UPPER/INITCAP, 7. CONCAT, 8. REPLACE
   3-2. 숫자 처리 함수 : 1. ABS, 2. MOD, 3. ROUND, 4. FLOOR, 5. TRUNC, 6. CEIL
   3-3. 날짜 처리 함수 : 1. SYSDATE, 2. MONTHS_BETWEEN, 3. ADD_MONTHS, 4. NEXT_DAY, 5. LAST_DAY, 6. EXTRACT
4. 형변환 함수
   4-1. TO_CHAR
   4-2. TO_DATE
   4-3. TO_NUMBER
5. Null 처리 함수
   5-1. NVL
   5-2. NVL2
6. 선택 함수
   6-1. DECODE
   6-2. CASE
7. 함수 사용 예제

---

 

학습점검 Check

✅ 함수에 대해 이해할 수 있다.
✅ 단일행 함수와 그룹 함수에 대해 이해할 수 있다.
✅ 문자 처리 함수를 이용하여 데이터를 조회할 수 있다.
✅ 숫자 처리 함수를 이용하여 데이터를 조회할 수 있다.
✅ 날짜 처리 함수를 이용하여 데이터를 조회할 수 있다.
✅ 형변환 함수를 이용하여 데이터를 조회할 수 있다.
✅ 데이터 조회 시 자동 형변환을 이용할 수 있다.
✅ NULL 데이터를 처리하여 데이터를 조회할 수 있다.
✅ 선택함수를 이용하여 데이터를 조회할 수 있다.
✅ 그룹함수를 이용하여 데이터를 조회할 수 있다.

 

---

 

1. 함수(function)

1-1. 함수의 정의

• 함수란, 하나의 큰 프로그램에서 반복적으로 사용되는 부분들을 분리하여 작성해 놓은 작은 서브 프로그램을 일컫는다.
• 함수 호출 시 전달 값을 주면 → 수행 결과를 리턴하는 방식으로 쓰인다. 즉, 데이터베이스에서는 컬럼값을 읽어 계산한 결과를 리턴한다.
• 자바 언어를 배울 때는 메소드(method)를 이 같은 함수에 빗대어 표현했다.

 

1-2. 함수의 유형

• 함수 유형에는 단일행함수와 그룹함수가 있다.
• 단일행함수(single row function)는 컬럼에 기록된 n개의 값을 읽어 n개의 결과를 낸다. 예를 들어 23개의 값을 전달한다면 23개의 값을 반환 받는 것이다.
• 그룹함수(group function)는 컬럼에 기록된 n개의 값을 읽어 1개의 결과를 리턴한다. 즉 23개의 값을 전달해 평균값 하나를 전달 받는 경우처럼 하나의 결과를 도출하는 기능을 가졌다.

❗ SELECT절에서 단일행함수와 그룹함수 혼용할 수 없다. 각각의 특성상 결과 행 개수가 다르기 때문이다.

 

---

 

2. 그룹함수(group function)

• 컬럼에 기록된 n개의 값을 읽어 1개의 결과를 리턴한다.
• 하나 이상의 행을 그룹으로 묶어 연산하고, 하나의 컬럼으로 리턴하는 함수이다.

❗ 그룹함수 종류
① SUM : 그룹의 누적 합계 리턴
② AVG : 그룹의 평균 리턴
③ MIN : 그룹의 최소값 리턴
④ MAX : 그룹의 최대값 리턴
⑤ COUNT : 그룹의 개수 리턴

 

2-1. SUM 누적 합계

SELECT
　　　　SUM(SALARY)
  FROM EMPLOYEE;

• SUM(숫자가 기록된 컬럼명)으로 쓴다.

 

---

 

2-2. AVG 평균

SELECT
　　　　AVG(SALARY)
  FROM EMPLOYEE;

• AVG(숫자가 기록된 컬럼명)으로 쓴다.

 

---

 

2-3. MIN 최소값

SELECT
　　　　MIN(EMAIL)
　　　 , MIN(HIRE_DATE)
　　　 , MIN(SALARY)
  FROM EMPLOYEE;

• MIN(컬럼명)으로  쓴다.
• 취급하는 자료형은 ANY TYPE이다. MIN 함수에서는 컬럼값이 반드시 숫자가 아니어도 괜찮다.

그룹함수 MIN은 모든 TYPE 취급한다

• 예시에서는 각각 사전순으로 가장 앞서 있고, 날짜순으로 가장 이르며, 숫자순으로 가장 적은 값이 리턴됐다.

 

---

 

2-4. MAX 최대값

SELECT
　　　　MAX(EMAIL)
　　　 , MAX(HIRE_DATE)
　　　 , MAX(SALARY)
  FROM EMPLOYEE;

• MAX(컬럼명)으로 쓴다.
• 취급하는 자료형은 ANY TYPE이다. MIN과 마찬가지로 컬럼값이 반드시 숫자가 아니어도 괜찮다.
• 예시에서는 사전순으로 가장 뒤에 있고, 날짜순으로 가장 늦으며, 숫자순으로 가장 큰 값이 리턴된다.

 

---

 

2-5. COUNT 행의 개수

SELECT
　　　　COUNT(*)
　　　 , COUNT(DEPT_CODE)
　　　 , COUNT(DISTINCT DEPT_CODE)
  FROM EMPLOYEE;

• COUNT(*) : NULL 포함 전체 행 개수를 리턴한다.
• COUNT(컬럼명) : NULL 제외 실제 값이 기록된 행 개수를 리턴한다.
• COUNT([DISTINCT] 컬럼명) : 중복 제거한 행 개수 리턴한다.
• 예시 결과 역시 23, 21, 6으로 소괄호에 쓰인 조건 따라 다르게 추출됨을 알 수 있다.

 

---

 

3. 단일행함수(single row function)

• 단일행함수는 컬럼에 기록된 n개의 값을 읽어 n개의 결과를 리턴한다.
• 크게 문자 처리 함수, 숫자 처리 함수, 날짜 처리 함수로 나뉜다.

❗ FROM DUAL;
DUAL 가상 테이블, 범위 테이블이라고 불린다. 함수 테스트를 위한 테이블로 여기면 된다.

 

3-1. 문자 처리 함수

문자처리함수1. LENGTH 문자의 길이

/ LENGTHB 문자 byte 크기

✅ LENGTH(CHAR | STRING)으로 쓴다: (CHARACTER 타입의 컬럼 또는 임의의 STRING 문자열)
리턴값 타입 NUMBER

✅ LENGTHB(CHAR | STRING)으로 쓴다: (CHARACTER 타입의 컬럼 또는 임의의 STRING 문자열)
리턴값 타입 NUMBER

SELECT
　　　　LENGTH('오라클')
　　　 , LENGTHB('오라클')
  FROM DUAL;

• 예시 결과는 각각 3, 9로 나온다. 한글의 경우 글자당 3 byte이기 때문에 '오라클' 세 글자에 대해 9 byte가 반환되고 있는 것이다.

SELECT
　　　　LENGTH('oracle')
　　　 , LENGTHB('oracle')
  FROM DUAL;

• 영문자는 1 byte이기 때문에 예시에서 LENGTH, LENGTHB 차이 없이 'oracle'에 대해 6을 리턴한다.
• 이처럼 LENGTH는 문자의 길이, LENGTHB는 문자가 가지는 byte 크기를 리턴한다.

 

---

 

문자처리함수2. INSTR 시작 위치

✅ INSTR('문자열' | 컬럼명, '문자', 찾을 위치의 시작값, [빈도])
리턴값 타입 NUMBER

SELECT
　　　　EMAIL
　　　 , INSTR(EMAIL, '@', -1) 위치
  FROM EMPLOYEE;

EMAIL 컬럼에서 '@'를 뒤에서부터 조회

• 사실 EMAIL 컬럼값에서 '@' 문자는 결국 하나뿐이기 때문에 시작값을 -1, 1, 공란 어떻게 두든 위치값은 똑같다.
• 위치는 1부터 시작된다. 여기서 위치란 자바 언어에서 다루던 INDEX 개념과 비슷하다.

 

SELECT INSTR('AABAACAABBAA', 'B') FROM DUAL;
SELECT INSTR('AABAACAABBAA', 'B', 1) FROM DUAL;

• 시작값, 빈도값을 전달 안 했을 때는 가장 앞에서부터 찾아 3이 반환된다.
• 시작값으로 1이라는 값을 입력했을 때도 똑같이 3이 리턴된다.

SELECT INSTR('AABAACAABBAA', 'B', -1) FROM DUAL;

• 뒤에서부터 찾아 10이 반환된다.

SELECT INSTR('AABAACAABBAA', 'B', 1, 2) FROM DUAL;

• 1에서 시작해 2번째 'B' 위치는 9이다.

SELECT INSTR('AABAACAABBAA', 'B', -1, 2) FROM DUAL;

• 뒤에서부터 찾기 시작해 2번째 'B' 위치는 9이다.

 

---

 

문자처리함수3. LPAD 문자를 왼쪽에 덧붙임

/RPAD 문자를 오른쪽에 덧붙임

✅ LPAD('문자열' | 컬럼명, 반환할 길이, '덧붙일 문자')
리턴값 타입 CHARACTER

✅ RPAD('문자열' | 컬럼명, 반환할 길이, '덧붙일 문자')
리턴값 타입 CHARACTER

 

SELECT
　　　　LPAD(EMAIL, 20, '#')
  FROM EMPLOYEE;
  
SELECT
　　　　RPAD(EMAIL, 20, '#')
  FROM EMPLOYEE;

LPAD(EMAIL, 20 '#')

RPAD(EMAIL, 20, '#')

 

• LPAD/RPAD 함수는 주어진 컬럼 문자열에 임의의 문자열을 덧붙여 길이 n인 문자열을 반환한다.
• 위 예시에서처럼 원래 문자열보다 긴 수를 입력하면 LPAD는 왼쪽, RPAD는 오른쪽에 지정된 문자를 덧붙인다.

LPAD(EMAIL, 10)

• 반환한 길이 : 반대로 원래 문자열 길이보다 작은 값을 반환할 길이 n으로 입력하면 n만큼만 잘라서 내놓는다.
• '덧붙일 문자' : 생략 시 공백문자이다.

 

---

 

문자처리함수4. LTRIM 왼쪽부터 지정 문자 제거

/RTRIM 오른쪽부터 지정 문자 제거

✅ LTRIM('문자열' | 컬럼명, '제거하려는 문자')
리턴값 타입 CHARACTER

✅ RTRIM('문자열' | 컬럼명, '제거하려는 문자')
리턴값 타입 CHARACTER

SELECT LTRIM('   REMINDER') FROM DUAL;
SELECT LTRIM('   REMINDER', ' ') FROM DUAL;

• 이때 제거하려는 문자를 지정하지 않고 생략한다면 공백문자를 뜻한다.
• 따라서 위 두 예시의 결과값은 REMINDER로 동일하다.

SELECT LTRIM('123123REMINDER', '123') FROM DUAL;
SELECT LTRIM('123123REMINDER123', '123') FROM DUAL;

• 123, 즉 지정 문자가 아닌 다른 값을 만나는 순간 제거 작업이 끝난다. 따라서 두 번째 함수의 결과값은 REMINDER123이 된다.

SELECT LTRIM('ABACAREMINDER', 'ABC') FROM DUAL;

• ABC 모두 제거 후 REMINDER만 남는다.

SELECT LTRIM('349762REMINDER', '0123456789') FROM DUAL;

• 왼편에 기재된 임의의 숫자를 모두 제거한다.

 

• RTRIM()도 위와 같다. 오른편에서 제거 과정이 치러진다는 차이만 있다.

 

✅ TRIM('문자열' | 컬럼명, 제거하려는 문자)
TRIM(BOTH '제거하려는 문자' FROM '문자열' | 컬럼명) = TRIM
TRIM(LEADING '제거하려는 문자' FROM '문자열' | 컬럼명) = LTRIM
TRIM(TRAILING '제거하려는 문자' FROM '문자열' | 컬럼명) = RTRIM

• 주어진 컬럼이나 문자열 앞뒤에 지정한 문자를 제거하려거든 TRIM()을 사용할 수 있다.

SELECT TRIM('Z' FROM 'ZZZREMINDERZZZ') FROM DUAL;
SELECT TRIM(BOTH 'Z' FROM 'ZZZREMINDERZZZ') FROM DUAL;

• BOTH / LEADING / TRAILING 키워드를 함께 적어 각각 TRIM / LTRIM / RTRIM 기능을 하도록 만들 수 있다.
• 이때 BOTH는 생략 가능하다.

SELECT TRIM(LEADING 'Z' FROM 'ZZZREMINDER') FROM DUAL; =LTRIM
SELECT TRIM(TRAILING 'Z' FROM 'REMINDERZZZ') FROM DUAL; =RTRIM

 

---

 

문자처리함수5. SUBSTR 지정 위치 잘라내 추출

/SUBSTRB byte 단위로 잘라내 추출

✅ SUBSTR('문자열' | 컬럼명, 잘라낼 위치, [반환할 문자 개수])
리턴값 타입 CHARACTER

 

SELECT SUBSTR('Rolling In The Deep', 9, 2) FROM DUAL; In

SELECT SUBSTR('Rolling In The Deep', 9) FROM DUAL; In The Deep

• 반환할 문자 개수를 지정하지 않으면 선택한 위치에서부터 끝까지 포함한다.

SELECT SUBSTR('Rolling In The Deep', -8, 3) FROM DUAL; The

• 잘라낼 위치값이 음수이면 뒤에서부터 헤아린 자리를 의미한다.

SELECT SUBSTR('롤링 인 더 딥', 4, 5) FROM DUAL;

• 문자열이 한글일 때도 마찬가지이다. 

SELECT
　　　　SUBSTR('ORACLE', 3, 2)
　　　 , SUBSTRB('ORACLE', 3, 2)
  FROM DUAL;

SELECT
　　　　SUBSTR('오라클', 2, 2)
　　　 , SUBSTRB('오라클', 4, 6)
  FROM DUAL;

• 결과값은 AC, 라클로 같지만 잘라낼 위치와 반환 개수는 한글 byte의 경우 특히 다르다. 한글은 글자당 3 byte이기 때문에 두 글자 출력을 위해 6 byte를 적었음을 알 수 있다. 따라서 세 글자 전체 출력에 필요한 byte는 총 9 byte이다.

 

---

 

문자처리함수6. LOWER 소문자로 일괄 변경

/UPPER 대문자로 일괄 변경

/INITCAP 첫 글자만 대문자로 변경

✅ LOWER('문자열' | 컬럼명)
✅ UPPER('문자열' | 컬럼명)
✅ INITCAP('문자열' | 컬럼명)
리턴값 타입 CHARACTER

SELECT
　　　　LOWER('Welcome To My World')
　　　 , UPPER('Welcome To My World')
　　　 , INITCAP('welcome to my world')
  FROM DUAL;

 

---

 

문자처리함수7. CONCAT 문자열 합치기

✅ CONCAT('문자열' | 컬럼명, '문자열' | 컬럼명)
리턴값 타입 CHARACTER

SELECT
　　　　CONCAT('가나다라', 'ABCD')
  FROM DUAL;

• 컬럼의 문자 또는 문자열 두 개를 저달 받아 하나로 합친 후 리턴하는 함수이다.
• 결과만 놓고 본다면 연결연산자 ||와 같은 기능을 한다: '가나다라' || 'ABCD'

 

---

 

문자처리함수8. REPLACE 문자열 대체

✅ REPLACE('문자열' | 컬럼명, 변경 전 문자열, 변경 후 문자열)
리턴값 타입 CHARACTER

SELECT
　　　　REPLACE('서울시 강남구 역삼동', '역삼동', '삼성동')
  FROM DUAL;

 

---

 

3-2. 숫자 처리 함수

숫자처리함수1. ABS 절대값

✅ ABS(숫자 | 숫자로 된 컬럼명)

SELECT
　　　　ABS(-10)
　　　 , ABS(10)
  FROM DUAL;

 

---

 

숫자처리함수2. MOD 나머지

✅ MOD(숫자 | 숫자로 된 컬럼명, 숫자 | 숫자로 된 컬럼명)

SELECT
　　　　MOD(10, 5)
　　　 , MOD(10, 3)
  FROM DUAL;

• 두 수를 나눠 나머지를 구하는 함수이다.
• 처음 인자는 나누어지는 수, 두 번째 인자는 나눌 수에 해당한다.

 

---

 

숫자처리함수3. ROUND 반올림

✅ ROUND(숫자 | 숫자로 된 컬럼명, [반올림 할 위치])

SELECT ROUND(123.456) FROM DUAL;
SELECT ROUND(123.456, 0) FROM DUAL;

• 위 두 예시에서처럼 반올림 할 위치를 지정하지 않거나, 숫자 0으로 둔다면 정수 123을 반환한다.

SELECT ROUND(123.456, 1) FROM DUAL;
SELECT ROUND(123.456, 2) FROM DUAL;

• 소수점 첫째자리에 반올림 진행하면 123.5, 둘째자리는 123.46이 된다.

SELECT ROUND(123.456, -2) FROM DUAL;

• 이밖에도 음수를 위치값으로 지정할 경우, 정수 자리가 바뀌게 된다. 여기서는 결과값이 100이다.

 

---

 

숫자처리함수4. FLOOR 내림처리

*위치 지정 X*

✅ FLOOR(숫자 | 숫자로 된 컬럼명)

SELECT FLOOR(123.456) FROM DUAL;
SELECT FLOOR(123.678) FROM DUAL;

• 소수점 첫째자리 값이 5이상이든 5이하이든 관여치 않고, 소수점자리를 버린 뒤 정수로 반환한다.
• FLOOR 함수는 버림할 위치를 지정하지 않는 특징이 있다. 다음에 기술할 TRUNC 함수와의 차이점이라고 봐도 되겠다.

 

---

 

숫자처리함수5. TRUNC 내림처리(절삭)

✅ TRUNC(숫자 | 숫자로 된 컬럼명, [버림할 위치])

SELECT TRUNC(123.456) FROM DUAL;
SELECT TRUNC(123.678) FROM DUAL;

• TRUNC 함수를 쓸 때는 버림할 위치를 지정할 수 있다.
• 하지만 물론 위처럼 생략하고 사용할 수도 있다. 이때는 123이라는 정수값이 반환된다.

SELECT TRUNC(123.456, 1) FROM DUAL;
SELECT TRUNC(123.456, 2) FROM DUAL;

• 절삭처리 진행되므로 뒤에 오는 숫자가 5이상이든 5이하이든 관계없이 123.4, 123.45라는 결과를 낸다.

SELECT TRUNC(123.456, -1) FROM DUAL;

• 버림할 위치를 음수로 지정한 경우, 정수 자리가 변한다. 따라서 여기서는 120이라는 결과를 얻을 수 있다.

 

---

 

숫자처리함수6. CEIL 올림처리

*위치 지정 X*

✅ CEIL(숫자 | 숫자로 된 컬럼명)

SELECT CEIL(123.456) FROM DUAL;
SELECT CEIL(123.678) FROM DUAL;

• 정수로 올림처리한다. 두 결과 모두 124이다.

SELECT
　　　　ROUND(123.456)
　　　 , FLOOR(123.456)
　　　 , TRUNC(123.456)
　　　 , CEIL(123.456)
  FROM DUAL;

• 다른 함수들은 123이라는 값을 내는 반면, CEIL만 정수로 올림처리하여 124라는 값을 반환한다.

 

---

 

3-3. 날짜 처리 함수

날짜처리함수1. SYSDATE 시스템 날짜

SELECT SYSDATE FROM DUAL;

• 시스템에 저장된 현재 날짜를 반환한다.

---

날짜처리함수2. MONTHS_BETWEEN 개월 수 차이

✅ MONTHS_BETWEEN(날짜, 날짜)
리턴값 타입 NUMBER

 

-- 현재 날짜 기준 근속 개월 수
SELECT
　　　　EMP_NAME
　　　 , HIRE_DATE
　　　 , CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE))
  FROM EMPLOYEE;

• CEIL 함수와 중첩해 쓰지 않으면 굉장히 긴 소수점자리가 나온다.

 

---

 

날짜처리함수3. ADD_MONTHS 날짜에 개월 수 합산

✅ ADD_MONTHS(날짜, 숫자)

SELECT 
　　　　ADD_MONTHS(SYSDATE, 5)
  FROM DUAL;

• 특정 날짜에 개월 수를 더해 반환한다.

 

---

 

날짜처리함수4. NEXT_DAY 돌아오는 요일

✅ NEXT_DAY(기준날짜, 구하려는 요일[또는 지정 숫자])

SELECT SYSDATE, NEXT_DAY(SYSDATE, '목요일') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE, '목') FROM DUAL;

• '목요일', '목' 모두 가능하다.

SELECT SYSDATE, NEXT_DAY(SYSDATE, 5) FROM DUAL;

• 일요일(1)부터 토요일(7)까지 요일별로 지정된 숫자를 통해서도 조회할 수 있다.

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
SELECT SYSDATE, NEXT_DAY(SYSDATE, 'THURSDAY') FROM DUAL;

• 세션이 한글로 돼 있을 때는 'THURSDAY'를 읽지 못한다.
• 위와 같이 SESSION SET 작성해 오라클 설정 언어를 영어로 바꿔 적용할 수 있다.

 

---

 

날짜처리함수5. LAST_DAY 해당 월의 마지막 날짜

✅ LAST_DAY(날짜)

SELECT SYSDATE, LAST_DAY(SYSDATE) FROM DUAL;

• 22/01/31이 조회된다.

 

---

 

날짜처리함수6. EXTRACT 년, 월, 일 추출

✅ EXTRACT(YEAR FROM 날짜) | EXTRACT(MONTH FROM 날짜) | EXTRACT(DAY FROM 날짜)

SELECT
　　　　EXTRACT(YEAR FROM SYSDATE) 년도
　　　 , EXTRACT(MONTH FROM SYSDATE) 월
　　　 , EXTRACT(DAY FROM SYSDATE) 일
  FROM DUAL;

• 2022, 1, 18 형태로 반환된다.

 

---

 

4. 형변환 함수

• 숫자, 문자, 날짜간 형변환을 의미한다.

 

4-1. TO_CHAR

✅ TO_CHAR(날짜, [포맷]) | TO_CHAR(숫자, [포맷])

• 날짜형 데이터를 문자형 데이터로 변경한다.
• 숫자형 데이터를 문자형 데이터로 변경한다.

 

SELECT TO_CHAR(1234, '99999') FROM DUAL;

• 다섯 자리 중 맨 앞에 공백이 자리한다.

SELECT TO_CHAR(1234, '00000') FROM DUAL;

• 다섯 자리 중 맨 앞에 0이 자리한다.

 

SELECT TO_CHAR(1234, 'L99999') FROM DUAL;

• 시스템 기준 화폐단위로 값을 내놓는다. 원화 \1234로 반환된 모습을 확인할 수 있다.

SELECT TO_CHAR(1234, '$99999') FROM DUAL;

• 달러($)로 지정한 경우 달러로 리턴된다.

 

SELECT TO_CHAR(1234, '99,999') FROM DUAL;
SELECT TO_CHAR(1234, '00,000') FROM DUAL;

• 쉼표(,)로 자리 구분할 수 있다. 예시에서는 각각 1,234와 01,234로 출력된다.

 

SELECT TO_CHAR(1234, '999') FROM DUAL;

• 포맷될 자리가 원래 값보다 작으면 잘못된 결과가 출력된다: ####

---

SELECT TO_CHAR(SYSDATE, 'PM HH24:MI:SS') FROM DUAL; 오후 22:57:54
SELECT TO_CHAR(SYSDATE, 'AM HH24:MI:SS') FROM DUAL; 오후 22:57:54

• 날짜 데이터 포맷 적용 시에도 TO_CHAR 함수 사용한다.
• PM/AM 어떤 걸 쓰더라도 날짜 기준 오전오후로 출력한다.

 

SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY') FROM DUAL;

SELECT TO_CHAR(SYSDATE, 'YYYY-fmMM-DD DAY') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY') FROM DUAL;

• DY는 화, DAY는 화요일로 반환된다.
• fmMM-DD 붙이면 1-18, 기본 MM-DD는 01-18처럼 리턴된다.

 

SELECT TO_CHAR(SYSDATE, 'YEAR, Q') || '분기' FROM DUAL;

• TWENTY TWENTY-TWO, 1분기로 반환된다.

 

SELECT 
　　　　TO_CHAR(SYSDATE, 'MM')
　　　 , TO_CHAR(SYSDATE, 'MONTH')
　　　 , TO_CHAR(SYSDATE, 'MON')
　　　 , TO_CHAR(SYSDATE, 'RM')
  FROM DUAL;

• 오늘 날짜에서 월만 출력한 예시이다.
• RM은 로마자 표기를 말한다.

SELECT 
　　　　TO_CHAR(SYSDATE, '"1년 기준 " DDD"일째"')
　　　 , TO_CHAR(SYSDATE, '"달 기준 " DD"일째"')
　　　 , TO_CHAR(SYSDATE, '"주 기준 " D"일째')
  FROM DUAL;

• 오늘 날짜에서 일만 출력한 예시이다.

SELECT
　　　　TO_CHAR(SYSDATE, 'Q"분기"')
　　　 , TO_CHAR(SYSDATE, 'DAY')
　　　 , TO_CHAR(SYSDATE, 'DY')
  FROM DUAL;

• 오늘 날짜에서 분기와 요일 출력한 예시이다.

 

❗ RR과 YY의 차이
RR은 두 자리 년도를 네 자리로 바꿀 때 바꿀 년도가 50년 미만이면 2000년을 적용하고, 50년 이상이면 1900년을 적용한다.
반면 YY는 년도를 바꿀 때 항상 현재 세기(2000년)를 적용한다.

SELECT
　　　　TO_CHAR(TO_DATE('980630', 'YYMMDD'), 'YYYY-MM-DD') 2098-06-30
  FROM DUAL;

SELECT
　　　　TO_CHAR(TO_DATE('980630', 'RRMMDD'), 'YYYY-MM-DD') 1998-06-30
  FROM DUAL;

 

---

 

4-2. TO_DATE

✅ TO_DATE(문자형데이터, [포맷]) | TO_DATE(숫자형데이터, [포맷])

• 문자형 혹은 숫자형 데이터를 받아 날짜형 데이터로 변환하여 리턴한다.

SELECT
　　　　TO_DATE('20100101', 'RRRRMMDD') 10/01/01
  FROM DUAL;

SELECT
　　　　TO_CHAR(TO_DATE('20100101', 'RRRRMMDD'), 'RRRR, MON') 2010, 1월
  FROM DUAL;

SELECT
　　　　TO_DATE('041030 143000', 'RRMMDD HH24MISS')
  FROM DUAL;
SELECT
　　　　TO_CHAR(TO_DATE('041030 143000', 'RRMMDD HH24MISS'), 'DD-MON-RR HH:MI:SS PM')
  FROM DUAL;

WHERE HIRE_DATE >= '20000101'; 자동형변환 OK
WHERE HIRE_DATE >= 20000101; 자동형변환 X... 숫자는 날짜로 자동형변환 X

 

---

 

4-3. TO_NUMBER

✅ TO_NUMBER(문자데이터, [포맷])

SELECT '123' + '456' FROM DUAL;

• 문자 → 숫자는 자동형변환 대상이다.

SELECT '123' + '456A' FROM DUAL; 숫자만 가능
SELECT 123 + '456' FROM DUAL;

• 단, 숫자로 된 문자열만 가능하다.

WHERE HIRE_DATE = '90/02/06'; 자동형변환 OK

WHERE MOD(EMP_ID, 2) = 1; VARCHAR2타입의 문자 → 숫자로 자동형변환 OK 

 

SELECT '1,000,000' + '500,000' FROM DUAL; 사용 불가. 포맷팅 된 문자열을 숫자화하는 작업 필요...

SELECT
　　　　TO_NUMBER('1,000,000', '99,999,999') + TO_NUMBER('500,000', '999,999') 1500000
  FROM DUAL;

• 포맷팅 된 문자열을 숫자화할 때 쓰이는 것이 바로 TO_NUMBER 형변환 함수이다.

 

---

 

5. Null 처리 함수

• NULL로 되어 있는 컬럼값을 지정한 숫자/문자로 변경하여 리턴한다.

 

5-1. NVL

✅ NVL(컬럼명, 컬럼값이 NULL일 때 바꿀 값)

SELECT 
　　　　EMP_NAME
　　　 , BONUS
　　　 , NVL(BONUS, 0)
  FROM EMPLOYEE;

 

5-2. NVL2

✅ NVL2(컬럼명, 값이 있을 때 바꿀 값1, 값이 NULL일 때 바꿀 값2)

• 해당 컬럼에 값이 있으면 바꿀값1, NULL이면 바꿀값2로 변경한다.

SELECT
　　　　EMP_NAME
　　　 , BONUS
　　　 , NVL2(BONUS, 0.7, 0.5)
  FROM EMPLOYEE;

• 기존 보너스 포인트가 있는 직원들은 0.7, NULL인 대상들은 0.5 반영된다.

 

---

 

6. 선택 함수

6-1. DECODE

✅ DECODE(계산식 | 컬럼명, 조건값1, 선택값1, 조건값2, 선택값2...)

• 조건값-선택값을 나열해 여러 경우에 대해 선택할 수 있는 기능을 제공한다.
• 즉 조건이 만족해 일치하는 값을 찾아가도록 한다.

SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , EMP_NO
　　　 , DECODE(SUBSTR(EMP_NO, 8, 1), '2', '여', '남')
  FROM EMPLOYEE;

• 마지막 인자로 조건값 없는 선택값을 작성하면, 어느 것에도 해당하지 않을 때 무조건 그 값이 반영되게 돼 있다. 디폴트(default) 개념과 유사한 것이다.

 

---

 

6-2. CASE

✅ CASE
　　WHEN 조건식1 THEN 결과값1
　　WHEN 조건식2 THEN 결과값2
　　　ELSE 결과값
　 END

• ELSE에 자리하는 결과값은 모든 조건이 불일치할 때 리턴할 디폴트 값을 말한다.

 

---

 

7. 함수 사용 예제

❗ WHERE절에서는 단일행함수만 사용 가능하다.
WHERE SUBSTR(EMP_NO, 8, 1) = '2'; 사용 가능
WHERE SALARY > AVG(SALARY); 사용 불가

 

-- EMPLOYEE 테이블에서 직원들의 주민번호를 조회하여
-- 사원명, 생년, 생월, 생일을 각각 분리하여 조회 ✅
-- 단, 컬럼의 별칭은 사원명, 생년, 생월, 생일로 한다.
SELECT 
　　　　EMP_NAME 사원명
　　　 , SUBSTR(EMP_NO, 1, 2) 생년
　　　 , SUBSTR(EMP_NO, 3, 2) 생월
　　　 , SUBSTR(EMP_NO, 5, 2) 생일
  FROM EMPLOYEE;

▶날짜 데이터에 함수 적용 가능하다.

-- 직원들의 입사일에도 입사년도, 입사월, 입사 날짜를 분리하여 조회 ✅
SELECT
　　　　SUBSTR(HIRE_DATE, 1 ,2) 입사년도
　　　 , SUBSTR(HIRE_DATE, 4, 2) 입사월
　　　 , SUBSTR(HIRE_DATE, 7, 2) 입사 날짜
  FROM EMPLOYEE;

▶WHERE절에서도 함수 사용이 가능하다. 단, 단일행함수만 쓰일 수 있다.

-- EMP_NO를 통해 성별을 판단하여 여직원들의 모든 컬럼 정보를 조회 ✅
SELECT
　　　　*
  FROM EMPLOYEE
 WHERE SUBSTR(EMP_NO, 8, 1) = '2';

▶함수 중첩 사용 가능하다. 함수 안에서 함수를 사용할 수 있다.

-- EMPLOYEE 테이블에서 사원명, 주민번호 조회 ✅
-- 단, 주민번호는 생년월일만 보이게 하고, '-' 다음의 값은 '*'로 바꿔서 출력
SELECT
　　　　EMP_NAME
　　　 , RPAD(SUBSTR(EMP_NO, 1, 7), 14, '*')
  FROM EMPLOYEE;

▶'@' 문자 자리까지 읽지 않도록 -1 적용한다.

-- EMPLOYEE 테이블에서 사원명, 이메일과
-- @ 이후를 제외한 아이디 조회 ✅✅
SELECT
　　　　EMP_NAME
　　　 , EMAIL
　　　 , SUBSTR(EMAIL, 1, INSTR(EMAIL, '@') -1)
  FROM EMPLOYEE;

 

-- EMPLOYEE 테이블에서 사원의 이름, 입사일, 입사 후 6개월이 되는 날짜 조회 ✅
SELECT
　　　　EMP_NAME
　　　 , HIRE_DATE
　　　 , ADD_MONTHS(HIRE_DATE, 6)
  FROM EMPLOYEE;

 

-- EMPLOYEE 테이블에서 근무년수가 20년 이상인 직원의 모든 컬럼 조회(두 가지 방법 가능) ✅
SELECT
　　　　*
  FROM EMPLOYEE
-- WHERE SYSDATE >= ADD_MONTHS(HIRE_DATE, 240);
 WHERE MONTHS_BETWEEN(SYSDATE, HIRE_DATE) >= 240;

▶날짜 빼기 날짜는 숫자값이 나온다. 이를 통해 소요 일수를 계산해볼 수 있다.

▶근무일수1과 근무일수2는 결과적으로 같은 값이다.

-- EMPLOYEE 테이블에서 사원명, 입사일 - 오늘, 오늘 - 입사일 조회(총 근무일수 계산) ✅
SELECT
　　　　EMP_NAME
　　　 , CEIL(ABS(HIRE_DATE - SYSDATE)) "근무일수1"
　　　 , CEIL(SYSDATE - HIRE_DATE) "근무일수2"
  FROM EMPLOYEE;

 

-- EMPLOYEE 테이블에서 사원명, 입사일, 입사한 월의 근무일수 조회 ✅
SELECT
　　　　EMP_NAME
　　　 , HIRE_DATE
　　　 , LAST_DAY(HIRE_DATE) - HIRE_DATE + 1 "입사월의 근무일수"
  FROM EMPLOYEE;

 

-- EMPLOYEE 테이블에서 사원이름, 입사년, 입사월, 입사일 조회 ✅
SELECT
　　　　EMP_NAME 사원이름
　　　 , EXTRACT(YEAR FROM HIRE_DATE) 입사년
　　　 , EXTRACT(MONTH FROM HIRE_DATE) 입사월
　　　 , EXTRACT(DAY FROM HIRE_DATE) 입사일
  FROM EMPLOYEE;
 ORDER BY 입사년 ASC, 사원이름;
-- ORDER BY EMP_NAME ASC;
-- ORDER BY EMP_NAME DESC;
-- ORDER BY 사원이름;
-- ORDER BY 1;

❗ ORDER BY는 기본적으로 오름차순 정렬이다. ASC, DESC 키워드는 생략 가능하다.
컬럼명이 아닌 별칭을 써도 정상작동한다. 하단에 숫자 1은 SELECT절에서 나열된 순서를 말한다.

 

-- EMPLOYEE 테이블에서 직원의 이름, 입사일, 근무년수를 조회 ✅
-- 단, 근무년수를 현재년도 - 입사년도로 조회
SELECT
　　　　EMP_NAME 이름
　　　 , HIRE_DATE 입사일
　　　 , EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE) 근무년수
  FROM EMPLOYEE;

-- MONTHS_BETWEEN으로 만 근무년수 조회
SELECT
　　　　EMP_NAME 이름
　　　 , HIRE_DATE 입사일
　　　 , FLOOR(MONTHS_BETWEEN(SYSDATE, HIRE_DATE) / 12) 근무년수
  FROM EMPLOYEE;

 

-- 직원 테이블에서 사원명, 급여 조회 ✅
-- 급여는 '\9,000,000' 형식으로 표시
SELECT
　　　　EMP_NAME
　　　 , TO_CHAR(SALARY, 'L99,999,999')
  FROM EMPLOYEE;

 

-- 입사일 조회 예) 1999-12-31 ✅
SELECT
　　　　EMP_NAME
　　　 , TO_CHAR(HIRE_DATE, 'YYYY-MM-DD') 입사일
  FROM EMPLOYEE;

 

-- 입사일 조회 예) 1999년 12월 31일 ✅
SELECT
　　　　EMP_NAME
　　　 , TO_CHAR(HIRE_DATE, 'YYYY"년" MM"월" DD"일"') 입사일
  FROM EMPLOYEE;

 

-- 상세입사일 조회 예) 1999/12/31 00:00:00 ✅✅
SELECT
　　　　EMP_NAME
　　　 , TO_CHAR(HIRE_DATE, 'YYYY/MM/DD HH24:MI:SS') 상세입사일
  FROM EMPLOYEE;

 

-- EMPLOYEE 테이블에서 이름, 입사일 조회 ✅✅
-- 입사일 포맷은 '2018년 6월 15일 (수)' 형식으로 출력 처리
SELECT
　　　　EMP_NAME
　　　 , TO_CHAR(HIRE_DATE, 'RRRR"년" fmMM"월" DD"일" "("DY")"')
  FROM EMPLOYEE;

 

-- EMPLOYEE 테이블에서 2000년도 이후에 입사한 사원의 사번, 이름, 입사일을 조회 ✅✅
SELECT
　　　　EMP_ID
　　　 , EMP_NAME
　　　 , HIRE_DATE
  FROM EMPLOYEE
 WHERE HIRE_DATE >= '20000101'; 자동형변환 OK
 WHERE HIRE_DATE >= 20000101; 자동형변환 X... 숫자는 날짜로 자동형변환 X
 WHERE HIRE_DATE >= TO_DATE(20000101, 'RRRRMMDD'); OK

 

-- 직원 테이블에서 사원번호가 201인 사원의
-- 이름, 주민번호 앞자리, 주민번호 뒷자리,
-- 주민번호 앞자리와 뒷자리의 합을 조회 ✅✅
-- 단, 자동형변환 사용하지 않고 조회 자동형변환 허용한다면 TO_NUMBER, TO_CHAR 생략 가능!
SELECT
　　　　EMP_NAME
　　　 , SUBSTR(EMP_NO, 1, 6)
　　　 , SUBSTR(EMP_NO, 8)
　　　 , TO_NUMBER(SUBSTR(EMP_NO, 1, 6)) + TO_NUMBER(SUBSTR(EMP_NO, 8))
  FROM EMPLOYEE
 WHERE EMP_ID = TO_CHAR(201);

 

-- 직원의 급여를 인상하고자 한다.
-- 직급코드가 J7인 직원은 급여의 10%를 인상하고
-- 직급코드가 J6인 직원은 급여의 15%를 인상하고
-- 직급코드가 J5인 직원은 급여의 20%를 인상한다.
-- 그 외 직급의 직원은 5%만 인상한다.
-- 직원 테이블에서 직원명, 직급코드, 급여, 인상급여(위 조건)을 조회 ✅
SELECT
　　　　EMP_NAME 직원명
　　　 , JOB_CODE 직급코드
　　　 , SALARY 급여
　　　 , DECODE(JOB_CODE, 'J7', SALARY * 1.1,
　　　　　　　　　　　　'J6', SALARY * 1.15,
　　　　　　　　　　　　'J5', SALARY * 1.2,
　　　　　　　　　　　　SALARY * 1.05) 인상급여
  FROM EMPLOYEE;

 

-- 사번, 사원명, 급여를 EMPLOYEE 테이블에서 조회하고
-- 급여가 500만원 초과이면 '고급'
-- 300~500만원 사이이면 '중급'
-- 그 이하는 '초급'으로 출력 처리하고 별칭은 '구분'으로 한다. ✅✅
SELECT
　　　　EMP_ID 사번
　　　 , EMP_NAME 사원명
　　　 , SALARY 급여
　　　 , CASE
　　　　　 WHEN SALARY > 5000000 THEN '고급'
　　　　　 WHEN SALARY BETWEEN 3000000 AND 5000000 THEN '중급'
　　　　　 ELSE '초급'
　　　　END AS 구분
FROM EMPLOYEE;