[Oracle SQL] 숫자, 날짜, 변환, 일반 함수

본 게시글은 이수안컴퓨터연구소의 데이터베이스 유튜브 동영상을 개인적으로 정리하는 글입니다. 

---

숫자 함수

숫자 함수는 주로 숫자 계산과 추가 처리에 사용

 

CEIL() / FLOOR()

• 숫자를 정수로 올림하는 CEIL() 함수, 숫자를 정수로 내림하는 FLOOR() 함수

SELECT salary, salary/21, CEIL(salary/21), FLOOR(salary/21) FROM employees;

 

ROUND() / TRUNC()

• 숫자를 반올림하는 ROUND() 함수, 숫자를 절삭하는 TRUNC() 함수

SELECT salary, salary/21, 
    ROUND(salary/21), ROUND(salary/21, 2), ROUND(salary/21, -1),
    TRUNC(salary/21), TRUNC(salary/21, 2), TRUNC(salary/21, -1)
FROM employees;

 

MOD() 

• 숫자를 나눈 후 나머지를 구함

SELECT salary, salary/21, MOD(salary, 21) FROM employees;

 

SIGN()

• 숫자가 양수일 경우 1, 음수일 경우 -1, 나머지는 0을 반환

SELECT SIGN(-123), SIGN(0), SIGN(123) FROM dual;

 

POWER() / SQRT()

• 거듭제곱을 출력하는 POWER() 함수, 제곱근을 출력하는 SQRT() 함수

SELECT POWER(3, 3), SQRT(4) FROM dual;

 

날짜 함수

데이터 중의 날짜 형식을 가지는 데이터를 계산하기 위해서 사용되는 날짜 함수
• 날짜(Date) + 숫자(Number) = 날짜에 숫자 이후의 날짜
• 날짜(Date) - 숫자(Number) = 날짜에 숫자 이전의 날짜
• 날짜(Date) + 날짜(Date) = 날짜에서 날짜를 더한 날짜
• 날짜(Date) – 날짜(Date) = 날짜에서 날짜를 뺀 날짜

 

SYSDATE

• 오라클이 설치된 시스템의 현재 날짜를 반환

SELECT SYSDATE, SYSDATE + 1, SYSDATE - 1 FROM dual;

 

MONTH_BETWEEN()

• 날짜와 날짜 사이의 개월 수를 계산

SELECT SYSDATE, hire_date, MONTHS_BETWEEN(SYSDATE, hire_date) FROM employees;

 

ADD_MONTH()

• 오라클이 설치된 시스템의 현재 날짜를 반환

SELECT hire_date, 
    ADD_MONTHS(hire_date, 2), ADD_MONTHS(hire_date, -2)
FROM employees;

 

NEXT_DAY() / LAST_DAY()

• 지정된 날짜부터 돌아오는 요일 날짜를 출력하는 NEXT_DAY() 함수
• 월의 마지막 날짜를 계산하는 LAST_DAY() 함수

SELECT hire_date, 
    NEXT_DAY(hire_date, 3), NEXT_DAY(hire_date, '수요일'), LAST_DAY(hire_date)
FROM employees;

 

ROUND() / TRUNC()

• 날짜를 연도나 월 단위로 반올림하는 ROUND() 함수
• 날짜를 연도나 월 단위로 절삭하는 TRUNC() 함수

SELECT hire_date,
    ROUND(hire_date, 'YEAR'), TRUNC(hire_date, 'MONTH')
FROM employees;

 

변환 함수

• 오라클에서 제공하는 데이터 타입을 필요에 따라 변환

 

자동(암묵적) 변환

SELECT 1 + '2'
FROM dual;

 

수동(명시적) 변환

 

 

날짜 지정 형식

 

시간 지정 형식

 

기타 형식

 

숫자 지정 형식

 

TO_CHAR()

숫자와 날짜 데이터를 문자 데이터로 변환하는 함수

SELECT TO_CHAR(SYSDATE, 'CC AD Q') FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'W DAY') FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'AM HH:MI:SS') FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS PM') FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'YY-MM-DD') FROM dual;

 

SELECT TO_CHAR(SYSDATE, 'MM"월" DD"일"') FROM dual;

 

SELECT TO_CHAR(salary, '9999999') FROM employees;

 

SELECT TO_CHAR(salary, '0999999') FROM employees;

 

SELECT TO_CHAR(salary, '$999999') FROM employees;

 

SELECT TO_CHAR(salary, 'L999999') FROM employees;

 

SELECT TO_CHAR(salary, '99999.99') FROM employees;

 

SELECT TO_CHAR(salary, '9,999,999') FROM employees;

 

TO_NUMBER()

숫자로 된 문자열을 숫자 타입으로 변환하는 함수

SELECT TO_NUMBER('123') FROM dual;

 

SELECT TO_NUMBER('123.123') FROM dual;

 

TO_DATE()

숫자로 된 문자열을 날짜 타입으로 변환하는 함수

SELECT TO_CHAR(TO_DATE('20210909'), 'YYMMDD') FROM dual;

 

일반 함수

NVL()

NULL 값을 특정한 값으로 치환하는 함수

SELECT department_name, NVL(manager_id, 100) FROM departments;

 

