[3일차] 함수의 마무리

오늘은 3일차. 바로 시작해보겠다.

 

<날짜 처리 함수>

SYSDATE

시스템상 현재 날짜 및 시간을 반환하는 함수. 

SELECT SYSDATE FROM DUAL;

 

MONTHS_BETWEEN

두 날짜 사이의 개월 수를 반환해준다.

SELECT EMP_NAME, HIRE_DATE, TRUNC(SYSDATE - HIRE_DATE), 
CEIL(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) || '개월차' AS "근속개월"
FROM EMPLOYEE;

ADD_MONTHS

특정 날짜에 NUMBER개월수를 더해서 반환한다.

SELECT ADD_MONTHS(SYSDATE, 4) FROM DUAL;

NEXT_DATE(DATE, 요일(문자 | 숫자))

특정 날짜 이후 가장 가까운 요일의 날짜를 반환

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

번외(언어팩에 따른 오류)

SELECT NEXT_DAY(SYSDATE, 'FRIDAY') FROM DUAL;

위 코드는 실행하면 에러가 난다. 그 이유는 내 오라클에는 한국어로 언어팩이 설정되어 있기 때문에 FRIDAY를 알아보지 못한다.

ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
ALTER SESSION SET NLS_LANGUAGE = KOREAN;

그럴 땐 위 코드와 같이 필요한 언어로 언어를 변경해주면 잘 인식한다.

 

LAST_DAY(DAY) 

해당 월의 마지막 날짜 구해서 반환한다.

SELECT LAST_DAY(SYSDATE) FROM DUAL;

EXTARCT

특정 날짜로부터 년도|월|일 값을 추출해서 반환한다.

[표현법]
EXTRACT(YEAR FROM DATE) : 연도 만 추출
EXTRACT(MONTH FROM DATE) : 월 만 추출
EXTRACT(DAY FROM DATE) : 일 만 추출
=> 결과는 모두 NUMBER

SELECT EMP_NAME, EXTRACT (YEAR FROM HIRE_DATE) AS "입사년도", 
EXTRACT (MONTH FROM HIRE_DATE) AS "입사 월",
EXTRACT (DAY FROM HIRE_DATE) AS "입사 일"
FROM EMPLOYEE;

<형변환 함수>

TO_CHAR            숫자 OR 날짜-> 문자

숫자 타입 또는 날짜 타입의 값을 문자타입으로 변환시켜주는 함수다.

[표현법]
TO_CHAR(숫자 | 날짜, [포멧])

SELECT TO_CHAR(1234) FROM DUAL;
SELECT TO_CHAR(1234, '9999') AS "NUMBER" FROM DUAL; --9의 개수 만큼 공간 확보, 오른쪽 정렬, 빙칸공백
SELECT TO_CHAR(1234, '0000000') AS "NUMBER" FROM DUAL; --0의 개수 만큼 공간 확보 후 남은 공간0으로 채움
SELECT TO_CHAR(1234, 'L99999') FROM DUAL; --L쓰면 원 표시 붙어서나옴, 각 언어팩에 설정된 현재나라의 화폐
SELECT TO_CHAR(1234, 'L99,999')  FROM DUAL; --컴마도 가능하다.

 

출력값 : 

1234
 1234
0001234
         ￦1234
         ￦1,234

 

TO_CHAR       날짜 -> 숫자

날짜타입을 문자타입으로 바꿔준다

[표현법]
TO_CHAR(날짜 | [포멧])

SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE) FROM DUAL; --시간 짤리고 바뀜
SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL; --시간만 바꿈
SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM DUAL; --AM, PM 둘 다 가능
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DY') FROM DUAL;--월 만 나옴
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD DAY HH:MI:SS') FROM DUAL; --시간까지
SELECT TO_CHAR(SYSDATE, 'MON, YYYY') FROM DUAL;

 

년도와 관련된 포맷

SELECT TO_CHAR(SYSDATE, 'YYYY'),
        TO_CHAR(SYSDATE, 'YY'),
         TO_CHAR(SYSDATE, 'RRRR'), --RR룰이 따로 존재한다 -> 50년 이상 값이 +100 -> EX) 1954
          TO_CHAR(SYSDATE, 'RR')
FROM DUAL;

 

 

월과 관련된 포맷

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

 

 

일과 관련된 포맷

