본 게시글은 이수안컴퓨터연구소의 데이터베이스 유튜브 동영상을 개인적으로 정리하는 글입니다.
숫자 함수
숫자 함수는 주로 숫자 계산과 추가 처리에 사용
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%';
댓글