모방은 창조의 어머니

오라클(Oracle) 함수 정리

오라클 데이터베이스에서 사용되는 함수를 한곳에서 보고 싶어 보아 보았습니다.
오라클 함수는 크게 문자열 함수, 숫자 함수, 날짜 함수, 변환 함수, 집계 함수, 분석 함수로 구분 지어 정리.

1. 문자열 함수(String Functions)

• UPPER(문자열) : 문자열을 대문자로 변환하여 반환 합니다.
• LOWER(문자열) : 문자열을 소문자로 변환하여 반환 합니다.
• INITCAP(문자열) : 각 단어의 첫 글자를 대문자로 변환하여 반환 합니다.
• LENGTH(문자열) : 문자열의 길이를 반환합니다.
• LENGTHB(문자열) : 문자열의 길이를 바이트 단위로 구해서 반환합니다.
• SUBSTR(문자열, 시작 위치, 길이) : 지정한 위치에서 부분 문자열을 추출합니다.
• INSTR(문자열, 검색 문자열, 시작 위치, 발생 횟수) : 특정 문자열이 나타나는 위치를 반환합니다.
• REPLACE(문자열, 검색 문자열, 대체 문자열) : 특정 문자열을 다른 문자열로 대체합니다.
• LPAD(문자열, 검색 문자열, 길이, 대체 문자열) : 문자열에서 길이만큼 대체 문자열로 문자열 왼쪽을 대체 합니다.
• RPAD(문자열, 검색 문자열, 길이, 대체 문자열) : 문자열에서 길이만큼 대체 문자열로 문자열 오쪽을 대체 합니다.
• CONCAT(문자열1, 문자열2 ) : 두 개의 문자열을 연결합니다.
• TRIM(문자열) : 문자열의 앞뒤 공백을 제거합니다.

2. 숫자 함수(Numeric Functions)

• ROUND(숫자, 소수점 자리) : 지정한 자리에서 반올림합니다.
• TRUNC(숫자, 소수점 자리) : 지정한 자리에서 절삭합니다.
• MOD(숫자1, 숫자2) : 나머지 연산을 수행합니다.
• CEIL(숫자) : 올림 값을 반환합니다.
• FLOOR(숫자) : 내림 값을 반환합니다.
• ABS(숫자) : 절댓값을 반환합니다.

3. 날짜 함수(Date Functions)

• SYSDATE : 현재 날짜 및 시간을 반환합니다.
• CURRENT_DATE : 세션의 현재 날짜를 반환합니다.
• ADD_MONTHS(날짜, 개월 수) : 날짜에 지정한 개월 수를 추가합니다.
• MONTHS_BETWEEN(날짜1, 날짜2) : 두 날짜 간 개월 수를 반환합니다.
• NEXT_DAY(날짜, 요일) : 지정한 날짜 이후의 가장 가까운 해당 요일을 반환합니다.
• LAST_DAY(날짜) : 해당 월의 마지막 날을 반환합니다.
• EXTRACT(YEAR | MONTH | DAY FROM 날짜) : 날짜에서 연, 월, 일을 추출합니다.

4. 변환 함수(Conversion Functions)

• TO_CHAR(날짜 또는 숫자, 형식) : 날짜 또는 숫자를 문자열로 변환합니다.
• TO_DATE(문자열, 형식) : 문자열을 날짜로 변환합니다.
• TO_NUMBER(문자열, 형식) : 문자열을 숫자로 변환합니다.

5. 집계 함수(Aggregate Functions)

• COUNT(컬럼) : 행 개수를 반환합니다.
• SUM(컬럼) : 합계를 반환합니다.
  AVG(컬럼) : 평균 값을 반환합니다.
• MAX(컬럼) : 최대값을 반환합니다.
• MIN(컬럼) : 최소값을 반환합니다.
• GROUP BY 컬럼 : 그룹별 집계를 수행합니다.

6. 분석 함수(Analytic Functions)

• RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 순위를 반환하며, 동일 값에 동일 순위를 부여합니다.
• DENSE_RANK() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 중복 없이 연속된 순위를 반환합니다.
• ROW_NUMBER() OVER (PARTITION BY 컬럼 ORDER BY 컬럼) : 행 번호를 반환합니다.
• LEAD(컬럼, 이동 행 수, 기본값) OVER (ORDER BY 컬럼) : 다음 행의 값을 반환합니다.
• LAG(컬럼, 이동 행 수, 기본값) OVER (ORDER BY 컬럼) : 이전 행의 값을 반환합니다.
• NTILE(N) OVER (ORDER BY 컬럼) : 전체 데이터를 N개의 그룹으로 나눕니다.

7. 기타 함수

