[DATABASE][날짜 함수 - 변환 함수 - 일반 함수]

DATABASE

Dual 테이블과 sql 함수분류

날짜 함수

형 변환 함수

일반 함수

날짜 함수

SYSDATE

=함수는 시스템에 저장된 현재 날짜를 반환하는 함수입니다. 다음은 시스템에서 현재 날짜를 얻어 와서 출력하는 예제입니다. 

SELECT SYSDATE FROM DUAL;

날짜 형 데이터에 숫자를 더하면(날짜+숫자) 그 날짜로부터 그 기간만큼 지난 날짜를 계산합니다. 날짜 형 데이터에 숫자를 빼면(날짜-숫자) 그 날짜로부터 그 기간만큼 이전 날짜를 구합니다. 

SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일 FROM DUAL;

ROUND 

=함수는 숫자를 반올림하는 함수로 학습하였습니다. 하지만, 이 함수에 포멧 모델을 지정하면 숫자 이외에 날짜에 대해서도 반올림을 할 수 있습니다.

ROUND (date, format) 

ROUND 함수의 포멧 모델로 MONTH를 지정하였기에 특정 날짜(DATE)를 달(MONTH)을 기준으로 반올림한 날짜를 구합니다.

일을 기준으로 16일보다 적으면 이번 달 1일을 크면 다음달 1일을 구합니다. 6월 9일 반올림하며 6월 1일이 되고 11월 17일은 반올림하여 12월 1이 됩니다. 또한 1월 23일을 반올림하면 2월 1일 됩니다. 

SELECT HIRE_DATE, ROUND (HIRE_DATE, 'MONTH') FROM HR.EMPLOYEES;

TRUNC 

=함수 역시 숫자를 잘라내는 것뿐만 아니라 날짜를 잘라낼 수 있습니다. ROUND 함수와 마찬가지로 포맷 형식을 주어 다양한 기준으로 날짜를 잘라낼 수 있습니다. 

특정 날짜(DATE)를 달(MONTH)을 기준으로 버림한 날짜를 구하기 위해서는 다음과 같이 표현합니다.

TRUNC (date, format)

SELECT HIRE_DATE, TRUNC(HIRE_DATE, 'MONTH')

FROM HR.EMPLOYEES;

MONTHS_BETWEEN

=함수는 날짜와 날짜 사이의 개월 수를 구하는 함수입니다. 

MONTHS_BETWEEN (date1, date2) 

다음은 각 직원들의 근무한 개월 수를 구하는 예제입니다.

SELECT LAST_NAME, SYSDATE, HIRE_DATE, MONTHS_BETWEEN (SYSDATE, HIRE_DATE)

FROM HR;

이 결과 역시 소수점 이하 까지 구해집니다. 소수점 이하자리는 한 달이 되지 못한 일수를 나타냅니다. 이를 TRUNC 함수를 사용하면 소수점 이하를 절삭할 수 있습니다. 

SELECT LAST_NAME, HIRE_DATE, TRUNC( MONTHS_BETWEEN(SYSDATE, HIRE_DATE))

FROM HR.EMPLOYEES;

ADD_MONTHS 

=함수는 특정 개월 수를 더한 날짜를 구하는 함수입니다. 

다음은 입사날짜에서 6개월을 추가하는 예제입니다. 

ADD_MONTHS (date, number) 

SELECT LAST_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 6)

FROM HR.EMPLOYEE;

NEXT_DAY

=함수는 해당 날짜를 기준으로 최초로 도래하는 요일에 해당되는 날짜를 반환하는 함수입니다. 

다음은 오늘을 기준으로 최초로 도래하는 수요일은 언제인지 알아보는 예제입니다.

NEXT_DAY (date, 요일) 

SELECT SYSDATE, NEXT_DAY(SYSDATE, '수요일')

FROM DUAL;

LAST_DAY 

=함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수입니다.

다음은 입사한 달의 마지막 날을 구하는 예제입니다. 

