CH 6. 데이터 처리와 가공을 위한 오라클 함수

*출처: <비전공자도 기초부터 확실하게! 오라클로 배우는 데이터베이스 입문>, 이지훈, 이지스퍼블리싱

 

6-1. 오라클 함수

1) 오라클 함수의 종류 

- 내장 함수: 오라클에서 기본으로 제공 

- 사용자 정의 함수: 사용자가 필요에 의해 직접 정의 

 

2) 내장 함수의 종류

- 내장 함수는 입력 방식에 따라 데이터 처리에 사용하는 행이 나뉜다.

- 데이터가 한 행씩 입력되고 입력된 한 행당 결과가 하나씩 나오는 함수를 단일행 함수(single-row function)라고 한다.  

- 반면에 여러 행이 입력되어 하나의 행으로 결과가 반환되는 함수를 다중행 함수(multiple-row function)라고 한다.

- 단일행 함수와 다중행 함수는 다루는 자료형에 따라 조금 더 세분화된다.

- 6장에서는 실무에서 많이 사용하는 단일행 함수부터 먼저 살펴본다. 

 

6-2. 문자 데이터를 가공하는 문자 함수

- 실무에서 자주 사용하는 데이터는 문자, 숫자, 날짜 데이터이다.

 

1) 대소문자를 바꿔주는 UPPER, LOWER, INITCAP 함수

함수	설명
UPPER(문자열)	괄호 안 문자 데이터를 모두 대문자로 변환하여 반환
LOWER(문자열)	괄호 안 문자 데이터를 모두 소문자로 변환하여 반환
INITCAP(문자열)	괄호 안 문자 데이터 중 첫 글자는 대문자로, 나머지 문자를 소문자로 변환 후 반환

예) UPPER, LOWER, INITCAP 함수 사용하기 

SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) 
FROM EMP;

 - 조건식 양쪽 항목의 문자열 데이터를 모두 대문자나 소문자로 바꿔서 비교하면 실제 검색어의 대소문자 여부와 상관없이 검색 단어와 일치한 문자열을 포함한 데이터를 찾을 수 있다. 

 

 예) UPPER 함수로 문자열 비교하기 (사원 이름에 SCOTT 단어를 포함한 데이터 찾기)

SELECT * FROM EMP WHERE UPPER(ENAME) LIKE UPPER('%SCOTT%');

 

2) 문자열 길이를 구하는 LENGTH 함수 

- 특정 문자열의 길이를 구할 때 LENGTH 함수를 사용한다.

 

예) 선택한 열의 문자열 길이 구하기: 사원 이름(ENAME)의 문자열 길이 구하기

SELECT ENAME, LENGTH(ENAME) FROM EMP;

 

예)  WHERE절에서 LENGTH 함수 사용하기: 사원 이름의 길이가 5 이상인 행 출력하기

SELECT ENAME, LENGTH(ENAME) FROM EMP WHERE LENGTH(ENAME) >= 5;

 

- LENGTHB 함수: 문자열 데이터의 길이가 아닌 바이트 수를 반환하는 LENGTHB 함수도 존재한다. 

예) '한글' 문자열 길이는 2지만 바이트 수는 6이므로 두 값의 결과가 다르다.

* 오라클 설치 시 문자 집합을 어떻게 설정했느냐에 따라 한글을 인식하는 바이트 길이가 달라진다. 한글의 바이트 길이는 2바이트 또는 3바이트인데, UTF-8인 경우 3바이트를 소모한다. 

* dual 테이블: 더미(dummy) 테이블로 임시 연산이나 함수의 결과 값 확인 용도로 종종 사용된다. 

 

 

3) 문자열 일부를 추출하는 SUBSTR 함수 

- 주민등록번호 중 생년월일 앞자리만 필요하거나 전화번호의 마지막 네 자리 숫자만 추출하는 경우와 같이 문자열 중 일부를 추출할 때 SUBSTR 함수를 사용한다. 