SELECT NVL(state_province, 'None') FROM locations ORDER BY state_province;

 

NVL2()

NULL 값인 경우와 아닌 경우를 구분하여 특정한 값으로 치환하는 함수

SELECT department_name,  NVL2(manager_id, '관리자 있음', '관리자 없음') FROM departments;

 

SELECT city, NVL2(state_province, '주소 있음', '주소 없음') FROM locations ORDER BY state_province;

 

DECODE()

데이터가 조건 값과 일치하면 치환 값을 출력하고, 일치하지 않으면 기본값을 출력하는 조건 논리 처리 함수

SELECT job_title, min_salary,
    DECODE(min_salary, 2500, min_salary * 1.1, min_salary)
FROM jobs;

 

SELECT job_title, max_salary,
    DECODE(max_salary, 40000, max_salary * 0.9, max_salary)
FROM jobs;

 

CASE()

복잡한 논리 조건 처리 함수

SELECT job_title, min_salary,
CASE
    WHEN min_salary < 4000 THEN min_salary * 1.2
    WHEN min_salary BETWEEN 4000 AND 6000 THEN min_salary * 1.1
    ELSE min_salary
    END AS 최소급여변경
FROM jobs;

 

RANK(), DENSE_RANK(), ROW_NUMBER() 함수

• RANK(): 공통 순위는 건너뛰어 다음 순위를 출력하는 순위 함수
• DENSE_RANK(): 공통 순위를 건너뛰지 않고, 다음 순위를 출력하는 순위
함수
• ROW_NUMBER(): 공통 순위 없이 순위를 출력하는 순위 함수

SELECT first_name, salary,
    RANK() OVER(ORDER BY salary DESC) RANK,
    DENSE_RANK() OVER(ORDER BY salary DESC) DENSE_RANK,
    ROW_NUMBER() OVER(ORDER BY salary DESC) ROW_NUMBER
FROM employees;

 

연습문제

--jobs 테이블에서 min_salary 값을 30으로 나눈 값의 올림값과 내림값을 조회
SELECT min_salary, CEIL(min_salary / 30), FLOOR(min_salary / 30) FROM jobs;

 

-- jobs 테이블에서 max_salary 값을 30으로 나눈 값을 소수점 둘째 자리에서 반올림한 값과 정수 첫째 자리에서 반올림한 값을 조회
SELECT max_salary, ROUND(max_salary / 30, 2), ROUND(min_salary / 30, -1) FROM jobs;

 

--jobs 테이블에서 max_salary 값을 30으로 나눈 값을 소수점 셋째 자리에서 절삭한 값과 정수 둘째 자리에서 절삭한 값을 조회
SELECT max_salary, TRUNC(max_salary / 30, 2), TRUNC(max_salary / 30, -2) FROM jobs;

 

--현재 날짜와 현재 날짜에서 한 달 뒤의 날짜를 조회
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 1) FROM dual;

 

--현재 날짜와 현재 날짜에서 돌아오는 월요일과 금요일의 날짜를 조회
SELECT SYSDATE, NEXT_DAY(SYSDATE, 2), NEXT_DAY(SYSDATE, 6) FROM dual;

 

--현재 날짜의 월/일과 요일을 조회
SELECT TO_CHAR(SYSDATE, 'MM/DD DAY') FROM dual;

 

-- 현재 시간의 오전 또는 오후 그리고 시:분을 조회'
SELECT TO_CHAR(SYSDATE, 'AM HH:MI') FROM dual;

 

--2021년 01월 01일의 요일을 조회
SELECT TO_CHAR(TO_DATE('20210101', 'YYMMDD'), 'DAY') FROM dual;

 

-- employees 테이블에서 salary, commission_pct, 그리고 salary에 commission_pct를 곱한 값을 salary에 반영하고,
--commission_pct 정렬 순으로 조회 (commission_pct가 null인 경우에는 salary 그대로 반영)
SELECT salary, commission_pct, salary + salary * NVL(commission_pct, 1)
FROM employees ORDER BY(commission_pct);

 

--employees 테이블에서 first_name, last_name, department_id,  salary 그리고 department_id가 50인 경우 salary를 10% 증가시킨
--값을 ‘급여인상’, department_id가 100인 경우 salary를 10%  감소시킨 값을 ‘급여감소’로 조회
SELECT first_name, last_name, department_id, salary,
    DECODE(department_id, 50, salary * 1.1, salary) AS 급여인상,
    DECODE(department_id, 100, salary * 0.9, salary) AS 급여감소
FROM employees;

 

--jobs 테이블에서 job_title에 Manager가 들어간 데이터 중에 job_title, max_salary, 그리고 max_salary가 20000 초과인 것은
--‘상위’, 10000에서 20000 사이인 것은 ‘중위’, 그 밖에는 ‘하위’로 표기하는 ‘급여등급’을 조회
SELECT job_title, max_salary,
CASE
    WHEN max_salary > 20000 THEN '상위'
    WHEN max_salary BETWEEN 20000 AND 10000 THEN '중위'
    ELSE '하위'
END AS 급여등급
FROM jobs WHERE job_title LIKE '%Manager%';