오라클 데이터(타입) 변환 및 처리 함수 정리 – (TO_CHAR, TO_NUMBER, TO_DATE, NVL, NVL2, NULLIF, COALESCE, DECODE, CASE)

by

오라클 데이터(타입) 변환 및 처리 함수 정리 – (TO_CHAR, TO_NUMBER, TO_DATE, NVL, NVL2, NULLIF, COALESCE, DECODE, CASE)


오라클 데이터베이스는 다양한 데이터 타입을 지원하며, 때로는 데이터 타입 간의 변환이 필요한 상황이 발생하는데요, 이때 명시적 데이터 타입 변환이나, 함수들을 활용하여 데이터를 원하는 형태로 변환할 수 있습니다. 이번 글에서는 오라클 데이터 타입 변환의 기본 개념과 TO_CHAR, TO_NUMBER, TO_DATE 등의 함수를 사용하여 데이터를 원하는 포맷으로 변환하는 방법에 대해 알아보겠습니다.

오라클 데이터(타입) 변환 및 처리 함수 정리 - (TO_CHAR, TO_NUMBER, TO_DATE, NVL, NVL2, NULLIF, COALESCE, DECODE, CASE)

TO_CHAR (날짜/숫자 타입의 문자 변환)

TO_CHAR 함수는 날짜나 숫자 데이터를 문자로 변환할 때 유용하게 사용.

날짜 데이터의 출력 형식을 지정할 때는 작은 따옴표로 묶어서 표현.

사용법

TO_CHAR(변환할 데이터, 형식)
변환할 데이터 : 날짜나 숫자 형태로 표현된 데이터.
형식 : 변환된 문자열의 출력 형식을 지정(생략가능), 형식을 지정하지 않으면 기본 형식으로 변환됨.

예) 학생 테이블에서 전인하 학생의 학번과 생년월일 중에서 년월만 출력

SELECT studno, TO_CHAR(birthdate, 'YY-MM') birthdate
FROM student
WHERE name = '전인하';

예) 학생 테이블에서 102번 학과 학생의 이름, 학년, 생년월일 출력

SELECT name, grade, TO_CHAR(birthdate, 'Day Month DD, YYYY') birthdate
FROM student
WHERE deptno=102;

TO_DATE (문자열을 날짜 데이터로 변환)

TO_DATE 함수는 숫자와 문자로 구성된 문자열을 날짜 데이터로 변환하는데 사용.

날짜 데이터의 입력 형식을 지정하여 원하는 날짜 형태로 변환할 수 있음.

사용법

TO_DATE(날짜형식의문자열, [날짜포맷])
날짜형식의문자열 : 날짜 형식을 가진 문자열입니다. 일반적으로 ‘YYYY-MM-DD’와 같은 날짜 형식을 사용합니다.
날짜포맷 (선택사항) : 날짜 형식을 지정하는 옵션입니다. 이는 생략할 수 있으며, 입력하지 않으면 데이터베이스의 기본 날짜 형식을 따릅니다.

예) 교수 테이블에서 입사일이 “june 01, 01″인 교수의 이름과 입사일 출력

SELECT name, hiredate
FROM professor
WHERE hiredate = TO_DATE('june 01, 01', 'MONTH DD, YY');

TO_NUMBER

숫자로 구성된 문자열을 숫자로 변환

사용법

TO_NUMBER(문자열, [포맷])

문자열

숫자로 변환하고자 하는 문자열.

예)

SELECT TO_NUMBER(‘123’) num FROM dual;
— 결과: num
— 123

포맷 (선택사항)

숫자를 원하는 형식으로 변환하기 위해 사용.(생략가능)

포맷을 지정하면 숫자를 원하는 형태로 변환할 수 있음.

예)

SELECT TO_NUMBER(‘1,234.56’, ‘999G999D99’, ‘NLS_NUMERIC_CHARACTERS = ”.,”’) num FROM dual;
— 결과: num
— 1234.56

‘999G999D99’는 숫자를 표시하는 형식을 지정한 것으로, 3자리마다 ‘,’로 구분하고 소수점을 ‘.’로 표시함. ‘NLS_NUMERIC_CHARACTERS = ”.,”’는 숫자를 인식할 때 소수점과 천단위 구분 기호로 ‘.’과 ‘,’을 사용하도록 설정하는 부분.

결국, 문자열 ‘1,234.56’이 숫자 1234.56으로 변환됨.

NULL 처리 함수 (NVL, NVL2, NULLIF, COALESCE)

NVL 함수

NULL값이 존재 할 경우 대체값으로 지정된 값을 반환.

사용법
NVL(exp1, exp2)
  • exp1: NULL을 포함하는 칼럼(필드)
  • exp2: NULL 대체값으로 사용할 값

예)

SELECT name, NVL(salary, 0) AS modified_salary FROM employee;

employee 테이블의 salary 칼럼 값이 NULL인 경우 0으로 대체하여 modified_salary라는 별칭으로 출력.

NVL2 함수

주어진 표현식이 NULL인 경우와 NULL이 아닌 경우에 서로 지정된 다른 값을 반환

사용법
NVL2(exp1, exp2, exp3)
  • exp1: NULL을 포함하는 칼럼(필드)
  • exp2: exp1이 NULL이 아닐 때 반환되는 값
  • exp3: exp1이 NULL일 때 반환되는 값

예)

SELECT name, NVL2(salary, salary * 1.1, 0) AS updated_salary FROM employee;

employee 테이블의 salary 칼럼 값이 NULL이 아닌 경우에는 salary 값에 10%를 더하여 updated_salary로 출력하고, NULL인 경우에는 0으로 변환

NULLIF 함수

NULLIF 함수는 두 개의 표현식을 비교하여 값이 동일한 경우 NULL을 반환하고, 값이 다른 경우에는 첫 번째 표현식의 값을 반환함