함수	설명
SUBSTR(문자열 데이터, 시작 위치, 추출 길이)	문자열 데이터의 시작 위치부터 추출 길이만큼 추출한다. 시작 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 시작한다.
SUBSTR(문자열 데이터, 시작 위치)	문자열 데이터의 시작 위치부터 문자열 데이터의 끝까지 추출한다. 시작 위치가 음수일 경우에는 마지막 위치부터 거슬러 올라간 위치에서 끝까지 추출한다.

예) SUBSTR 함수를 사용하는 예

SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)
FROM EMP;

- 다른 함수의 결과값을 SUBSTR 함수의 입력값으로 사용할 수도 있다. 

- SUBSTR 함수 안에 다음과 같이 LENGTH 함수를 사용하는 경우가 종종 있다.

예) SUBSTR 함수 안에 다른 함수 (LENGTH) 함께 사용하기: 음수로 시작 위치 값 사용 

SELECT JOB, 
       SUBSTR(JOB, -LENGTH(JOB)),
       SUBSTR(JOB, -LENGTH(JOB), 2),
       SUBSTR(JOB, -3)
FROM EMP;

4) 문자열 데이터 안에서 특정 문자 위치를 찾는 INSTR 함수 

- 문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지를 알고자 할 때 INSTR 함수를 사용한다.

- INSTR 함수는 총 네 개의 입력 값을 지정할 수 있으며 최소 두 개의 입력값, 즉 원본 문자열 데이터와 원본 문자열 데이터에서 찾으려는 문자 이렇게 두 가지는 반드시 지정해 주어야 한다. 

-- 기본형식
INSTR([대상 문자열 데이터(필수)], 
      [위치를 찾으려는 부분 문자(필수)],
      [위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1)],
      [시작 위치에서 찾으려는 문자가 몇 번째인지 지정(선택, 기본값은 1)])

예) INSTR 함수로 문자열 데이터에서 특정 문자열 찾기

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR_1,
       INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR_2, --5번째 글자 O부터 확인하여 첫 번째로 등장한 L을 찾는다.
       INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR_3 -- 두 번째 글자 E부터 시작하여 두번째 L을 찾는다. 
FROM DUAL;

 

- INSTR 함수의 세 번째 입력 데이터, 즉 위치 찾기를 시작하는 위치 값에 음수를 쓸 때 원본 문자열 데이터의 오른쪽 끝부터 왼쪽 방향으로 검색한다. 만약 찾으려는 문자가 문자열 데이터에 포함되어 있지 않다면 위치 값이 없으므로 0을 반환한다.

- 따라서 INSTR 함수를 LIKE와 비슷한 용도로 사용할 수도 있다. 예를 들어 사원 이름에 S가 포함된 사원을 출력하고 싶다면 LIKE 연산자나 INSTR 함수를 WHERE절에 다음과 같이 적용하기도 한다.

SELECT * FROM EMP WHERE INSTR(ENAME, 'S') > 0;

SELECT * FROM EMP WHERE ENAME LIKE '%S%'

 

 

5) 특정  문자를 다른 문자로 바꾸는 REPLACE 함수 

- REPLACE 함수는 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우에 유용한 함수이다.

- 만약 대체할 문자를 입력하지 않는다면 찾는 문자로 지정한 문자는 문자열 데이터에서 삭제된다.

-- 기본 형식
REPLACE([문자열 데이터 또는 열 이름(필수)], [찾는 문자(필수)], [대체할 문자(선택)])

 

예) REPLACE 함수로 문자열 안에 있는 특정 문자 바꾸기 

SELECT '010-1234-5678' AS REPLACE_BEFORE,
       REPLACE('010-1234-5678', '-', ' ') AS REPLACE_1, -- '-'를 ' ' 공백으로 대체 
       REPLACE('010-1234-5678','-') AS REPLACE_2 -- '-'를 없앰
FROM DUAL;

 

6) 데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수

- 데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우에 나머지 공간을 특정 문자로 채우는 함수이다. 

- LPAD: Left Padding(왼쪽 패딩), 남은 빈 공간을 왼쪽에 채운다. 

- RPAD: Right Padding(오른쪽 패딩), 남은 빈 공간을 오른쪽에 채운다. 

- 만약 빈 공간에 채울 문자를 지정하지 않으면 LPAD와 RPAD 함수는 빈 공간의 자릿수만큼 공백 문자로 띄운다. 

