2022-11-08 (2) 오라클 함수 - 형 변환 함수, NULL 처리 함수, CASE/DECODE

[형 변환 함수]

명시적 형 변환을 수행한다. 문자를 중심으로 숫자 또는 날짜 데이터의 변환이 가능하다.

 

[날짜, 숫자를 문자로 변환 TO_CHAR]

: 주로 날짜를 문자로 변환하는데 많이 사용한다.

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

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;

출력되길 원하는 문자 형태를 표현하기 위해 사용하는 형식(fmt:format)은 다음과 같다.

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

여러 예시를 봐두자.

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM'),
    TO_CHAR(SYSDATE, 'MON'),
    TO_CHAR(SYSDATE, 'MONTH'),
    TO_CHAR(SYSDATE, 'DD'),
    TO_CHAR(SYSDATE, 'DY'),
    TO_CHAR(SYSDATE, 'DAY')
FROM DUAL;

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'MM'),
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = KOREAN') AS KOR,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = JAPANESE') AS JPN,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH') AS ENG,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = KOREAN') AS KOR,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = JAPANESE') AS JPN,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH') AS ENG
FROM DUAL;

 

시간 형식은 다음과 같다.

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

SELECT SYSDATE,
    TO_CHAR(SYSDATE, 'HH24:MI:SS'),
    TO_CHAR(SYSDATE, 'HH12:MI:SS AM'),
    TO_CHAR(SYSDATE, 'HH:MI:SS P.M.')
FROM DUAL;

 

[문자를 숫자로 변환 TO_NUMBER]

: 숫자가 가공된 문자 데이터로 저장되어 있고, 그 데이터를 산술 연산에 사용하려는 경우 형변환이 필요하다.

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

TO_NUMBER는 문자를 지정한 형태의 숫자로 인식하여 숫자로 변환시킨다.

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

[문자를 날짜로 변환 TO_DATE]

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

SELECT TO_DATE('2022-11-08', 'YYYY-MM-DD'),
    TO_DATE('20221108', 'YYYY-MM-DD')
FROM DUAL;

---

[NULL 처리 함수]

[NVL]

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

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

SELECT FIRST_NAME, NVL(COMMISSION_PCT, 0) FROM EMPLOYEES;

 

[NVL2]

: NVL과 비슷하지만 NULL이 아닐때 반환할 데이터를 추가로 지정해 줄 수 있다는 차이점이 있다.

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

SELECT FIRST_NAME, 
    NVL(COMMISSION_PCT, 0),
    NVL2(COMMISSION_PCT, SALARY*12*(1 + COMMISSION_PCT), SALARY*12)
FROM EMPLOYEES;

참고로 실무에서는 NVL이 더 많이 사용된다고 한다.

 

예제) MANAGER_ID 가 있는 사원은 사번, 이름, 관리자번호를 출력하고, 없는 곳은 사번, 이름, '관리자없음'을 출력하시오.

SELECT EMPLOYEE_ID, FIRST_NAME,
    NVL2(MANAGER_ID, TO_CHAR(MANAGER_ID), '관리자없음') 
FROM EMPLOYEES;

 

---

[CASE]

: 특정 조건에 따라 반환할 데이터를 설정할 때 사용한다. 각 조건에 사용하는 데이터가 서로 상관없어도 되고, 다양한 조건을 사용할 수 있다.

CASE [검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과(선택)]
	WHEN [조건1] THEN [조건1의 결과 값이 TRUE 일때, 반환할 결과]
    WHEN [조건2] THEN [조건2의 결과 값이 TRUE 일때, 반환할 결과]
    ...
    WHEN [조건N] THEN [조건N의 결과 값이 TRUE 일때, 반환할 결과]
    ELSE [위의 모든 조건에 해당하는 경우가 없을 때 반환할 결과]
END

SELECT EMPLOYEE_ID, FIRST_NAME,
    CASE 
        WHEN EMPLOYEE_ID <= 150 THEN SALARY*1.1
        WHEN EMPLOYEE_ID <= 200 THEN SALARY*1.05
        ELSE SALARY
    END AS SAL
FROM EMPLOYEES;

 

 

[DECODE]

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

예제) MANAGER_ID 가 있는 사원은 사번, 이름, 관리자번호를 출력하고, 없는 곳은 사번, 이름, '관리자없음'을 출력하시오.

SELECT EMPLOYEE_ID, FIRST_NAME,
    DECODE(MANAGER_ID,
        NULL, '관리자없음',
        MANAGER_ID)
FROM EMPLOYEES;