• NVL(컬럼, 기본값) : NULL 값을 기본값으로 대체합니다.
• NVL2(컬럼, 값1, 값2) : 컬럼이 NULL이 아닐 경우 값1, NULL이면 값2를 반환합니다.
• DECODE(컬럼, 조건1, 결과1, 조건2, 결과2, ...) : IF-ELSE와 유사한 조건 처리를 수행합니다.
• CASE WHEN 조건 THEN 결과 ELSE 결과 END : 조건문을 처리합니다.

문자에 포함된 %, _ 찾기

데이터에서 '%'나 '_'가 포함되어 있는 경우 어떻게 찾으면 될까요? LIKE문에서 '%'와 '_'는  와일드 카드로 사용되어 일반적인 방법으로는 해당 문자열의 포함여부를 찾으실수 없습니다. 이때에는 ESCAPE절을 사용하시면 됩니다. 문자열에서 '0_0'로 시작하는 데이터를 찾으려면 아래와 같이 SQL문을 작성하시면 됩니다.

SELECT *
FROM SAMPLE_TABLE
WHERE SAMPLE_COLUMN LIKE '0\_0%' ESCAPE '\';

'\'는 '\'문자 바로 뒤에 있는 '_'를 와일드 카드가 아닌 문자로 인식하라는 의미입니다. 이스케이프 문자 '\'는 ESCAPE절에서 지정할 수 있습니다. '\'이외에 다른 문자도 지정하여 사용 할 수 있습니다.

[Oracle] 형변환 함수

오라클에서는 동일한 자료 형에 대한 연산을 제공합니다. 연산을 위해서 자동으로 형을 변환해 주는 경우도 있으나, 명확한 결과를 위해서는 자동변환(암시적 형 변환, Implicit type conversion)을 이용하기 보다는 형변환후 사용하는 추천합니다.

자동 변환(암시적 형변환)의 예

SELECT  SAL, SAL + '777'
FROM EMP;

숫자형 데이터에 문자형 데이터 '777'을 더하면 오류가 날것 같지만, 자동변환을 통하여 연산되어 집니다.

명시적 형변환(Explicit type conversion)

명시적 형변환은 자동으로 자료형이 변환되는 방식이 아닌, 사용자가 직접 자료형을 변환하는 방식입니다. 명시적 형변환에 사용하는 함수는 아래와 같은 것들이 있습니다.

[Oracle] 날짜 함수 - 날짜의 버림 TRUNC 함수

날짜에 대한 버림시에는 숫자 데이터를 다룰 때와는 달리 버림의 기준이 될 포맷을 정의 합니다. 

ROUND ( 날짜 데이터, [반올림 기준 포맷] )

기준 포멧

SELECT  SYSDATE,
        TRUNC(SYSDATE, 'CC'),
        TRUNC(SYSDATE, 'YYYY'),
        TRUNC(SYSDATE, 'Q'),
        TRUNC(SYSDATE, 'DDD'),
        TRUNC(SYSDATE, 'HH')
FROM DUAL;

[Oracle] 날짜 함수 - 날짜를 반올림 하는 ROUND 함수

날짜에 대한 반올림시에는 숫자 데이터를 다룰 때와는 달리 반올림의 기준이 될 포맷을 정의 합니다. 

ROUND ( 날짜 데이터, [반올림 기준 포맷] )

기준 포멧

SELECT  SYSDATE,
        ROUND(SYSDATE, 'CC'),
        ROUND(SYSDATE, 'YYYY'),
        ROUND(SYSDATE, 'Q'),
        ROUND(SYSDATE, 'DDD'),
        ROUND(SYSDATE, 'HH')
FROM DUAL;

해당 월의 마지막 날을 구하는 함수 LAST_DAY

우리는 1월은 31일, 2월은 28일과 같이 매월 마지막 날을 알고 있습니다. 하지만, 2월은 29일인 경우도 있고, 특정월이 지정되지 않는다면 마지막 날이 몇일인지 구하는 것이 쉽지 않을 수도 있습니다. 이때 사용하는 함수가 LAST_DAY 함수 입니다.

LAST_DAY ( 날짜 데이터) 

LAST_DAY 함수는 날짜 데이터가 속한 달의 마지막 날을 리턴하는 함수 입니다. 이번달의 마지막 날은 몇일 인지 알아 보겠습니다.

SELECT SYSDATE,
        LAST_DAY(SYSDATE)
FROM DUAL;

[Oracle] 날짜 함수 - 돌아오는 요일을 구하는 (NEXT_DAY)

NEXT_DAY 함수는 날짜데이터와 요일 문자열을 지정하여 입력한 날짜 데이터 다음에 돌아오는 요일이 몇일인지를 반환합니다.

NEXT_DAY( 날짜 데이터, 요일문자 )

요일문자는 요일을 나타내는 문자로 다음과 같습니다.

오늘 다음에 오는 월요일이 몇일인지 확인해 봅시다.

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

오늘을 기준으로 이전에 특정요일이 몇일이었는지 알고 싶으면 어떻게 하면 될까? 기준일을 1주일 전(SYSDATE-8)으로 하면 간단히 해결할 수 있습니다. 

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