--기본 형식
LPAD([문자열 데이터 또는 열이름(필수)], [지정한 자릿수(필수)], [빈 공간에 채울 문자(선택)])
RPAD([문자열 데이터 또는 열이름(필수)], [지정한 자릿수(필수)], [빈 공간에 채울 문자(선택)])

SELECT 'Oracle',
   LPAD('Oracle', 10, '#') AS LPAD_1,
   RPAD('Oracle', 10, '*') AS RPAD_1,
   LPAD('Oracle', 10) AS LPAD_2,
   RPAD('Oracle', 10) AS RPAD_2
FROM DUAL;

- 이러한 문자열 데이터의 특정 문자로의 채움, 즉 패딩 처리는 데이터의 일부만 노출해야하는 개인정보를 출력할 때 다음과 같이 사용하기도 한다. 

예) RPAD 함수를 사용하여 개인정보 뒷자리 * 표시로 출력하기 

SELECT
   RPAD('971225-', 14, '*') AS RPAD_JMNO,
   RPAD('010-1234-', 13, '*') AS RPAD_PHONE
FROM DUAL;

 

7) 두 문자열 데이터를 합치는 CONCAT 함수

- CONCAT 함수는 두 개의 문자열 데이터를 하나의 데이터로 연결해 주는 역할을 한다.

- 두 개의 입력 데이터 지정을 하고 열이나 문자열 데이터 모두 지정할 수 있다. 

예) 두 열 사이에 콜론(:) 넣고 연결하기 

SELECT CONCAT(EMPNO, ENAME),
       CONCAT(EMPNO, CONCAT(' : ', ENAME))
FROM EMP
WHERE ENAME = 'SCOTT';

- CONCAT을 사용한 결과 값은 다시 다른 CONCAT 함수의 입력 값으로 사용하는 것도 가능하다.

 

8) 특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수

- TRIM, LTRIM, RTRIM 함수는 문자열 데이터 내에서 특정 문자를 지우기 위해 사용한다. 

- 원본 문자열 데이터를 제외한 나머지 데이터는 모두 생략할 수 있다. 삭제할 문자가 생략될 경우에 기본적으로 공백을 제거한다. 

- 삭제 옵션은 왼쪽에 있는 글자를 지우는 LEADING, 오른쪽에 있는 글자를 지우는 TRAILING, 양쪽의 글자를 모두 지우는 BOTH를 사용한다. 

- 보통 실무에서 TRIM 함수는 검색 기준이 되는 데이터에 혹시나 들어 있을지도 모르는 양쪽 끝의 공백을 제거할 때 많이 사용한다. 예를 들어 유저가 로그인을 하려고 아이디를 입력했을 때 사용자 실수로 스페이스바가 눌러져 공백이 함께 입력되는 경우다. 

 

8-1) TRIM 함수의 기본 사용법 

-- 기본 형식
TRIM([삭제 옵션(선택)] [삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)])

- TRIM 함수의 삭제할 문자는 필수가 아니므로 지정하지 않아도 된다. 다만 삭제할 문자가 없으면 공백이 제거된다.

 

예) TRIM 함수로 공백 제거하여 출력하기 (삭제할 문자가 없을 때) 

SELECT '[' || TRIM(' _ _ORACLE_ _ ') || ']' AS TRIM,
       '[' || TRIM(LEADING FROM ' _ _ORACLE_ _ ') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING FROM ' _ _ORACLE_ _ ') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH FROM ' _ _ORACLE_ _ ') || ']' AS TRIM_BOTH
FROM DUAL;

예) TRIM 함수로 공백 제거하여 출력하기 (삭제할 문자가 있을 때)

SELECT '[' || TRIM('_' FROM '_ _ORACLE_ _') || ']' AS TRIM,
       '[' || TRIM(LEADING '_' FROM '_ _ORACLE_ _') || ']' AS TRIM_LEADING,
       '[' || TRIM(TRAILING '_' FROM '_ _ORACLE_ _') || ']' AS TRIM_TRAILING,
       '[' || TRIM(BOTH '_' FROM '_ _ORACLE_ _') || ']' AS TRIM_BOTH