사용법
NULLIF(exp1, exp2)
  • exp1, exp2: 비교할 두 개의 표현식

예)

SELECT name, NULLIF(salary, 0) AS non_zero_salary FROM employee;

employee 테이블의 salary 칼럼 값이 0인 경우에는 NULL을 반환, 0이 아닌 경우에는 salary 값을 non_zero_salary로 출력.

COALESCE 함수

여러 개의 인수 중에서 NULL이 아닌 첫 번째 인수를 반환함.

인수로 전달된 표현식들을 왼쪽부터 순서대로 검사하며, NULL이 아닌 첫 번째 값을 반환.

사용법
COALESCE(exp1, exp2, exp3, ...)
  • exp1, exp2, exp3, … 여러 개의 표현식.

예)

SELECT name, COALESCE(salary, bonus, overtime_pay) AS total_pay FROM employee;

employee 테이블의 salary, bonus, overtime_pay 칼럼들 중에서 NULL이 아닌 첫 번째 값을 total_pay로 출력.

DECODE 함수와 CASE 문을 활용한 데이터 변환

DECODE 함수와 CASE 문은 복잡한 알고리즘을 하나의 SQL 명령문으로 표현하는데 유용함(조건에 따라 다른 결과를 반환하는데 사용)

DECODE 함수

DECODE 함수는 값에 따라 각각의 다른 결과를 반환하고자 할 때 사용.

주어진 값과 비교하여 해당하는 결과를 반환.

주로 프로그래밍에서 사용하는, IF문이나 CASE문으로 표현되는 조건문을 간결하게 표현하기 위해 사용.

사용법
DECODE(비교값, 조건1, 결과1, 조건2, 결과2, …, 기본결과)

예)

SELECT name, deptno,
       DECODE(deptno, 101, '컴퓨터공학과', 102, '멀티미디어학과', 201, '전자공학과', '기계공학과') DNAME
FROM professor;

professor 테이블의 deptno 값에 따라 다른 학과명을 DNAME으로 출력.

deptno가 101이면 ‘컴퓨터공학과’, 102면 ‘멀티미디어학과’, 201이면 ‘전자공학과’, 그 외의 값은 default인 ‘기계공학과’로 반환됨.

CASE 문

CASE 문은 DECODE 함수의 기능을 확장한 함수로, IF문이나 CASE문과 유사한 동작을 수행 할 수 있음.

마찬가지로, 조건에 따라 다른 결과를 반환하고자 할 때 사용가능.

사용법

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ...
    ELSE 기본결과
END
  • 조건1, 조건2, … : 조건에 해당하는 식 또는 비교식(각 조건에 맞는 경우 해당하는 결과를 반환)
  • 결과1, 결과2, … : 조건1인경우 결과1, 조건2인경우 결과2….를 반환
  • 기본결과 (선택) : 모든 조건과 일치하지 않을 때 반환할 기본값(default)

예)

SELECT name, deptno, sal,
       CASE
           WHEN deptno = 101 THEN sal * 0.1
           WHEN deptno = 102 THEN sal * 0.2
           WHEN deptno = 201 THEN sal * 0.3
           ELSE 0
       END bonus
FROM professor;

professor 테이블의 deptno 값에 따라 다른 보너스를 계산하여 출력.

deptno가 101이면 급여의 10%를, 102면 20%를, 201이면 30%를 반환, 그 외의 보너스는 0을 반환.

DECODE와 CASE 차이점?

DECODE와 CASE 각 함수의 차이점 이라면, DECODE 함수는 주로 단순한 값과 대응하는 결과를 지정하는 방식으로 사용되고, CASE 문은 조금 더 복잡한 조건식을 활용하여 다양한 경우의 수를 처리할 경우 사용됨.

맺으며

오라클 데이터베이스에서 명시적 데이터 타입 변환과 다양한 함수들을 활용하면 데이터를 원하는 형태로 변환하고, NULL 값을 처리하는데 유용하게 활용할 수 있는데요, TO_CHAR, TO_NUMBER, TO_DATE 함수를 활용하여 날짜와 숫자 데이터를 문자로 변환하거나 문자열을 날짜와 숫자로 변환하는 방법을 익히고, NVL, NVL2, NULLIF, COALESCE 함수를 사용하여 NULL 값을 대체하거나 처리하는 방법을 익히면 데이터 처리 작업을 보다 효율적으로 수행할 수 있을 것 입니다. 또한 DECODE 함수와 CASE 문은 복잡한 데이터 변환과 연산에 유용하므로 적절히 활용하는 것이 중요합니다.

이전 글 – [오라클] 조인 INNER JOIN(EQUI JOIN과 NON-EQUI JOIN)에 대해 알아보자

이전 글 – 오라클 데이터 조작어 DML(INSERT, UPDATE, DELETE) 활용 방법

이전 글 – 오라클 데이터 무결성 제약조건 정리 (기본키,외래키,참조키, 제약조건 생성, 추가, 삭제, 비활성화)

이전 글 – 오라클 인덱스에 대해 알아보자 (데이터베이스 실무에서 효율적으로 사용 할 수 있는 Oracle Index)

이전 글 – 오라클 뷰(VIEW) 조회 및 사용방법 (개념과 특징,종류, 장단점, 인라인뷰)

이전 글 – 오라클 사용자 권한 제어 (4가지 – 시스템 권한, 객체 권한, 롤, 동의어)

이전 글 – 오라클(데이터 웨어하우스) 분석 함수(효과적인 데이터 다차원 분석을 위한 강력한 도구)

이전 글 – 오라클 스케줄러와 JOB 관리 방법

이전 글 – 오라클 테이블 복구(테이블 데이터 복원하기)

Leave a Comment