[Oracle] 날짜 / 변환 / NULL / 기타 함수 정리

[Oracle]  날짜 / 변환 / NULL / 기타 함수 정리 

날짜 함수

날짜 함수는 DATE 함수나 TIMESTAMP 함수와 같은 날짜형을 대상으로 연산을 수행해 결과를 반환하는 함수다. 

날짜 함수 역시 대부분 반환 결과는 날짜형이나 함수에 따라 숫자를 반환할 때도 있다.

 

SYSDATE, SYSTIMESTAMP

SYSDATE와 SYSTIMESTAMP 함수는 현재일자와 시간을 각각 DATE, TIMESTAMP 타입을 반환한다.

EXAMPLE :

	SELECT SYSDATE, SYSTIMESTAMP
	FROM DUAL;

view raworacle_09_01.sql hosted with ❤ by GitHub

 

ADD_MONTHS(date, integer)

ADD_MONTHS 함수는 매개변수로 들어온 날짜에 integer 만큼의 월을 더한 날짜를 반환한다.

EXAMPLE :

	SELECT ADD_MONTHS(SYSDATE, 1), ADD_MONTHS(SYSDATE, -1)
	FROM DUAL; -- 응용 SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-12),'YYYY')|| '/12/31' FROM DUAL; 이런식으로 마지막 달과 마지막 일을 표현가능

view raworacle_09_02.sql hosted with ❤ by GitHub

 

MONTHS_BETWEEN(date1, date2)

MONTHS_BETWEEN 함수는 두 날짜 사이의 개월 수를 반환하는데, date2가 date1보다 빠른 날짜가 오는 방식이다.

EXAMPLE :

	SELECT MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(SYSDATE, 1)) MON1,
	MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, 1), SYSDATE) MON2
	FROM DUAL;

view raworacle_09_03.sql hosted with ❤ by GitHub

 

LAST_DAY(date)

LAST_DAY 함수는 date 날짜를 기준으로 해당 월의 마지막 일자를 반환한다.

EXAMPLE :

	SELECT LAST_DAY(SYSDATE)
	FROM DUAL;

view raworacle_09_04.sql hosted with ❤ by GitHub

 

ROUND(date, format), TRUNC(date, format)

ROUND와 TRUNC 함수는 숫자 함수이면서 날짜 함수로도 쓰이는데, ROUND 함수는 format에 따라 반올림한 날짜를, TRUNC 함수는 내림한 날짜를 반환한다.

EXAMPLE :

	SELECT SYSDATE, ROUND(SYSDATE, 'month'), TRUNC(SYSDATE, 'month')
	FROM DUAL;

view raworacle_09_05.sql hosted with ❤ by GitHub

위 예제에서 ROUND 함수로 작성 날짜 기준 현재 날짜인 13일을 반올림하면 11월이므로 11월 1일이 된 거고, TRUNC 함수로 내림 했으므로 11월 1일이 된 것을 확인할 수 있다.

 

NEXT_DAY(date, char)

NEXT_DAY 함수는 date를 char에 명시한 날짜로 다음 주 주중 일자를 반환한다. char로 올 수 있는 값은 '일요일'에서 '토요일'까지인데, 이 값은 NLS_LANG이란 오라클 환경 변수에 따라 한글이 올 수도, 'SUNDAY', 'MONDAY'처럼 영문이 올 수도 있다.

EXAMPLE :

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

view raworacle_09_06.sql hosted with ❤ by GitHub

 

변환 함수

변환 함수란 서로 다른 유형의 데이터 타입으로 변환해 결과를 반환하는 함수를 말한다. 오라클이 자동으로 형변환을 해주는 것을 묵시적 형변환이라고 하고, 변환 함수를 통해 형변환을 직접 처리하는 것을 명시적 형변환이라고 한다.

 

TO_CHAR(숫자 혹은 날짜, format)

숫자나 날짜를 문자로 변환해 주는 함수가 TO_CHAR로 , 매개변수로는 숫자나 날짜가 올 수 있고 반환 결과를 특정 형식에 맞게 출력할 수 있다.

EXAMPLE :

	SELECT TO_CHAR(123456789, '999,999,999'), TO_CHAR(SYSDATE, 'YYYY-MM-DD')
	FROM DUAL;

view raworacle_09_07.sql hosted with ❤ by GitHub

 

매개변수로 오는 숫자나 날짜에 따라 자주 사용하는 포맷을 정리하면 다음과 같다.

• 날짜 변환 형식