SELECT HIRE_DATE, LAST_DAY(HIRE_DATE)

FROM HR.EMPLOYEES;

형 변환 함수

-오라클을 사용하다 보면 숫자, 문자, 날짜의 데이터 형을 다른 데이터형으로 변환해야 하는 경우가 생깁니다.

-이럴 때 사용하는 함수가 형 변환 함수입니다. 형 변환 함수로는 TO_NUMBER, TO_CHAR, TO_DATE 가 있습니다. 

*문자형으로 변환하는 TO_CHAR 함수 날짜형을 문자형으로 변환하기

DATE 

=형태의 데이터를 지정한 양식에 의해 VARCHAR2 형의 문자로 변환합니다.

TO_CHAR (날짜 데이터, '출력형식') 

다음은 날짜 출력 형식의 종류를 나열한 표(이런형식으로 사용)

다음은 현재 날짜를 기본 형식과 다른 형태로 출력해 봅시다.

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

사원들의 입사일을 출력하되 요일까지 함께 출력해 봅시다.

SELECT HIRE_DATE, TO_CHAR (HIRE_DATE, 'YYYY/MM/DD DAY') 

FROM HR.EMPLOYEES;

년도를 2자리(YY)로 출력하고 월은 문자(MON)로 표시하고 요일을 약어(DY)로 표시한 예입니다.

DATABASE

Dual 테이블과 sql 함수분류

날짜 함수

형 변환 함수

일반 함수

날짜 함수

SYSDATE

=함수는 시스템에 저장된 현재 날짜를 반환하는 함수입니다. 다음은 시스템에서 현재 날짜를 얻어 와서 출력하는 예제입니다. 

SELECT SYSDATE FROM DUAL;

날짜 형 데이터에 숫자를 더하면(날짜+숫자) 그 날짜로부터 그 기간만큼 지난 날짜를 계산합니다. 날짜 형 데이터에 숫자를 빼면(날짜-숫자) 그 날짜로부터 그 기간만큼 이전 날짜를 구합니다. 

SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일 FROM DUAL;

ROUND 

=함수는 숫자를 반올림하는 함수로 학습하였습니다. 하지만, 이 함수에 포멧 모델을 지정하면 숫자 이외에 날짜에 대해서도 반올림을 할 수 있습니다.

ROUND (date, format) 

ROUND 함수의 포멧 모델로 MONTH를 지정하였기에 특정 날짜(DATE)를 달(MONTH)을 기준으로 반올림한 날짜를 구합니다.

일을 기준으로 16일보다 적으면 이번 달 1일을 크면 다음달 1일을 구합니다. 6월 9일 반올림하며 6월 1일이 되고 11월 17일은 반올림하여 12월 1이 됩니다. 또한 1월 23일을 반올림하면 2월 1일 됩니다. 

SELECT HIRE_DATE, ROUND (HIRE_DATE, 'MONTH') FROM HR.EMPLOYEES;

TRUNC 

=함수 역시 숫자를 잘라내는 것뿐만 아니라 날짜를 잘라낼 수 있습니다. ROUND 함수와 마찬가지로 포맷 형식을 주어 다양한 기준으로 날짜를 잘라낼 수 있습니다. 

특정 날짜(DATE)를 달(MONTH)을 기준으로 버림한 날짜를 구하기 위해서는 다음과 같이 표현합니다.

TRUNC (date, format)

SELECT HIRE_DATE, TRUNC(HIRE_DATE, 'MONTH')

FROM HR.EMPLOYEES;

MONTHS_BETWEEN

=함수는 날짜와 날짜 사이의 개월 수를 구하는 함수입니다. 

MONTHS_BETWEEN (date1, date2) 

다음은 각 직원들의 근무한 개월 수를 구하는 예제입니다.

SELECT LAST_NAME, SYSDATE, HIRE_DATE, MONTHS_BETWEEN (SYSDATE, HIRE_DATE)

