[오라클] 함수 function - 2

날짜 함수

종류	의미
SYSDATE(시스템 현재 날짜) SYSTIMESTAMP(시스템 현재 날짜와 시간)	날짜와 관련된 시스템 값
add_months(날짜,정수)	날짜에 정수 달을 더했을떼 변경되는 날짜를 반환
month_between(날짜1, 날짜2)	두 날짜 사이에 몇 개월의 차이가 있는지 반환
last_day(날짜)	특정 달의 마지막 날이 며칠인지 반환
next_day(날짜, 요일)	날짜를 기준으로 그 다음 요일이 며칠인지 반환

 

일 단위 처리

SELECT SYSDATE, SYSTIMESTAMP FROM DUAL;
SELECT SYSDATE +1 AS "내일", SYSDATE -1 AS "어제" FROM DUAL;

 

월 단위 처리

SELECT ADD_MONTHS(SYSDATE, 10) FROM DUAL;
SELECT ADD_MONTHS(SYSDATE, -10) FROM DUAL;

 

SELECT TO_DATE('23/01/01'), '23/01/01' FROM DUAL; 
-- 앞은 연산 가능한 데이터 , 뒤에는 단지 문자열, 연산 불가

 

시간도 포함되어 있다.

SELECT ADD_MONTHS(SYSDATE, -10) FROM DUAL;
SELECT CEIL(SYSDATE- TO_DATE('23/01/01')) FROM DUAL;

 

[Q] 이번 달의 마지막 날을 구하시오.

SELECT LAST_DAY(SYSDATE) FROM DUAL;

 

문제풀기

문제 1] 입사한 달의 근무일수를 계산하고 이름, 부서번호, 근무일수를 각각 출력

SELECT FIRST_NAME, DEPARTMENT_ID,LAST_DAY(HIRE_DATE)-HIRE_DATE AS "WORKING DAY"
FROM EMPLOYEES;

 

 

문제 2] 80번 부서에 근무하는 직원들의 시간당 임금을 계산하시오(이름, 급여, 시간당 임금)

SALARY 를 월급 ==> 소수점 이하 2자리 절삭 (주 5일 근무, 하루 8시간 근무한다고 가정)

SELECT FIRST_NAME, SALARY, TRUNC(SALARY/160, 2) AS "시간당 임금"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
ORDER BY "시간당 임금" DESC;

 

문제 3] JOB_HISTORY 테이블의 정보를 이용해 102번 직원이 특정 부서에서 몇개월을 근무했는지 계산하시오

SELECT CEIL((END_DATE - START_DATE)/30) AS "근무 개월수", department_id
FROM JOB_HISTORY
WHERE EMPLOYEE_ID = 102;

 

문제 5] 수료일까지 남은 날짜 출력하시오

SELECT CEIL(TO_DATE('24/05/08') - SYSDATE ) FROM DUAL;

 

변환 함수 종류

변환 함수	의미
TO_CHAR(날짜/숫자, 포맷)	날짜를 특정 포맷에 맞춰 문자열로 변환
TO_DATE(문자열)	날짜 형태로 된 문자열을 날짜 타입으로 변환
TO_NUMBER(문자열)	날짜형태로 된 문자열을 숫자타입으로 변환

 

앞 123.5 = 문자열 // 뒤 123.5 = 숫자

SELECT '123.5', TO_NUMBER('123.5') FROM DUAL;

 

TO_CHAR 를 이용한 날짜 포맷

SELECT SYSDATE , 
       TO_CHAR(SYSDATE, 'YYYY-MM-DD') AS "현재 날짜 ", 
       TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI') AS "현재시간 1)", 
       TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH:MI') AS "현재시간 2)",-- ***
       TO_CHAR(SYSDATE, 'YYYY-Mon-DDD HH:MI') AS "현재시간 3)"
FROM DUAL;

 

TO_CHAR 를 이용한 숫자포맷

SELECT
    TO_CHAR(1234560,'999,999,999.99'),
    TO_CHAR(1234560,'$999,999,999.00'),
    TO_CHAR(1234560,'L999,999,999.00')
FROM DUAL;
-- .99 있으면 표시하라는 의미
-- .00 없어도 소수로 만드는

 

[Q] 이름, 급여(천자리 ',' 소수점 3자리 표현), 입사일(4-2-2)을 출력하시오

SELECT FIRST_NAME, TO_CHAR(SALARY,'$999,999,999.000') AS SALARY,
    TO_CHAR(HIRE_DATE,'YYYY-MM-DD')
FROM EMPLOYEES;

 

NULL 함수 종류

NULL  함수	의미
NVL(컬럼, 데이터)	특정 컬럼의 데이터가 (null) 이면 NULL 대신 데이터 값으로 출력한다.
NVL2( 컬럼, 데이터1, 데이터2)	특정 컬럼의 데이터가 null이면 데이터2,아니면 데이터 1

 

NVL 예제

SELECT FIRST_NAME, NVL(COMMISSION_PCT,0)
FROM EMPLOYEES;

 

NVL2 예제

SELECT FIRST_NAME, SALARY, COMMISSION_PCT,
    NVL2(COMMISSION_PCT,SALARY*COMMISSION_PCT,0) AS "커미션"
FROM EMPLOYEES;

 

문제풀이

문제 1] 이름, 부서 번호를 출력. 부서번호가 없으면 0이라고 출력

SELECT FIRST_NAME, NVL(DEPARTMENT_ID,0)
FROM EMPLOYEES;

 

문제 2] 이름, 부서 번호를 출력. 부서 번호가 없으면 "임시"라고 출력

SELECT FIRST_NAME, NVL(TO_CHAR(DEPARTMENT_ID),'임시')AS "DEPARTMENT"
FROM EMPLOYEES;

 

문제 3] 사원 번호, 이름, 팀장번호 를 출력. 팀장 번호가 없으면 "회장"라고 출력

SELECT EMPLOYEE_ID, FIRST_NAME,MANAGER_ID, NVL(TO_CHAR(MANAGER_ID),'회장') AS "매니저"
FROM EMPLOYEES;