포맷	설명	사용 예
AM, A.M.	오전	TO_CHAR(SYSDATE, 'AM') -> 오전
PM, P.M.	오후	TO_CHAR(SYSDATE, 'PM') -> 오후
YYYY, YYY, YY, Y	연도	TO_CHAR(SYSDATE, 'YYYY') -> 2016
MONTH, MON	월	TO_CHAR(SYSDATE, 'MONTH') -> 11월
MM	01~12형태의 월	TO_CHAR(SYSDATE, 'MM') -> 11
D	주중의 일을 1~7로 표시(일요일이 1)	TO_CHAR(SYSDATE, 'D') -> 1
DAY	주중 일을 요일로 표시	TO_CHAR(SYSDATE,'DAY') -> 일요일
DD	일을 01~31 형태로 표시	TO_CHAR(SYSDATE, 'DD') -> 13
DDD	일을 001~365 형태로 표시	TO_CHAR(SYSDATE, 'DDD') -> 013
DL	현재 일을 요일까지 표시	TO_CHAR(SYSDATE, 'DL') -> 2016년 11월 13일 일요일
HH, HH12	시간을 01~12시 형태로 표시	TO_CHAR(SYSDATE, 'HH') -> 04
HH24	시간을 01~23시 형태로 표시	TO_CHAR(SYSDATE, 'HH24') -> 16
MI	분을 00~59분 형태로 표시	TO_CHAR(SYSDATE, 'MI') -> 56
SS	초를 01~59초 형태로 표시	TO_CHAR(SYSDATE, 'SS') -> 33
WW	주를 01~53주 형태로 표시	TO_CHAR(SYSDATE, 'WW') -> 06

 

• 숫자 변환 형식

포맷	설명	사용 예
,(콤마)	콤마로 표시	TO_CHAR(123456, '999,999') -> 123,456
.(소수점)	소수점 표시	TO_CHAR(123456.4, '999,999.9') -> 123,456.4
9	한 자리 숫자, 실제 값보다 크거나 같게 명시	TO_CHAR(123456, '999,999') -> 123,456
PR	음수일 때 <>로 표시	TO_CHAR(-123, '999PR') -> <123>
RN, rn	로마 숫자로 표시	TO_CHAR(123, 'RN') -> CXXlll
S	양수이면 +, 음수이면 -로 표시	TO_CHAR(123, 'S999') -> +123

 

TO_NUMBER(expr, format)

문자나 다른 유형의 숫자를 NUMBER 타입을 반환하는 함수이다.

EXAMPLE :

	SELECT TO_NUMBER('123456')
	FROM DUAL;

view raworacle_09_08.sql hosted with ❤ by GitHub

 

TO_DATE(char, format), TO_TIMESTAMP(char, format)

문자 타입의 매개변수를 날짜 타입의 매개변수로 변환하는 함수다. format 매개변수로는 위 표에 있는 항목이 올 수 있으며, TO_DATE 함수는 DATE 타입으로, TO_TIMESTAMP 함수는 TIMESTAMP 타입으로 변환해 값을 반환한다.

EXAMPLE :

	SELECT TO_DATE('20140101', 'YYYY-MM-DD'), TO_TIMESTAMP('20140101 13:44:50', 'YYYY-MM-DD HH24:MI:SS')
	FROM DUAL;

view raworacle_09_09.sql hosted with ❤ by GitHub

 

NULL 관련 함수

NULL을 비교할 때는 IS NULL이나 IS NOT NULL 구문을 사용하는데, 오라클에서는 NULL을 연산 대상으로 처리하는 SQL 함수를 제공하고 있다.

 

NVL(expr1, expr2), NVL2(expr1, expr2, expr3)

NVL 함수는 expr1이 NULL일 때 expr2를 반환하는 함수다.

EXAMPLE : MANAGER_ID가 NULL인 사원의 MANAGER_ID가 NULL일 때, MANAGER_ID 대신 EMPLOYEE_ID를 조회하는 쿼리

	SELECT NVL(MANAGER_ID, EMPLOYEE_ID)
	FROM EMPLOYEES
	WHERE MANAGER_ID IS NULL;

view raworacle_09_10.sql hosted with ❤ by GitHub

 

NVL2 함수는 NVL 함수를 확장한 함수로, expr1이 NULL이 아니면 expr2를 반환하고, NULL이면 expr3을 반환하는 함수다.

EXAMPLE : COMMISSION_PCT가 NULL인 사원은 그냥 SALARY를, NULL이 아니면 SALARY + (SALARY + COMMISSION_PCT)를 조회하는 쿼리

	SELECT EMPLOYEE_ID, NVL2(COMMISSION_PCT, SALARY + (SALARY * COMMISSION_PCT), SALARY) AS SALARY2
	FROM EMPLOYEES;

view raworacle_09_11.sql hosted with ❤ by GitHub

 

COALESCE(expr1, expr2, …)

COALESCE 함수는 매개변수로 들어오는 표현식에서 NULL이 아닌 첫 번째 표현식을 반환하는 함수다.