[Oracle] 날짜 함수 - 두 날짜 간의 개월 차이를 구하는 함수(MONTHS_BETWEEN)

MONTHS_BETWEEN 함수는 두개의 날짜 데이터 간의 개월 수 차이를 구할 때 사용합니다.

MONTHS_BETWEEN 함수를 이용하여 직원(EMP) 테이블에서 사원들이 입사한지 얼마나 되었는지 알아 봅시다.

 SELECT ENAME, HIREDATE, SYSDATE, MONTHS_BETWEEN(HIREDATE, SYSDATE)
FROM EMP;

아래는 입사일로 부터 오늘까지의 기간(개월)을 조회한 결과 입니다. 

입사일에서 오늘까지의 개월수 차이를 구하니, 음수에, 소수점 까지 나오네요. 이것을 보기 좋게 정리해 봅시다.

SELECT  ENAME, HIREDATE, SYSDATE, 
        MONTHS_BETWEEN(SYSDATE, HIREDATE),
        TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS TOTAL_MONTH,
        TRUNC( TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))/12) AS YEAR,
        MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)),12) AS MONTH
FROM EMP;

너무 복잡해 보이시나요?

• MONTHS_BETWEEN(SYSDATE, HIREDATE) : 입사일로 부터 오늘 까지의 개월 수를 구합니다.
• TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) : 개월수의 소수점 이하는 버리고 정수부분만 취합니다.
• TRUNC( TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE))/12)  : 개월수를 년으로 환산하기 위해서 12로 나누고 소수점 이하는 버립니다. 
• MOD(TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)),12) : 개월수를 12로 나눈 나머지를 구합니다.

하나씩 생각해 보면 익숙하지 않아서 그렇지 쿼리에서 모르는 부분은 없을 것 같네요.

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.
※ 예제에서 소수점 이하를 버리는데 사용된 TRUNC 함수가 궁금하신 분은 '[Oracle] 숫자 함수 - 특정 위치에서 버리는 함수(TRUNC) '부분을 참고 하시면 됩니다. 

[Oracle] 날짜 함수 - 몇 개월 이후의 날짜를 구하는 함수(ADD_MONTHS)

ADD_MONTHS 함수는 특정 날짜에 지정한 개월 수 이후 날짜 데이터를 반환 합니다. 이때 지정한 개월 수는 정수여야 합니다. 

ADD_MONTHS ( 날짜 데이터, 더할 개월 수 )

ADD_MONTHS 함수의 예로 지금 부터 6개월 뒤의 날짜를 구해 봅시다.

SELECT  ADD_MONTHS(SYSDATE, 6) 
FROM DUAL;

이번에는 직원들이 입사한지 10년 되는 날이 언제인지 구해 봅시다.

SELECT  ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 10*12) 
FROM EMP;

위 쿼리에서는 10년을 개월수로 표현하기 위하여 120이라는 개월 수를 사용하는 대신 10년을 구분하기 쉽게 '10*12'라는 수식을 사용하였습니다. 

ADD_MONTHS 함수를 이용하여 30년 이상 근무한 사람을  출력할 수도 있습니다.

SELECT ENAME, HIREDATE, SYSDATE
FROM EMP
WHERE ADD_MONTHS(HIREDATE, 30*12) < SYSDATE;

※ 예제로 사용된 TABLE의 정보는 'SCOTT 계정 예제 Table(emp, dept, salgrade)'을 참고하시면 됩니다.

[Oracle] 날짜 함수 - 오늘이 몇일(SYSDATE) 

오늘은 SYSDATE 함수로 알 수있습니다. 다만, SYSDATE 함수로 알수 있는 날짜와 시간은 오라클 데이터베이스 서버상의 현재 날짜와 시간입니다. 이 함수를 이용하여, 오늘부터 100일 뒤가 몇월 몇일일까? 오늘이 1000일이면 우리가 만난 날은 언제이지? 와 같은 것을 쉽게 알 수 있는 방법이 없을까요? 오라클에서는 날짜 데이터를 이용한 간단한 연산을 제공합니다.

• 날짜 데이터 + 숫자 : 날짜 데이터보다 숫자만큼 일수 이후의 날짜를 반환합니다.
• 날짜 데이터 - 숫자 : 날짜 데이터보다 숫자만큼 일수 이전의 날짜를 반환합니다.
• 날짜 데이터 - 날짜 데이터 : 두 날짜 데이터 간의 일 수 차이를 반환합니다.
• 날짜 데이터 + 날짜 데이터 : 지원하지 않습니다.(연산불가)

예제를 통해 간단히 알아 봅시다. 

SELECT  SYSDATE AS NOW,
        SYSDATE+100 AS AFTER_100,
        SYSDATE-1000 AS BEFOR_1000
FROM DUAL;