FROM DUAL;

 

8-2) LTRIM, RTRIM 함수의 기본 사용법 

- LTRIM, RTRIM 함수는 각각 왼쪽, 오른쪽의 지정 문자를 삭제하는데 사용한다.

- TRIM과 마찬가지로 삭제할 문자를 지정하지 않을 경우 공백 문자가 삭제된다. 

- TRIM 함수와 다른점은 삭제할 문자를 하나만 지정하는 것이 아니라 여러 문자 지정이 가능하다는 것이다. 

-- 기본 형식
LTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])
RTRIM([원본 문자열 데이터(필수)], [삭제할 문자 집합(선택)])

예) TRIM, LTRIM, RTRIM 사용하여 문자열 출력하기 

SELECT '[' || TRIM(' _ORACLE_ ') || ']' AS TRIM,
       '[' || LTRIM(' _ORACLE_ ') || ']' AS LTRIM,
       '[' || LTRIM('<_ORACLE_>', '<_') || ']' AS LTRIM_2,
       '[' || RTRIM(' _ORACLE_ ') || ']' AS RTRIM,
       '[' || RTRIM('<_ORACLE_>', '_>') || ']' AS RTRIM_2
FROM DUAL;

 

6-3. 숫자 데이터를 연산하고 수치를 조정하는 숫자 함수

함수	설명
ROUND	지정된 숫자의 특정 위치에서 반올림한 값을 반환
TRUNC	지정된 숫자의 특정 위치에서 버림한 값을 반환
CEIL	지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환
FLOOR	지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환
MOD	지정된 숫자를 나눈 나머지 값을 반환

1) 특정 위치에서 반올림하는 ROUND 함수

- ROUND 함수는 특정 숫자를 반올림하되 반올림할 위치를 지정할 수 있다.

- 반올림할 위치를 지정하지 않으면 소수점 첫째 자리에서 반올림한 결과가 반환된다.

--기본 형식 
ROUND([숫자(필수)], [반올림 위치(선택)]) -- 0: 소수점 첫째자리, 1: 소수점 둘째자리

SELECT ROUND (1234.5678) AS ROUND,
       ROUND (1234.5678) AS ROUND_0, -- 소수점 첫째자리 반올림 
       ROUND (1234.5678, 1) AS ROUND_1, -- 소수점 둘째자리 반올림 
       ROUND (1234.5678, 2) AS ROUND_2,  -- 소수점 셋째자리 반올림 
       ROUND (1234.5678, -1) AS ROUND_MINUS1, -- 자연수 첫째자리 반올림
       ROUND (1234.5678, -2) AS ROUND_MINUS2  -- 자연수 둘째자리 반올림 
FROM DUAL;

 

2) 특정 위치에서 버리는 TRUNC 함수

- TRUNC 함수는 지정된 자리에서 숫자를 버림 처리하는 함수이다. 

- 버림 처리할 자릿수 지정이 가능하며, 버림 위치를 지정하지 않으면 소수점 첫째 자리에서 버림 처리 된다.

-- 기본 형식 
TRUNC([숫자(필수)], [버림 위치(선택)])

SELECT TRUNC (1234.5678) AS ROUND,
       TRUNC (1234.5678) AS ROUND_0, -- 소수점 첫째자리 이하 버림 
       TRUNC (1234.5678, 1) AS ROUND_1, -- 소수점 둘째자리 이하 버림  
       TRUNC (1234.5678, 2) AS ROUND_2,  -- 소수점 셋째자리 이하 버림 
       TRUNC (1234.5678, -1) AS ROUND_MINUS1, -- 자연수 첫째자리 이하 버림
       TRUNC (1234.5678, -2) AS ROUND_MINUS2  -- 자연수 둘째자리 이하 버림
FROM DUAL;

 

3) 지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수

- CEIL 함수와 FLOOR 함수는 각각 입력된 숫자와 가까운 큰 정수, 작은 정수를 반환하는 함수이다.

-- 기본 형식
CEIL([숫자(필수)])
FLOOR ([숫자(필수)])