EXAMPLE : SALARY * COMMISSION_PCT 값이 NULL이면 SALARY를,  NULL이 아니면 SALARY * COMMISSION_PCT 값을 반환하는 쿼리

	SELECT EMPLOYEE_ID, SALARY, COMMISSION_PCT,
	COALESCE(SALARY * COMMISSION_PCT, SALARY) AS SALARY2
	FROM EMPLOYEES;

view raworacle_09_12.sql hosted with ❤ by GitHub

 

LNNVL(조건식)

LNNVL 함수는 매개변수로 들어오는 조건식의 결과가 FALSE나 UNKNOWN이면 TRUE를, TRUE이면 FALSE를 반환한다. 예를 들어, COMMISSION_PCT가 0.2 이하인 EMPLOYEE_ID와 COMMISSION_PCT를 조회한다고 해보자

	SELECT EMPLOYEE_ID, COMMISSION_PCT
	FROM EMPLOYEES
	WHERE COMMISSION_PCT < 0.2;

view raworacle_09_13.sql hosted with ❤ by GitHub

총 11건이 조회되었다. 그런데 COMMISSION_PCT가 NULL인 사원도 0.2 이하라고 봐야하는데, 위의 쿼리에서는 조회되지 않는다. NULL인 사원까지 조회하려면 NVL 함수를 써서 구할 수 있다.

	SELECT COUNT(*)
	FROM EMPLOYEES
	WHERE NVL(COMMISSION_PCT, 0) < 0.2;

view raworacle_09_14.sql hosted with ❤ by GitHub

또한 LNNVL 함수를 써서 동일한 결과가 출력되도록 구현할 수 있다. 주의할 점은 LNNVL 함수는 매개변수로 들어오는 조건의 결과가 TRUE이면 FALSE를, FALSE나 UNKNOWN이면 TRUE를 반환하므로, 조건식이 바뀐다는 것을 유념하자.

	SELECT COUNT(*)
	FROM EMPLOYEES
	WHERE LNNVL(COMMISSION_PCT >= 0.2);

view raworacle_09_15.sql hosted with ❤ by GitHub

 

NULLIF(expr1, expr2)

NULLIF 함수는 expr1과 expr2를 비교해 같으면 NULL을, 다르면 expr1을 반환하는 함수다.

EXAMPLE : JOB_HISTORY 테이블에서 START_DATE와 END_DATE의 연도만 추출해 두 연도가 같으면 NULL을, 같지 않으면 END_DATE의 연도를 조회하는 쿼리

	SELECT EMPLOYEE_ID,
	TO_CHAR(START_DATE, 'YYYY') START_YEAR,
	TO_CHAR(END_DATE, 'YYYY') END_YEAR,
	NULLIF(TO_CHAR(END_DATE, 'YYYY'), TO_CHAR(START_DATE, 'YYYY')) NULLIF_YEAR
	FROM JOB_HISTORY;

view raworacle_09_16.sql hosted with ❤ by GitHub

 

기타 함수

 

GREATEST(expr1, expr2, …), LEAST(expr1, expr2, …)

GREATEST 함수는 매개변수로 들어오는 표현식에서 가장 큰 값을, LEAST 함수는 가장 작은 값을 반환하는 함수다. 숫자 뿐만 아니라 문자도 비교할 수 있다.

EXAMPLE :

	SELECT GREATEST(1, 2, 3, 2), LEAST(1, 2, 3, 2), GREATEST('이순신', '강간찬', '세종대왕'), LEAST('이순신', '강감찬', '세종대왕')
	FROM DUAL;

view raworacle_09_17.sql hosted with ❤ by GitHub

 

DECODE(expr, search1, result1, search2, result2, …, default)

DECODE 함수는 expr과 search1을 비교해 두 값이 같으면 result1을, 같지 않으면 다시 search2와 비교해 값이 같으면 result2를 반환하고, 이런 식으로 계속 비교한 뒤 최종적으로 같은 값이 없으면 default 값을 반환하는 함수다. 일반 프로그래밍 언어에 있는 IF-ELSE문과 처리 방식이 같고, CASE와도 처리 방식이 비슷하다.

EXAMPLE : SALES 테이블에서 CHANNEL_ID가 3, 9인 건은 'Direct', 4,5인 건은 'Indirect', 나머지는 'Others'로 보여주는 쿼리

	SELECT PROD_ID,
	DECODE(CHANNEL_ID, 3, 'Direct',
	9, 'Direct',
	5, 'Indirect',
	4, 'Indirect',
	'Others') DECODES
	FROM SALES
	WHERE ROWNUM < 10;

view raworacle_09_18.sql hosted with ❤ by GitHub

이 이상 잘 정리된글이 없다 생각되어 펌글 그대로 올리며 출저를 밝힙니다.

추가 내용및 수정 한 부분있습니다, 원본은 다음과같습니다,

출처: http://metelwiki.tistory.com/65 [METEL WIKI]