[Oracle] 숫자 함수, 날짜 함수, 형변환 함수, NULL처리 함수, DECODE와 CASE

 

 

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

 

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

 

 

 

 

숫자 함수 (단일행 함수)

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

 

 

  ■ 특정 위치에서 반올림 (ROUND)

ROUND([숫자(필수)], [반올림 위치(선택)])

-- ex
SELECT ROUND(1234.5678) AS ROUND,
    ROUND(1234.5678, 0) 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;

특정 숫자를 반올림하되 반올림할 위치를 지정할 때  ROUND 를 사용한다. 위치 값이 양수로 갈수록 소수점 자리가 한 자리씩 낮아지고 음수로 내려가면 자연수 쪽으로 한 자리씩 위로 반올림하게 된다. ( 0 은 지정하지 않은 것과 같음) 위치를 지정하지 않으면 소수점 첫째 자리에서 반올림한 결과가 반환된다.

 

실행결과

 

 

  ■ 특정 위치에서 버림 (TRUNC)

TRUNC([숫자(필수)], [버림 위치(선택)])

-- ex
SELECT TRUNC(1539.125023, 4) AS TRUNC_EX1,
    TRUNC(4586.89453, 2) AS TRUNC_EX2,
    TRUNC(2560.48522, -1) AS TRUNC_EX3
FROM DUAL;

지정된 자리에서 숫자를 버림 처리할 때  TRUNC 를 사용한다. 위치를 지정하지 않으면 소수점 첫째 자리에서 버림 처리된다.

 

실행결과

 

 

  ■ 가까운 정수를 반환 (CEIL, FLOOR)

CEIL([숫자(필수)])
FLOOR([숫자(필수)])

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

 CEIL 은 입력된 숫자와 가까운 큰 정수,  FLOOR 은 입력된 숫자와 가까운 작은 정수를 반환한다.

 

실행결과

입력된 값이 음수일 경우에는 절대값이 작은 값이 더 큰 수이므로 주의한다.

 

  ■ 나머지 값 (MOD)

MOD([나눈셈 될 숫자(필수)], [나눌 숫자(필수)])

-- ex
MOD(15, 6)

특정 숫자로 나눈 나머지를 구할 때  MOD 를 사용한다.

 

 

 

 

---

날짜 함수 (단일행 함수)

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

 

 

  ■ 날짜 관련 함수

함  수	설  명
SYSDATE	SYSDATE 함수는 별다른 입력 데이터 없이, 오라클 데이터 서버가 놓인 OS의 현재 날짜와 시간을 보여준다.
ADD_MONTHS([날짜 데이터], [더할 개월 수(정수)])	ADD_MONTHS 함수는 특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환하는 함수이다. 윤년 등의 이유로 복잡해질 수 있는 날짜 계산을 간단하게 만들어 주어서 자주 사용된다.
MONTHS_BETWEEN([날짜 데이터1], [날짜 데이터2])	두 날짜 데이터 간의 날짜 차이를 개월 수로 계산하여 출력한다
NEXT_DAY([날짜 데이터], [요일 문자])	입력한 날짜 데이터에서 돌아오는 요일의 날짜를 반환합니다.
LAST_DAY([날짜 데이터])	해당 날짜가 속한 달의 마지막 날짜를 반환해 주는 함수

 

 

  ■ 날짜의 반올림, 버림 (ROUND, TRUNC)

   ROUND 와  TRUNC 함수는 숫자 데이터뿐만 아니라 날짜 데이터를 입력 데이터로 사용할 수 있다. 소수점 위치 정보를 입력하지 않고 반올림, 버림의 기준이 될 포맷(format) 값을 지정해준다.

 

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

 

 

  오라클에서 날짜 데이터를 사용할 때 기준 포맷 값

포맷 모델	기준 단위
CC, SCC	네 자리 연도의 끝 두 자리를 기준으로 사용 ex) 2016년이면 2050 이하이므로, 반올림 할 경우 2001년으로 처리
SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	날짜 데이터의 해당 연/월/일의 7월 1일을 기준 ex) 2016년 7월 1일의 경우 2017년으로 처리
IYYY, IYY, IY, I	ISO 8601에서 제정한 날짜 기준년도 포맷 기준
Q	각 분기의 두 번째 달의 16일 기준
MONTH, MON, MM, RM	각 달의 16일 기준
WW	해당 연도의 몇 주(1 ~ 53번째 주)를 기준
IW	ISO 8601에서 제정한 날짜 기준 해당 연도의 주(week)를 기준
W	해당 월의 주(1 ~ 5번째 주)를 기준
DDD, DD, J	해당 일의 정오(12:00:00)를 기준
DAY, DY, D	한 주가 시작되는 날짜를 기준
HH, HH12, HH24	해당일의 시간을 기준
MI	해당일 시간의 분을 기준

 

 

 

 

---

형변환 함수(단일행 함수)