FROM HR;

이 결과 역시 소수점 이하 까지 구해집니다. 소수점 이하자리는 한 달이 되지 못한 일수를 나타냅니다. 이를 TRUNC 함수를 사용하면 소수점 이하를 절삭할 수 있습니다. 

SELECT LAST_NAME, HIRE_DATE, TRUNC( MONTHS_BETWEEN(SYSDATE, HIRE_DATE))

FROM HR.EMPLOYEES;

ADD_MONTHS 

=함수는 특정 개월 수를 더한 날짜를 구하는 함수입니다. 

다음은 입사날짜에서 6개월을 추가하는 예제입니다. 

ADD_MONTHS (date, number) 

SELECT LAST_NAME, HIRE_DATE, ADD_MONTHS(HIRE_DATE, 6)

FROM HR.EMPLOYEE;

NEXT_DAY

=함수는 해당 날짜를 기준으로 최초로 도래하는 요일에 해당되는 날짜를 반환하는 함수입니다. 

다음은 오늘을 기준으로 최초로 도래하는 수요일은 언제인지 알아보는 예제입니다.

NEXT_DAY (date, 요일) 

SELECT SYSDATE, NEXT_DAY(SYSDATE, '수요일')

FROM DUAL;

LAST_DAY 

=함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수입니다.

다음은 입사한 달의 마지막 날을 구하는 예제입니다. 

SELECT HIRE_DATE, LAST_DAY(HIRE_DATE)

FROM HR.EMPLOYEES;

형 변환 함수

-오라클을 사용하다 보면 숫자, 문자, 날짜의 데이터 형을 다른 데이터형으로 변환해야 하는 경우가 생깁니다.

-이럴 때 사용하는 함수가 형 변환 함수입니다. 형 변환 함수로는 TO_NUMBER, TO_CHAR, TO_DATE 가 있습니다. 

*문자형으로 변환하는 TO_CHAR 함수 날짜형을 문자형으로 변환하기

DATE 

=형태의 데이터를 지정한 양식에 의해 VARCHAR2 형의 문자로 변환합니다.

TO_CHAR (날짜 데이터, '출력형식') 

-다음은 날짜 출력 형식의 종류를 나열한 표(이런형식으로 사용)

-다음은 현재 날짜를 기본 형식과 다른 형태로 출력해 봅시다.

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

-사원들의 입사일을 출력하되 요일까지 함께 출력해 봅시다.

SELECT HIRE_DATE, TO_CHAR (HIRE_DATE, 'YYYY/MM/DD DAY') 

FROM HR.EMPLOYEES;

-년도를 2자리(YY)로 출력하고 월은 문자(MON)로 표시하고 요일을 약어(DY)로 표시한 예입니다.

-다음은 현재 날짜와 시간을 출력하는 예제입니다

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

FROM DUAL;

-다음 표는 숫자 출력 형식을 나열한 표입니다.