SELECT CEIL(3.14),
       FLOOR(3.14),
       CEIL(-3.14),
       FLOOR(-3.14)
FROM DUAL;

 

3) 숫자를 나눈 나머지 값을 구하는 MOD 함수 

- MOD 함수는 나머지를 구하는 함수이다. 

//기본 형식
MOD([나눗셈 될 숫자(필수)], [나눌 숫자(필수)])

SELECT MOD(15,6),
       MOD(10,2),
       MOD(11,2)
FROM DUAL;

 

6-4. 날짜 데이터를 다루는 날짜 함수

- 오라클에서 날짜 데이터, 즉 DATE 형 데이터는 다음과 같이 간단한 연산이 가능하다.

- 그러나 날짜 데이터끼리의 더하기는 연산이 되지 않는다는 것에 주의해야한다.

연산	설명
날짜 데이터 + 숫자	날짜 데이터보다 숫자만큼 일수 이후의 날짜
날짜 데이터 - 숫자	날짜 데이터보다 숫자만큼 일수 이전의 날짜
날짜 데이터 - 날짜 데이터	두 날짜 데이터 간의 일수 차이
날짜 데이터 + 날짜 데이터	연산 불가, 지원하지 않음

 

1) SYSDATE 함수

- SYSDATE 함수는 별다른 입력 데이터 없이 , 오라클 데이터베이스 서버가 놓인 OS의 현재 날짜와 시간을 보여준다. 

SELECT SYSDATE AS NOW,
       SYSDATE-1 AS YESTERDAY,
       SYSDATE+1 AS TOMORROW
FROM DUAL;

2) 몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수

- ADD_MONTHS 함수는 특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수이다.

- 다음과 같이 날짜형 데이터 그리고 더할 개월 수를 정수로 지정하여 사용한다.

-- 기본 형식
ADD_MONTHS([날짜 데이터(필수)], [더할 개월 수(정수)(필수)])

SELECT SYSDATE,
       ADD_MONTHS(SYSDATE,3)
FROM DUAL;

- ADD_MONTHS 함수는 은근히 자주 사용되는 함수로 윤년 등의 이유로 복잡해질 수 있는 날짜 계산을 간단히 만들어 준다.

- 예를 들어 EMP 테이블에서 사원이 입사한 지 10주년이 되는 날짜를 구하고 싶다면 아래와 같이 ADD_MONTHS 함수에 120개월, 즉 10년만큼의 개월 수를 입력하여 날짜를 구하는 것도 가능하다.

3) 두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수 

- MONTHS_BETWEEN 함수는 두 개의 날짜 데이터를 입력하고 두 날짜 간의 개월 수 차이를 구하는데 사용한다.

-- 기본 형식
MONTHS_BETWEEN([날짜 데이터1(필수)], [날짜 데이터2(필수)])

SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
       MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,
       MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTHS2,
       TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTHS3
FROM EMP;

 

4) 돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수

- NEXT_DAY 함수는 특정 날짜를 기준으로 돌아오는 요일의 날짜를 출력해 주는 함수이다. 

//기본 형식 
NEXT_DAY([날짜 데이터(필수)], [요일 문자(필수)])

- LAST_DAY 함수는 하나의 날짜 데이터만을 입력 데이터로 사용하며 해당 날짜가 속한 달의 마지막 날짜를 출력해 주는 함수이다.

// 기본 형식
LAST_DAY([날짜 데이터(필수)])

예)

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

 

5) 날짜의 반올림, 버림을 하는 ROUND, TRUNC 함수 

- ROUND, TRUNC 함수는 날짜 데이터를 입력 데이터로 사용할 수도 있다. 

- 이때는 소수점위치 정보를 입력하지 않고 반올림, 버림의 기준이 될 포맷(format)값을 지정해준다.

입력 데이터 종류	사용 방식
숫자 데이터	ROUND([숫자(필수)],[반올림위치(선택)])
	TRUNC([숫자(필수)],[버림위치(선택)])
날짜 데이터	ROUND([날짜데이터(필수)],[반올림 기준 포맷])
	TRUNC([날짜데이터(필수)],[버림 기준 포맷])