SELECT TO_CHAR(SYSDATE, 'DDD'), --오늘의 이번년도에서 몇 번째 일수
        TO_CHAR(SYSDATE, 'DD'),
         TO_CHAR(SYSDATE, 'D') --요일 ->숫자
FROM DUAL;

 

 

요일에 관련된 포맷

SELECT TO_CHAR(SYSDATE, 'DAY'),
        TO_CHAR(SYSDATE, 'DY')
FROM DUAL;

 

 

 

TO_DATE       숫자 OR 문자 -> 날짜

숫자타입 또는 문자타입을 날짜타입으로 변경하는 함수

[표현법]
TO_DATE(숫자 | 문자, [포맷])

SELECT TO_DATE(20100101) FROM DUAL;
SELECT TO_DATE(650219) FROM DUAL;--50년 미만은 자동으로 20XX으로 설정 50년 이상은 19XX로 설정된다
SELECT TO_DATE(020505) FROM DUAL; --숫자는 0으로 시작하면 안됨
SELECT TO_DATE('020505') FROM DUAL; --문자는 됨

SELECT TO_DATE('20240219 120800','YYYYMMDD HH24MISS ') FROM DUAL;

 

 

 

TO_NUMBER     문자 -> 숫자

문자타입의 데이터를 숫자타입으로 변환시켜주는 함수

[표현법]
TO_NUMBER(문자, [포맷])

SELECT TO_NUMBER('05123456789') FROM DUAL; --0 없어짐
SELECT '100000' + '55000' FROM DUAL; --자동으로 형변환해서 더해준다
SELECT '100000' + '55000' FROM DUAL;
SELECT TO_NUMBER('100,000','999,999') + TO_NUMBER('55,000', '99,999') FROM DUAL;

 

 

<NULL 처리 함수>

 

NVL

[표현법]
NVL(컬럼, 해당 컬럼이 NULL일 경우 보여줄 값)

SELECT EMP_NAME, NVL(BONUS, 0) FROM EMPLOYEE;

*BONUS가 NULL일 경우 0으로 대체해서 표시

 

NVL2

[표현법]
NVL(컬럼, 반환값1, 반환값2)
반환값 1: 해당컬럼이 존재할 경우 보여줄 값
반환값 2: 해당 컬럼이 NULL이지 않을 경우 보여줄 값

SELECT EMP_NAME, NVL2(BONUS, 'O', 'X') FROM EMPLOYEE;

*존재하면 O NULL일 경우 X

 

NULLIF

[표현법]
NULLIF(비교대상1, 비교대상2)
비교대상의 값이 일치하면 NULL 반환
일치하지 않는다면 비교대상 1 반환

SELECT NULLIF('123','123') FROM DUAL; --두 값이 일치하면 NULL
SELECT NULLIF('123','1234') FROM DUAL; --두 값이 일치하지 않는다면 비교대상1 반환

 

 

<선택함수>

DECODE

DECODE(비교하고자하는 대상(컬럼, 연산식, 함수식), 비교값 1, 결과값 1, 비교값 2, 결과값 2....)
자바의 스위치랑 비슷하다.

 

SELECT EMP_ID, EMP_NAME, EMP_NO,
DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여','외계인') AS "성별"-- < 외계인은 디폴트값
FROM EMPLOYEE;

SELECT EMP_NAME, SALARY AS "기존급여" , 
    DECODE(JOB_CODE, 
    'J7', SALARY * 1.1, 
    'J6', SALARY * 1.5, 
    'J5', SALARY * 1.2 , 
          SALARY * 1.05) AS "인상된 급여"
FROM EMPLOYEE;

이런식으로 비교값 결과값 1대1 매칭을하여 사용할 수 있다.

 

CASE WHEN THEN

 

CASE
    WHEN 조건식1 THEN 결과값1
    WHEN 조건식2 THEN 결과값2
    ...
    ELSE 결과값
END

SELECT EMP_NAME, SALARY,
    CASE WHEN SALARY>=5000000 THEN '고급'
         WHEN SALARY>=3500000 THEN '중급'
         ELSE '초급'
    END AS "급수"
FROM EMPLOYEE;

뭔가 개인적으로 손이 잘 가지않는다

 

 

 

 

 

 

함수는 여기까지이다. 원래 그룹함수도 남아있지만 그룹함수는 다음 포스팅인 GROUP_BY와 함께 다뤄보도록 하겠다.