-각 지역별 통화 기호를 앞에 붙이고 천 단위마다 콤마를 붙여서 출력(예 : \1,230,000）하려면 어떻게 해야 할까요? 위 표를 참조하여 다음과 같이 표현해야 합니다.

SELECT LAST_NAME, SALARY, TO_CHAR (SALARY, 'L999,999')

FROM HR.EMPLOYEES;

-날짜형으로 변환하는 TO_DATE 함수

TO_DATE

-날짜 형으로 변환하기에 앞서 날짜 형에 대해서 살펴보도록 합시다. 날짜 형은 세기, 년도, 월, 일. 시간, 분. 초와 같이 날짜와 시간에 대한 정보를 저장합니다.

-오라클에서 기본 날짜 형식은 ‘YY/MM/DD'형식으로 ’년/월/일‘ 예를 들면 ’06/03/08‘ 식으로 나타냅니다.

-만일 년도를 4자리로 출력하려면 ‘YYYY/MM/DD' 형식으로 지정합니다.

TO_DATE 함수는 문자열을 날짜 형으로 변환합니다. 

TO_DATE(‘문자’, ‘format')

-숫자형태인 19810220 을 TO_DATE 함수를 사용해서 날짜형으로 변환합시다. 이때 두 번째 인자는 출력할 형식을 지정해 주어야 합니다.

SELECT LAST_NAME, HIRE_DATE FROM HR.EMPLOYEES

WHERE HIRE_DATE=TO_DATE(19801012,'YYYYMMDD’);

-다음은 올해 며칠이 지났는지 현재 날짜에서 2008/01/01을 뺀 결과를 출력하는 예제입니다.

SELECT TRUNC(SYSDATE-TO_DATE('2008/01/01', 'YYYY/MM/DD'))

FROM DUAL;

다음은 올해 며칠이 지났는지 현재 날짜에서 2008/01/01을 뺀 결과를 출력하는 예제입니다.

SELECT TRUNC(SYSDATE-TO_DATE('2008/01/01', 'YYYY/MM/DD'))

FROM DUAL;

숫자형으로 변환하는 TO_NUMBER 함수

TO_NUMBER 

=함수는 특정 데이터를 숫자형으로 변환해 주는 함수입니다.

다음과 같이 '20,000'을 '10,000'의 차이를 알아보기 위해서 빼기를 해 봅시다. 

산술 연산을 하려면 문자형을 숫자형으로 변환한 후에 실행해야 합니다. 

SELECT TO_NUMBER('20,000', '99,999') - TO_NUMBER('10,000', '99,999')

FROM DUAL;

NULL을 다른 값으로 변환하는 NVL 함수

NVL 

=함수는 NULL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수입니다. 

SELECT ENAME, SAL, COMM, SAL*12+COMM,

NVL(COMM, 0), SAL*12+NVL(COMM, 0)

FROM EMP

ORDER BY JOB;

선택을 위한 DECODE 함수

-DECODE 함수는 프로그램 언어에서 가장 많이 사용되는 switch case 문과 같은 기능을 갖습니다.

-즉, 여러 가지 경우에 대해서 선택할 수 있도록 합니다.

다음은 DECODE 함수의 기본 형식입니다

DECODE (표현식, 조건1, 결과1,

                           조건2, 결과2,

                           조건3, 결과3,

                           기본결과n

)

부서번호에 해당되는 부서명을 구하는 예제를 이번에는 CASE 함수를 사용하여 작성해 봅시다. 

SELECT ENAME, DEPTNO,DECODE(DEPTNO,  10, 'ACCOUNTING',

  20, 'RESEARCH',

  30, 'SALES',

  40, 'OPERATIONS' )

  AS DNAME

FROM EMP;

CASE 

=함수 역시 여러 가지 경우에 대해서 하나를 선택하는 함수입니다.

-DECODE 함수와 차이점이 있다면 DECODE 함수는 조건이 일치(= 비교 연산자)하는 경우에 대해서만 적용되는 반면, CASE 함수는 다양한 비교 연산자를 이용하여 조건을 제시할 수 있으므로 범위를 지정할 수도 있습니다.

-CASE 함수는 프로그램 언어의 if else if else 와 유사한 구조를 갖습니다. 

CASE 표현식  WHEN 조건1 THEN 결과1

                     WHEN 조건2 THEN 결과2

                     WHEN 조건3 THEN 결과3

                     ELSE 결과n

                 END

부서번호에 해당되는 부서명을 구하는 예제를 이번에는 CASE 함수를 사용하여 작성해 봅시다. 

SELECT ENAME, DEPTNO,

              CASE WHEN DEPTNO=10 THEN 'ACCOUNTING'

                        WHEN DEPTNO=20 THEN 'RESEARCH'

                        WHEN DEPTNO=30 THEN 'SALES'

                        WHEN DEPTNO=40 THEN 'OPERATIONS'

               END AS DNAME

FROM EMP;