- 오라클에서 날짜 데이터를 사용할 때 기준 포맷 값은 아래와 같다.

 *출처:https://pridiot.tistory.com/96

예1) ROUND 함수 사용하여 날짜 데이터 출력하기

SELECT SYSDATE,
       ROUND(SYSDATE, 'CC') AS FORMAT_CC,
       ROUND(SYSDATE, 'YYYY') AS FORMAT_YYYY,
       ROUND(SYSDATE, 'Q') AS FORMAT_Q,
       ROUND(SYSDATE, 'DDD') AS FORMAT_DDD,
       ROUND(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL;

 

예2) TRUNC 함수 사용하여 날짜 데이터 출력하기 

SELECT SYSDATE,
       TRUNC(SYSDATE, 'CC') AS FORMAT_CC,
       TRUNC(SYSDATE, 'YYYY') AS FORMAT_YYYY,
       TRUNC(SYSDATE, 'Q') AS FORMAT_Q,
       TRUNC(SYSDATE, 'DDD') AS FORMAT_DDD,
       TRUNC(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL;

 

6-5. 자료형을 변환하는 형 변환 함수

- 오라클에서는 저장할 데이터 종류, 즉 자료형을 다양하게 제공한다. 이렇게 지정된 자료형을 필요에 따라 바꿔주어야 하는 때가 있다.

- 각 데이터에 지정된 자료형을 바꿔주는 함수를 형 변환 함수라고 한다.

 

- 오라클에서 숫자와 문자열(숫자)을 더해서 출력하는 것이 가능하다. 이는 '자동 형 변환' 덕분이다.

예) EMPNO (숫자 자료형 열 값, 여기서 예시로 1000이라고 하겠음)+ '500'  -> 출력 시 1500이 나옴

- 하지만 이와 반대로 문자열(문자)과 숫자를 더해서 출력하는 것은 불가능하다.

예) 'ABCD' + EMPNO(숫자 자료형 열 값, 여기서 예시로 1000이라고 하겠음)  -> 에러 

 

- 오라클에서 자료형이 자동으로 변환되는 방식이 아닌 사용자, 즉 우리가 자료형을 직접 지정해주는 방식을 명시적 형 변환이라고 한다.

- 형 변환 함수를 사용하여 자료형을 변환해 주는 방식이 바로 명시적 형 변환에 해당한다. 

- 형 변환 함수의 종류는 아래와 같다.

종류	설명
TO_CHAR	숫자 또는 날짜 데이터를 문자 데이터로 변환
TO_NUMBER	문자 데이터를 숫자 데이터로 변환
TO_DATE	문자 데이터를 날짜 데이터로 변환

- 형 변환 함수를 사용하면 다음과 같이 숫자 데이터와 문자 데이터, 문자 데이터와 날짜 데이터 간의 형 변환이 가능하다.

- 문자를 중심으로 숫자 또는 날짜 데이터의 변환이 가능하다.

** 숫자 데이터(NUMBER) <-> 문자 데이터(CHARACTER) <-> 날짜 데이터(DATE)

 

1) 날짜, 숫자를 문자 데이터로 변환하는 TO_CHAR 함수 

- TO_CHAR 함수는 날짜, 숫자 데이터를 문자 데이터로 변환해주는 함수이다. 

- 주로 날짜 데이터에서 문자 데이터로 변환하는 데 많이 사용하며 다음과 같이 작성한다.

//기본 형식
TO_CHAR([날짜데이터(필수)], '[출력되길 원하는 문자 형태(필수)]')

1-1) 원하는 출력 형태로 날짜 출력하기 

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS 현재날짜씨간
FROM DUAL;

- 자주 사용하는 날짜 표현 형식은 아래와 같다.

형식	설명	형식	설명
CC	세기	DD	일(2자리 숫자)
YYYY, RRRR	연(4자리 숫자)	DDD	1년 중 며칠(1~366)
YY, RR	연(2자리 숫자)	DY	요일(언어별 요일 이름 약자)
MM	월(2자리 숫자)	DAY	요일(언어별 요일 이름 전체)
MON	월(언어별 월 이름 약자)	W	1년 중 몇 번째 주(1~53)
MONTH	월(언어별 월 이름 전체)