종  류	설  명
TO_CHAR([날짜 데이터], '[출력되길 원하는 문자형태]', 'NLS_DATE_LANGUAGE = language'(선택))	숫자 또는 날짜 데이터를 문자 데이터로 변환
TO_NUMBER('[문자열 데이터]', '[인식될 숫자형태]')	문자 데이터를 숫자 데이터로 변환
TO_DATE('[문자열 데이터]', '[인식될 날짜형태]')	문자 데이터를 날짜 데이터로 변환

 

 

■ 날짜, 숫자 데이터 → 문자 데이터 변환 (TO_CHAR)

 

  TO_CHAR에서 자주 사용하는 날짜 표현 형식

형  식	설  명
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, 'MON', 'NLS_DATE_LANGUAGE = KOREAN') AS MON_KOR,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = KOREAN') AS MONTH_KOR,
    TO_CHAR(SYSDATE, 'MON', 'NLS_DATE_LANGUAGE = ENGLISH') AS MON_ENG,
    TO_CHAR(SYSDATE, 'MONTH', 'NLS_DATE_LANGUAGE = ENGLISH') AS MONTH_ENG
FROM DUAL;

날짜를 특정 언어에 맞는 이름으로 출력하려면 기존  TO_CHAR 함수에 날짜 출력 언어를 추가로 지정해 줄 수 있다.

 

실행결과

 

  TO_CHAR에서 사용하는 시간 형식

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

 

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

시간 형식을 지정해서 현재시간을 출력하는 예제이다.

 

실행결과

 

  + 한글로 년월일시분초 출력

SELECT TO_CHAR(SYSDATE, 'YYYY"년"MM"월"DD"일 "HH"시"MI"분"SS"초"')
FROM DUAL;

한글로  2019년09월03일 17시30분12초 와 같은 형식으로 출력하고 싶을 경우 위와 같이 하면 된다.

(24시간 형식일 경우  HH24 를 사용하면 된다.)

 

 

 

  TO_CHAR로 숫자 데이터를 문자 데이터로 변환할 때 사용하는 형식

형  식	설  명
9	숫자의 한 자리를 의미(빈자리를 채우지 X)
0	빈자리를 0으로 채움
$	달러($)표시를 붙여서 출력
L	L(Locale)지역 화폐 단위 기호를 붙여서 출력
.	소수점을 표시
,	천 단위의 구분 기호를 표시

 

SELECT SAL,
    TO_CHAR(SAL, '$999,999') AS SAL_$,
    TO_CHAR(SAL, 'L999,999') AS SAL_L,
    TO_CHAR(SAL, '999,999.00') AS SAL_1,
    TO_CHAR(SAL, '000,999,999.00') AS SAL_2,
    TO_CHAR(SAL, '000999999.99') AS SAL_3,
    TO_CHAR(SAL, '999,999,00') AS SAL_4
FROM EMP;
    

숫자 형식을 사용하여 급여를 출력하는 예제이다.

 

실행결과

 

 

  ■ 문자 데이터 → 숫자 데이터 변환 (TO_NUMBER)

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

문자 데이터(ex 1,500)를 산술 연산할 경우,   TO_NUMBER 를 이용해서 숫자 형태로 변형시킨 후에 계산해 주어야 한다.

 

실행결과

 

 

  ■ 문자 데이터 → 날짜 데이터 변환 (TO_DATE)

SELECT *
FROM EMP
WHERE HIREDATE > TO_DATE('1981/06/01', 'YYYY/MM/DD');

날짜 데이터는 간단한 연산이 가능하기 때문에 비교 연산자를 사용하여 데이터를 찾아내는 것도 가능하다.

 

실행결과

 

 

 

 

---

NULL 처리 함수(단일행 함수)

  ■ NVL

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

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

 

 

  ■ NVL2

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

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

 

 

 

 

---

상황에 따라 다른 데이터를 반환 (DECODE. CASE) / 단일행 함수

  ■ DECODE

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

-- ex
SELECT EMPNO, ENAME, JOB, SAL,
    DECODE(JOB,
    	'MANAGER', SAL*1.1,
        'SALESMAN', SAL*1.05,
        'ANALYST', SAL,
        SAL*1.03) AS UPSAL
FROM EMP;

 DECODE 함수는 프로그래밍 언어의 조건문과 비슷하다.

주의할 점은 모두 조건별로 동일한 자료형의 데이터를 반환해야 한다는 것이다.

 

 

  ■ CASE

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

-- ex (DECODE를 CASE로 바꾸는 경우)
SELECT EMPNO, ENAME, JOB, SAL,
    CASE JOB
    	WHEN 'MANAGER' THEN SAL*1.1
    	WHEN 'SALESMAN' THEN SAL*1.05
    	WHEN 'ANALYST' THEN SAL
    	ELSE SAL*1.03
    END AS UPSAL
FROM EMP;

   CASE 도  DECODE 함수와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용한다.  CASE 문은  DECODE 함수와는 달리 조건식의 true, false 여부만 검사하므로 기준 데이터를 지정하지 않고 값이 같은 조건 이외의 조건도 사용할 수 있다. 주의할 점은 모두 조건별로 동일한 자료형의 데이터를 반환해야 한다는 것이다.