*YY: 어떤 두 자리수가 입력되어도 현 시점의 연도와 동일한 연도로 계산
예) 51/01/01 -> 2051/01/01 
*RR: 현 시점의 연도의 끝 두자리 수가 00~49, 50~99 그리고 입력된 수치가 00~49, 50~99인 경우를 계산하여 비교적 가까운 날짜 데이터를 계산 

예) 51/01/01 -> 1951/01/01

- 월과 요일의 표기는 사용 언어에 따라 다르게 출력되는데 기본적으로는 현재 사용하고 있는 언어에 맞게 출력된다.

 

1-2) 특정 언어에 맞춰서 날짜 출력하기 

//기본 형식 
TO_CHAR([날짜 데이터(필수)], '[출력되길 원하는 문자 형태(필수)]','NLS_DATE_LANGUAGE = language'(선택))

1-3) 시간 형식 지정하여 출력하기

형식	설명	형식	설명
HH24	24시간으로 표현한 시간	SS	초
HH, HH12	12시간으로 표현한 시간	AM, PM, A.M., P.M.	오전, 오후 표시
MI	분

 

 

2) 문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수

- 문자 데이터와 숫자 데이터를 연산하는 것이 아니라, 아래와 같이 문자 데이터끼리 연산하여 출력할 때는 에러가 발생한다.

SELECT '1,300' - '1,500' FROM DUAL; -- 연산 불가, 에러

- 흔한 일은 아니지만 위와 같이 숫자 데이터가 가공된 문자 데이터로 저장되어 있고 그 데이터를 산술 연산에 사용하고자 하는 경우, 문자 데이터를 숫자 형태로 강제로 인식시켜주어야 한다. 이때 사용하는 함수가 TO_NUMBER 함수이다.

TO_NUMBER('[문자열 데이터(필수)]', '[인식될 숫자형태(필수)]')

SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999') FROM DUAL;

 

3) 문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수

- TO_DATE 함수는 문자열 데이터를 날짜 데이터로 바꿔줄 수 있다.

TO_DATE('[문자열 데이터(필수)]', '[인식될 날짜형태(필수)]')

SELECT TO_DATE('2018-07-14', 'YYYY-MM-DD') AS TODATE1,
       TO_DATE('20180714', 'YYYY-MM-DD') AS TODATE2
FROM DUAL

- 날짜 함수에서 언급했듯이 날짜 데이터끼리는 간단한 연산이 가능하다. 날짜 데이터는 상대적으로 이전 날짜인 데이터가 이후 날짜인 데이터보다 크기가 작은 데이터로 여겨지기 때문에 다음과 같이 TO_DATE 함수와 비교 연산자를 이용하여 EMP 테이블에서 2000년 1월 1일 이후 입사한 사원을 찾는 것도 가능하다.

SELECT * FROM EMP 
WHERE HIREDATE > TO_DATE('2000-01-01', 'YYYY/MM/DD'); -- 2000년 1월 1일 이후 입사자를 찾는다.

 

6-6. NULL 처리 함수

- 데이터가 NULL인 경우 산술 연산자나 비교 연산자는 우리가 예상한 대로 동작하지 않는다. 

- 하지만 특정 열의 데이터가 NULL일 경우 연산 수행을 위해 데이터를 NULL이 아닌 다른 값으로 대체해 주어야 할 때가 종종 발생한다. 

- 이때 NVL 함수와 NVL2 함수가 유용하게 사용된다.

 

1) NVL 함수의 기본 사용법

// 기본 형식
NVL([NULL인지 여부를 검사할 데이터 또는 열(필수)], [앞의 데이터가 NULL일 경우 반환할 데이터(필수)])

- NVL 함수는 첫 번째 입력 데이터가 NULL이 아니면 그 데이터를 그대로 반환하고 NULL이라면 두 번째 입력 데이터에 지정한 값을 반환한다. 

SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM,
      NVL(COMM, 0), --COMM 열의 값 중 null 이 있으면 0으로 반환  
      SAL+NVL(COMM, 0) -- COMM 열의 값 중 null 이 있으면 0으로 반환하고 나서 SAL을 더한다. 
FROM EMP;

2) NVL2 함수의 기본 사용법

- NVL2 함수는 NVL 함수와 비슷하지만 데이터가 NULL이 아닐 때 반환할 데이터를 추가로 지정해줄 수 있다. 

// 기본 형식
NVL2([NULL인지 여부를 검사할 데이터 또는 열(필수)], 
    [앞의 데이터가 NULL이 아닐 경우 반환할 데이터 또는 계산식(필수)],
    [앞의 데이터가 NULL일 경우 반환할 데이터 또는 계산식(필수)])

- 예를 들어 COMM 열이 null 이 아니라면 O를, null 이라면 X를 표기하여 급여 외 추가 수당의 존재 여부만 확인할 때 NVL2 함수를 사용할 수 있다. 왜냐하면 금액 같은 미감한 데이터는 노출되지 않아야 하는 경우도 있기 때문이다.

SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM,
      NVL2(COMM, 'O', 'X'), 
      NVL2(COMM, SAL*12+COMM, SAL*12) AS ANNSAL 
FROM EMP;

 

6-7. 상황에 따라 다른 데이터를 반환하는 DECODE 함수와 CASE 문 

- NVL, NVL2 함수는 데이터가 NULL인 경우에 어떤 데이터를 반환할지 정하는 함수이다. 

- 특정 열 값이나 데이터 값에 따라 어떤 데이터를 반환할지 정할 때는 DECODE 함수 또는 CASE 문을 사용한다.

 

1) DECODE 함수

- 기준이 되는 데이터를 먼저 지정한 후 해당 데이터 값에 따라 다른 결과 값을 내보내는 함수이다.

- 만약 DECODE 함수의 맨 마지막 데이터, 즉 조건에 해당하는 값이 없을 때 반환 값을 지정하지 않으면 NULL이 반환된다. 

//기본 형식 
DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과],
       [조건1], [데이터가 조건1과 일치할 때 반환할 결과],
       [조건2], [데이터가 조건2와 일치할 때 반환할 결과],
       [조건3], [데이터가 조건3과 일치할 때 반환할 결과],
       ...
       [조건n], [데이터가 조건n과 일치할 때 반환할 결과],
       [위 조건1~조건n과 일치한 경우가 없을 때 반환할 결과])

 

2) CASE 문 

- CASE 문은 DECODE 함수와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용한다.

- 기준 데이터를 반드시 명시하고 그 값에 따라 반환 데이터를 정하는 DECODE 함수와 달리 CASE 문은 각 조건에 사용하는 데이터가 서로 상관이 없어도 된다. 

- 또 기준 데이터 값이 같은(=) 데이터 외에 다양한 조건을 사용할 수 있다. 

- 작성 형식 면에서 볼 때 WHEN이나 THEN, ELSE를 사용하는 CASE문은 DECODE 함수보다 더 프로그래밍 언어적인 표현 방식을 사용한다. 

//기본 형식 
CASE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과],
       WHEN [조건1] THEN [데이터가 조건1과 일치할 때 반환할 결과],
       WHEN [조건2] THEN [데이터가 조건2와 일치할 때 반환할 결과],
       WHEN [조건3] THEN [데이터가 조건3과 일치할 때 반환할 결과],
       ...
       WHEN [조건n] THEN [데이터가 조건n과 일치할 때 반환할 결과],
       ELSE [위 조건1~조건n과 일치한 경우가 없을 때 반환할 결과])
END

3) 기준 데이터 없이 조건식만으로 CASE문 사용하기 

- CASE문은 DECODE 함수와는 달리 비교할 기준 데이터를 지정하지 않고 값이 같은 조건 이외의 조건도 사용할 수 있다. 

- 아래 예제는 COMM 열 값의 범위에 따라 각각 출력을 달리하고자 CASE문을 활용한 예이다.

- 위 결과에서 알 수 있듯이 CASE문은 각 조건식의 true, false 여부만 검사하므로 기준 데이터가 없어도 사용이 가능하다. 

- 다만 DECODE 함수와 CASE문은 모두 조건별로 동일한 자료형의 데이터를 반환해야 한다.